Open a Report based on List Box Selections

[CKING]

Hi guys,

I have a list of "skillsets" that populate into a textbox based on different check boxes. For example, if person A is a farmer and an electrician, when I put a check mark in those boxes, the following is placed in the skillset textbox; farmer, electrician.

I'm trying to filter out people based on specific skill sets, so I created a skillset search form with a list box (SkillSetList) and a "Run" button. I want to be able to select 2-3 skillsets from the listbox and then click the run button and have the people that meet the criteria show up in a report (Candidate Skillsets).

I've got the listbox to display all the skillsets and I can select multiple skillsets, but when I click the run, I get stuck. I get the message "You must select at least 1 skillset." Which is only supposed to happen if I don't have anything selected.

Any help would be much appreciated!


"Private Sub cmdRunReport_Click() 'User must select at least 1 value from the list box
If IsNull(SkillSetList) = True Then


MsgBox "You must select at least 1 skillset."

'Open report called Candidate Skillsets where the Skillsets field
' contains the values in the SillSetList list box
Else
DoCmd.OpenReport "Candidate Skillsets", acViewReport, , "Skillsets like '*" & SkillSetList & "*'"
End If

End Sub

[pbaldy]

A multiselect listbox is always Null. Use the test here (or all of it):

http://www.baldyweb.com/multiselect.htm

[CKING]

Thanks! I appreciate the quick response. When I try to use your code, I get the following: I select 1-2 skills from the list box and then click on "Open Report" I get a popup box for each skillset selection stating "enter parameter value" If I click OK on all of them, my rptCandidates comes up, but has no data populated. I don't want the report to be limited to exact matches, for example if someone only has 1 of the 3 skillsets I still want them to populate.

If I select the exact skillsets from one of the candidates and click "Open Report" I get a message stating "Syntax error (missing operator) in query expression 'Skillsets IN(Computer Repair, SharePoint Developer)'.

Any help or advice is much appreciated!

here is my code:

Option Compare Database
Option Explicit

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.SkillsListBox.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 skillset"
Exit Sub
End If

'add selected values to string
Set ctl = Me.SkillsListBox
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
Next varItem

'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)

'open the report, restricted to the selected items
DoCmd.OpenReport "rptCandidates", acPreview, , "Skillsets IN(" & strWhere & ")"
Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click
End Sub

[pbaldy]

Looks like your values are text, so you'd need the other line within the loop that was commented out.

[CKING]

I'm sorry to be such a noob, what exactly does that mean?

[pbaldy]

In the link were two lines commented out:

'Use this line if your value is text
'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

you want that second one instead of the one you used:

strWhere = strWhere & ctl.ItemData(varItem) & ","

[CKING]

Thanks for the help!! I'm just about there~ I can get the report to run correctly for entries with just 1 skill set in their textbox but if there are multiple it will not bring up anything. For example one candidate has the following in their textbox: Acquisition, Computer Repair, Engineer, JCIDS, If I select Computer Repair and Engineer, I want that candidate to be included in the list. I believe my code needs a LIKE statement, but I can't get it to work. Any help would be appreciated.

THANKS!!!

[pbaldy]

What is your code now? Use this to see what strWhere is ending up as:

BaldyWeb-Immediate window

[CKING]

If I have the following code the only candidate that shows up in the report are the ones with only 1 skillset listed in the textbox

DoCmd.OpenReport "rptCandidates", acPreview, , "Skillsets IN(" & strWhere & ")"

If I use DoCmd.OpenReport "rptCandidates", acPreview, , "Skillsets LIKE(" & strWhere & ")" I get Error: Syntax error (comma) in query expression 'Skillsets LIKE('JCODS','JFO')'.

I added the Debug.Print strWhere and in the Immediate widow I get a list of any of the skill sets I selected from the SkillsList Box

Example: 'Computer Repair','JCIDS','JFO',


Current Code:

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.SkillsListBox.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 skillset"
Exit Sub
End If

'add selected values to string
Set ctl = Me.SkillsListBox
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem

Debug.Print strWhere

'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)

'open the report, restricted to the selected items
DoCmd.OpenReport "rptCandidates", acPreview, , "Skillsets LIKE(" & strWhere & ")"


Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click


End Sub

[pbaldy]

What does the Skillsets field contain? Can you post the db here? The In() clause should pull up any record with any of the selected items, but perhaps I've misunderstood the structure.

[CKING]

The Skillsets field contains a list of all the skillsets a particular candidate has. This field is populated by checkboxes in the Candidate_Details form. Database Link: http://www.iicorps.com/Candidate Tracking.accdb

[pbaldy]

I was assuming a normalized structure. With that structure, you'd have to build a string along the lines of:

SkillSets Like '*Computer Repair*' OR SkillSets Like '*JCIDS*' OR SkillSets Like '*JFO*'

[CKING]

Thanks for the help.

This is what I ended up with, the report runs but returns no results.

Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made
If Me.SkillsListBox.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 skillset"

Exit Sub
End If

'add selected values to string
Set ctl = Me.SkillsListBox
For Each varItem In ctl.ItemsSelected

strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
Debug.Print strWhere

'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)


'open the report, restricted to the selected items
DoCmd.OpenReport "rptCandidates", acPreview, , strWhere = Skillsets Like " Acquisition*" Or Skillsets Like "Computer Repair*" Or Skillsets Like "Engineer*" Or Skillsets Like "JCIDS*" Or Skillsets Like "JFO*"

Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click


End Sub