Multiple Multi-select boxes on one form

[bujaman]

I am a total newbie to Access. I am trying to build a form that can take input from three list boxes and output a print preview of the report meeting the selected criteria. I have it working great when the multiselect option is switched to "None," but as soon as I change it to "Simple" or "Extended" nothing works. I think I am missing something and may need to do a bit of coding. I have some experience with VBA for excel, but Access is an entirely new beast for me. ANy help is greatly appreciated. Thanks!

[pbaldy]

A multi-select listbox will require code. Here's how it's done for 1:

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

For 3, you'd just build 3 strings instead of 1, and incorporate all 3 into the wherecondition.

[bujaman]

Thanks pbaldy,
just one question, I am struggling with the "EmpID IN(" & strWhere & ") on the last line of the code in your link. What does that refer to? I checkout the linked database and couldn't figure out what this goes to and how it works. I think I have the code working in my form, except for that part. Thanks again.

[bujaman]

OK, never mind, I figured that one out, now onto adding multiple strings. I will try and figure it out and let you know if I get stuck. Thanks for the help so far!

[pbaldy]

No problem, and welcome to the site by the way!

[bujaman]

Thanks for the help! I got it working great, below is the code that I used. They may be a simpler, more elegant solution, but this works well.

Private Sub ElementPrgType_Click()
Dim strWhere As String
Dim strWhere1 As String
Dim ctl As Control
Dim ctl1 As Control
Dim varItem As Variant

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

'add selected values to string
Set ctl = Me.Countries1

For Each varItem In ctl.ItemsSelected
  'strWhere = strWhere & ctl.ItemData(varItem) & ","
  'Use this line if your value is text
  strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)

Set ctl1 = Me.Competition1
For Each varItem In ctl1.ItemsSelected
  'strWhere = strWhere & ctl.ItemData(varItem) & ","
  'Use this line if your value is text
  strWhere1 = strWhere1 & "'" & ctl1.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere1 = Left(strWhere1, Len(strWhere1) - 1)

'open the report, restricted to the selected items
DoCmd.OpenReport "WBS Element by Prg Type - Summary Report", acPreview, , "COUNTRY_NAME IN(" & strWhere & ") "
DoCmd.OpenReport "WBS Element by Prg Type - Summary Report", acPreview, , "Competition IN(" & strWhere1 & ") "

[pbaldy]

That works? I'd expect a single OpenReport:

DoCmd.OpenReport "WBS Element by Prg Type - Summary Report", acPreview, , "COUNTRY_NAME IN(" & strWhere & ") AND Competition IN(" & strWhere1 & ")"

[bujaman]

Yeah, weird, it did work. I modified it to match what you added in your last post and it is working great. Now I have run into another problem. I am running basically the same thing to call a different report, but I am running into problems. The layout of the logic seems to be the same to me, I have checked, re-checked, and tripple checked everything and the two report generators are basically the same, however, when I select the options in the listboxes and click the button, Access prompts me to enter the parameter values "COUNTRY_NAME" and "Competition" like I am running a query. I notice that when I change the text in red in the code below, the parameter values that Access asks for also change. Any thoughts?

Dim strPrg As String
Dim strPrg1 As String
Dim ctl As Control
Dim ctl1 As Control
Dim varItem As Variant

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

'add selected values to string
Set ctl = Me.Countries1

For Each varItem In ctl.ItemsSelected
  'strPrg = strPrg & ctl.ItemData(varItem) & ","
  'Use this line if your value is text
  strPrg = strPrg & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strPrg = Left(strPrg, Len(strPrg) - 1)

Set ctl1 = Me.Competition1
For Each varItem In ctl1.ItemsSelected
  'strPrg = strPrg & ctl.ItemData(varItem) & ","
  'Use this line if your value is text
  strPrg1 = strPrg1 & "'" & ctl1.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strPrg1 = Left(strPrg1, Len(strPrg1) - 1)

'open the report, restricted to the selected items
DoCmd.OpenReport "WBS Element by Prg Type - Summary Report", acPreview, , "COUNTRY_NAME IN(" & strPrg & ") AND Competition IN(" & strPrg1 & ") "

[pbaldy]

The parameter prompt implies that those fields are not in the record source of that report. Are they?