Requery form data

[Roni Sutton]

I have a form that has a picklist which is pre-filled based on user login upon open. I want the data on the form filtered by that field. If the user chooses another entry from the pick list, I want the form to requery and filter by the new value. I have put the following code into my On Update property of the picklist: forms!frmName.Requery. I have the following in the filter property of the form: =[picklistname]. However, it doesn't seem to be working. The records are not filtered at all. I tried basing the form on a query instead of the table with the criteria for that field as forms!frmName!picklistname. However, that seems like a circular reference to me and it doesn't work anyway. When the user chooses a different item from the pick list, the records change, but the pick list doesn't change. Can anyone offer some advice?

 

[Chris RR]

First of all, make sure that you have the form's "Allow Filters" property set to Yes.

Usually, I base my forms on a query, and I don't make any reference to the filter at all in the query itself. Often I use these queries for other things, like reports, so I like to keep 'em clean.

Then I build the filter in code, in the AfterUpdate event. I call the filter "theFilter" because I have no imagination:
Dim theFilter as variant
Dim strDept_ID as String
strDept_ID = me.cboDept_ID
theFilter = "[Dept_ID] = '" & strDept_ID & "'"
DoCmd.ApplyFilter , theFilter

If you want to give the user a button that will show everything in the table, the code is:

DoCmd.ShowAllRecords

 

[Roni Sutton]

By jove, I think that'll do it! Thanks so much, Chris!

Roni