Parent and Child forms

Question

I have created parent and child forms in the past, but for some reason this new one I created will not show the correct data from the child form when I enter a date into the parent form. I have the linked Master and Child fields as "Day" which is the field header in the table. I hope you can read this screen ok.

Many Thanks, Darren

Answer 1

You can change the RowSource query of the combo to sort by date DESCENDING to show the most recent record first. Perhaps even better, if the data entry folks will mainly be working with recent data, put a criterion on the query to only select recent dates:

SELECT DISTINCT [datefield] FROM yourtable WHERE [datefield] > DateAdd("m", -1, Date()) ORDER BY [datefield] DESC;

The DISTINCT will return one row per date; the WHERE clause will show only data from a month ago to now; and the ORDER BY will put the most recent date first.

Answer 2

No image was posted in your message. What are the Recordsource properties of the main form and the subform? If they're queries please post the SQL view of the query; if they're tables post the relevant fieldnames and datatypes. And what are the Master and Child Link Fields?

It's usually not a good idea to use Date/Time datatype fields for linking; a Date is actually a double float number, a count of days and fractions of a day (times) since an arbitrary start point. As such a Date with a Time component is a specific point in time, accurate to a few microseconds (though you can only use down to one second precision); if the values in the mainform and subform are entered independently you have next to zero chance of them matching. You're better off having an Autonumber primary key in the parent table linked to a Long Integer field in the child table, and treat dates as data in a differerent field.

Answer 3

What is the datatype of the field in both tables? The Datepicker only shows when a text control is formatted for a date.

Answer 4

Hi Scott, I changed the name in the table and no change. Also, the date picker isn't showing up on the form.

Answer 5

Day is a reserved word in Access and shouldn't be used for Object names. That could be the source of the problem.