Hello everyone!
I have been trying for two days now researching how to complete this but after trying to make adjustments to multiple examples found I can not get this to work.
I have a form with a start date, end date and a multi select listbox that feeds a query (with no criteria). After reading up multi select listboxes I realized that I would have to have code to insert a WHERE statement to pass along the multiple listbox parameters to the query. Previously it was just a single-selection combo box but then the powers that be asked to be able to select multiple values and I had to change it to the multi select listbox.
I found and adjusted this code:
Private Sub DateOKButton_Click()
On Error GoTo Err_DateOKButton_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 Claims"
'Build the IN string by looping through the listbox
For i = 0 To DentalPracticeNameListBx.ListCount - 1
If DentalPracticeNameListBx.Selected(i) Then
If DentalPracticeNameListBx.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & DentalPracticeNameListBx.Column(0, i) & "',"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [DentalPracticeName] 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 "ClaimsBatchSummaryQuery"
Set qdef = MyDB.CreateQueryDef("ClaimsBatchSummaryQuery", strSQL)
'Open the query, built using the IN clause to set the criteria
DoCmd.OutputTo acOutputQuery, "ClaimsBatchSummaryQuery", acFormatXLS, _
"\\cifs04\NetworkDevelopment\MS_Database\Reports\Claims Batch Summary\Claim Batch Summary Report_" & "'" & Forms!CBSParameterDateForm!DentalPracticeNameListBx & "_" & Format(Date, "dd-mmm-yyyy") & ".xls", True
'Clear listbox selection after running query
For Each varItem In Me.DentalPracticeNameListBx.ItemsSelected
Me.DentalPracticeNameListBx.Selected(varItem) = False
Next varItem
Exit_DateOKButton_Click:
Exit Sub
Err_DateOKButton_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_DateOKButton_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_DateOKButton_Click
End If
End Sub
This works fine for passing the list box selections as parameters but does not put the start and end date parameters in there. My VBA is not strong at all and could use some help adjusting this code to do the following:
1. Pass both the date parameters (>=[Forms]![CBSParameterDateForm]![StateDateTxtBx] And <=[Forms]![CBSParameterDateForm]![EndDateTxtBx]) and the list box selections
2. In the section of the code where I am exporting the query to excel, I need to save the list box values into the file name where the current code shows "Forms!CBSParameterDateForm!DentalPracticeNameListBx".I am guessing I would need to separate each Listbox value (which is a Dental Practice name) with a "_". I wish I could get around this part but the powers that be asked for it.
Any help with this would be greatly appreciated!
msk7777
I have been trying for two days now researching how to complete this but after trying to make adjustments to multiple examples found I can not get this to work.
I have a form with a start date, end date and a multi select listbox that feeds a query (with no criteria). After reading up multi select listboxes I realized that I would have to have code to insert a WHERE statement to pass along the multiple listbox parameters to the query. Previously it was just a single-selection combo box but then the powers that be asked to be able to select multiple values and I had to change it to the multi select listbox.
I found and adjusted this code:
Private Sub DateOKButton_Click()
On Error GoTo Err_DateOKButton_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 Claims"
'Build the IN string by looping through the listbox
For i = 0 To DentalPracticeNameListBx.ListCount - 1
If DentalPracticeNameListBx.Selected(i) Then
If DentalPracticeNameListBx.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & DentalPracticeNameListBx.Column(0, i) & "',"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [DentalPracticeName] 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 "ClaimsBatchSummaryQuery"
Set qdef = MyDB.CreateQueryDef("ClaimsBatchSummaryQuery", strSQL)
'Open the query, built using the IN clause to set the criteria
DoCmd.OutputTo acOutputQuery, "ClaimsBatchSummaryQuery", acFormatXLS, _
"\\cifs04\NetworkDevelopment\MS_Database\Reports\Claims Batch Summary\Claim Batch Summary Report_" & "'" & Forms!CBSParameterDateForm!DentalPracticeNameListBx & "_" & Format(Date, "dd-mmm-yyyy") & ".xls", True
'Clear listbox selection after running query
For Each varItem In Me.DentalPracticeNameListBx.ItemsSelected
Me.DentalPracticeNameListBx.Selected(varItem) = False
Next varItem
Exit_DateOKButton_Click:
Exit Sub
Err_DateOKButton_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_DateOKButton_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_DateOKButton_Click
End If
End Sub
This works fine for passing the list box selections as parameters but does not put the start and end date parameters in there. My VBA is not strong at all and could use some help adjusting this code to do the following:
1. Pass both the date parameters (>=[Forms]![CBSParameterDateForm]![StateDateTxtBx] And <=[Forms]![CBSParameterDateForm]![EndDateTxtBx]) and the list box selections
2. In the section of the code where I am exporting the query to excel, I need to save the list box values into the file name where the current code shows "Forms!CBSParameterDateForm!DentalPracticeNameListBx".I am guessing I would need to separate each Listbox value (which is a Dental Practice name) with a "_". I wish I could get around this part but the powers that be asked for it.
Any help with this would be greatly appreciated!
msk7777
