jtovar3
asked on
can I filter a subform table with combo boxes?
Hello experts!
I have a table on my subform that I would like to filter based on combo boxes in the same subform. I have a table that is in datasheet view that shows a bunch of items and i want to be able to filter it based on criteria i select in a combo box. For example, I have a combo box where I can choose obs2, and I want the table to show me the items that are associated with obs2.
thanks!
I have a table on my subform that I would like to filter based on combo boxes in the same subform. I have a table that is in datasheet view that shows a bunch of items and i want to be able to filter it based on criteria i select in a combo box. For example, I have a combo box where I can choose obs2, and I want the table to show me the items that are associated with obs2.
thanks!
You can create a query for the subform instead of a table:
SELECT * FROM myTable WHERE obs Like " & Me!obs2 & "*" OR <the rest of the fields formatted the same>;
Now if nothing is entered, you will get all the fields - if something is entered in one or more of the controls, you will filter the subform accordingly
SELECT * FROM myTable WHERE obs Like " & Me!obs2 & "*" OR <the rest of the fields formatted the same>;
Now if nothing is entered, you will get all the fields - if something is entered in one or more of the controls, you will filter the subform accordingly
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I love your suggestions, however, I don't know where to implement these. should i have the entire subform selected and change the source object? or do i go into the form and change the control sources. I've included a screenshot of what i am working with.
i would like the recommendations subform to filter based on the observation ID selected by the combo box above. I figure that i should put one of your suggestions in the after update of the combobox, but how do i reference the table below?
thanks!
i would like the recommendations subform to filter based on the observation ID selected by the combo box above. I figure that i should put one of your suggestions in the after update of the combobox, but how do i reference the table below?
thanks!
if the subform-control is named "child1" and the combobox named combox1 then...
yes, on combobox1 After Update do one of following choices:
make a complete select-statemenet based on the value of combobox1
and next step: use the Select-statement as the source for the subform
this gets the subform loaded without having to load all records (that access has a bad tendency to do).
the Filter will be just the same, except not setting the .RecordSource but .Filter and the value will be only the "WHERE something=" & combobox1.value
yes, on combobox1 After Update do one of following choices:
make a complete select-statemenet based on the value of combobox1
Select case combobox1.value
case 1
sql=" Select something From tableA WHERE something = whatever
end select
or add the combobox1-value to a Select-statementsql=" Select something From tableA WHERE something =" & combobox1.value
and next step: use the Select-statement as the source for the subform
subformCtl.Form.RecordSource = sql
subformCtl.Form.refresh
the .refresh may not be necessary...this gets the subform loaded without having to load all records (that access has a bad tendency to do).
the Filter will be just the same, except not setting the .RecordSource but .Filter and the value will be only the "WHERE something=" & combobox1.value
Can you post an example of your DB. That way we can get all your control names and provide you with accurate code to produce your desired effect.
TLH
TLH
ASKER
Hi TLH,
Here is a sample of the database, in the combo subform, I would like to use the Observation ID combo box (ObsCombo) to update the Recommendation Table to only display the recommendations for a given observation ID.
Thanks!
AuditTrackerVersionTest.accdb
Here is a sample of the database, in the combo subform, I would like to use the Observation ID combo box (ObsCombo) to update the Recommendation Table to only display the recommendations for a given observation ID.
Thanks!
AuditTrackerVersionTest.accdb
The combo boxes should not be in the subform. Put them in the main form. Combo boxes in the subform would be used to change the value of a field in the current record. See my Fancy Filters sample database for an illustration of filtering a suform by values selected in combo boxes in the main form. Here is a link for downloading it:
http://www.helenfeddema.com/Files/accarch129.zip
And here is a screen shot of the form:
Fancy-Filters-Form.jpg
http://www.helenfeddema.com/Files/accarch129.zip
And here is a screen shot of the form:
Fancy-Filters-Form.jpg
ASKER
Wow Helen, your Fancy Filters Form is amazing, but I think it is much more than what I am trying to do. I would just simply like to sort the subform after the ObsID combo box is selected.
I just don't know what to in order to set the filter for the subform table and whether to put something in the afterupdate of the obsID Combo box.
I just don't know what to in order to set the filter for the subform table and whether to put something in the afterupdate of the obsID Combo box.
in the combobox add an event, OnChange or AfterUpdate for exemple, and make the code in the style of:
Open in new window