Combo Box in Query

[j3andc]

I am struggling trying to utilize a combo box to generate a query. Is it possible? and if so, what do I need to do to make it work?

Jim

 

[Joe4]

OK, for simplicity sake, let's say we have a table named "MyTable", and it has a field named "Amount", which is the field we want to use the combo box on to select records via a query.

1. In the VB Editor, go to Tools | References and select the reference to "Microsoft DAO x.y Object Library" where x.y is some version number.

2. Create a query where you select all the fields you want to display and do any temporary filtering out of certain records. Name this query "MyQuery".

3. Create a form with a combo box (named "cboAmount"), that is linked to your amount field, and add a command button and name it "cmdProcess".

In the VB editor, we are going to enter SQL code to create a query to run. The easiest way to do this is to create a "temporary" query selecting what you want to select, go to SQL view and steal the code you need. So:

4. Create another query named "MyNewQuery". Add the "MyQuery" query to the query, and click on the "*" to add all fields. Now select the "Amount" field and place criteria on it.

5. Go to SQL view of the query and copy or print the SQL code. This is the format of the SQL code we need to build.

6. Go back to our form and go to the Properties of the "cmdProcess" button, go to the Event tab, go to the "On Click" event, and select "Build Code". This should open up the VB editor.

7. Using the code we have copied, we are going to build SQL code to run the query we want, except we are going to replace our hard-coded criteria with the value found in the Combo Box ("cboAmount"). The code will look something like this:

Private Sub cmdProcess_Click()
    Dim strSQL As String
    strSQL = "SELECT [MyQuery].* FROM [MyQuery] WHERE ((MyTable.Amount=" & cboAmount & "));"
    CurrentDb.QueryDefs("MyNewQuery").SQL = strSQL
End Sub

The second line builds our SQL code, and the third line runs it. Here I am electing to write the new code to the "MyNewQuery" query we were using to view/steal the code.

8. Now, save the code and test it out by selecting a value from the combo box and clicking on the Process command button. If you view "MyNewQuery", you will see the results.

Of course, you may want to add validation and error handling, but this describes the process and should get you started.