How to filter a form from a listbox selection.
Q: Martin later wrote with a related question: he is getting Runtime error ’13’: Type mismatch when running the following code (which is intended to filter the current form by the double-clicked item in the listbox):
Private Sub lstSamtaler_DblClick(Cancel As Integer)
Dim LngID As Long
Dim strFilter As String
LngID = Nz(Me.lstSamtaler.Column(0))
If LngID > 0 Then
strFilter = “[klient_id] = ” & LngID
Me.FilterOn = True
Me.Filter = strFilter
End If
End Sub
A: Is klient_id a numeric field or a text field? The syntax is correct for a numeric (Long) field, except that you need a bang (!) rather than a dot(.) after the Me keyword. Here is the revised code:
Private Sub lstSamtaler _DblClick(Cancel As Integer)
Dim strFilter As String
Dim lngID As Long
lngID = Nz(Me![ lstSamtaler].Column(0))
If lngID > 0 Then
strFilter = “[ klient_id] = ” & lngID
Me.FilterOn = True
Me.Filter = strFilter
End If
End Sub
If you want to go to the selected record, but allow navigation to other records, the syntax is a little different; you need to use the RecordsetClone, like this (you can save the value from the listbox to a variable of the appropriate type):
Dim strFilter As String
‘For text IDs
strFilter = “[______ID] = ” & Chr$(39) & _
Me![lstSamtaler].Column(0) & Chr$(39)
‘For numeric IDs
strFilter = “[______ID] = ” & Me![lstSamtaler].Column(0)
‘Find the record that matches the selected value
Me.RecordsetClone.FindFirst strFilter
Me.Bookmark = Me.RecordsetClone.Bookmark
