How can I work out the next number ?

R

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 !
 
R

Rick Brandt

Roger said:
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 !
Click to expand...

In the BeforeUpdate event of your form...

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

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
 
R

Rick Brandt

Roger said:
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
Click to expand...

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
 
R

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.
 
R

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
 
R

Rick Brandt

Roger said:
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
Click to expand...

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.
 
R

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.
 
R

Rick Brandt

ruralguy said:
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.
Click to expand...

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.
 
Top