Filter on a form

[Gasman]

So you could even be bothered to amend the code I had already said needed amending?

    If Len(strSQL) > 0 Then
    strSQL = strSQL & "And"
    End If

I placed your code from the Form AfterUpdate event into the afterupdate event of each control
I also added Debug.Print to see what the sql code was.

You had a spelling error for Type of Encounter.
You had quotes around a value for a numeric field

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything

This is the resulting code for what you are trying to do.

Sub RequeryForm()
    Dim strSQL As String

    If Len("" & Me!cboTypeOfEncounter) > 0 Then
        strSQL = "Type_Of_Encounter = '" & Me!cboTypeOfEncounter & "'"
    End If

    If Len("" & Me!cboReviewer) > 0 Then
        If Len(strSQL) > 0 Then
            strSQL = strSQL & " And "
        End If
        strSQL = strSQL & "[Reviewer] = " & Me!cboReviewer
    End If
Debug.Print strSQL
    If Len(strSQL) = 0 Then
        Me.FilterOn = False
    Else
        Me.Filter = strSQL
        Me.FilterOn = True
    End If

End Sub

 

[capogna1]

So you could even be bothered to amend the code I had already said needed amending?

    If Len(strSQL) > 0 Then
    strSQL = strSQL & "And"
    End If

I placed your code from the Form AfterUpdate event into the afterupdate event of each control
I also added Debug.Print to see what the sql code was.

You had a spelling error for Type of Encounter.
You had quotes around a value for a numeric field

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything

This is the resulting code for what you are trying to do.

Sub RequeryForm()
    Dim strSQL As String

    If Len("" & Me!cboTypeOfEncounter) > 0 Then
        strSQL = "Type_Of_Encounter = '" & Me!cboTypeOfEncounter & "'"
    End If

    If Len("" & Me!cboReviewer) > 0 Then
        If Len(strSQL) > 0 Then
            strSQL = strSQL & " And "
        End If
        strSQL = strSQL & "[Reviewer] = " & Me!cboReviewer
    End If
Debug.Print strSQL
    If Len(strSQL) = 0 Then
        Me.FilterOn = False
    Else
        Me.Filter = strSQL
        Me.FilterOn = True
    End If

End Sub

You are amazing man. Thank You so much! Let me try it...

 

[Gasman]

You are amazing man. Thank You so much! Let me try it...

Not really. I just carried out the steps that I advised you to do.

I do not think unbound controls even affect the Form AfterUpdate event (the experts here will advise on that), plus I also think you have to make the form dirty even if it did, which you were not doing, and did not need to do, as the process should be on the afterupdate of each control, I would have thought.?
You also might need to clear those controls and the filter at some time?

See the Debugging Access VBA link in my signature for tips on how to debug code.

 

[capogna1]

Not really. I just carried out the steps that I advised you to do.

I do not think unbound controls even affect the Form AfterUpdate event (the experts here will advise on that), plus I also think you have to make the form dirty even if it did, which you were not doing, and did not need to do, as the process should be on the afterupdate of each control, I would have thought.?
You also might need to clear those controls and the filter at some time?

See the Debugging Access VBA link in my signature for tips on how to debug code.

I can't get this right. it's not working for me.

 

[Gasman]

Well here it is back.

 

[capogna1]

Well here it is back.

TY I got to figure out where I messed up at. I name your file GASMAN!!!

 

[Gasman]

Don't forget to comment out the Debug.Print until you need it again.