automatically advance date field by one day

[cfljanet]

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?

[Welshgasman]

I would just walk the code.
31/12/1899 is actually 1 in date number, so it is never getting a correct value, but a zero.

Learn to walk your code with breakpoints and F8

[Micron]

After you save a record, check your variable and field values via the immediate window. I suspect that the date being returned is one(1) which IIRC would be represented as 12/31/1899

[cfljanet]

There actually isn't any code in this. Just to text boxes with formulas as a control source and the data field with the default value specified. So I don't think F8 works with that?

[Welshgasman]

You can debug.print the control values in various events?, as you are getting a zero, then adding your 1.

[cfljanet]

So the MaxDate field briefly flashes up 31/12/1899 before correcting itself. The catchtest field updates correctly straight away.

It makes sense that the CatchDate field is basing it's default value on the first step of the MaxDate formula resolution and thus returning 31/12/1899. I'm not sure how to get around this though.

[Welshgasman]

You could also move to a code solution perhaps, easier to debug. Something has changed, and it sounds like it must be the data, if nothing else has changed.
You can also run various commands in the immediate window like DMax() to confirm values.

[Micron]

Note: default value applies to new records only. If unsaved I dare say that this value is null for a bound field. Consider posting a compacted and zipped copy of your db.