Form with Multiple Filters

1

123dstreet

Registered User.
Local time
Today, 07:19
Joined
Apr 14, 2010
Messages
122
Hi All! I have a form based on a query, it is a continuous form that shows every record from the query. The form shows: Serial #, Order Date, Part #, and Repaired(yes/no). I would like to create multiple filters so the user can:

1. Choose a time period based on Order Date (Pick 2 dates)
2. Select their desired part # from a Combo Box
3. Filter those parts that have been repaired.

Basically just implementing one filter after the next without the first filter reseting. If anyone could give me hand with the VBA or anything on this it would be greatly appreciated!! Thanks!
 
You would want something like this code from this sample of mine which shows you how to use some or all of a set of form's criteria to open a report. But in your case you would want to change it so that the Where Clause is used to set the Me.Filter part. Then you just have the function or sub run in the after update event of each control.

Code: 
Private Sub cmdGenerateReport_Click()
    On Error GoTo Err_cmdGenerateReport_Click

    Dim stDocName As String
    Dim stWhere As String
    Dim stDates As String
    Dim blnTrim As Boolean
    
        If Not IsNull(Me.cboSelectName) Then
            stWhere = "[NamesID]=" & Me.cboSelectName & " And "
            blnTrim = True
        End If

        If Not IsNull(Me.cboSelectCity) Then
            stWhere = stWhere & "[CityID]=" & Me.cboSelectCity & " And "
            blnTrim = True
        End If

        If IsNull(Me.txtFrom) And Me.txtFrom = "" Then
            If Not IsNull(Me.txtTo) And Me.txtTo <> "" Then
                stWhere = stWhere & "[TravelDate]  <=" & Me.txtTo & "#"
                blnTrim = False
            End If
        Else
            If IsNull(Me.txtTo) And Me.txtTo = "" Then
                If Not IsNull(Me.txtFrom) And Me.txtFrom <> "" Then
                    stWhere = stWhere & "[TravelDate]>=" & Me.txtFrom
                    blnTrim = False
                End If
            Else
                If (Not IsNull(Me.txtFrom) And Me.txtFrom <> "") And (Not IsNull(Me.txtTo) Or Me.txtTo <> "") Then
                stWhere = stWhere & "[TravelDate] Between #" & Me.txtFrom & "# And #" & Me.txtTo & "#"
                blnTrim = False
                End If
            End If
        End If

        If blnTrim Then
            stWhere = Left(stWhere, Len(stWhere) - 5)
        End If
    stDocName = "rptTipsLog"
    DoCmd.OpenReport stDocName, acPreview, , stWhere
Exit_cmdGenerateReport_Click:
    Exit Sub

Err_cmdGenerateReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdGenerateReport_Click

End Sub

Again, this code is just for opening a report based on selected criteria. See if you can modify it for your use.
 
Thanks for your reply Bob! I am going to try this out instead of trying to filter on the form, that makes more sense. For the Boolean, would you recommend that I use a Check Box or an Option Button? or does it make a difference? Thanks again
 
Ok so it somewhat works. When I click the report button it asks me to enter parameter value for the [Part] combo box. And when I click the Repair (Checkbox) it doesn't filter for records that are repaired. When I just have the start and end dates and leave the combo and the boolean blank, the report works fine.. What do you suggest I am missing?? Thanks!!
 
123dstreet said:
Ok so it somewhat works. When I click the report button it asks me to enter parameter value for the [Part] combo box. And when I click the Repair (Checkbox) it doesn't filter for records that are repaired. When I just have the start and end dates and leave the combo and the boolean blank, the report works fine.. What do you suggest I am missing?? Thanks!!
Click to expand...

I wouldn't know without seeing your code. Remember, the way I had it in the sample, it bypasses parts if there is no entry. You need to build up the where clause by doing that. What does your code currently look like?
 
