Filter using List box

[raisbecp]

I have an approval form that is based off a query called ID. On the form I have a list box called filter containing four values, Yes, No, Pending, All. I would like a user to be able to change the list box and have the form requery filtering out everything except the records with the approval status equal to the status of the list box. By default it should display pending only and when I choose all, all records should display.

Ex. A manager opens the form and wants to view the reqs that he did not approve. He would move the list box to no and all records that he did not approve would display. (There is a list box with Yes, NO, Pending that the manager chooses for approval status.)

Any help would be appreciated, I have tried many ways and am unable to get them to work.

 

[Fizzio]

You can do this by including criteria on your query that populates the form. It depends how you store the status though. If you have a separate lookup table with the status items in then the following solution will need a little tinkering. If, however, you store the actual text 'Pending, 'Yes', 'No' in each record then the follwing will work (Although the first method is strictly the correct method for data storage - but I digress)

make the listbox 2 column but hide the first column and order the data as a value list eg

Yes;Yes;No;No;Pending;Pending;*;All

then in the query populating the form in the status field, set the criteria to 'Like Forms!NameofForm!NameofListbox or Forms!NameofForm!NameofListbox is null' (the last bit is to ensure that if the listbox is null then it will pull all values)

on the afterupdate of the listbox in the vba code,

me.requery

HTH

 

[Timbo99]

Thanks!.. I've been trying to fix a problem simular to this for a week.. Today was my deadline to have it done and the answer appeared!

Thanks again guys