MS Access 2010 Multi Selection List Box from Form to Report

[JMarcus]

I have to create a report with a list box which a user can select multiple fields which will run on the query. The field is called Category. Category A, B, C, and so on. It is pretty straight forward with a combo box with just one choice. Any one done this before?

 

[pbaldy]

One way:

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

 

[JMarcus]

I saw that. Thanks

 

[pbaldy]

And rejected it? Not what you're trying to do, or?

 

[JMarcus]

At first I didn't see the file but looked at it so the vb makes more sense. I saw a couple methods so wanted to see it straight forward.

 

[pbaldy]

Post back if you have trouble implementing something.

 

[JMarcus]

I have to run it off of queries with this.

DoCmd.TransferSpreadsheet acExport, 10, _
"qryPlanTypes&EffDate", CurrentProject.Path & "\Test.xlsx", True
MsgBox "Data export completed", vbInformation, "Completed"

Do you know what parts I change from this?


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.lstEmployees.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 plan"
Exit Sub
End If

'add selected values to string
Set ctl = Me.lstEmployees
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 "qrywouldbehere", acPreview, , "Plan Name IN(" & strWhere & ")"

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

End Sub

 

[pbaldy]

That's not for a report then, it's for an export. To do that you'd have to change the SQL of the query, using a DAO QueryDef. Did the other method you found to that?

 

[JMarcus]

The report method I got to work but Im dealing with bulk data. I found this on a query. I suppose this would work if I configure it.



Private Sub cmdOpenQuery_Click()

On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM tblCompanies"

'Build the IN string by looping through the listbox
For i = 0 To lstCounties.ListCount - 1
If lstCounties.Selected(i) Then
If lstCounties.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & lstCounties.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [strCompanyCounty] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"

'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "qryCompanyCounties"
Set qdef = MyDB.CreateQueryDef("qryCompanyCounties", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryCompanyCounties", acViewNormal

'Clear listbox selection after running query
For Each varItem In Me.lstCounties.ItemsSelected
Me.lstCounties.Selected(varItem) = False
Next varItem


Exit_cmdOpenQuery_Click:
Exit Sub

Err_cmdOpenQuery_Click:

If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_cmdOpenQuery_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdOpenQuery_Click
End If

End Sub

 

[pbaldy]

Yes, part of that changes the SQL of the query as I described. You could then export the query instead of opening it.