I have a form for which there is a new record for every day of the week. In practice this form is updated only every couple of weeks or so and as there are very often errors on entering the date, I wanted to automate it so that the date field defaults to the last date + 1. Simple I thought.
I've tried everything over the years to no great success, but thought I had finally found the solution. It worked, until at some point it stopped working (I very rarely enter the data myself and I hadn't been told that it was not longer working), but the principle seems sound and I would like to tweak it back to life again.
The set up is:
- A field called Catchdate based on a field in a query with an input mask, formatted as Short Date and with a Default Value of: Iif(Max([CatchDate]) + 1 = Max([MaxDate]),[catchtest]+2,[MaxDate))
- A hidden field called [MaxDate], formatted as Short Date with the Control Source as: DateAdd("d",1,[catchtest])
- A hidden field called [catchtest], formatted as Short Date with the Control Source as: Max([CatchDate])
Effectively the field [catchtest] returns the date of the last record in the table. MaxDate then adds 1 day onto the [Catchtest] date. The default on the Catch Date field provides a formula, which used to work, for updating the Catch Date field by 1 day every record.
Now, however, the [CatchDate] field is correct on opening the form - it equals the MaxDate field - but after the first record the date defaults to 31/12/1899. And I don't know why.
Does anyone have any ideas?