Listbox selection(s) to open/display records

[ChrisCione]

Hello,

I found the code below from a very old thread and am attempting to modify. It is hanging on the italicized line, prompting me for a Parameter Value for cboSpecialist. cboSpecialist is the name of the field on the form that I am using as the filter. I.e, match the lstSpecialist selection(s) with cboSpecialist field and display only those records that match.

strFilter = strFilter & "cboSpecialist = """ & Me![lstSpecialist].ItemData(varNumber) & """"

Private Sub Command79_Click()
On Error GoTo Err_Command79_Click
Dim varNumber As Variant
Dim strFilter As String
If Me.lstSpecialist.ListCount = 0 Then
 MsgBox "Please select a Specialist.", vbOKOnly + vbExclamation, "Select a Specialist"
Else
 For Each varNumber In Me![lstSpecialist].ItemsSelected
  If strFilter <> "" Then
   strFilter = strFilter & " or "
  End If
    strFilter = strFilter & "cboSpecialist = """ & Me![lstSpecialist].ItemData(varNumber) & """"
 Next varNumber
 DoCmd.OpenForm "frmRecruitment", acNormal, , strFilter
End If
Exit_Command79_Click:
 Exit Sub
Err_Command79_Click:
 MsgBox Err.Description
 Resume Exit_Command79_Click
 
End Sub

Many thanks for any help.

 

[boblarson]

Here's how I would modify it:

Private Sub Command79_Click()
    On Error GoTo Err_Command79_Click
    Dim varItem As Variant
    Dim strFilter As String
    
    If Me.lstSpecialist.ListCount = 0 Then

        MsgBox "Please select a Specialist.", vbOKOnly + vbExclamation, "Select a Specialist"

    Else

        For Each varItem In Me.lstSpecialist.ItemsSelected
            strFilter = strFilter & Me.lstSpecialist.ItemData(varItem) & ","
        Next varNumber
        
        If Right(strFilter, 1) = "," Then
           strFilter = Left(strFilter, Len(strFilter) - 1)
        End If
        
        strFilter = "[cboSpecialist] In(" & strFilter & ")"
        
        
        DoCmd.OpenForm "frmRecruitment", acNormal, , strFilter

    End If

Exit_Command79_Click:
    Exit Sub

Err_Command79_Click:
    MsgBox Err.Description
    Resume Exit_Command79_Click
End Sub

The way I did it is to use an IN statement instead of building a long OR statement.

 

[ChrisCione]

Bob,

Thanks much for the help. I had to put the project aside, but I will trying this in the near future and will report the outcome.

 

[ChrisCione]

Here's how I would modify it:

Private Sub Command79_Click()
    On Error GoTo Err_Command79_Click
    Dim varItem As Variant
    Dim strFilter As String
 
    If Me.lstSpecialist.ListCount = 0 Then
 
        MsgBox "Please select a Specialist.", vbOKOnly + vbExclamation, "Select a Specialist"
 
    Else
 
        For Each varItem In Me.lstSpecialist.ItemsSelected
            strFilter = strFilter & Me.lstSpecialist.ItemData(varItem) & ","
        Next varNumber
 
        If Right(strFilter, 1) = "," Then
           strFilter = Left(strFilter, Len(strFilter) - 1)
        End If
 
        strFilter = "[cboSpecialist] In(" & strFilter & ")"
 
 
        DoCmd.OpenForm "frmRecruitment", acNormal, , strFilter
 
    End If
 
Exit_Command79_Click:
    Exit Sub
 
Err_Command79_Click:
    MsgBox Err.Description
    Resume Exit_Command79_Click
End Sub

The way I did it is to use an IN statement instead of building a long OR statement.

It halts at this line

" Next varNumber"

and gives a "compile error - variable not defined."

Not sure if this makes a difference, but all these fields I'm referencing are text fields.

 

[boblarson]

Oops, my bad - I left varNumber after that. Just take varNumber out so it just says

NEXT

 

[ChrisCione]

Oops, my bad - I left varNumber after that. Just take varNumber out so it just says

NEXT

It threw up this error:

Syntax error (missing operator) in query expression 'cboSpecialist IN (TheListboxItemISelected).'

 

[boblarson]

It threw up this error:

Syntax error (missing operator) in query expression 'cboSpecialist IN (TheListboxItemISelected).'

Please Post EXACTLY the whole code of what you are now using which gives that error.

 

[ChrisCione]

Please Post EXACTLY the whole code of what you are now using which gives that error.

Private Sub Command79_Click()
    On Error GoTo Err_Command79_Click
    Dim varItem As Variant
    Dim strFilter As String
 
    If Me.lstSpecialist.ListCount = 0 Then
        MsgBox "Please select a Specialist.", vbOKOnly + vbExclamation, "Select a Specialist"
    Else
        For Each varItem In Me.lstSpecialist.ItemsSelected
            strFilter = strFilter & Me.lstSpecialist.ItemData(varItem) & ","
        Next 
 
        If Right(strFilter, 1) = "," Then
           strFilter = Left(strFilter, Len(strFilter) - 1)
        End If
 
        strFilter = "[cboSpecialist] In(" & strFilter & ")"
 
 
        DoCmd.OpenForm "frmRecruitment", acNormal, , strFilter
    End If
Exit_Command79_Click:
    Exit Sub
Err_Command79_Click:
    MsgBox Err.Description
    Resume Exit_Command79_Click
End Sub

 

[boblarson]

Okay, that is helpful. I believe that for one we need quotes as you had that in your original post that the listbox was returning the specialist as a text value and not the ID. If that is the case then we would need to modify - see red below:

Private Sub Command79_Click()
    On Error GoTo Err_Command79_Click
    Dim varItem As Variant
    Dim strFilter As String
 
    If Me.lstSpecialist.ListCount = 0 Then
        MsgBox "Please select a Specialist.", vbOKOnly + vbExclamation, "Select a Specialist"
    Else
        For Each varItem In Me.lstSpecialist.ItemsSelected
            strFilter = strFilter & Chr(34) &  Me.lstSpecialist.ItemData(varItem) & Chr(34) & ","
        Next 
 
        If Right(strFilter, 1) = "," Then
           strFilter = Left(strFilter, Len(strFilter) - 1)
        End If
 
        strFilter = "[cboSpecialist] In(" & strFilter & ")"
 
 
        DoCmd.OpenForm "frmRecruitment", acNormal, , strFilter
    End If
Exit_Command79_Click:
    Exit Sub
Err_Command79_Click:
    MsgBox Err.Description
    Resume Exit_Command79_Click
End Sub

 

[ChrisCione]

Okay, that is helpful. I believe that for one we need quotes as you had that in your original post that the listbox was returning the specialist as a text value and not the ID. If that is the case then we would need to modify - see red below:

Private Sub Command79_Click()
    On Error GoTo Err_Command79_Click
    Dim varItem As Variant
    Dim strFilter As String
 
    If Me.lstSpecialist.ListCount = 0 Then
        MsgBox "Please select a Specialist.", vbOKOnly + vbExclamation, "Select a Specialist"
    Else
        For Each varItem In Me.lstSpecialist.ItemsSelected
            strFilter = strFilter & Chr(34) &  Me.lstSpecialist.ItemData(varItem) & Chr(34) & ","
        Next 
 
        If Right(strFilter, 1) = "," Then
           strFilter = Left(strFilter, Len(strFilter) - 1)
        End If
 
        strFilter = "[cboSpecialist] In(" & strFilter & ")"
 
 
        DoCmd.OpenForm "frmRecruitment", acNormal, , strFilter
    End If
Exit_Command79_Click:
    Exit Sub
Err_Command79_Click:
    MsgBox Err.Description
    Resume Exit_Command79_Click
End Sub

Bob,

I made one minor modification, from this

strFilter = "[cboSpecialist] In(" & strFilter & ")"

to

strFilter = "[Specialist] In(" & strFilter & ")"

The original statement was pointing to a combo box on the form; the modified points to the field in the table.

It works beautifully.

Many, many thanks. I couldn't have done it without your expertise.