Cant use autonumber

[Van T. Dinh]

* 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)

 

[TC]

* 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)

 

[TC]

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

 

[Van T. Dinh]

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.

 

[TC]

I normally do all data validations first before the Recordset so there
shouldn't be a problem in saving the Record.

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.

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

 

[Van T. Dinh]

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.