Populating a 2nd combo box from a combo box selection

[forkEP]

I have a form/subform based on querys. The query which the form is based on has 4 tables attached, with all related relationships. I have two combo box's pulling their information from tblInvoiceNumber: "WeekEndingDateShown" and "JobNumber" through the query.

What I need to accomplish is, after selecting the correct "WeekEndingDateShown" from the combo Box, it populates the "JobNumber"'s that correspond to the date selection. I also want to eliminate the multiple instances of a date in the date combo box.

I found a segment of code on the net:
SELECT distinct tblInvoiceNumber.JobNumber FROM tblInvoiceNumber WHERE (((tblInvoiceNumber.WeekEndingDate)=[forms]![frmInvoice]![WeekEndingDate])) UNION select distinct null, null FROM tblInvoiceNumber
ORDER BY tblInvoiceNumber.JobNumber;

While playing with that (had to remove the UNION part), I got it "partially" working, but far from desirable results. If anybody knows how to fix this problem, I would grealy appreciate some assistance.

 

[PHV]

Say your combos are named WeekEndingDateShown and JobNumber.
The RowSource of WeekEndingDateShown may be:
SELECT DISTINCT WeekEndingDate FROM tblInvoiceNumber;

Then, in the AfterUpdate event procedure of WeekEndingDateShown:
cboJobNumber.RowSource = "SELECT JobNumber FROM tblInvoiceNumber WHERE WeekEndingDate=#" & Format(Me!WeekEndingDateShown, "yyyy-mm-dd") & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[forkEP]

Thank you very much for the assistance. After making a tweak or two to my actual setup, your code worked perfectly. Now I get to deal with updating the bookmark. I posted another question concerning that also, if you were interested in that also

http://www.tek-tips.com/viewthread.cfm?qid=1009687