I found a code which is work good with subform table for searching multivalued Listbox but it doesn’t work with open report vba code
this is the code is used for filtering the subform and i need it for openreport command
If Me.cboDiscipline > "" Then
varWhere = varWhere & "[Discipline].value = '" & Me.cboDiscipline & "'"
End If
this the code (note: its for open filtered report)
Private Sub Toggle3_Click()
Dim strReport As String
Dim strDateField As String
Dim lngLen As Long
Dim strWhere As String
Dim lngView As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "rptFLM"
strDateField = "[Date]"
lngView = acViewReport
If Not Me.txtlocation = "" Then
strWhere = strWhere & "([Location] Like ""*" & Me.txtlocation & "*"") AND "
End If
If Not Me.txtDateFrom = "" Then
strWhere = strWhere & "([Date] >= " & Format(Me.txtDateFrom, conJetDate) & ") AND "
End If
If Not Me.txtDateTo = "" Then
strWhere = strWhere & "([Date] < " & Format(Me.txtDateTo + 1, conJetDate) & ") AND "
End If
If Not Me.txtTagNumber = "" Then
strWhere = strWhere & "([Tag Number] Like ""*" & Me.txtTagNumber & "*"") AND "
End If
If Not Me.txtJSA1 = "" Then
strWhere = strWhere & "([JSA / Procedure] Like ""*" & Me.txtJSA1 & "*"") AND "
End If
If Not Me.txtCreatedby = "" Then
strWhere = strWhere & "([Created by] Like ""*" & Me.txtCreatedby & "*"") AND "
End If
If Not Me.cboDiscipline = "" Then
strWhere = strWhere & "[Discipline].value = '" & Me.cboDiscipline & "'"
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If
DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
Exit Sub
End Sub
this is the code is used for filtering the subform and i need it for openreport command
If Me.cboDiscipline > "" Then
varWhere = varWhere & "[Discipline].value = '" & Me.cboDiscipline & "'"
End If
this the code (note: its for open filtered report)
Private Sub Toggle3_Click()
Dim strReport As String
Dim strDateField As String
Dim lngLen As Long
Dim strWhere As String
Dim lngView As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"
strReport = "rptFLM"
strDateField = "[Date]"
lngView = acViewReport
If Not Me.txtlocation = "" Then
strWhere = strWhere & "([Location] Like ""*" & Me.txtlocation & "*"") AND "
End If
If Not Me.txtDateFrom = "" Then
strWhere = strWhere & "([Date] >= " & Format(Me.txtDateFrom, conJetDate) & ") AND "
End If
If Not Me.txtDateTo = "" Then
strWhere = strWhere & "([Date] < " & Format(Me.txtDateTo + 1, conJetDate) & ") AND "
End If
If Not Me.txtTagNumber = "" Then
strWhere = strWhere & "([Tag Number] Like ""*" & Me.txtTagNumber & "*"") AND "
End If
If Not Me.txtJSA1 = "" Then
strWhere = strWhere & "([JSA / Procedure] Like ""*" & Me.txtJSA1 & "*"") AND "
End If
If Not Me.txtCreatedby = "" Then
strWhere = strWhere & "([Created by] Like ""*" & Me.txtCreatedby & "*"") AND "
End If
If Not Me.cboDiscipline = "" Then
strWhere = strWhere & "[Discipline].value = '" & Me.cboDiscipline & "'"
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If
DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
Exit Sub
End Sub