Code: 
Private Sub QA_Report_Click()
    On Error GoTo Err_QA_Report_Click
    
    Dim stDocName As String
    Dim stWhere As String
    Dim stDates As String
    Dim blnRepair As Boolean
    
        If Not IsNull(Me.Combo44) Then
            stWhere = "[Part]=" & Me.Combo44 & " And"
            blnRepair = True
        End If
        
        If IsNull(Me.Text22) And Me.Text22 = "" Then
            If Not IsNull(Me.Text24) And Me.Text24 <> "" Then
                stWhere = stWhere & "[Cus Order Date] <=" & Me.Text24 & "#"
                blnRepair = False
            End If
        Else
            If IsNull(Me.Text24) And Me.Text24 = "" Then
                If Not IsNull(Me.Text22) And Me.Text22 <> "" Then
                    stWhere = stWhere & "[Cus Order Date]>=" & Me.Text22
                    blnRepair = False
                End If
            Else
                If (Not IsNull(Me.Text22) And Me.Text22 <> "") And (Not IsNull(Me.Text24) Or Me.Text24 <> "") Then
                    stWhere = stWhere & "[Cus Order Date] Between #" & Me.Text22 & "# And #" & Me.Text24 & "#"
                    blnRepair = False
                    End If
                End If
            End If
            
            If blnRepair Then
                stWhere = Left(stWhere, Len(stWhere) - 5)
            End If
        stDocName = "QA Report"
        DoCmd.OpenReport stDocName, acViewPreview, , stWhere
Exit_QA_Report_Click:
    Exit Sub
Err_QA_Report_Click:
    MsgBox Err.Description
    Resume Exit_QA_Report_Click
End Sub

[Part] text22 text24 combo44 and the Repair Checkbox are all UNBOUND, does that make a difference?
 
Okay, first of all I think you took the blnRepair from a part that is crucial to this function. What field does blnRepair supposed to correspond to on the report?

The use in the sample function used it to flag if something was used and therefore to trim the extra AND off of the code at the end if it was used.
 
Each record in the form has a checkbox called "Repair". I inserted a Checkbox in the Form Header called "Repaired". When that checkbox on the form header is checked, I want the report to show ONLY those records that have their "Repair" box checked. On the report I added a checkbox and its data source is "Repair" from the main form.
 
Okay, let's see (I made a bit of a modification to the code due to things I've learned since I put up that sample. I'll need to go revise that sample to make it better):

Code: 
Private Sub QA_Report_Click()
    On Error GoTo Err_QA_Report_Click
    
    Dim stDocName As String
    Dim stWhere As String
    Dim stDates As String
    Dim blnRepair As Boolean

    
        If Not IsNull(Me.Combo44) Then
            stWhere = "[Part]=" & Chr(34) & Me.Combo44 & Chr(34) & " AND "
        End If
        
        If Len(Me.Text22 & "") = 0 Then
            If Len(Me.Text24 & "") > 0 Then
                stWhere = stWhere & "[Cus Order Date] <=" & Me.Text24 & "# AND "
            End If
        Else
            If Len(Me.Text24 & "") = 0 Then
                If Len(Me.Text22 & "") > 0 Then
                    stWhere = stWhere & "[Cus Order Date]>=#" & Me.Text22 & # AND "
                End If
            Else
                If Len(Me.Text22 & "") > 0 And Len(Me.Text24 & "") > 0 Then
                    stWhere = stWhere & "[Cus Order Date] Between #" & Me.Text22 & "# And #" & Me.Text24 & "# AND "
                    blnRepair = False
                    End If
                End If
            End If
            
            If Me.Repair Then
               strWhere = strWhere & "[Repair]= True"
            End If
           
            If Right(strWhere, 5) = " AND " Then
                stWhere = Left(stWhere, Len(stWhere) - 5)
            End If

        stDocName = "QA Report"

        DoCmd.OpenReport stDocName, acViewPreview, , stWhere

Exit_QA_Report_Click:
    Exit Sub

Err_QA_Report_Click:
    MsgBox Err.Description
    Resume Exit_QA_Report_Click
End Sub
 
