Incrementing a number and avoiding duplicates

[Guest]

I'm using Access 2003. I have a very simple data base that several user will
need to use. The database is on a file server and a few user will access this
database.
Basically I need to increment a number (+1, starttng at 500) everytime a
user click on "Submit". The number must be saved on a table so next time a
user opens the database and click on submit, the last number is incremented
(+1). This number is then saved on different table with more information.

What would be the best way to do it ? How can I avoid to have duplicates
numbers ?

The below obvioulsy does not work as numbers can be duplicates (it also save
all numbers which I do not need, I just need the last one) :

Set rstIDLog = db.OpenRecordset("tblIDLog")
rstIDLog.MoveLast
intNewID = rstIDLog("IDProblem") + 1
rstIDLog.AddNew
rstIDLog("IDProblem") = intNewID
rstIDProblem.Update

 

[Keith Wilby]

I'm using Access 2003. I have a very simple data base that several user
will
need to use. The database is on a file server and a few user will access
this
database.
Basically I need to increment a number (+1, starttng at 500) everytime a
user click on "Submit". The number must be saved on a table so next time a
user opens the database and click on submit, the last number is
incremented
(+1). This number is then saved on different table with more information.

What would be the best way to do it ? How can I avoid to have duplicates
numbers ?

You could have something like this in a text box's default value property:

Nz(DMax("FieldName", "tblTableName")) + 1

where "FieldName" is the name of the field and "tblTableName" is the name of
the table where "FieldName" is contained. You wouldn't even need a button
if you used this method. If you want it to start at 500 then the easiest
way would be to have a dummy record where the value is 499.

Incidentally, it's considered to be good practice by a lot of developers to
split a database where there will be more than one user. Check the help for
"split database".

HTH - Keith.
www.keithwilby.com

 

[Keith Wilby]

Oh, nearly forgot, you prevent duplicates at table level.

 

[Guest]

In a multiuser environment, there are at least three ways to do this. Which
way you choose will depend on how your form works and what you want to happen.

First way is to create the number when the user selects a new record. Then
in the Before Update event of the form, check to make sure another user has
not already saved a record with the number. If they have, then create a new
number or alert the user to take some action.

The second way is to wait until all other data in the record has been
entered and create the number in the After Update event. In reality, it is
still possible that two users could collide, so the check for a new number is
necessary.

The other way is to get the next number and immediatley create the record
before any data entry starts. This way is the least likely to get a
collision; however, the danger is that if a user cancels the update you will
either have a record that is empty except for the number or, if you delete
the record, a gap in numbering.


Now, I see you had another post with a way to increment the number. I think
I would do something like this:

Me.NextNumber = Nz(DMax("[MyCounterField]", "MyTable"), 499) + 1

This way, you don't need a dummy record. The very first record created will
get a null value from the DMax. The Nz will return 499 and you will add 1 to
that creating 500 as the first number.

 

[Keith Wilby]

Now, I see you had another post with a way to increment the number. I
think
I would do something like this:

Me.NextNumber = Nz(DMax("[MyCounterField]", "MyTable"), 499) + 1

This way, you don't need a dummy record. The very first record created
will
get a null value from the DMax. The Nz will return 499 and you will add 1
to
that creating 500 as the first number.

Nice touch, thanks Klatuu

Keith.