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

[Tripodkid66]

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 quite 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 ​

 

[bob fitz]

Try:

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)=" & Me.[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)=" & Me.[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

 

[Tripodkid66]

Thanks your help Bob it's now working perfectly ! I also had to change
Me!cboVenderID.RowSource = rs to Me!cboVenderID.RowSource = strSQL

 

[Tripodkid66]

Well I thought I had it working! The combo boxes now populate correctly but when I make a selection it updates all the records in the subform. Thanks in advance for your help!

Private Sub cboVenderTypeID_AfterUpdate()

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

If Me.cboVenderTypeID = 4 Then

strSQL = "SELECT tblVenders.VenderID, tblVenders.VenderFullName, tblVenders.VenderTypeID " & _
"FROM tblNodes " & _
"INNER JOIN tblVenders " & _
"ON tblNodes.NodeID = tblVenders.NodeID " & _
"WHERE (((tblVenders.VenderTypeID)=" & Me.[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)=" & Me.[cboVenderTypeID] & ") " & _
"AND ((tblNodes.NodeID)=" & [Forms]![frmNavigation]![NavigationSubform]![OrderNode] & ")) " & _
"ORDER BY tblVenders.VenderFullName;"

End If

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

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

End Sub

 

[bob fitz]

Can you post a copy of your db