Adding Data to a Multi-Column Combobox

[Steve R.]

The code below works, but first grumble, grumble. Access "HELP" was not all that informative and lacked a usable example. So it took a bit of experimenting to solve. Basically you need to build the string as shown below to add data to a multicolumn combobox.

 strItem = RMS!RepsNameID & ";" & RMS!RepsName

Combobox10 is used for a query and the code below "skips" adding a name to the combobox if the person has not completed an inspection.

Private Sub BuildRepList()
    Dim DBS As dao.Database
    Dim RMS As dao.Recordset
    Dim lonRecordCount As Long
    Dim strItem As String
    Set DBS = CurrentDb
    Set RMS = DBS.OpenRecordset("RepsList")
    RMS.MoveFirst  
    Me.Combo10.RowSourceType = "Value List"
    Me.Combo10.ColumnHeads = False
    Do Until RMS.EOF
        lonRecordCount = DCount("[Representative]", "SiteInspRptQry", "[Representative] LIKE '*" & RMS!RepsName & "*'")
        If lonRecordCount > 0 Then
            strItem = RMS!RepsNameID & ";" & RMS!RepsName
            Me.Combo10.AddItem Item:=strItem
            End If
        RMS.MoveNext
        Loop
    RMS.Close
    Set RMS = Nothing
End Sub

 

[wiklendt]

i think you might have done this the long/complex way around... what exactly are you wanting to have access do for you...? i can't make sense of the logic flow in that code...

if you're using a combobox to select a representative, but want to add a new one that isn't there, you could just use the "not in list" event, and once the new name goes in, access would normally create and ID for you (assuming you've made your PK ID an autonumber)

 

[Steve R.]

i think you might have done this the long/complex way around... what exactly are you wanting to have access do for you...? i can't make sense of the logic flow in that code...

if you're using a combobox to select a representative, but want to add a new one that isn't there, you could just use the "not in list" event, and once the new name goes in, access would normally create and ID for you (assuming you've made your PK ID an autonumber)

No, I am not adding a representative. The combobox is used to define a filter for a subform. The subform displays all the inspections made by the person selected in the combobox. Thanks for looking and examining the code. Any assistance at improving it would be helpful.

Here is what is happening, I have a list of people that is contained in a table. Some of those people perform inspections and others don't. Originally, I populated the combobox using SQL. When the combobox was clicked, I had a test to see if the selected person had performed any inspections, if not a pop-up message appeared. Needless to say, that resulted in a lot of "bad" clicks. So there had to be a better way.

I then came-up with the concept of building the combobox (one field only) with only those names that had performed an inspection. That made the drop down list a lot shorter and eliminated clicking on a name that had not performed any inspections. A this point I could have quit. However ....

After getting that to work, I wanted to experiment with two fields since one field was the person's unique ID number. That's were I ran into "trouble". Access HELP wasn't much help. A search of this forum did not seem to suggest a solution either. So after finally figuring it out, I posted here to share.

 

[DCrake]

If you had first created a query that included both the inspection table and the inspectors, assuming that the inspection table holds the inspectors ID and not their name you could then link the two tables together. Then group by inspector and inspectorID

Now save the query as QryJobsByInspector

Then on your form use this query as your rowsource for the combo box.

Make the 2nd column the bound column (InspectorID)

Simple as that.

David

 

[Steve R.]

Good suggestion. Still learning. Always more than one approach to the solution. That's what makes this forum so valuable.

 

[Cameo]

To Steve R.
Thank you so much for posting the very simple answer to how to code entries into a multi-column combobox. I spent an hour trying to find this answer. Why MS help doesn't just add {;value} onto their AddItem help is a mystery.
Everyone got too complex just for this answer.
Thanks very much.

 

[boblarson]

To Steve R.
Thank you so much for posting the very simple answer to how to code entries into a multi-column combobox. I spent an hour trying to find this answer. Why MS help doesn't just add {;value} onto their AddItem help is a mystery.
Everyone got too complex just for this answer.
Thanks very much.

Cameo -

For 99% of the time, in Access you use a QUERY and not iterating through code to add items to a combo. So, perhaps that is why it is not included.