Link a combo-box to another table

[Rich]

Hi All,

Here's my situation,

I have a form based on a table with a list of clients. There is a
Primary Key called ClientID

I have another table with with only two columns (ClientID and
verified_date) there could be duplicate ClientID's in this table. I
have setup the relationship between these two tables.

I would like to have a combo box in my form which shows all the dates
that match for that ClientID.

I've been fiddling with this for a couple days, but to no avail. I
keep getting a list of clientID's or the entire list of dates.

Any help would be greatly appreciated.

- Rich

 

[Roger Carlson]

This would be easier if you gave the name of your form and table. But
suppose your form was named Form1 and you have a textbox on it called
txtClient which holds the client id for the form. Further suppose your
other table is called ClientList.

In the Row Source of your combo box put the following SQL statement

Select VerifiedDate From ClientList where ClientID = forms!Form1!txtClient.

that will create a list of dates for that client.

Now, in the OnCurrent event of the form, you have to Requery the combo box.
Assuming the combo box is named cboVerifiedDate, the code to requery would
be:

cboVerifiedDate.Requery

The reason you do this is because you want the combo to re-read it's row
source each time you change records.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L