Full Version: Filter a subform from the form
UtterAccess Forums > Microsoft® Access > Access Forms
I have been trying multiple things to get this to work and haven't had any luck yet. I have a form with 3 combo boxes and a text box, and a subform that shows a query in datasheet view. It currently is set to where you can fill out all 4 boxes in the form, and press search and results matching that criteria will populate. I would like to be able to set it to where you only have to fill out 1 or more boxes to get matching criteria. I have been trying to use filters and also trying to alter the query to get this to work.
To clarify some more, the boxes on the form are:
Right now you have to use all four boxes to get results. I would like it to begin filtering/searching after the first box is selected, such as displaying all people on 1st shift, then if you wanted to narrow it down and choose an Area too.
Any help would be greatly appreciated.
Well, I do this kind of thing inside the query, so I would do it like this:
On an otherwise unused Field cell in the query, put a reference to say the Shift combo box. In the Criteria cell of that combo box put Is Not Null. In otherwise unused Field cells, put references to the other form controls. In each criteria cell of those columns, put Is Null. In the criteria cell of the Shift column, put a reference to the Shift combo box. All of this will need to be on the same row. This will give you a criteria phrase something like this:
WHERE TableName.Shift = [Forms]![NameOfForm]![NameOfComboBox] AND [Forms]![NameOfForm]!NameOfComboBox] Is Not Null AND the other relevant controls are NULL.
Then in the After Update event of the Shift combo box, requery the subform.
Similarly for the other controls, you can also mix and match as you want by having any two filled in, the others null, any three filled in, the other null, etc.
There are a large number of combinations that you can put. I haven't found a limit on how long the SQL can be yet and I have used this technique with several list boxes, combo boxes, and text boxes in a bewildering variety of combinations.
Ok, Thanks for the help. That is getting me closer. thumbup.gif
It works just as I expected for when you have one field set to filter. But when I had it set to where two fields are used, it needed both filled in to find any results, which I assume is what it was supposed to do. But I would really like to be able to choose either Shift OR Position OR Team OR Area OR any combination of the previous. Is that possible?
Yes, just as for any other OR, just put the conditions on different rows.
o if you wanted Shift = 1 OR Team = 3
just put the values on different rows.
I'm still not quite getting this where I would like it. Let me try to rephrase.
With the 4 combo boxes, I would like to search by any combination of the 4 boxes.
So say I select "1st" shift, it will show all of first shift.
FOr if I select "Associate" in the Position field, it will show all associates.
But then also, I would like to be able to select "1st" shift, AND "Associate," and then it will only show the Associates that are on 1st shift.
Or I want to select "1st" Shift, Position of "Office", and Area/Line of "HR." It would only show HR people on 1st Shift.
And so on with any combination? Any more suggestions?
Thanks a lot
Yes, I understand what you want to do---that is not the problem.
I have a simple example at work. I'll post it tomorrow if you still need it.
As promised, an example of how I do this is attached. Query1 does most of the work.
Thanks a lot for the help. I'm probably just not getting it right on my end. I know a fair amount about access, but am not completely fluent in everything it does. I'll check out the attachment and see what I can come up with.
Thanks a bunch.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.