Vba in access. Filtering query

Question

I am having a issue with vba in access. I wrote a code to filter a form and generate a report via button. Now when I run the query , it will only filter and generate the report if I put the report on the same page as the form. Is there a way to make it so that my report will be created seperately?

Private Sub Search_Click()
    Dim strWhere As String
    Dim i As Variant
    Dim varItem As Variant
    Dim strDelim As String

    If Nz(Me.txtFullName, "") <> "" Then
        strWhere = strWhere & "FullName Like '*" & Replace(Me.txtFullName, "'", "''") & "*' AND "
    End If

    If Nz(Me.txtCivil, "") <> "" Then
        strWhere = strWhere & "CivilService_Status Like '*" & Replace(Me.txtCivil, "'", "''") & "*' AND "
    End If

    If Nz(Me.txtOffice, "") <> "" Then
        strWhere = strWhere & "Office_Title Like '*" & Replace(Me.txtOffice, "'", "''") & "*' AND "
    End If

    If Nz(Me.cboPosition, "") <> "" Then
        strWhere = strWhere & "[Full Time],[Part Time] = '" & Me.cboPosition.Value & "' AND "
    End If

    If Nz(Me.txtStartdate, "") <> "" Then
        strWhere = strWhere & "AgencyStart_Date Like '*" & Replace(Me.txtStartdate, "'", "''") & "*' AND "
    End If

    For Each i In Me.lstBureau.ItemsSelected 'listbox
        If Nz(Me.txtdivision, "") <> "" Then
            strWhere = strWhere & "Division Like '*" & Replace(Me.txtdivision, "'", "''") & "*' AND "
        End If
    Next i

    If strWhere <> "" Then
        strWhere = Left(strWhere, Len(strWhere) - 5)
        Report_Onboard_summary.Filter = strWhere
        Report_Onboard_summary.FilterOn = True
        'DoCmd.OpenReport "Onboard_summary", acViewReport, strWhere
    Else
        Report_Onboard_summary.Filter = ""
        Report_Onboard_summary.FilterOn = False
    End If
End Sub

Any help would be greatly appreciated

Olivier Jacot-Descombes Olivier Jacot-Descombes · Accepted Answer · 2018-08-21T19:47:05

I usually open reports with the OpenReport command.

DoCmd.OpenReport "Report_Onboard_summary", acViewPreview, WhereCondition:=strWhere

for previewing, or

DoCmd.OpenReport "Report_Onboard_summary", acViewNormal, WhereCondition:=strWhere

for immediate printing.

This assumes that the report is created as a Report object stored separately in the database, not a Form object. You said that you stored it on the same page, this leads me to believe that you have a sub-Form and not an actual Report.

Vba in access. Filtering query

1 Answers