Combo not showing all results

[shabbaranks]

Hi All,

Ive got a strange issue with my DB. The below code looks up a company ID from the CompanyInfo_tbl and then looks up all records which have that ID and takes the first and last name and populated that within a combo box.

Private Sub Contact_Combo_GotFocus()
Dim strCompanyID As Integer
strCompanyID = DLookup("ID", "CompanyInfo_tbl", "[Company]='" & [CompanyNamelookup_txtbx] & "'")
Me.Contact_Combo = DLookup("[Firstname]&"" ""&[Surname]", "CompanyContact_tbl", "[CompanyID]=" & [strCompanyID])
    
End Sub

Whats happening is the combo box is being populated with one name but there are infact 2 names in the results -and if I add more the same thing happens it only shows the first result in the combo.

Any ideas please?

 

[pr2-eugin]

No, DLookUp is not how you set the values in the ComboBox.. You have to use the RowSource Property.. Also, your DLookUp is not how it should be.. You need two separate DLookUp's one for First Name and the other one for Last Name.. So your code should be..

Private Sub Contact_Combo_GotFocus()
    Dim lngCompanyID As Long
    lngCompanyID = Nz(DLookup("ID", "CompanyInfo_tbl", "[Company]='" & [CompanyNamelookup_txtbx] & "'"), 0)
    Me.Contact_Combo.RowSource = "SELECT [Firstname]& ' ' & [Surname] AS cName FROM CompanyContact_tbl WHERE [CompanyID]=" & [lngCompanyID]
End Sub

 

[shabbaranks]

Thanks Ive never really understood where the brackets should go and looking at your example above youve commented the start of the select agreement and parethsis (brackets) the end, shouldnt it end with "'" ?

 

[pr2-eugin]

OOPS !! I have edited the code, check again..