Currently I am using a multiple select list box in a form to select the engineer names for a report. I am looking for some enhancement.
Firstly when I do not select any name, there is no records displayed. I need to show all records if I do not select any name in the dropdown list.
I would like to create anther format of report to suit other purpose. Can I add the selection of these two reports apart from the selection of engineer name.
After I complete the selection on the form, I click a command button that has On Click event that consists of following code.
Private Sub Command15_Click()
'Create a string that we can apply to the filter criteria
Dim strWhere As String
strWhere = "" 'Initialize string variable
Dim varSelection As Variant
'Iterate the selections in the listbox
'and apply the results to frmByState's filter property
If Me.List0.ItemsSelected.Count = 0 Then
Exit Sub 'Go ahead and bail, no items selected
ElseIf Me.List0.ItemsSelected.Count = 0 Then 'There is no need for OR operator with a single selection
For Each varSelection In Me.List0.ItemsSelected
strWhere = "[Engineer] = " & "'" & Me.List0.Column(1, varSelection) & "'"
Next varSelection
Else
For Each varSelection In Me.List0.ItemsSelected
strWhere = strWhere & "[Engineer] = " & "'" & Me.List0.Column(1, varSelection) & "'" & " OR "
Next varSelection
strWhere = Left(strWhere, InStrRev(strWhere, " OR ")) 'Use the left function to trim the last 4 characters
End If
'Open the report and filtered to the selection
DoCmd.OpenReport "rptPerformanceByEngineer", acViewReport, , strWhere
'Close this form
DoCmd.Close acForm, "frmSearchCriteria"
End Sub
Firstly when I do not select any name, there is no records displayed. I need to show all records if I do not select any name in the dropdown list.
I would like to create anther format of report to suit other purpose. Can I add the selection of these two reports apart from the selection of engineer name.
After I complete the selection on the form, I click a command button that has On Click event that consists of following code.
Private Sub Command15_Click()
'Create a string that we can apply to the filter criteria
Dim strWhere As String
strWhere = "" 'Initialize string variable
Dim varSelection As Variant
'Iterate the selections in the listbox
'and apply the results to frmByState's filter property
If Me.List0.ItemsSelected.Count = 0 Then
Exit Sub 'Go ahead and bail, no items selected
ElseIf Me.List0.ItemsSelected.Count = 0 Then 'There is no need for OR operator with a single selection
For Each varSelection In Me.List0.ItemsSelected
strWhere = "[Engineer] = " & "'" & Me.List0.Column(1, varSelection) & "'"
Next varSelection
Else
For Each varSelection In Me.List0.ItemsSelected
strWhere = strWhere & "[Engineer] = " & "'" & Me.List0.Column(1, varSelection) & "'" & " OR "
Next varSelection
strWhere = Left(strWhere, InStrRev(strWhere, " OR ")) 'Use the left function to trim the last 4 characters
End If
'Open the report and filtered to the selection
DoCmd.OpenReport "rptPerformanceByEngineer", acViewReport, , strWhere
'Close this form
DoCmd.Close acForm, "frmSearchCriteria"
End Sub