Adding a lookup list to further filter form results

[Nick]

Created a form to list outstanding invoices, the datasource is a query called
"invoices due" When I open that form it list all my outstanding invoices, so
far so good.

Now I want to add a drop down list control on the form to further filter the
returned results to show only specific customers.

The form (and query) allready have and display the customer name, so that
data is allready there.

3 things are stumping me.

1) I tried to add a list box pulling the data from the customer name in the
query, but that returned multiple copies of each name in the list box.

2) Also, when I choose a name it did not up date the form.

3) I also want an option in the list to allow me to show all the record.

Really just need a control on the form that lets me choose the paramiter for
"cutomer" in the filter/search. Not sure the easiest way to do this.



Thanks,

Nick

 

[Al Campagna]

Nick,
You really should use a more "unique" value than customer name to filter
your records. Think about using something like a CustomerID instead.
It's very possible that John Smiths exist...

But... we'll work with ex. [CustName] and [cboCustName]
(A "drop down list" is really a Combobox. A Listbox is open at all
times,
and does not "drop down.")

1) ...query, but that returned multiple copies of each name in the list
box.

Use a Totals query behind your list box, and group on CustName, and
make sure you do not include any other fields that would cause CustName to
duplicate.

2) Also, when I choose a name it did not up date the form.

Use the AfterUpdate event of cboCustName to Requery the form.
Me.Requery

3) I also want an option in the list to allow me to show all the record.

Add this [CustName] criteria to the query behind your form.
Like Forms!frmYourFormName!cboCustName & "*"
If you choose a CustName, and Requery the form, only that record
will be displayed.
If you delete the cboCustName value... all records will show.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."