Printing Dynamic Filter Records

[emilyrogers]

I have a filter on my form which uses multiple comboxes and textboxes.



I have a print button- however when clicked it prints all records, I would like to know what needs to be done/if its possible. that when the button is clicked it only prints the records that have been filtered??

the form is linked to a report.

Thank you so much!

[weekend00]

you need to put filter on the report, not only on the form. because you print the reprot, not to print the form.

codes to open a report may look like:
docmd.openreport "report name",,,criteriaString

you need to make up criteriaString based on you combo boxes and text boxes before you open the report.

[emilyrogers]

This is the code behind the "apply" button on my form.....

Dim strSQL As String
    Dim strWhere As String
    Dim qdf As QueryDef
    strSQL = csfixedSQL

    If ((Chk_log_name) And Not (IsNull(Cmb_name.Value))) Then
        strWhere = " AND [Opportunities].[Location Country] = """ & Cmb_name.Value & """"
    End If
    
    
    If ((Check205) And Not (IsNull(Check207))) Then
        strWhere = " AND [Opportunities].[Customer has order to place] = True"
    End If



    If ((Chk_log_id) And Not (IsNull(Cmb_TLA.Value))) Then
        strWhere = strWhere & " AND [Opportunities].[Customer] = """ & Cmb_TLA.Value & """"
    End If
    
    If ((Chk_log_sap) And Not (IsNull(Txt_log_sap.Value))) Then
        strWhere = strWhere & " AND [Opportunities].[Location City] Like """ & "*" & Txt_log_sap.Value & "*"""
    End If
    
    If ((Chk_log_job) And Not (IsNull(Txt_log_job.Value))) Then
        strWhere = strWhere & " AND [Opportunities].[ID] Like """ & "" & Txt_log_job.Value & """"
    End If
    
    If ((Chk_log_incident) And Not (IsNull(Txt_log_incident.Value))) Then
        strWhere = strWhere & " AND [Opportunities].[Customer Enquiry Reference] Like """ & "*" & Txt_log_incident.Value & "*"""
    End If
    
    If ((Chk_log_report) And Not (IsNull(Combo196.Value))) Then
        strWhere = strWhere & " AND [Opportunities].[Sales Responsibility] Like """ & "*" & Combo196.Value & "*"""
    End If
    
    
    If (strWhere <> "") Then strWhere = "(" & Mid(strWhere, 6) & ")"
        
    If (Chk_log_date) Then
        If Not (IsNull(DT_log_date1.Value)) Then
            strDate = " AND [Opportunities].[Bid Required By] >= " & _
            "#" & Format$(DT_log_date1.Value, "mm/dd/yyyy") & "#"
        End If
        If Not (IsNull(DT_log_date2.Value)) Then
            strDate = strDate & " AND [Opportunities].[Bid Required By] <= " & _
            "#" & Format$(DT_log_date2.Value, "mm/dd/yyyy") & "#"
        End If
    End If
    If (Check191) Then
        If Not (IsNull(ActiveXCtl194.Value)) Then
            strDate = " AND [Opportunities].[Enquiry Date] >= " & _
            "#" & Format$(ActiveXCtl194.Value, "mm/dd/yyyy") & "#"
        End If
        If Not (IsNull(ActiveXCtl195.Value)) Then
            strDate = strDate & " AND [Opportunities].[Enquiry Date] <= " & _
            "#" & Format$(ActiveXCtl195.Value, "mm/dd/yyyy") & "#"
        End If
    End If
    If (strWhere <> "") Then
      strWhere = strWhere & strDate
    Else
      strWhere = Mid(strDate, 6)
    End If
    If (strWhere <> "") Then strSQL = strSQL & "WHERE " & strWhere

    
    'If (Chk_log_date) Then
    '    If Not (IsNull(DT_log_date1.Value)) Then
    '        strWhere = strWhere & " AND tblLog.Date >= " & _
    '        "#" & Format$(DT_log_date1.Value, "mm/dd/yyyy") & "#"
    '    End If
    '    If Not (IsNull(DT_log_date2.Value)) Then
    '        strWhere = strWhere & " AND tblLog.Date <= " & _
    '        "#" & Format$(DT_log_date2.Value, "mm/dd/yyyy") & "#"
    '    End If
    'End If
        
    'If (strWhere <> "") Then strSQL = strSQL & "WHERE " & Mid(strWhere, 6)
    
    
 
 
Debug.Print strSQL
    
Forms!frmSiteLog![New Enquiry].Form.RecordSource = strSQL
Forms!frmSiteLog![New Enquiry].Form.Requery
Forms!frmSiteLog![New Enquiry].Form.Visible = True

is this the criteria string?

thanks

[weekend00]

the criteria string is perfect. but the code only apply the criteria for the form. I see nothing about the report.

still, you need some code like this to open the report:
docmd.openreport "report name",,,strWhere

do not forget to set filter on in the report.

[emilyrogers]

I am not sure what you mean about criteria string for report......

SELECT Opportunities.ID, Opportunities.Project, Opportunities.Customer, Opportunities.[Sales Responsibility], Opportunities.[Market Sector], Opportunities.Status, Opportunities.[Enquiry Date], Opportunities.[Bid Required By], Opportunities.[Quoted Value Total (£)], Opportunities.Comments, Opportunities.[Location Country], Opportunities.[Location City], Opportunities.[Customer Enquiry Reference], Opportunities.[Customer has order to place], Opportunities.[End User], Opportunities.[Complete AMB sets], Opportunities.[Cabinet Only], Opportunities.Commissioning, Opportunities.Engineering, Opportunities.[Maintenance Contract], Opportunities.[Remote tuning and monitoring], Opportunities.Spares, Opportunities.[Number of sets that would be ordered], Opportunities.[Bid Date], Opportunities.[Validity Expires], Opportunities.[Order Won date], Opportunities.[Order won value], Opportunities.[Follow-up Date], Opportunities.[Follow-up Person], Opportunities.[Follow-up Action], Opportunities.Closed
FROM Opportunities;

would it be this.- the recordsource for the report is this query.
I want the user to be able to apply the filter, view the records that the filter produces and then print just those records in a report. the report i am using is the report that is used by this form anyway-but when i click print it prints all records.

[weekend00]

After you set filters to your form( your codes in post #3), you want to open the report to review, right?

so add following code after you codes in post #3:

docmd.openreport "report name",acview_preview,,strWhere


in design view of the report, set property filter on to "YES". then save and close. then run you form

[emilyrogers]

Thanks for your help, however I don't really want them to go straight to the report view, as most of the time they will just want to see the results in form view and be able to edit them. should i place some sort of code in the emmbedded macro button that prints the form?

I still want them to be able to print all if they want.

EDIT- I have placed this in the on click section of a new command print button(not macro)
docmd.openreport "All Enquiries",acview_preview,,strWhere

And it works in such a way that it opens the report in report view but it still doesnt filter. I have set "filter on load" in the report to yes. is there anything else i am misssing??