Automate a field

[Maclain]

[Access 2007 automatically puts the = in I think.

Anyhow, this is what i have in the default value field on the Job register form:

=Right(Year("Date"),2) & "/" & Format(Nz(DMax("Right([Job No], 5)","[Job Register and Report Log]","Year([Date Rec]) = " & Year("Date")),0)+1,"00000")

And I get #error in the Job No field..

edit:
I've altered code to the following:

 [FONT=&quot]=Right(Year(Date()),2) & "/" & Format(nz(DMax("right([Job No],5)","[Job Register and Report Log]","Year([Date Rec]) = " & Year(Date())),0)+1,"00000")[/FONT]

and now it updates the field. However when I start entering a new record using the form, the next line automatically fills in the same jonb number, giving me duplicates...

 

[pbaldy]

Sorry, missed this. Looks like you already caught the fact that Access made some bad assumptions about what you meant, and corrected it.

That's a problem with using that as the default value. The current record is not yet saved, so the next new record pulls the same value. I would probably place that value in code, using the before insert event (I can't remember offhand which works better for this type of thing, but it may be the after insert event). That should also get you around the problem of duplicating numbers in a multi-user environment.

 

[Maclain]

Sounds like a plan!

So batman, what should we do?