Get Access-data in UserForm

[Stevenn]

I want to fill a dropdown list in my UserForm with data from Access.

Right now I'm getting the data with the code below

Private Sub UserForm_Initialize()
 
    Dim objConnection   As New ADODB.Connection
    Dim objRecordset    As New ADODB.Recordset
 
    objConnection.Open "Provider=Microsoft.Ace.OLEDB.12.0; Data Source=" & DataSource & ";"
 
    objRecordset.Open "SELECT DISTINCT Name FROM Persons", objConnection, adOpenStatic
 
    objRecordset.MoveFirst
 
    With Me.cboPersons
 
        .Clear
 
        Do
 
            .AddItem objRecordset.Fields("Name").Value
 
            objRecordset.MoveNext
 
        Loop Until objRecordset.EOF
 
    End With
End Sub

But I want to show Persons.Name, but save Persons.Id instead of Persons.Name. Is it possible with VBA in Excel 2007 and Access 2007? Can I by the way do the Access data selection smarter than the code I am using right now?

 

[RoryA]

Something like

objRecordset.Open "SELECT DISTINCT ID, Name FROM Persons", objConnection, adOpenForwardOnly
    With Me.cboPersons
        .ColumnCount = 2
        .BoundColumn = 1
        .Columnwidths = "0;75"
        .Clear
        .Column = objRecordset.GetRows()
    End With
objRecordset.Close

 

[Stevenn]

It works when I clear the RowSource element. Thank you