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]

hi,

This forum is not the best place to ask MS Access questions.

forum702
forum705

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

I gues the question is why / how they add more than 1 record at a time?

to solve this you need to get into the design and make it an atomic commit i.e. when data is inserted, it is done 1 row at a time so that your query to fetch the next "number" is accurate

Without knowing how the user can add more than 1 record at a time it is hard to suggest exactly how you would go about this

Can you confirm the process for adding records? is it via a form or direct access to underlying table? This could of course be easily solved by setting the field to be an autonumber - that way you would not have to worry about the number at all

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question