This is what I entered in the forms BeforeInsert event. It is all red.
What did I do wrong
For one thing, the continuation character is a SPACE followed by an
underscore, and you need to put it on every line that you're carrying
onto a new line. It can be tricky going back and forth from Usenet to
VBA - here's my take on it with all lines fitting both, I hope:
Me!ChildIDExt = Nz(DMax("[ChildIDExt]", _
"[Child]", _
"[ApplicantID]=" & Me![ApplicantID]))+1
The Child Table
ChildIDExt is Long Integer
Default is -100
Remove the Default - leave it blank. You will NEVER have a child
number -100; it's pointless (and gets in the way of the NZ() function)
to provide a default.
And the format is irrelevant in any case, since you're just treating
it as a number.
What is "' or ""' Is it a quote and exclamation together
I made the (mistaken) assumption that you had an applicant name (a
Text field which requires a quote delimiter) rather than a numeric ID
(which must have NO delimiter).
Another problem was that the second argument of the DMax() function
must be a string containing the *name of the table* from which you
want to look up the maximum value. You had what appeared to be two
fieldnames with a period between them - in brackets - which Access
would not know how to handle.
The way the code works is that it uses DMax() to search the table
Child for records with the ApplicantID found on the (currently open)
form. If it finds such records, it returns the MAXimum value of
ChildIDExt - say if you have children 1 and 2 in the table already, it
will return 2. If there IS no record for this applicantID (you're
entering the first child of this applicant), DMax() will return NULL.
The function NZ() returns the value of its argument - the maximum
child number in this case - if that value is not NULL; if it is null,
the NZ() function returns zero.
Finally, you add 1 to the value returned by NZ to get the next
sequential child number. That value is stored in the ChildIDExt
control on the Form.
John W. Vinson[MVP]
