Combo Box Display

[Guest]

Hello,

I have two related combo boxes named "cboIssuedTo" and "cboContact", both
pulling data from table "Contacts". As of now, I have only been able to
poplate cboContact with one item based on ContactID. I would like to
populate cboContact with multiple names, from the same company. My attempts
pretty much look like this:

Private Sub cboIssuedTo_AfterUpdate()

Me.cboContact.RowSource = "SELECT FirstName & "" "" & LastName FROM" & _
" Contacts WHERE CompanyName = " & Me.cboIssuedTo & _
" ORDER BY CompanyName"
Me.cboContact = Me.cboContact.ItemData(0)

The above does not put any data in cboContact. Any help wold be greatly
appreciated.

TIA

 

[Rick Brandt]

Hello,

I have two related combo boxes named "cboIssuedTo" and "cboContact",
both pulling data from table "Contacts". As of now, I have only been
able to poplate cboContact with one item based on ContactID. I would
like to populate cboContact with multiple names, from the same
company. My attempts pretty much look like this:

Private Sub cboIssuedTo_AfterUpdate()

Me.cboContact.RowSource = "SELECT FirstName & "" "" & LastName FROM"
& _ " Contacts WHERE CompanyName = " & Me.cboIssuedTo & _
" ORDER BY CompanyName"
Me.cboContact = Me.cboContact.ItemData(0)

The above does not put any data in cboContact. Any help wold be
greatly appreciated.

TIA

You might need to issue a Requery on the ComboBox after changing its
RowSource...

Me.cboContact.Requery

 

[John Spencer]

I suspect a problem in the SQL you are building. I think that companyName
is text. If so, try

Me.cboContact.RowSource = "SELECT FirstName & "" "" & LastName FROM" & _
" Contacts WHERE CompanyName = " & Chr(34) & Me.cboIssuedTo & Chr(34) & _
" ORDER BY CompanyName"

I found that setting the row source to an invalid SQL statement fails
silently and simply returns no matches. During development I often use a
text variable to build the SQL statement so I use debug.print to examine it
for syntax.

Dim strSource as String

StrSource = "SELECT FirstName & "" "" & LastName FROM" & _
" Contacts WHERE CompanyName = " & Chr(34) & Me.cboIssuedTo & Chr(34) & _
" ORDER BY CompanyName"

Debug.Print strSource
Me.CboContact.RowSource = strSource