Hi Allen
I got this working perfectly in Northwind and modified code slightly to use
a form and my field called CODE (have pasted code below) . I'm not getting
any records returned which I think is probably to do with the string length.
My field is Long Integer and numbers can be up to (currently) 4 digits and
could go into 5 digits. How would I cater for this or am I on the wrong
track? Many thanks for your help.
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the FORM filtered to the items selected in the list box.
'Author: Allen J Browne, 2004.
http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "Test List Box"
'Loop through the ItemsSelected in the list box.
With Me.CODE
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With
'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "
Code:
IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If
'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenForm strDoc, WhereCondition:=strWhere
Exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub