Query based on value of Combo Boxes

[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

 

[TheCandyman]

I made a quick insert query, a select would basically be the same. I guessed at your form names with "cmb1x1" and "cmb1x2".


Dim adoRst As ADODB.Recordset, adoCmd As ADODB.Command
Dim adoConn As ADODB.Connection, strSQL As String

Set adoRst = New ADODB.Recordset
Set adoCmd = New ADODB.Command
Set adoConn = CurrentProject.Connection

strSQL = "Insert Into |your table| VALUES ('" & Me.cmb1x1 & "'), ('" & Me.cmb1x2 & "')""
adoConn.BeginTrans
With adoCmd
.ActiveConnection = adoConn
.CommandText = strSQL
.CommandType = adCmdText
.Execute , , adExecuteNoRecords
End With
adoConn.CommitTrans

Set adoRst = Nothing
Set adoCmd = Nothing
Set adoConn = Nothing

 

[dhookom]

You might have to build the entire SQL on the fly. Are the records from this query feeding a report or a form?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[lskuff]

I guess it will be feeding a report. I have not made it yet. It is going to be a bunch of mailing labels.

 

[dhookom]

If the query feeds a report then you can use your two combo boxes to build the Where clause in the OpenReport method.
Dim strWhere as String
'if the field is numeric
strWhere = "[" & Me.[cmbFieldName] & "]=" & Me.[cmbValue]
DoCmd.OpenReport "rptLabels",acPreview, , strWhere

The the field is text or date, you will need to add in delimiters of quotes or "#".


Duane
MS Access MVP
Find out how to get great answers faq219-2884.