Results 1 to 7 of 7
  1. #1
    emilyrogers is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2010
    Posts
    46

    Talking Printing Dynamic Filter Records

    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!

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    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.

  3. #3
    emilyrogers is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2010
    Posts
    46
    This is the code behind the "apply" button on my form.....
    Code:
    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

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    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.
    Last edited by weekend00; 02-14-2011 at 04:01 PM.

  5. #5
    emilyrogers is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2010
    Posts
    46
    I am not sure what you mean about criteria string for report......
    Code:
    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.

  6. #6
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    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

  7. #7
    emilyrogers is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2010
    Posts
    46
    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??

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filter Records
    By Sir_Hugh in forum Queries
    Replies: 2
    Last Post: 12-15-2010, 06:03 AM
  2. printing multi records
    By shenix2005 in forum Reports
    Replies: 7
    Last Post: 08-20-2010, 09:56 AM
  3. Filter Records
    By Grooveline in forum Access
    Replies: 13
    Last Post: 07-28-2010, 01:00 PM
  4. Printing reports and cycling through records
    By dabman in forum Programming
    Replies: 4
    Last Post: 12-15-2009, 11:45 PM
  5. Best way of displaying and printing records?
    By Orabidoo in forum Access
    Replies: 1
    Last Post: 05-11-2009, 10:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums