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]

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.

I had wondered about that but I have seen people name their fields some strange stuff so I didn't ask about it.

 

[ChrisCione]

One minor issue:

When no selection is made in the listbox, it throws up the syntax error:

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

instead of Please select a Specialist. from

If Me.lstSpecialist.ListCount = 0 Then
MsgBox "Please select a Specialist.", vbOKOnly + vbExclamation, "Select a Specialist"
Else


Any idea how to fix that?

 

[boblarson]

One minor issue:

When no selection is made in the listbox, it throws up the syntax error:

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

instead of Please select a Specialist. from

If Me.lstSpecialist.ListCount = 0 Then
MsgBox "Please select a Specialist.", vbOKOnly + vbExclamation, "Select a Specialist"
Else


Any idea how to fix that?

How about

If Me.lstSpecialist.ItemsSelected.Count = 0 Then

 

[ChrisCione]

Perfect.

Bob, you're hired.

 

[boblarson]

Perfect.

Bob, you're hired.

Those are 3 words I've been longing to hear from my supervisor at my 6 month contract-to-hire job for almost 2½ years.

 

[ChrisCione]

You have my vote, for whatever it's worth (not much, I know).