Select Case Filter was working but now its not without any changes to the code

[jbeets]

Hello- I have a database that is used for quality reviews. When functioning properly the user should be able to select from 3 comboboxes and the form will populate with records based on the selection.

The problem: The database has been used for 2 months with no issues but now the filter options are not displaying the data in the form. There are 3 comboboxes that the user selects in order to provide them with a record to review.

cboStatus (Pending Review or Completed)
cboEmployee(selects from a list of employees)
cboQuarter (Q1, Q2, Q3, Q4)

When these options are selected the form should fill in some details from the table. When you choose “Completed”, select an Employee, and select either Q1 or Q2 the filters are working and the form fills in with the completed audits based on the selections but when you choose “Pending Review” and select the other filter options the fields on the form that should fill in “flash” like they are going to show records but then it goes away and the fields are left blank.



I have put in msgboxes before the ‘Select Case Me.cboStatus.Value’ and the ‘Select Case Me.cboQuarter.Value’ and cboEmployee to see what responses I am getting in hopes of narrowing down the issue but the msgboxes show exactly as the filters are selected.

I have also tried removing the VBA completely, putting it in a txt file and then putting it back in with the thought that maybe something was corrupt in my code. No change.

I am sort of at a loss now. This database needs to go live July 1st for the organization and I can't figure out what the heck is going on. I attached some screenshots of the Property Sheet as well as the code.

Option Compare Database
Private Sub cboStatus_AfterUpdate()
    SetFilters
End Sub
Private Sub cboEmployee_AfterUpdate()
Me.Requery
MsgBox (cboEmployee.Value)
End Sub
Private Sub cboQuarter_AfterUpdate()
    SetFilters
End Sub
Private Sub SetFilters()
Dim MyFilter
MyFilter = ""
Me.Filter = ""
Me.FilterOn = False
Select Case Me.cboStatus.Value
Case "Pending Review"
    If MyFilter = "" Then
        MyFilter = "Auditor Is Null"
    Else
        MyFilter = MyFilter & " AND Auditor = ''"
    End If
Case "Completed"
    If MyFilter = "" Then
        MyFilter = "DateAudited Is Not Null"
    Else
        MyFilter = MyFilter & " AND DateAudited Is Not Null"
    End If
    
Case Else
End Select
Select Case Me.cboQuarter.Value
Case "Q1"
    If MyFilter = "" Then
        MyFilter = "[AuditName] = 'Q1'"
    Else
        MyFilter = MyFilter & " AND [AuditName] = 'Q1'"
    End If
Case "Q2"
    If MyFilter = "" Then
        MyFilter = "[AuditName] = 'Q2'"
    Else
        MyFilter = MyFilter & " AND [AuditName] = 'Q2'"
    End If
    
Case "Q3"
    If MyFilter = "" Then
        MyFilter = "[AuditName] = 'Q3'"
    Else
        MyFilter = MyFilter & " AND [AuditName] = 'Q3'"
    End If
Case "Q4"
    If MyFilter = "" Then
        MyFilter = "[AuditName] = 'Q4'"
    Else
        MyFilter = MyFilter & " AND [AuditName] = 'Q4'"
    End If

End Select
If Len(MyFilter) > 0 Then
   Me.Filter = MyFilter
   Me.FilterOn = True
Else
    Me.FilterOn = False
End If
Me.Refresh
End Sub

Thank you in advance for any help you can provide!

[June7]

There is no parameter for Auditor.

MyFilter = MyFilter & " AND Auditor = 'need something here'"

BTW, Select Case for Quarter is not necessary.

If Not IsNull(Me.cboQuarter) Then
        MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[AuditName] = '" & Me.cboQuarter & "'"
End If

Every code module should have Option Explicit in header. Can set this as default for new module in VBE > Tools > Options > check Require Variable Declaration.

Sometimes weirdness is corruption that can't be fixed without rebuilding something. Maybe a control or form or something else and importing everything to a new file might be needed. There is even a process to 'decompile' then 'recompile' a db that might help. Google it.

[CJ_London]

there is an awful lot of unnecessary code, in addition to the case statement June mentions. Also not typing variables means they are all variants

Dim MyFilter - will be null so you have to use the next line. If you used Dim MyFilter AS String then its default value is ""
MyFilter = "" - see previous line
Me.Filter = "" - is this really required?
Me.FilterOn = False - is this really required?
Select Case Me.cboStatus.Value
Case "Pending Review"
    If MyFilter = "" Then - it can't be anything else since you've just set it to ""
        MyFilter = "Auditor Is Null"
    Else - not required, it will never be called
        MyFilter = MyFilter & " AND Auditor = ''"
    End If
Case "Completed"
    If MyFilter = "" Then as above
        MyFilter = "DateAudited Is Not Null"
    Else as above
        MyFilter = MyFilter & " AND DateAudited Is Not Null"
    End If
    
Case Else
End Select
...
...


Else
    Me.FilterOn = False - you've already set it to false above, no need to set it again
