Filter on a form (1 Viewer)

[capogna1]

HI I created combo boxes on my form to select what I want to filter and I used the following:

Sub RequeryForm()
Dim strSQL As String
strSQL = ""

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

If Len(strSQL) = 0 Then
    Me.FilterOn = False
Else
 
    

    Me.Filter = strSQL
    Me.FilterOn = True
End If


End Sub

Private Sub cboReviewer_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub cboTypeOfEncounter_AfterUpdate()

End Sub

Private Sub Form_AfterUpdate()
RequeryForm
End Sub

But it's not filtering, anyone can help?
Is there an easier way? Like from Criteria in the query to get a list to pick from? Trying to make something for the user different from applying filters from the query.
Thanks in advance.

 

[theDBguy]

Consider posting a sample db with test data, if you can.

 

[Gasman]

So what are the bound fields for your combos?, numeric or strings?
You are catering for strings?

Use Debug.Print strSQl to see what you actually have, not what you think you have?

 

[capogna1]

So what are the bound fields for your combos?, numeric or strings?
You are catering for strings?

Use Debug.Print strSQl to see what you actually have, not what you think you have?

Unbound and Row source: SELECT DISTINCT T_Encounter.Type_Of_Encounter FROM T_Encounter ORDER BY T_Encounter.Type_Of_Encounter;

 

[Gasman]

So show the output of the debug.print as I mentioned?, just before you try and use it.
Just noticed that you have no space either side of the AND
Again a debug.print reveals so much more.

 

[capogna1]

Unbound and Row source: SELECT DISTINCT T_Encounter.Type_Of_Encounter FROM T_Encounter ORDER BY T_Encounter.Type_Of_Encounter;

If I do Debug.print it ask to choose a macro

 

[Uncle Gizmo]

This thread may provide inspiration:-

Form Will Not Display All Records

I have a continuous form connected to tblMedia, containing right now vinyl LPs, DVDs, and Blue-Rays. When the form was created there were 280 records for vinyl LPs and the form could be filtered using a combo box for Artist. The combo box can also display blank and all records will show. I have...

www.access-programmers.co.uk

 

[Uncle Gizmo]

I made a "Universal Filter" ... See here:-

Universal Filter - Nifty Access​

search form by field yes / no

hola tengo este formulario de búsqueda, en la tabla tengo un campo sí / no, necesitaría crear un campo combinado más en el que pueda buscar si es "verdadero" o "falso" .... ¿puedes? ¿No sé cómo escribir el código si alguien me ayuda?

www.access-programmers.co.uk

 

[Gasman]

If I do Debug.print it ask to choose a macro

You are meant to say what you want to debug.print?, see post #3

 

[capogna1]

So what are the bound fields for your combos?, numeric or strings?
You are catering for strings?

Use Debug.Print strSQl to see what you actually have, not what you think you have?

Not getting nothing from Debug.Print

 

[Gasman]

You are looking in the immediate window?
You do know how to use debug.print <variable> I hope?

 

[capogna1]

You are looking in the immediate window?
You do know how to use debug.print <variable> I hope?

I Ctrl + G put my cursor in immediate window then F5

 

[capogna1]

I Ctrl + G put my cursor in immediate window then F5

I get this

 

[Gasman]

No.
You put

Debug.Print strSQL

before

If Len(strSQL) = 0 Then
    Me.FilterOn = False

Then you go to the immediate window, and copy and paste the output back here within code tags.

A simple example

and the result

 

[capogna1]

No.
You put

Debug.Print strSQL

before

If Len(strSQL) = 0 Then
    Me.FilterOn = False

Then you go to the immediate window, and copy and paste the output back here within code tags.

A simple example
View attachment 103717

and the result
View attachment 103718

This is what I get:

 

[Gasman]

You have to run that code?
Put a prefix with that variable like
Debug.Print "sql is " & strsql
Then copy and paste the output back here.

 

[Gasman]

Why not just walk through the code by setting a breakpoint and then use F8 to step through line by line, and see where it goes?

 

[capogna1]

Why not just walk through the code by setting a breakpoint and then use F8 to step through line by line, and see where it goes?

Sub RequeryForm()
Dim strSQL As String
strSQL = ""

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 "sql is " & strSQL

If Len(strSQL) = 0 Then
    Me.FilterOn = False
Else
    Me.Filter = strSQL
    Me.FilterOn = True
End If


End Sub

Private Sub cboReviewer_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub cboTypeOfEncounter_AfterUpdate()

End Sub

Private Sub cboTypeOfEncounter_Click()

End Sub

Private Sub Form_AfterUpdate()
RequeryForm
End Sub

This is what it did and nothing happens with F8, if I run it open the macro dialog box

 

[Gasman]

Upload a sample of your DB. We are at post #19 for a simple filter issue.
Normally you would set a breakpoint on the first executable line of code in that sub, then step through with F8. That is how I do it, or F5 to another breakpoint.

 

[capogna1]

Upload a sample of your DB. We are at post #19 for a simple filter issue.
Normally you would set a breakpoint on the first executable line of code in that sub, then step through with F8. That is how I do it, or F5 to another breakpoint.