CODEWITHSUNDEEP
sqlexcelvba
YasserKhalil
YasserKhalil

Reputation: 9568

Populate listbox from access file

I have found a piece of code that enables me to list or populate listbox based on access database

Private Sub CommandButton1_Click()
    Dim cnn         As ADODB.Connection
    Dim rst         As ADODB.Recordset
    Dim stConn      As String
    Dim strSQL      As String
    Dim vaData      As Variant
    Dim k           As Long
    Dim sDBPath As String
    sDBPath = ThisWorkbook.Path & "\Movies.accdb"
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
        "Data Source=" & sDBPath & ";"
    
    Set rst = New ADODB.Recordset
    
    strSQL = "SELECT [ActorName] FROM tblActor WHERE [ActorGenderId]= 'Male'"
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    
    
    With rst
        k = .Fields.Count
        
        vaData = .GetRows
    End With
    rst.Close
    cnn.Close
    
    With Me
        With .ListBox1
            .Clear
            .BoundColumn = k
            .list = Application.Transpose(vaData)
            .ListIndex = -1
        End With
    End With
    
    Set rst = Nothing
    Set cnn = Nothing
End Sub

When trying that I encountered an error Type data mismatch in criteria expression at this line rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText

Upvotes: 0

Views: 190

Answers (1)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11998

Your table got a field related to a secondary table (let's say it's called tblGender). If the primary Key field in that table is numeric, then datatype of field ActorGenderId in table tblActor is numeric, even if you see a text.

This happens because relations are caused by primary key fields. But in the dropdown list, you can customize and hide the first column (bound column), and see secondary fields (gender in this case).

So that explains why it works if you use:

strSQL = "SELECT [ActorName] FROM tblActor WHERE [ActorGenderId]=2;"

But you can modify your query, join both tables (tblActor and tblGender) and bring the real text field of gender, so you can specify a text criteria instead of a number.

Something like this should work for you:

strSQL="SELECT tblActor.ActorName FROM tblGender INNER JOIN tblActor ON tblGender.Id = tblActor.ActorGenderId WHERE tblGender.Gender)='male'"

Access and Excel got the same feature: what we see is not always the real value. This case is a perfect example of it.

Upvotes: 1

Related Questions