Populating a multi-column combo box

Topic

New Reply

Hi,

I have multiple combo boxes on a form. Depending on what a user selects in one combo box, I’d like the next combo box to populate with certain values. Eg, when a user selects a certain city/state, I would like another combo box to be populated with ‘clients’ that only exists in that city or state. Here is some sample code that I have:

Set ThisDB = CurrentDb
StateText = Me.State

SQLString = “SELECT tblClient.Surname, tblClient.FirstName, tblClient.State” & _
” FROM tblClient INNER JOIN tblState ON tblClient.State = tblState.StateID” & _
” WHERE tblState.StateID =” + StateText + “;”

Set ClientRecords = ThisDB.OpenRecordset(SQLString)

ClientRecords.MoveLast
ClientCount = ClientRecords.RecordCount
ClientRecords.MoveFirst

If ClientCount = 0 Then
MsgBox “There are no clients listed under that state”
Exit Sub
End If

Do Until ClientRecords.EOF
Me.ctrlClient.AddItem ‘???
Loop

As you can see, I can look up the list of users based on what state the user has selected. I’d then like to add the clients surname and first name to one row in the ‘ctrlClient’ combo box, looping through all clients until it is populated. I can add an item, but I assume this adds one particular field to one column in the combo box – how do I go about adding other fields to other columns?

Cheers,
Jason

Reply | Quote

Replies

Personally, I’d just set the row source of the second combo in the after update of the first combo rather than looking through the recordset and using AddItem. Either that or just use a reference to the first combo in the rowsource you create at design time.

If you have the second setup with three columns then the following should work (note – as StateText is a string you need to surround it with double quotes. Correction, now that I see you’ve used an ID for the state, you can forget the double quotes, sorry but a more appropriate name for the variable would be lngState as it’s a long integer.

Code:

SQLString = "SELECT tblClient.Surname, tblClient.FirstName, tblClient.State" & _
" FROM tblClient INNER JOIN tblState ON tblClient.State = tblState.StateID" & _
" WHERE tblState.StateID =" & lngState
Me.ctrlClient.RowSource = strSQL

and no, you don’t need the “;” on the end

Reply | Quote

Thanks Ken for the quick reply. That worked perfectly.

Reply | Quote