Populating combobox with SQL query prevents setting the value found in bound column

A activex combobox on a userform has been working with two columns, with the first column showing an item name and the second displaying the item code. The second column is set as bound and the item code is used elsewhere in the form. I populated it by setting the .RowSource = tablerange and the combobox displays the first column when an item is selected but the code in the bound column is what sets the value.

I recently switched to populating it with a SQL query to an Access database rather than from a table in the excel file and use .List = Application.Transpose(var) using SQL query results , but this has caused it to stop working. The combobox populates fine but I can't select a value from VBA code. Previously, I could make one of the options become selected in the combobox by setting the value of the combobox (e.g. Combobox.Value = 123) to a number that was listed in the bound column (2) and it worked fine. Now, it gives me a run time error '380' "could not set the value property. Invalid property rule". However, if I select the item in the combobox and have the combobox.value returned (e.g., msgbox(Combobox.value)), it shows the value I am trying to select. So the value is there but for some reason I can't select the item using the code with VBA. I've tried changing the variable type of the value I'm trying to set to the combobo to variant and integer but still the same problem. Help is greatly appreciated!

Oh good to know, thanks, I've switched it to .Column. How is this different/more efficient, out of curiosity?

for the combobox, boundcolumn is 2, textcolumn is 1. columncount is 2

Public Sub UpdateChemList(DBconnect As Boolean)
Dim varr As Variant
Dim sqlstr As String
Dim table As String, recordlabel As String, returnvalue As String, recordid As String

If SetupForm.radioligandsradio = True Then
sqlstr = "SELECT Chemicals.Compound, Inventory.SampleID FROM Chemicals INNER JOIN Inventory ON Chemicals.ChemID = Inventory.ChemID WHERE (((Inventory.Retired)=False) AND (Not (Chemicals.Radionuclide)=""""))"
    Else
    
    If SetupForm.allsamplesradio = True Then
    sqlstr = "SELECT Chemicals.Compound, Inventory.SampleID FROM Chemicals INNER JOIN Inventory ON Chemicals.ChemID = Inventory.ChemID"
    Else

        If SetupForm.activesamplesradio = True Then
        sqlstr = "SELECT Chemicals.Compound, Inventory.SampleID FROM Chemicals INNER JOIN Inventory ON Chemicals.ChemID = Inventory.ChemID WHERE (((Inventory.Retired)=False))"
        Else
        sqlstr = "SELECT Chemicals.Compound, Inventory.SampleID FROM Chemicals INNER JOIN Inventory ON Chemicals.ChemID = Inventory.ChemID"
        End If
    End If
End If

If SetupForm.chemsortname = True Then
 sqlstr = sqlstr & " ORDER BY Chemicals.Compound;"
Else
    
    If SetupForm.chemsortrecent = True Then
     sqlstr = sqlstr & " ORDER BY Inventory.SampleID DESC;"
    Else
     sqlstr = sqlstr & ";"
    End If
End If



varr = SQLBE(sqlstr) ' custom function below

SetupForm.ChemList.Column = varr

End Sub

this is the function I use to get results from the database

Public Function SQLBE(sqlstr As String) As Variant
Dim MyDB As New Connection, MyRS As Recordset
Dim vArray As Variant
Dim i As Integer

Set MyDB = New ADODB.Connection
MyDB.Open (strcon)
Set MyRS = New ADODB.Recordset
With MyRS
.CursorLocation = adUseClient

.Open sqlstr, MyDB, adOpenDynamic, adLockReadOnly

If Not (MyRS.BOF And MyRS.EOF) Then

vArray = .GetRows

If .RecordCount = 0 Then
 SQLBE = "no record" ' No records; abort processing
Else
 
  SQLBE = vArray

End If
End If

.Close
End With

End Function

this is simplified for brevity but is the gist of what the code does that causes the problem. This worked find when I used a named range to populate the combobox.

Public Sub ChangeChemList()
Dim sampleid as Integer
sampleid = 337

SetupForm.ChemList.value = sampleid

End Sub

So if the item in the list has the id 337 and I do msgbox(SetupForm.Chemist.Value) it will display 337. But if I try to select this option by setting the value to 337 I get that error.

Still haven't figured this out. Any help is greatly appreciated

Still haven't figured this out. Any help is greatly appreciated

Ok good to know thanks. Is there an optimal/most efficient way to do this? I need to pull the data from the access database but loading it into a spreadsheet first seems like it would be inefficient. Would iterating through the results from the query and doing additem be better?

Oh that's a good idea, thanks!!