Access problem with incrementing field value entries.

[weightinwildcat]

I have been working on an application for maintaining an inventory. One of the fields in the table has internal company part numbers. The field is a character field, but the entries are integers. The field is not a key field, by the way.

When a new record is added, I have a query run to get the maximum value. The code for calling this is as follows:

longValue = CurrentDb.OpenRecordSet(“SELECT MaxPartNumber from MaxPartNumber”).Collect(0)

The value placed in longValue is then incremented by adding 1, and the result is placed in the text box for the field in the main table.

The problem comes in when a person adds more than one record at a time. The query will run, but it gives me the previous maximum value for the part number. Does anybody have any thoughts on how to solve this problem?

 

[SkipVought]

Rather than adding 1 to an index, do a query to return the MAX() index and add 1 to THAT.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lionelhill]

As a possible nuance on this, I always worry what happens if two different people are using the database at the same moment, both adding a new part. It's unlikely, but the risk is this:
User_1's application queries the database to find the maximum part-number (gets 1234)
User_2's application queries the database to find the maximum part-number (also gets 1234)
User_1's application adds a new part: 1235
User_2's application adds a new part: 1235
And whoops! We've got two parts with the same number.

For this reason, I prefer to make the part-number a unique key so that an attempt to create a new one with the same number will cause an error. I then initialise an Error-Counter at zero, and put an "On Error Goto" before creating the new record, so that User_2's application gets an error, increments its ErrorCounter, and tries again. After a few attempts (Error-Counter > 5), I assume that something more catastrophic has gone wrong, and tell the user it's a disaster, but otherwise, User_2's application sees its error, tries again, and correctly adds a new part 1236.

 

[softhemc]

Many years ago (Access97), I had a similar problem requiring different counts in different tables (each table had an auto-increment pk used for different purposes). To overcome the issue I simply created a one row table with a separate column for each table that required the number. All I did was lock the table, retrieve, increment and save the required value, then unlock the table. Concurrent callers had to wait for the lock to be released, however at its peak the program could theortically have had over 100 concurrent users and no one ever reported any delays in the program as a result of getting this number.