Is there a way to remove the subform fields (Datasheet) depending on another combo box selection.
So if combobox at top = "FieldsSelection1" then the subform displayed below (as datasheet) only shows the appropriate fields??
Whats the easier way to do this to link the fields to the combobox at the top of the form?
I have attempted to use the .visible = true or false property but this doesn't change anything on the datasheet subform?
i believe i know what you mean, but surely the best way around this is just to create seperate queries for each selection (which I have done) and link the different queries as the record source for the subform?
I have done this but would the code still stay the same as above, as it's not doing what it should?!
(*Type is linked to Combobox selection box)
So when I select Risk Assessment it shows all the Risk Assessments using only the fields selected above, and the same for Eye test, etc..
Understand what I need it to do better?
P.S. I can do this using a normal form and using IF statements but I can't seem to work it out on a Subform Datasheet.
I have tried using say an IF statement to make visible the appropriate fields but that didn't work, and now I'm onto the idea that I should just using an IF statement to change the recordsource of the subform, that way I can easily add new types and change criteria using the appropriate queries.
Dim SQL As String
If me.MyCombobox.Value = "Risk Assessment" Then
SQL = "ID, Name, Type, Date, AssessRisks, AssessSolutions, AssessResponsible, Completed FROM [Risk Assesment]"
ElseIf me.MyCombobox.Value = "Eye Test" Then
SQL = "ID, Name, Type, Date, EyeGlasses, Completed FROM [Eye Test]"
MsgBox "Oh dear, something's not right"
me.MySubForm.RecordSource = SQL
Try that - this is not tidy code, but it should give you a better idea.
I don't think this will work with a bound subform.
Can you possibly post a VERY cutdown version of your app...
The form taht we are dealing with and it's relative tables.
remove any sensitive data before posting.