Results 1 to 5 of 5

Data Source: Query | Create a form with multiple filters link to query to run report

  1. #1
    researchRE is offline Novice
    Windows XP Access 2016
    Join Date
    Sep 2018
    Posts
    6

    Data Source: Query | Create a form with multiple filters link to query to run report


    I've created a query that references two different tables and need to create an interface so that a user can filter on multiple fields. I was able to create a form with one field and then enter in coding within the criteria in that query to print a report. One of the issues I'm having is the query is saved based on what was filtered. How do I reset the query so that I can run the report again? Another question is how do I create multiple filters within the same form to run the report? Is that possible?

    Thanks so much!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,789
    make a continuous form that shows all records in the query.
    put unbound filter boxes in the header,
    when user is done entering params, click a filter button to run this code:

    Code:
    '----------------
    sub btnFilter_click()
    '----------------
    dim sWhere as string 
    
    sWhere = "1=1"
    if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
    if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
    if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"
    
    If sWhere = "1=1" Then
      Me.FilterOn = False
    Else
      Me.Filter = sWhere
      Me.FilterOn = True
    End If
    end sub

  3. #3
    researchRE is offline Novice
    Windows XP Access 2016
    Join Date
    Sep 2018
    Posts
    6
    Thank you so much for your response, ranman256!

    Would a continuous form be in a table format or does it look more like an interface with questions?

    Here is an example. Is it possible to have multiple filters similar to this on one form?

    Click image for larger version. 

Name:	form filter.png 
Views:	12 
Size:	20.9 KB 
ID:	35486

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,789
    that form looks like SINGLE RECORD. Tho Im not sure Im looking at a text box for the filter, or the data.

    it works either way, but I like the users to see all records at once.

  5. #5
    researchRE is offline Novice
    Windows XP Access 2016
    Join Date
    Sep 2018
    Posts
    6
    Thank you so much, ranman256!

    I got the filter to work to find the corresponding data based on the fields, but the end result I need to print a report based on the data. When I use these filters it will give me the results within the form, but how do I apply these filters to the query so that the report prints correctly? Is that possible?

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

Similar Threads

  1. Replies: 8
    Last Post: 04-02-2016, 03:56 AM
  2. Replies: 2
    Last Post: 08-28-2015, 05:26 PM
  3. Replies: 7
    Last Post: 11-01-2013, 08:14 AM
  4. Replies: 5
    Last Post: 03-27-2013, 04:10 PM
  5. Replies: 4
    Last Post: 04-26-2012, 07:25 PM

Posting Permissions

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