Combo box to pass criteria to Query (1 Viewer)

[mumbles10]

Okay... I have 3 comboboxes... cmb1, cmb2, cmb3.

cmb1 is a field list of tblInfo. cmb3 is the values of the cmb1 selection. So say lastname is chosen in cmb1, cmb 3 displays all the lastnames from the tblInfo. cmb2 is a value list with "=" and "<>".

My question is, how do I put these together to create a query? I am stumped.

Thanks.

 

[jdraw]

What exactly do you want in the "derived query"?
If Lastname <>???

 

[mumbles10]

What exactly do you want in the "derived query"?
If Lastname <>???

Yeah... except whenever the user creates the choices based on the 3 combos... I want the query to populate. I imagine I have to build out the SQL Where statement in VBA but having issues...

Sub CreateQuery()
On Error GoTo Err_CreateQuery
Dim db As DAO.Database
Dim qdf As QueryDef
Dim simSQL As String
Dim strOderBy As String
Set db = CurrentDb
'*** create the query based on the information on the form
simSQL = "SELECT * FROM [tblInfoForAudit]" & " WHERE "
simSQL = simSQL & Me!Combo0 & " " & Me![Combo4] & " " & Me![Combo2] & " "
MsgBox simSQL
'*** delete the previous query
db.QueryDefs.Delete "qMyQuery"
Set qdf = db.CreateQueryDef("qMyQuery", simSQL)
Exit_CreateQuery:
    Exit Sub
    
Err_CreateQuery:
    If Err.Number = 3265 Then   '*** if the error is the query is missing
        Resume Next             '*** then skip the delete line and resume on the next line
    Else
        MsgBox Err.Description  '*** write out the error and exit the sub
        Resume Exit_CreateQuery
    End If
    
End Sub

This is what I was testing out... but not working. It says I have a SYNTAX ERROR then deletes the query without building a new one.

 

[jdraw]

What fields are in tblInfoForAudit?

What exactly is put in each combo?

How were you running this procedure? Was it in one of the Forms events?

 

[mumbles10]

What fields are in tblInfoForAudit?

What exactly is put in each combo?

How were you running this procedure? Was it in one of the Forms events?

Combo0 = is the FieldNames for tblInfoForAudit.
Combo2 gets the rowsource based on the selection of Combo0. It shows the values of the selection. So if the Fieldname is LastName, it shows all the lastnames.
Combo4 is the expression, either "=" or "<>"

I was trying to do this from the OnClick of a Command Button.

 

[jdraw]

Try this

I attached a version based on some assumptions.
Post back as necessary.

 

[mumbles10]

Try this

I attached a version based on some assumptions.
Post back as necessary.

That's pretty much exactly what I need it to do.

So I have to create the module to find the values? Or did you do that just for the sake of the example?

Also, I got an error when I clicked on the "Document My Tables" button.

Thanks JDraw...

 

[jdraw]

I looked at the Form and removed and rebuilt the button's On Click event. I have no idea what was causing the issue. I did modify the On error stuff , but I only deleted and moved lines. I guess there was some punctuation left, anyway it cleared and works as intended. This is another copy with same name, so please replace the previous one.

Not sure what you're asking. Everything to Create the query is there. It should do any field and operators <,>, <>, =

It builds the query -- it DOES NOT EXECUTE the query , as it stands.

Note: My code requires the use of the data dictionary , SO YOU MUST RUN THE Document My Tables BEFORE ANYTHING ELSE.

Post or PM with questions.

If you can't sort out the Document My Tables stuff, let me know.