How can I work out the next number ?

[Roger]

I have Access 2002 on WinXP sp3. In my database I have a table of records
with each record having a number (not the record ID). The numbers start at
31202 and are consecutive from then on. Sometimes the last record is
deleted, but only ever the last record, hence I can’t use the ID to work out
the next number since the IDs are not all there after a record is deleted.



I have a query that looks at the table and gives me the last number (the
highest). How can I put this into a form so that it automatically gives and
saves the last number plus one as the next number to be used in the table of
records !

 

[Rick Brandt]

I have Access 2002 on WinXP sp3. In my database I have a table of
records with each record having a number (not the record ID). The
numbers start at 31202 and are consecutive from then on. Sometimes
the last record is deleted, but only ever the last record, hence I
can’t use the ID to work out the next number since the IDs are not
all there after a record is deleted.


I have a query that looks at the table and gives me the last number
(the highest). How can I put this into a form so that it
automatically gives and saves the last number plus one as the next
number to be used in the table of records !

In the BeforeUpdate event of your form...

If Me.NewRecord Then
Me!FieldName = Nz(DMax("FieldName", "TableName"),0) + 1
End If

 

[Roger]

Thanks for the help. I am having some difficulty getting it to work.

The form is opened in ADD mode and goes to the 1st record. That is okay, and
we can use the navigation buttons to work through the records, no problems
at all. The number comes up as it should with the last record (at the
moment) being number 31229. So the next number should be 31330, but when I
click on the new record navigation button I get 0 as the number.

The Field name is "Number" and it is in the table "Reper". So the line in
the expression is as follows :
Me!Number=Nz(DMax("Number","Reper"),0)+1

Obviously I have done something wrong !!!! ... Ro

 

[Rick Brandt]

Thanks for the help. I am having some difficulty getting it to work.

The form is opened in ADD mode and goes to the 1st record. That is
okay, and we can use the navigation buttons to work through the
records, no problems at all. The number comes up as it should with
the last record (at the moment) being number 31229. So the next
number should be 31330, but when I click on the new record navigation
button I get 0 as the number.
The Field name is "Number" and it is in the table "Reper". So the
line in the expression is as follows :
Me!Number=Nz(DMax("Number","Reper"),0)+1

Obviously I have done something wrong !!!! ... Ro

If you use the BeforeUpdate event as I suggested the value is not calculated
and assigned until you save a new record. The zero you are seeing is almost
certainly the default value for that field and you should remove that. Then
the control will be blank (Null) until you save the record.

 

[Roger]

Yes, you are correct. When I look in the table, sure enough, the number is
there as it should be. Is there any way I can refresh the number field so
that it is also shows the number as soon as it is inserted ? The number
needs to be seen as it is written on the file once the details are in the
computer... thanks .. Roger

 

[Rick Brandt]

Yes, you are correct. When I look in the table, sure enough, the
number is there as it should be. Is there any way I can refresh the
number field so that it is also shows the number as soon as it is
inserted ? The number needs to be seen as it is written on the file
once the details are in the computer... thanks .. Roger

It will show up on the form the moment you save the record. You can either do
that from the menu, use <Shift Enter> or add a button to the form to save
with...

DoCmd.RunCommand acCmdSaveRecord

or

Me.Dirty = False

 

[ruralguy via AccessMonster.com]

You can also put: Me!FieldName = Nz(DMax("FieldName", "TableName"),0) + 1
...in the BeforeInsert event of the form and the "new" number will show up as
soon as the form goes Dirty. It must also be in the BeforeUpdate event as
well to minimize multi-user problems.

 

[Roger]

Great, thanks. yes putting the code in BeforeInsert fixes it and it appears
straightaway. I never understood the difference between before insert and on
current before, now I do !

thanks for the help ... Ro

 

[Rick Brandt]

Great, thanks. yes putting the code in BeforeInsert fixes it and it
appears straightaway. I never understood the difference between
before insert and on current before, now I do !

thanks for the help ... Ro

Note that BeforeInsert is not reliable in multi-user situatiuons and in some
circumstance won't ever fire.

Since BeforeInsert fires at the first keystroke of a new record there can be
an indefinite amount of time between the assignment of the number and saving
the record to disk. During that interval any other user who starts a new
record will be given the same number. Whoever saves first will have no
problem and everyone else will get a duplicate key error.

The ONLY form event that has a commit to disk at the end is BeforeUpdate
which is why it is the best event to use for stuff like this. With
BeforeUpdate there should only be fractions of a second between the
calculation of the next ID and saving the record to disk.

 

[ruralguy via AccessMonster.com]

Which is why I said it must be in both events to minimize multi-user problems.
There is still a potential for duplicates and good programming practice would
dictate code to anticipate and correct the issue in a multi-user situation,
Well stated Rick, as usual.

 

[Rick Brandt]

Which is why I said it must be in both events to minimize multi-user
problems. There is still a potential for duplicates and good
programming practice would dictate code to anticipate and correct the
issue in a multi-user situation, Well stated Rick, as usual.

So it might be one value when they start the record and then upon saving it
could change to a new value? What if you have already quoted the first number
to someone else?

If the only number that can be trusted to be "final" is the second one then I
fail to see any benefit from the first one.