Limit drop-down field list by another field

[NicoleOB]

I am using MS Access 97 & have a form with two look up fields (Client List and Matter List). They both look up values from a separate table. Each record in the separate table contains a Client Number and Matter number.

The user will select a "Client" number & then I would like the "Matter" drop-down to list only matters for that Client.

Generally - they are 2-5 "matters" per "client". I have read through a lot of posts & found similar questions but still not sure how to do this.

 

[Blorf]

in the row source of your drop down list is a select query of some sort.

Add criteria to your query to say something like like "*" & me.form.[What ever your other drop down list name is] & "*"

ChaZ

Ascii dumb question, get a dumb Ansi

 

[NicoleOB]

I got this to work by using the following in the Row Source field -

SELECT tblClientMatter1.matter, tblClientMatter1.matter FROM tblClientMatter1 WHERE (((tblClientMatter1.client)=forms![tblInvoice]![ClientID]));

This works for the first record - but I need to have it requery for this field with each new record - I think I saw something about this in another post & I'm searching now.

Thanks!

 

[Blorf]

Yes. on the after update event for both of your drop downs, run a macro to requery the other.

Your reqery just by name. You dont have to put forms!blah.Blah, just requery, and the object name.

ChaZ

Ascii dumb question, get a dumb Ansi