Filter Command Button

[StephenB]

This should be simple, but I can't figure this out and my searches don't come up with anything similar.

I'd like to place a command button on a form that filters for records in which a field is not null. I know I can go to filter by form and enter Like "*". But the users are nurses with little access experience and I'd rather not put them through that, so I'd rather use a command button to display these records.

I'm also considering displaying the form the opposite way: Having the form open filtered with the records in which the field is populated are displayed and then clicking a command button to display all records unfiltered.

I'd rather not use different forms as the users will have to toggle back and forth.

Also, the form triggers and update query. The code for this is on the on_open event. If I open the form filtered, there should be no reason the update query doesn't update all the records (not just the filtered records), is there? I don't see any reason why it should, but I'd like more experience opinions.

TIA

 

[wh00t]

try this

create a button (make sure you have the wizard off)

then place this code in the onclick event (change field name to which field you are filtering by and the form name to the form name you are using)

Forms!FormName.Filter = "FieldName > ' '"
Forms!FormName.FilterOn = True



EDIT - if you wish to run this filter when the form is loaded, insert the code in the onload event for the form.

the filter on the form will not effect the query in any way, the query will update all records, not just the filtered ones.


HTH

 

[StephenB]

Thanks for your reponse.

I'm getting a "compile error: Expected:="

Any suggestions on what I can change?

 

[wh00t]

not entirely sure, as I tested the above and it worked OK

don't mean to sound sarcastic but have you put

Forms!FormName.Filter = "FieldName > ' '"
as in = "FieldName > '(single quote) Space '(single Quote)"(double Quote)

 

[StephenB]

Yep. In fact I copied and pasted your code in and then pasted in the names of the form and field.

 

[wh00t]

which version of access are you using? I tried this on Access 97, that me cause a difference.

If you are using Access 2k I will not be able to test until I get home, as my work is too cheap to upgrade

 

[StephenB]

It may have to do with the name of the form. I'm piggy backing on someone else's db and they're using a naming convention I don't agree with, but went along with for the sake of consistancy. They named the forms with spaces in between the words. When compiling, the error pops up and the defaults to and highlights the first word after the first space in the form name.

Think that may have something to do with it?

 

[StephenB]

I'm on 97 also.

 

[wh00t]

OK try

Forms![Form Name].Filter = "FieldName > ' '"

if the field has a space in then it would be [Field Name]

Hopefully that will sort it

 

[StephenB]

I had tried that also, still get and error.

 

[StephenB]

I'm getting Run Time Error 2448 "you can't assign a value to this object" with the brackets.

 

[wh00t]

do you know if it's stopping on form name or field name?

if it's the field name, are you using the fields Name or the fields Control Source in the code?
if you are using the fields control source, try using field name, which you can change to anything (checking references to it in the rest of the code) eg to field1 then the code would be "field1 > ' '"

 

[StephenB]

I'm using the field name as opposed to the control source name.

This is what I have now:

Forms![ISO Referral form All].Filter = "[PhysiciansFullName] = > ' '"
Forms![ISO Referral form All].FilterOn = True

And I'm getting Run Time Error 2448. When debug, it highlight the whole first line yellow. I've copied and pasted the names of the form and field to rule out spelling errors.

 

[wh00t]

as the field name has no spaces, try the field name without brackets

 

[StephenB]

Tried that too. I was getting the same run time error, highlighting the first line in debug. Quirkey, huh?

 

[Rich]

Add this to a command button
DoCmd.ApplyFilter "", "[MyField]>' ' "
Note there are two single quotes after >

 

[StephenB]

With this I get a message box prompting me to enter a parameter value for the field. If I click OK without entering anything, my form goes blank. If I enter any data (tried *, fragments of names, complete names and then copied and pasted a known name) the form goes to a filtered status but still displays all records. If I hit cancel, I get run time error 2501: the apply filter action was canceled.

I even tried fooling around with the parameters in the code. For example I tried = 'John Doe'". Nothing I tried worked.

 

[Rich]

If your getting a parameter prompt then access can't find the field, use the code builder on an unbound textbox to get the field name.

 

[StephenB]

Bingo! I put in the control source instead of the name. Thank you both, Wh00t and Rich.