Query based on multiple combo box values

[lskuff]

I am working on a form that has 2 combo boxes. The first in the first box the user selects a field from a table and then the second box the user selects one of the values in the field.

I need to write a query to get the results of what the user selected. I have something like this:

SELECT [Attendee].[FIRST_NAME], [Attendee].[LAST_NAME], [Attendee].[STREET], [Attendee].[CITY], [Attendee].[State], [Attendee].[ZIP]
FROM Attendee
WHERE
[Attendee].[FORMS]![MyForm].[cmb1x1]=[FORMS]![MyForm].[cmb1x2]

ORDER BY [Attendee].[LAST_NAME];

but of course this is not work. I know
[FORMS]![MyForm].[cmb1x2] part works because if I go

Attendee.Job_DESC = [FORMS]![MyForm].[cmb1x2] then the query works. So how do I get this to work? I hope I explained this clearly enough. Thanks

 

[scriverb]

You need to modify the SQL iteself with in the query. Just update the actual query name:

Dim db as DAO.Database
Set db = CurrentDB
db.QueryDefs("[highlight]qryYourQueryName[/highlight]").SQL = Mid$( db.QueryDefs("[highlight]qryYourQueryName[/highlight]").SQL,1,Instr(1,db.QueryDefs("[highlight]qryYourQueryName[/highlight]").SQL,"WHERE") + 5) & "Attendee." & [FORMS]![MyForm].[cmb1x1] & " = " & [FORMS]![MyForm].[cmb1x2] & " ORDER BY [Attendee].[LAST_NAME];"
DoCmd.OpenQuery "[highlight]qryYourQueryName[/highlight]"
db.close

Start your query off with everything hardcoded but make sure that there is a WHERE clause as I am keying off of that word to select the SQL prior to that and then updating everything after that word.

Post back if you have any questions.




[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[lskuff]

Cool, thank you! I just have one question. What is the + 5 for right after the WHERE ?

 

[scriverb]

I am identifying the number of characters from the entire SQL string to start with. The third parameter of the Mid$ as youcan see is the Instr function. The Mid$ function starts at character 1 and continues until the position in the string of the value of this third parameter. So, the Instr function finds the "WHERE " statement which is actually the position of the "W". We want to select and save the SQL code from character 1 through the space after the WHERE which is the reason for the +5.

Hope that isn't confushing.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]