Query from a Form

[rachel_harder]

Hello,
I have a form with drop down menus that I use to run a query. On the form I have 4 different drop down menus. I am wondering if there is a way to refine the query if more menus are filled in but if one is left blank then the query will still work. Right now the only way I have figured out how to make the query work is if everything is an "or" statement and only one menu can be filled out at a time. I have so many records that I need a better way to run queries so the record I am searching for can be found with ease. Thanks for any help.

 

[plog]

You are going to have to do something more advanced than your current set up. You will need VBA of some sort to construct and apply a filter:

https://msdn.microsoft.com/en-us/library/office/ff197651.aspx

Basically, the user clicks the button, the VBA creates a string that implements the criteria they have chosen (and excludes the criteria not chosen). Then it opens the query and applies that string via the ApplyFilter method.

 

[rachel_harder]

What would an example of VBA look like. The names of the fields I am working with are "QC Inspector" "Division" "Type of Inspection" and "Bus Number" Thank you for your help.

 

[plog]

Did you check the link?

The following example uses the ApplyFilter method to display only records that contain the name "King" in the LastName field:

DoCmd.ApplyFilter , "LastName = 'King'"

 

[rachel_harder]

That example does not really help me because it will only pull up records with the last name of King in the last name field. But in my fields I have a possibility of 17 different options that could be selected per the drop down menus. In the shown example you only have one option you can choose. Would I need to write out every option that can be picked in the VBA? If so how would that look?

 

[plog]

This VBA would open YourTableNameHere to just the records that match the value in the drop down named "DivisionDropDown", if it is being used.

str_Table = "YourTableNameHere"
    ' name of table to open and filter
    
str_Filter = "(1=1)"
    ' filter to be used when opening table, default will show all values

If (IsNull(Me.DivisionDropDown) = False) Then str_Filter = str_Filter & " AND ([Division] ='" & Me.DivisionDropDown & "')"
    ' if using Division drop down, uses its value in filter

    ' add more filter building logic here

DoCmd.OpenTable str_Table, acViewNormal, acEdit
DoCmd.ApplyFilter , str_Filter
    ' opens table and applies filter

In the above code, you need to replace "YourTableNameHere" with the name of your table and add more filter building statements in the space I designated (' add more filter building logic here).

 

[rachel_harder]

Thank you for all your help!