Cascading Combo boxes

[scheeps]

I'm working in an ADP project (for in case it might influence any feedback/suggestions being made)

I've got a simple Company combo box and depending on the selection will update the Contact combo box with all the contacts belonging to that company.

My code is as follow:

Private Sub cboCompany_AfterUpdate()
    
    Dim strSQL As String
    
    strSQL = "Select distinct Contact_Name from ODS.Contact "
    strSQL = strSQL & " where Company_ID = " & cboCompany & " "
    
    cboContact.RowSource = strSQL
    
    Me.cboContact.Requery
    
End Sub

But this piece of code just don't do anything. If I select a company (with contacts allocated to that company), the Contact combo just stays empty.

The Contact combo is bound to a field and should display the contact if one is already allocated, but also a list of possible contacts to that company in the combo box.

I'm sure it is something small, but I can't seem to identify my problem.

Hope you guys will be able to assist.

 

[JamesMcS]

Is the row source type set to table/query?

 

[JamesMcS]

Actually it might be something to do with cboCompany's bound column number. Is it set to the column number that company_ID is stored in?

 

[scheeps]

Actually it might be something to do with cboCompany's bound column number. Is it set to the column number that company_ID is stored in?

Yes it is James, the bound column number is set to 2, which is Company_ID.

Is that correct in your opinion? Would cboCompany be equal to bound column number 1 or 2?

 

[boblarson]

umm, based on the SQL Statement you posted there is only ONE field and that is Contact_Name. If you want to have the ID you also would need

    strSQL = "Select Distinct Contact_Name[B][COLOR=red], Company_ID[/COLOR][/B] from ODS.Contact "
    strSQL = strSQL & " Where Company_ID = " & cboCompany & " "

 

[scheeps]

umm, based on the SQL Statement you posted there is only ONE field and that is Contact_Name. If you want to have the ID you also would need

    strSQL = "Select Distinct Contact_Name[B][COLOR=red], Company_ID[/COLOR][/B] from ODS.Contact "
    strSQL = strSQL & " Where Company_ID = " & cboCompany & " "

Thanks Bob (and James)...it is working!

 

[JamesMcS]

Oh yeah, well spotted Bob! Glad it works now...