DMAX Function

  • Thread starter Thread starter Michael Stengel via AccessMonster.com
  • Start date Start date
M

Michael Stengel via AccessMonster.com

I have the dmax function working properly on assinging the next available
ID number. Problem I have is with multiple users. I need to be able to add
more then one person to an ID so I can not set the "allow Dups" to no. How
do I prevent auser getting the same ID number someone else has already been
assigned using the DMAX function.
 
Michael said:
I have the dmax function working properly on assinging the next
available ID number. Problem I have is with multiple users. I need to
be able to add more then one person to an ID so I can not set the
"allow Dups" to no. How do I prevent auser getting the same ID number
someone else has already been assigned using the DMAX function.
Click to expand...

What event are you using to retrieve the DMax() value? If you use
BeforeUpdate there is little chance that two users woul ever get the same
value. Any other event or trying to use it in the Default Value will give
you this problem.
 
Do you use the DMAX function in the "beforeupdate" of the form? or the
field? to prevent two users getting dups.
 
Michael said:
Do you use the DMAX function in the "beforeupdate" of the form? or the
field? to prevent two users getting dups.
Click to expand...

The form. Before Update is the only event that ends with a save of the record
so there are only fractions of a second between the assignment of the value and
the record being committed to the table at which point another call to DMax()
will see the value from user 1 so that user 2 gets a different value. All of
the other events have indefinite periods of time between assigment and saving
the record.

The only caveat is that BeforeUpdate can occur many times on a single record so
you need to use an If-Then block to insure that it only assigns the DMax() value
on the first save.

If Nz(Me.IDField, 0) = 0 Then...
 
This still allows me to keep the property of the field to allow dups, when
needed?
 
Michael said:
This still allows me to keep the property of the field to allow dups,
when needed?
Click to expand...

I don't understand the question. Allowing or disallowing dups is a
table-level design issue and has nothing to do with any of this.
 
Back
Top