Combo box that displays Last date but drop down shows all dates

[cdgeer]

OK Let's say I have a form that queries 2 tables. One master table called PROJECT with Project_ID field as the primary key and a date table called DATES with field named Date that is related to the PROJECT table by the Project_ID field as well. I want to have a combo box on the form that displays the Last[Date] from the DATES table where the Project_ID matches PROJECT.Project_ID currently displayed on the form but, when you click the drop down list it shows all the dates that match that same Project_ID.

* Also, I would like the capability to be able to enter in a new date in the combo box and have it populate to the DATES table.

I was able to query the Last(Date) but, can't seem to get it to display correctly. (I usually don't have trouble setting ControlSource / RowSource but this time I'm in over my head.) Thanks for any help!

 

[Andrzejek]

How about:
[tt]
Select Max([Date]) As DateToBeDisplayed
From DATES
WHERE Project_ID = 1234[/tt]

Where 1234 is the "PROJECT.Project_ID currently displayed on the form "

BTW, Date is very bad name for the field in your table. It is a reserved word.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[cdgeer]

Thanks Andrzejek. I can get the Last / Max date but can't get it to show up in the Combobox. I'm not sure what to put in for the Control Source property. It doesn't like when I put in a select statement. I can't seem to put in an expression either.

I don't really use the name "Date" for that field. It's actually [31MPlanDate].

 

[Andrzejek]

You could try [tt]DMax()[/tt] function to populate your combo box instead. More about it here.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.