Updating query criteria based on a form field

[Lochwood]

I am trying to use code that will change a static criteria value in a query depending on what is selected from a combo box value in a form.

I know i can have the criteria field looking directly at the form but this doesn't work when i am looping the query. The code below looks at the query1 then adds the criteria to query2 but it adds the actual forms location not the value from the combo.

Say the form combo box displays 100, i want this to be the static value in the criteria of query_2. hope this makes sense.

Private Sub Command243_Click()
Dim db As dao.Database
Set db = CurrentDb
Dim qdf As dao.QueryDef

Set qdf = db.QueryDefs("Query_2")
qdf.SQL = "Select * From [Query_1] WHERE [Job Number]=[Forms]![Personal_Details_Form]![Job_No_Combo]"


End Sub

 

[Ranman256]

you do not need ANY code, just run the query

docmd.openQuery "Query_2"
(or the macro version of it)

 

[Lochwood]

I have code that looks at query 2 and extracts all the email addresses into outlook. The code does not work when the criteria in query 2 reads [Forms]![Personal_Details_Form]![Job_No_Combo] it will only work if there is a static entry like say 100.