Results 1 to 5 of 5
  1. #1
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91

    Question Form/sub-form: multiple filtering

    Click image for larger version. 

Name:	access.PNG 
Views:	18 
Size:	14.3 KB 
ID:	44790

    I'm trying to create a form with a built-in subform for filtering all of our returns data. The check boxes do nothing yet, so we can ignore that for now. What I've tried so far is having a query that pulls all of the fields I want to display from the table sources with a criteria of setting the customer number equal to the combo box value for customer. I only have this criteria in so far for testing purposes - once I get that working, the others should work by same method. Here's my SQL for that query:

    Code:
    SELECT tblRMAs.RMA_nb, tblRMAs.cust_nb, tblPartsReturned.partNumber, tblRMAs.dateReceived, tblPartsReturned.failureCategory, tblPartsReturned.creditFROM tblRMAs INNER JOIN tblPartsReturned ON tblRMAs.RMA_nb = tblPartsReturned.RMA_nb
    WHERE (((tblRMAs.cust_nb)=[Forms]![frmAnalysisPartsCustomers]![cboCust]));
    The search button on the form:
    Code:
    Private Sub btnSearch_Click()    Me.subfrmPartsReturnedByCustomers.Requery
    End Sub
    I also tried that with
    Code:
    Me.subfrmPartsReturnedByCustomers.Form.Requery
    to no avail.

    If I remove the WHERE clause, it does show all of the records in the subform, so I know that that is sourcing records correctly. Any ideas what I'm doing wrong?



    Last edited by Pawtang; 03-24-2021 at 09:44 AM. Reason: picture

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make the continuous form show ALL records.
    then filter via controls:

    Code:
    sub btnFind_click()
    sWhere = "1=1"
    if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
    if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
    if not IsNull(cboGender) then    sWhere = sWhere & " and [Gender]='" & cboGender & "'"
    
     'then filer
    if sWhere = "1=1" then
      me.filterOn = false
    else
      me.filter = sWhere
      me.filterOn = true
    endif
    end sub

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    If I remove the WHERE clause, it does show all of the records in the subform.....
    1) Have you tried using the "builder" to create your WHERE expression
    2) Is the Data Type of "tblRMAs.cust_nb" the same as that returned by the query statement of "cboCust".
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Quote Originally Posted by ranman256 View Post
    make the continuous form show ALL records.
    then filter via controls:
    So filter the form's records rather than changing the underlying query's criteria & requerying?
    I'm going to give this a shot - but is there any reason the query WHERE method shouldn't work?

  5. #5
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Quote Originally Posted by Bob Fitz View Post
    1) Have you tried using the "builder" to create your WHERE expression
    2) Is the Data Type of "tblRMAs.cust_nb" the same as that returned by the query statement of "cboCust".
    Hi Bob,
    Yes, I built the query expression using the drag-and-drop from tables interface initially, then reviewed the SQL
    They are same data type, both are short text. The cboCust uses that table as its source of options available, as well.

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

Similar Threads

  1. Form not filtering on multiple listboxes
    By PinkLady50 in forum Forms
    Replies: 3
    Last Post: 02-23-2017, 05:03 PM
  2. Replies: 3
    Last Post: 11-27-2014, 06:38 AM
  3. Replies: 5
    Last Post: 07-26-2012, 02:30 PM
  4. Replies: 15
    Last Post: 04-17-2012, 01:42 PM
  5. Replies: 5
    Last Post: 03-12-2012, 02:58 AM

Tags for this Thread

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