Combo box query to post result to a seperate query

[stevefishwickuk]

Hello, I have a combo box on a form which lists some names generated from a table.

I would like the selected name to be inputted into the 'critera' of another query called 'qryPBCustLevel' and for that query to be run.

I have tried to code this, but it is crashing at the point it trys to add the name into the query.

Can anyone help? Code listed below.

Sub cmbPB_AfterUpdate()

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the Query definition

strSQL = "SELECT DISTINCT tblTempPB.PB_NAME" & _
"FROM tblTempPB"

strOrder = "tblTempPB.PB_NAME;"

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[PB_NAME] = '" & Me![cmbPB] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qryPBCustLevel")
qryDef.SQL = strSQL & " " & strOrder

'Open the Query
DoCmd.OpenQuery "qryPBCustLevel", acViewNormal

End Sub

Thanks, Steve.

 

[macca the hacke]

Why don't you reference the combo box from the form into the query - ie have the criteria of the field in the query set to forms!frmName!cmbName (where frmname is the name of the form, and cmbname is the name of the combo box). Only drawback is that form will have to be open when query is run.

 

[stevefishwickuk]

Worked a treat thanks.