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
Advertisement
Answer
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.