Apply Filter Command Button (1 Viewer)

[falcondeer]

Hi

Can somebody help me please

I added a command button to search for a specific record using ApplyFilter. It is working fine but I discovered when I click on the search command button when the text box is empty, it gives me a specific record.

why I don't know.

 

[arnelgp]

what is your code on applying the filter?

 

[falcondeer]

what is your code on applying the filter?

[P_MRN] Like "*" & [Forms]![tblPatient]![TextSearch] & "*"

 

[arnelgp]

you need to pass to Nz() function your textbox:

[P_MRN] Like "*" & Nz([Forms]![tblPatient]![TextSearch], "$@$@$@$@$") & "*"

 

[theDBguy]

Hi

Can somebody help me please

I added a command button to search for a specific record using ApplyFilter. It is working fine but I discovered when I click on the search command button when the text box is empty, it gives me a specific record.

why I don't know.

Why would you click on the search button when the box is empty? What would you like to happen then?

 

[falcondeer]

Why would you click on the search button when the box is empty? What would you like to happen then?

It is confusing to the user I guess.

 

[falcondeer]

you need to pass to Nz() function your textbox:

[P_MRN] Like "*" & Nz([Forms]![tblPatient]![TextSearch], "$@$@$@$@$") & "*"

Thanks, but it is giving the same result.

 

[arnelgp]

can you upload a striped version of your db.

 

[falcondeer]

can you upload a striped version of your db.

What do you mean by striped, my DB is splitted to be used by many users.

 

[falcondeer]

It is confusing to the user I guess.

and normally, nothing should appear when you search empty.

Second question if you don't mind,

What if I want a pop up message to appear if the patient is not found saying "Sorry the patient is not found".

Thanks

 

[arnelgp]

check first if the [Forms]![tblPatient]![TextSearch] is empty.
if empty just go to New record (to show blank).
otherwise display the filter:

If Len(Trim$([Forms]![tblPatient]![TextSearch] & "")) = 0 Then
    'goto new record
    docmd.GoToRecord,,acNewRec
Else
    'check if Any record will return
    If DCount("1", "yourTable", "[P_MRN] Like *" & [Forms]![tblPatient]![TextSearch] & "*") > 0 Then
        'apply the filter
        docmd.ApplyFilter , "[P_MRN] Like *" & [Forms]![tblPatient]![TextSearch] & "*"
    Else
        Msgbox "Sorry patient not found."
    End If
END IF

 

[falcondeer]

check first if the [Forms]![tblPatient]![TextSearch] is empty.
if empty just go to New record (to show blank).
otherwise display the filter:

If Len(Trim$([Forms]![tblPatient]![TextSearch] & "")) = 0 Then
    'goto new record
    docmd.GoToRecord,,acNewRec
Else
    'check if Any record will return
    If DCount("1", "yourTable", "[P_MRN] Like *" & [Forms]![tblPatient]![TextSearch] & "*") > 0 Then
        'apply the filter
        docmd.ApplyFilter , "[P_MRN] Like *" & [Forms]![tblPatient]![TextSearch] & "*"
    Else
        Msgbox "Sorry patient not found."
    End If
END IF

How can I enter this code while I am using the Macro builder not the Code Builder !!!

 

[arnelgp]

here is a sample with Macro.

 

[falcondeer]

here is a sample with Macro.

Oh my God, Thank you very much.