What would be the best way to filter a query in a subform? I want to use a combo box or a check box to filter the results. I am trying to make this easier than using the filter in the toolbar... need to make it simple for the pc noobs.
Start by creating a query that selects the fields of interest and includes a sample set of criteria.
Then, while still in the Query design view, Select the SQL View Option from the Menu's View Choice . This will show you the SQL syntax that includes the WHERE clause, the filter you wish to apply. This SQL statement is the .sql property for the query you are designing.
You can recreate this statement as a text string in VB, swapping out the portions of the "WHERE" clause that you wish to change. Also, you can use the MsgBox function to look at the proposed SQL text, before you actually attempt to run the revised query.
Once you are happy with how the proposed text reads, load your existing query's SQL property with the new text and call the RunQuery method of DoCmd. For example (assuming you have a query named "SimpleQuery" and a Textbox named "TextState"):
Currentdb.QueryDefs("simplequery").sql="Select * from Company Where Co_State = '" & Me.TextState.Text & "';"
Just remember to include any string values that are being passed within single quotation marks. Look carefully at the use of single quotes within double quotes in the above code snippet.
Once you have revised the initial query, then include the code:
This will cause the initial query to execute.
By the way, once you figure out how to rebuild SQL statements on the fly, you can use the revised Statements in a HUGE number of ways including creating temporary tables, revising a query that is used by a report, or re-populating a List Box or Combo Box.