We're getting there! so when both date fields are filled in and a part is selected, the filter works perfectly. When One or Both of the date fields are Blank, a syntax error comes up "missing operator in query expression..." And the Repair Checkbox still has no effect on anything.
 
123dstreet said:
We're getting there! so when both date fields are filled in and a part is selected, the filter works perfectly. When One or Both of the date fields are Blank, a syntax error comes up "missing operator in query expression..." And the Repair Checkbox still has no effect on anything.
Click to expand...

Can you upload a copy of the database but with bogus data (no real data)?
 
No, unfortunately I don't have permission to send this database out.
 
It just keeps telling me there is a syntax error in the code...... "Syntax Error(missing operator) in query expression '[Part] = "150-AD220-NP" AND [Cus Order Date] < = # July 8, 2010# AND ' .....
 
123dstreet said:
No, unfortunately I don't have permission to send this database out.
Click to expand...

Bummer, afraid I can't help a whole lot more without seeing it.

So, first of all - try this - Add a

Debug.Print strWhere

in the code just before where the report is supposed to open. Put in the criteria where you get the error and run it. Then copy and paste what comes out in the IMMEDIATE WINDOW in the VBA area here so I can analyze the string and see if I can't spot the syntax error.
 
I'm not quite sure I understand exactly what to do here.. ?
 
boblarson said:
Bummer, afraid I can't help a whole lot more without seeing it.

So, first of all - try this - Add a

Debug.Print strWhere

in the code just before where the report is supposed to open. Put in the criteria where you get the error and run it. Then copy and paste what comes out in the IMMEDIATE WINDOW in the VBA area here so I can analyze the string and see if I can't spot the syntax error.
Click to expand...


The red is what I'm not sure of.
 
123dstreet said:
I'm not quite sure I understand exactly what to do here.. ?
Click to expand...

Okay, in the code:
Code: 
        stDocName = "QA Report"

        DoCmd.OpenReport stDocName, acViewPreview, , stWhere

Add this part in red:

Code: 
        stDocName = "QA Report"
[COLOR="Red"][B]        Debug.Print strWhere[/B][/COLOR]
        DoCmd.OpenReport stDocName, acViewPreview, , stWhere

Then run the code by selecting the criteria, etc. and do it so that it gives you that error you have described.

Last, go to the Immediate Window:

attachment.php
 

Attachments

  • immwin.png
    immwin.png
    57.4 KB · Views: 159
When there error occurs I do not get the Debug pop up, it just has the error syntax and an OK button. It says:


'[Part] = "150-AD220-NP" AND [Cus Order Date] < = # July 8, 2010# AND '.

I don't know how else to get the error message to you.
 
123dstreet said:
When there error occurs I do not get the Debug pop up, it just has the error syntax and an OK button. It says:


'[Part] = "150-AD220-NP" AND [Cus Order Date] < = # July 8, 2010# AND '.

I don't know how else to get the error message to you.
Click to expand...

You shouldn't get the debug popup. Take a good look at the screen shot I provided. if you put the Debug.Print strWhere in the code like I said, you should get an output in the IMMEDIATE WINDOW. Do you see (in the screenshot) what the Immediate Window is? Once you run the code by clicking the button and then you can click OK on your error message, you go to the Immediate Window and copy the text that is in the Immediate Window and then post it here. I want to see the ENTIRE where clause as it has been created. Not just the part from the error message.

So, again - COPY THE TEXT from the IMMEDIATE WINDOW and paste it here.
 
You must log in or register to reply here.

Similar threads

A
Search box to find parts to add to job instead of a combo box
2 3
Replies
41
Views
1,637
Gasman
Gasman
MattBaldry
Solved Form Filter in VBA
Replies
15
Views
812
MattBaldry
MattBaldry
K
Filter with query
Replies
4
Views
492
Ken Sheridan
Ken Sheridan
C
Invalid use of Me.
Replies
8
Views
677
cheekybuddha
cheekybuddha
A
Filtering on a crosstab queried subform
Replies
7
Views
657
DHookom
DHookom

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom