Can a Combo Box be made to run a SQL statement?

[shelds]

I've created a form with a combo box which finds records based on the selection in the normal wizard way, which works fine and does the job to an extent. I would like to add extra functionaliy to this. Could the combo box also run a query so it only shows values that are the result of the query?

For example, I have 2 tables which are linked in a one to many relationship. I want to query tbl.checktype and display all checknames (field in this table) in the combo box, so it shows all checks with that checkname. This it does successfully using the standard find records in database and display feature in combo box wizard. But i would like it to query on checknames where the team (also a field in tbl.checktype) ="helpdesk", then shows those values in its dropdown (not all checknames in the table as it does previously).

I created a SQL query to display the checknames belonging to the helpdesk team but how can this be added to the combox box feature on the form so the combo box displays the query results in the dropdown, and does its usual job and displays records matching the checkname on the form. Hope this makes sense and you can help me solve this frustrating issue. Any queries please ask.

 

[PHV]

Modify the RowSource property of the combo with the proper SQL code ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shelds]

Please could you advise if I am on the right lines here, I've modified Access' generated code when it creates a combo box via the wizard. Not to brill at VB at the moment I'm returning to it after over a year. Should the only event needed be on After update as below? Thanks for your advice it is slowly starting to trigger some memory.

Private Sub Combo8_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = "SELECT [tblchecktype].[Check Name]FROM tblchecktype WHERE [tblchecktype].[Team] = 'helpdesk'"
    rs.FindFirst "[Typeid] = " & Str(Me![Combo8])
    Me.Bookmark = rs.Bookmark
End Sub