End If
Me.Refresh - not required - filteron=true will refresh automatically

It may be the answer to your problem is hidden within these comments

[June7]

I knew there was something else I meant to mention.

Also, filter construction code does not even include cboEmployee. I will assume its value is a numeric ID.

Private Sub SetFilters()
Dim MyFilter As String
Select Case Me.cboStatus
    Case "Pending Review"
        MyFilter = "Auditor Is Null"
    Case "Completed"
        MyFilter = "DateAudited Is Not Null"
End Select
If Not IsNull(Me.cboQuarter) Then
    MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[AuditName] = '" & Me.cboQuarter & "'"
End If
If Not IsNull(Me.cboEmployee) Then
    MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[EmployeeID] = " & Me.cboEmployee
End If
Me.Filter = False
Me.Filter = MyFilter
Me.FilterOn = True
End Sub

[jbeets]

Thank you Both for helping me not only fix my code but also taking the time to show me how I can make it more efficient!

I updated my code with the latest provided by June7 and I added the Option Explicit. When I attempt to filter the records I get a Sytax Error "Run-time error 3075 - Sytax error (comma) in the query expression 'Auditor Is Null AND [AuditName]- 'Q2' AND [UserName] = Doe, John'

The Employee field in the table is "UserName" and the control name is cboEmployee. The employee name in the table is formatted as Lastname, Firstname. When I look up this error I found that the error could be caused potentially by the comma in the employee name field... but they didn't show how they fixed the issue. Is this possibly whats causing the issue here?

[June7]

Post your actual attempted code.

Need = sign. Text parameter needs apostrophe delimiters.

Constructed criteria should be like: Auditor Is Null AND [AuditName] = 'Q2' AND [UserName] = 'Doe, John'

[jbeets]

Private Sub SetFilters()
Dim MyFilter As String
Select Case Me.cboStatus
    Case "Pending Review"
        MyFilter = "Auditor Is Null"
    Case "Completed"
        MyFilter = "DateAudited Is Not Null"
End Select
If Not IsNull(Me.cboQuarter) Then
    MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[AuditName] = '" & Me.cboQuarter & "'"
End If
If Not IsNull(Me.cboEmployee) Then
    MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[UserName] = " & Me.cboEmployee
End If
Me.Filter = False
Me.Filter = MyFilter
Me.FilterOn = True
End Sub

[June7]

Since you are using UserName (text field) instead of EmployeeID (number field), need apostrophe delimiters. Same as done for AuditName. Fix the code.

[jbeets]

Thank you June7. The error is gone! But we are back to the form not filling in when I select my filters..... So I guess I will try and rebuild the form and see if that can fix it.

Thank you again for your help

[jbeets]

Alrighty.... I started a new database, brought in the required table and created a new form based on the table. I put in the comboboxes for my filters and reentered all of the code and I am getting the same result. When I select "Completed" in the Status combo the form populates with the first completed audit based on the selection of the other comboboxes but once again, when I select Pending Review and fill in the other filter options the form is left blank.

Any other suggestions on what I can try?

Option Explicit
Private Sub SetFilters()
Dim MyFilter As String
Select Case Me.cboStatus
    Case "Pending Review"
        MyFilter = "Auditor Is Null"
    Case "Completed"
        MyFilter = "DateAudited Is Not Null"
End Select
If Not IsNull(Me.cboQuarter) Then
    MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[AuditName] = '" & Me.cboQuarter & "'"
End If
If Not IsNull(Me.cboEmployee) Then
    MyFilter = MyFilter & IIf(MyFilter = "", "", " AND ") & "[UserName] = '" & Me.cboEmployee & "'"
End If
Me.Filter = False
Me.Filter = MyFilter
Me.FilterOn = True
End Sub

Private Sub cboEmployee_AfterUpdate()
    Me.Requery
End Sub


Private Sub cboQuarter_AfterUpdate()
    Me.Requery
End Sub


Private Sub cboStatus_AfterUpdate()
    SetFilters
End Sub

[June7]

Provide the db for analysis.

[jbeets]

Sorry for the slow reply - I attached an example of the database.....

The filtering in this database appears to work but for the first filter only. If I change names or quarters the filter stops working and the form will be left blank. Sadly, I do not even get this much of a response with the actual database....

Thanks again for your help!

[June7]

Remove WHERE clause from form RecordSource. Just reference table as RecordSource.

SetFilters is not called by cboEmployee event.

[jbeets]

Remove WHERE clause from form RecordSource. Just reference table as RecordSource.

SetFilters is not called by cboEmployee event.

Thank you June7! I made those changes to the sample db and everything works correctly so I went to the actual db and made the same changes but still not getting any records. The thing I can't seem to understand is the filters are working when I change the status to Completed. When Completed is selected all of the other filters are working but then I switch over to Pending Review and I get nothing again....

What would you suggest I do next?

Thanks again for your help this far!

[June7]

I would have to review real database.

Are you sure there are Pending records meeting search criteria?

Have you step debugged?