Autonumber duplicate

  • Thread starter Thread starter Zanstemic
  • Start date Start date
Z

Zanstemic

=Nz(DMax(" [CPSAutoNumber]","Attendees","[RegistrationDate]
=Date()"),0)+1
Click to expand...

The configuration is a split database with MDE Front Ends for data entry.

The users select a data entry button that Opens a Form and goes to Record
New using a macro.

The issue is when multple users are going to enter at the same time. Whoever
gets there first, recieves the number and the next person gets the same
number which errors out when saving.

I'm anticipating needing to setup and Event Procedure to check the number
first to make sure it increments as a unique number.

Any suggestions on how to best approach this code and where to place the
code (in the button on click or in the form on open or etc.)?

Thanks in advance for all the help and guidance.
 
As I'm getting more into this, I'm starting to think that the best event
procedure would be in the "AutoCaseNumber" field to check and see if the
number already exists, than create a new record which will increment the
number again. If the number does not exist, it can Save the record and lock
in the number.

What I'm looking for is guidance with the Event Procedure to check for the
AutoNumber to see if it exists and saving the record if it does or creating a
new record if it does'nt.

Or a recommendation on how to best approach the problem.

Zanstemic said:
=Nz(DMax(" [CPSAutoNumber]","Attendees","[RegistrationDate]
=Date()"),0)+1
Click to expand...

The configuration is a split database with MDE Front Ends for data entry.

The users select a data entry button that Opens a Form and goes to Record
New using a macro.

The issue is when multple users are going to enter at the same time. Whoever
gets there first, recieves the number and the next person gets the same
number which errors out when saving.

I'm anticipating needing to setup and Event Procedure to check the number
first to make sure it increments as a unique number.

Any suggestions on how to best approach this code and where to place the
code (in the button on click or in the form on open or etc.)?

Thanks in advance for all the help and guidance.
Click to expand...
 
Zanstemic said:
What I'm looking for is guidance with the Event Procedure to check for the
AutoNumber to see if it exists and saving the record if it does or creating a
new record if it does'nt.
Click to expand...

The BeforeInsert event.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Here is the event added to the BeforeInsert

Private Sub Form_BeforeInsert(Cancel As Integer)
DoCmd.Save
End Sub

I've also tried this in On Open, On Load, On Active, etc. It does not seem
to perform a save until a second field has data entered.

I'm new to Access and VB. The intention was to have the form save on opening
and locking in the "Increment Number Code" described above.

Any help is appreciated.
 
Back
Top