Fill a ComboBox from a Database

[SkylabOne]

Dear all,

I am linking my Access Database to a UserForm in Word and have written below code. When I test the macro there are no errors, but the ComboBox is empty. Can anyone tell me what I'm doing wrong here?

Private Sub UserForm1_Initialize()    
    On Error GoTo UserForm1_Initialize_Err
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=C:\ClientDatabase\ClientDatabase.mdb"
    rst.Open "SELECT DISTINCT [ClientName] FROM tblClients ORDER BY [ClientName];"


             cnn , adOpenStatic
    rst.MoveFirst
    With Me.ComboBox1
        .Clear
        Do
             .AddItem rst![ClientName]
             rst.MoveNext
        Loop Until rst.EOF
    End With
UserForm1_Initialize_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
UserForm1_Initialize_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume UserForm1_Initialize_Exit
End Sub

 

[nuked]

It doesn't sound like your query is returning any records. Can you step through your code in debug mode and find out?

 

[SkylabOne]

             [B]cnn[/B] , adOpenStatic

If I compile the project cnn is selected and the error Compile error: Invalid use of property is shown. Does that help?

 

[SkylabOne]

Is it possible that I'm using an old code which doesn't work for Word/Access 2013?

I have added a ListBox now, with the exact same code and it doesn't populate with the list from the database either. So I presume there is something wrong with the way I've connected.

Hopefully someone sees my mistake.

Thank you,
Rolf

 

[nuked]

The code line should look something like this:

rst.Open "SELECT DISTINCT [ClientName] FROM tblClients ORDER BY [ClientName]", cnn, adOpenStatic, adLockReadOnly

 

[SkylabOne]

The code line should look something like this:

rst.Open "SELECT DISTINCT [ClientName] FROM tblClients ORDER BY [ClientName]", cnn, adOpenStatic, adLockReadOnly

Thank you for the suggestion, but it still doesn't work, even though the error is fixed.
The link to the source definitely works, as it opens the file when I paste it into Explorer.
I will transfer the data into Excel and try the same thing there. Perhaps it is something with Access or the particular database I've made.

Thanks for the help!