Date in Form

[molsen]

Hi All

I have created a simple database, with 2 x tables. The first is a 'Week Ending' table, with a dateid key and Week Ending field. I have created several week-ending dates in order for a user to input details of work done the previous week. The second table contains the details of the work.

I have a one to many relationship between the dateid in the first table, and a dateid of the work details table.

I created a query based on the work details table, and created a subform from that. In a main form I linked a combo box to allow a user to select a date and the relevant work details appear in the subform. However the Week Ending field only shows one date, not a list of the dates I created.

Any help would be appreciated!

Thanks

 

[billmeye]

Is the rowsource of the combobox the same as the table you do data entry of work done or the table that contains the several week-ending dates? It sounds like you want it to be the latter.

 

[molsen]

Thanks Bill. Yes, I've set the control source to the table with the Week Ending dates, and the Row Source to the same table, yet I can only get a return of a list of the WeekEndingID, not the week ending itself.

Should I delete the ID field and make the date field itself the key?

Rgds

Mark

 

[billmeye]

The row source of a combobox is independent of the record source for the form(s). It should return whatever is stated in the SQL. Do you want to post your SQL for the combobox?

 

[molsen]

Thanks Again Bill.

I'm one of those self-trained types, who only use the UI to create databases, and maybe a little VB, but no SQL. I used the tool box to create the combo box, which is directly from the table where the dates are stored.

Strangely enough, I can hit the page down key when the cursor is in the Week Ending box, and this goes to the next date, and cascades the data in the subform, but the drop down only displays the first date in the sequence.

 

[molsen]

I've cracked it!

SELECT tblResWeekEnding.[Week Ending/Date] FROM tblResWeekEnding;

simples - thanks Bill - Just you asking that question enabled me to write the code via a query!

Cheers

 

[billmeye]

Very good.

 

[molsen123]

Mmmm. That worked fine, and I have done some further modifications to pretty it up for the user.

When I'm in the master form in the single field showing the week ending date, if I page down, the relevant records for that date are shown. However if I use the drop down box (I've converted it to a combo)selecting a particular date, the records are not shown, and, worst still, if I hit the return key, the date is overwritten and the records.

I clearly need some formal training for this, but if anyone could help me out once again I'd be grateful....

:banghead: