Populating a combo box from a query - Run-time Error 3016

[cmb]

Hi There,



I really hope someone can help me with this. I am trying to populate a combo box (cboVenderID) with one of the two queries below. But "Set rs = db.OpenRecordset(strSQL)" causes a Run-time Error '3016': To few parameters. Expected 1.

I am new to VBA and SQL so any help would be really appreciated!


Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intVenNum As Integer

intVenNum = Me.cboVenderTypeID

If intVenNum = 4 Then

strSQL = "SELECT tblVenders.VenderID, tblVenders.VenderFullName, tblVenders.VenderTypeID " & _
"FROM tblNodes " & _
"INNER JOIN tblVenders " & _
"ON tblNodes.NodeID = tblVenders.NodeID " & _
"WHERE (((tblVenders.VenderTypeID)=[Form]![cboVenderTypeID])) " & _
"ORDER BY tblVenders.VenderFullName;"

Else

strSQL = "SELECT tblVenders.VenderID, tblVenders.VenderFullName, tblVenders.VenderTypeID, tblNodes.NodeID " & _
"FROM tblNodes " & _
"INNER JOIN tblVenders " & _
"ON tblNodes.NodeID = tblVenders.NodeID " & _
"WHERE (((tblVenders.VenderTypeID)=[Form]![cboVenderTypeID]) " & _
"AND ((tblNodes.NodeID)=[Forms]![frmOrdersWithSites]![OrderNode])) " & _
"ORDER BY tblVenders.VenderFullName;"

End If

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

Me!cboVenderID.RowSource = rs
DoCmd.RunCommand acCmdSaveRecord
Me!cboVenderID.Requery

[June7]

Can't set a combobox list with a recordset object. What you need to do is set the combobox RowSource property with an SQL statement.

If Me.cboVenderTypeID = 4 Then
Me.cboVenderID.RowSource = the first SQL statement
Else
Me.cboVendorID.RowSource = the second SQL statement
End If

Also, variables should not be enclosed in quote marks - concatenate variables. Reference to form controls/fields is a variable. So probably need to concatenate the references to form:

"WHERE tblVenders.VenderTypeID=" & [Form]![cboVenderTypeID] & _

[cmb]

Thanks June7 your help allowed me to get it working!