I have a main and a sub form that I am working with. I have a date field on the main form that I want to be displayed on the subform without the user having to enter it for each subform line. I have coded the "Oncurrent" event for the subform with:

If Me.Newrecord then
Me.Date=[Forms]![Mainform]![Date]
End If

This obviously works only when a new recored is entered. The date for the first record in the subform is null. What am I missing that I should I do to populate the first record automatically?

What is Date is that a field name? if so you need to change it, Date is a reserved word in Access.

It's not a field name. I was just trying to keep the example simple. The actual field is InvoiceDate.

I have found a way around the problem by using the "beforeupdate" event and the same code. However, I don't think that I should have to do that.

I'm not sure I understand your question. Can't you just put Default Value: Date() for the subform field? All new entries will have today's date (alternatively you can use Now() if you desire greater precision) by default. Lock the field if you want to keep your users from accessing it (no pun intended).

HTH,
David R

David R,

The InvoiceDate field will not be today's date. Invoices are received a week after the Invoice is generated. Therefore, the user has to input the correct InvoiceDate on the form.

I have solved the problem, although it's probably not the best way.

Thanks,

I guess I'm trying to figure why the date has to be in so many places. If it's already on the main form, why put it on the subform? Use Dlookup() if you want to see it there, without actually storing it.

Maybe I'm missing your question. Please post back if I am, maybe someone else can spot the trouble.

HTH,
David R

The main form updates one table and the sub form updates another. They both require the date. There's probably a better way of doing that but other than creating a temporary table to store the values and then appending to the appropriate tables, I couldn't come up with anything as efficient.

The date does NOT need to be stored in the second table. Whenever you need to use the date simply join to the Invoice table. You do not duplicate it. The joining of tables to obtain related information is what "relational" refers to in the phrase Relational Database Management System and is one of the first concepts you need to understand to create effective table structures.

I think I understand that, but the problem is that I have duplicate Invoice Numbers and needed a way to track a particular invoice. Some invoice numbers, depending on the vendor can "roll over" once a month. Without a date, I couldn't determine a way to insure that the invoices are matched. I can have duplicate invoice numbers on a regular basis with hundreds of vendors throughout the year.

If you have a suggestion on a better way to do that, I would appreciate it.