Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    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

  3. #3
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Ken for the quick reply. That worked perfectly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •