Cant use autonumber

  • Thread starter Thread starter Michael S. Montoya
  • Start date Start date
M

Michael S. Montoya

I have a table that is using InvoiceNumber as the primary key, however, I
can't have it to be autonumber because we don't want a user to back out of
adding a new entry and skip the invoice number. What I do is before update,
assign the invoice number to dmax+1.

This usually works, however, there have been a couple occasions where two
users start the new record at the EXACT same moment and both grab the same
number. Is there a way around this?

Thanks
Michael
 
Michael S. Montoya said:
I have a table that is using InvoiceNumber as the primary key, however, I
can't have it to be autonumber because we don't want a user to back out of
adding a new entry and skip the invoice number. What I do is before update,
assign the invoice number to dmax+1.

This usually works, however, there have been a couple occasions where two
users start the new record at the EXACT same moment and both grab the same
number. Is there a way around this?
Click to expand...

Are you sure you're using BeforeUpdate and not BeforeInsert? BeforeUpdate
is not concerned with when they *start* the record, only with when it is
saved. Also since the record is written to disk immediately after
BeforeUpdate fires it "should" leave only a few microseconds where two
people can grab the same value.

I suppose you could trap for the duplicate key error and increment the
number then try the save again repeating as necessary until successful, but
I have never had to do that using this method.
 
* Create a one-record Table just to hold the next
InvoiceNumber.

* In the Form_BeforeUpdate Event, check that the Form is
on NewRec. If it is on NewRec, open a Recordset from the
above with lock denying access by other users.

* Retrieve the InvoiceNumber for the current Record.

* Update the one-Record Table to next InvoiceNumber.

* Close the Recordset (and set it to Nothing) to release
the lock.

HTH
Van T. Dinh
MVP (Access)
 
Van T. Dinh said:
* Create a one-record Table just to hold the next
InvoiceNumber.

* In the Form_BeforeUpdate Event, check that the Form is
on NewRec. If it is on NewRec, open a Recordset from the
above with lock denying access by other users.

* Retrieve the InvoiceNumber for the current Record.

* Update the one-Record Table to next InvoiceNumber.

* Close the Recordset (and set it to Nothing) to release
the lock.

HTH
Van T. Dinh
MVP (Access)
Click to expand...
 
Nope! The update might fail, after the form_beforeupdate. If the gives up
(ie. does not try again), you now have an extra number!

I'm not convinced that it is possible to solve this problem in any way using
bound forms.

Of course you could solve it using unbound forms, where all the updates
could be wrapped within a single transaction.

HTH,
TC
 
This is a classic problem that occurs in any multiuser database - even the
big commercial ones.

To solve it, you need to do something like this.

(1) Lock the table containing the #s.
(2) Determine the next available #.
(3) Save all updates pertaining to the new #.
(4) Free the table containing the #s.

Step (4) ensures that no >other< user can do step (2), untill the >current<
user has finished step (3). If you let any other user do step (2), before
the current user has finished step (3), you will run the risk of holes, in
the following scenario:

- user 'A' gets next available #123;
- user 'B' is allowed to get next available #124 >before user 'A's changes
have saved<;
- user 'A's changes fail (for whatever reason).

Now the #s go 122, 124, and 123 has disappeared!

The sequence described above is relevant regardless of how many tables are
involved in the process.

I believe (but am happy to be shown otherwise!) that this problem >can not
be solved using normal bound forms<. This is because you need to increment
the # somehow, and you can only do that through code, but there is no way
you can wrap that update into the transaction used by the bound form.

It could be solved, of course, by using an unbound form, where you could
wrap >all< the relevant updates into a single transaction.

HTH,
TC
 
TC,

This is one problem that I have always thought I would run into and to be
honest I was not too sure how to solve it until I read your response to
Michael's problem. Would you be able to give an example of how you would
wrap >all< the relevant updates into a single transaction? I am not too sure
what u mean by this.

Sorry for jumping in Michael (i dont know if this would have been your next
question :-)), but I thought it would be easier to continue on in this
thread instead of starting a new one and totaly confusing everyone.

TIA,

Neil.
 
I just wrote a reply to the thread "I need a routine to generate a unique
number" which addresses this issue. Has been solved many times in the past.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
I normally do all data validations first before the Recordset so there
shouldn't be a problem in saving the Record.

However, the proper way (but a bit tortuous) to do this is to define the rs
as the Module-scope variable. In the Form_BeforeUpdate, (in a loop) create
and lock "Deny-Write" the rs. Get the NextAccountNumber to be used for the
new Record.

When the Record is actually saved into the Table, the Form_AfterInsert Event
(AfterUpdate should be OK provided that NewRec is checked) will happen. In
this Event, update the one-Record Table and then close the recordset to
release the one-Record Table.
 
Hi Neil

Another respondent has pointed you to his own answer. he feels the problem
is solvable (using his answer). I'm not convinced yet, so I'll need to do
some further testing. That will take a few days, then I will post back to
this thread.

As for wrapping multiple updates into a single transaction:

dim ws as workspace
set ws = dbengine(0) ' default workspace.
ws.begintrans ' start the transaction.
- do various updates here -
ws.commit ' commit the transaction.

The purpose of a transaction is to ensure that all the updates permed
therein, are written to the database as a single unit. If anything goes
wrong inside the treansaction, you can "roll it back" using the rollback
nethod, then any of the updates performned so far (in that transaction) are
automatically not written to the database. So a transaction is an "all or
nothing" affair. You can read more about this in online help.

As I say, I'm not convinced that simply locking the recordset solves the
problem 100% in all cases. So I'll do more testing, & post back here in due
course.

Cheers,
TC
 
I normally do all data validations first before the Recordset so there
shouldn't be a problem in saving the Record.
Click to expand...

That doesn't follow. It could still fail on a Jet-enforced restriction that
is done *after* BeforeUpdate has returned successfully.

However, the proper way (but a bit tortuous) to do this is to define the rs
as the Module-scope variable. In the Form_BeforeUpdate, (in a loop) create
and lock "Deny-Write" the rs. Get the NextAccountNumber to be used for the
new Record.

When the Record is actually saved into the Table, the Form_AfterInsert Event
(AfterUpdate should be OK provided that NewRec is checked) will happen. In
this Event, update the one-Record Table and then close the recordset to
release the one-Record Table.
Click to expand...

Nope. What happens if the form record saves, then the PC fails *before*
Form_AfterInsert has fired? Oops: the one-record table does not get updated.
Is that scenario likely? No. Is it *possible*? Yes!

Van, I'm talking about the tiny timing gaps that occur in any software
product. It is very difficult to get code like this working *100% reliably*
in all scenarios. I've been through all of this before, with heavy-duty
commercial multiuser database systems. The same principles apply to any
multiuser database, IMO.

Cheers,
TC
 
I tend to check for all business requirements / validations by code and
hardly use JET-enforced restriction.

Agree that nothing works 100%. Even if you use Transaction, things still
can go wrong.

I use SQL Server 2000 Back-End and I still get inconsistecies sometimes.
SQL Server 2000 has a few facilities to fix these incosistencies.
 
I just wrote a reply to the thread "I need a routine to generate a unique
number" which addresses this issue. Has been solved many times in the
Click to expand...
past.

That code doesn't solve the OP's problem. This was, to allocate next
available #s >with no risk of skipping numbers<.

In your code, the .Update frees the recordset. At that point, you have
irrevocably allocated the next available #, but there is no guarantee that
that number will ever get to the database (in some other new record). So
your code could very easily generate gaps.

The OP >wants no missing numbers<. IMO, there is no way to achive this, in
Access/Jet, using normal bound forms. I thought I was clearly addressing
that issue, in my example, when I said: "Now the #s go 122, 124, and 123 has
disappeared!".

HTH,
TC
 
Yes, I see your point. To virtually guarantee no missing numbers, you would
have to grab the number, write it to the destination table, and then read it
back, to verify that it got there. Only then can you update the source
table. Not impossible, but has to be done carefully. To allow for really
nasty problems - like a hard disk crash - , you probably need a mechanism to
re-synch the source table number with the highest value found in the
destination table. Again not too difficult. You get into the area of
ever-decreasing the risk of something going wrong. At some point you
probably just have to accept the residual risk.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Cheers for your input TC. I will have a look into begintrans and commit in
the help files. Awaiting your response to see if the problem is solvable :-)

Neil.
 
Back
Top