Create transaction number for multi-user front end (1 Viewer)

MAAS8000

Registered User.
Local time
Today, 03:35
Joined
Apr 3, 2018
Messages
38
I have a system with a back end and and multi front end.
many user use it in the same time. i am using the below function to create transaction number.
the problem i am facing that if two user hit the button save in the same second. they will take the same number. one of them will be saved the other will have a problem
my front end is in accde format
any suggestions??

Public Sub CreatTransNum()
txtTransNum.Value = Nz(DMax("TransNum", "[TblSavedPrepSummary]")) + 1
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:35
Joined
May 7, 2009
Messages
19,169
you'll need to Re-check the Number again before saving to make sure
nobody saved the same number as you have.
if there is already a number then get new number and use that:

'on your form
private sub form_beforeUpdate(Cancel as integer)
if me.NewRecord Then
me!txtTransNumber = ReCheckNumber(Me!txtTransNumber)
End If
end sub

'on your form
private sub form_BeforeInsert(Cancel As Integer)
Me!txtTransNumber = CreateTransNum
end sub

'on a Module
Public Function CreateTransNum() As Long
CreateTransNum = Nz(DMax("TransNum", "[TblSavedPrepSummary]")) + 1
End Sub

'on a Module
public function RecheckNumber(byval num As Long) As Long
Do Until DCount("1", "TblSavedPrepSummary","TransNum = " & num) = 0
num = CreateTransNum()
Loop
RecheckNumber = num
end function
 
Last edited:

MAAS8000

Registered User.
Local time
Today, 03:35
Joined
Apr 3, 2018
Messages
38
is there another way for creating transaction number rather than this function (Dmax) from the master table
 

MAAS8000

Registered User.
Local time
Today, 03:35
Joined
Apr 3, 2018
Messages
38
my system is like a point of sale
the user select the needed items then press "save"
In the database I have two tables one detail and the other is summary of the transaction
when press save the VBA code add the items in the detail table and create the transaction number according to the above function
then VBA add a line in the summary table depending on the transaction number created before
If I depend on auto number, this will not solve the problem
Mr. arnelgp solution I think is good and will solve it but i can't know how to insert it
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:35
Joined
Sep 21, 2011
Messages
14,038
Why is that?
He has shown you which events?
 

plog

Banishment Pending
Local time
Today, 05:35
Joined
May 11, 2011
Messages
11,611
If I depend on auto number, this will not solve the problem

What problem? You explained how you envisioned your forms to work but that neither presented a problem nor explained why an autonumber will not work.

What issue requires you to build transaction numbers as you envision?
 

MAAS8000

Registered User.
Local time
Today, 03:35
Joined
Apr 3, 2018
Messages
38
What problem? You explained how you envisioned your forms to work but that neither presented a problem nor explained why an autonumber will not work.

What issue requires you to build transaction numbers as you envision?
The problem in the first post
" if two user hit the button save in the same second. they will take the same number. one of them will be saved the other will have a problem"
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:35
Joined
Feb 28, 2001
Messages
26,996
Actually, that will not happen. Autonumbers are designed such that you won't get duplicates even on a shared back-end file. The issue will be that as long as you don't force Pessimistic Locking on transactions in these tables, you won't have a true simultaneous number collision. At least, I have never seen one in about 20 years.

I have sometimes used autonumbers as a validation number since the user neither knows nor cares about the number's origin, and a validation number doesn't need to be contiguously numbered.

The only reason you might need these special contiguous numbers is if an auditor required it. No other person would know or care.
 

MAAS8000

Registered User.
Local time
Today, 03:35
Joined
Apr 3, 2018
Messages
38
Actually, that will not happen. Autonumbers are designed such that you won't get duplicates even on a shared back-end file. The issue will be that as long as you don't force Pessimistic Locking on transactions in these tables, you won't have a true simultaneous number collision. At least, I have never seen one in about 20 years.

I have sometimes used autonumbers as a validation number since the user neither knows nor cares about the number's origin, and a validation number doesn't need to be contiguously numbered.

The only reason you might need these special contiguous numbers is if an auditor required it. No other person would know or care.
How I can enforce Pessimistic Locking?
 

plog

Banishment Pending
Local time
Today, 05:35
Joined
May 11, 2011
Messages
11,611
if two user hit the button save in the same second. they will take the same number. one of them will be saved the other will have a problem"

Sounds like you're making the case for using autonumbers. Further, in Access you don't need a 'Save' button. The record is created upon user entering data.

As for pessimistic locking, that's a different issue and involves existing records, not assigning numbers to new records. Here's a link to how Acces handles that:

 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:35
Joined
Feb 28, 2001
Messages
26,996
No, you DON'T want that. With Pessimistic locking you would guarantee file-lock collisions that would escalate as more users were active. With OPTIMISTIC locking, the database locks the record only long enough to do a SELECT query followed by an UPDATE query back-to-back in a way to update the autonumber's "seed value" in the table definition. Pessimistic locking would make that lock last too long.

In the properties of queries, there is a locking type property that allows you to select Pessimistic, Optimistic, or NO locks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:35
Joined
Feb 19, 2002
Messages
42,970
you are doing this backwards. The Order record needs to be created and saved first. Then the detail records are linked to it. Autonumbers will be fine for each table. If this is a POS application, the Order table contains the date and time of the transaction. The ID of the clerk doing the order entry and the customerID or phone number if you try to collect things like that. Perhaps, you also need a code for non-taxable orders but I'm not sure how that works in a retail environment. Usually, you would need a Customer table and the customer's TaxID for this.

If you are thinking that you need to sum the details and save the sum, think again. The total should always be calculated on the fly using a query or in a report.
 

MAAS8000

Registered User.
Local time
Today, 03:35
Joined
Apr 3, 2018
Messages
38
Wait guys, I need time to read about this then return to you
Mr. Plog Actually the button save is not the access save or is a VBA command add data in two tables
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:35
Joined
Feb 19, 2002
Messages
42,970
Are you saying that you are using an unbound form? You can use a bound order form with a bound order details subform. This will add the records in the correct sequence. If you cancel the order, you can either mark the Order header cancelled or delete it.
 

Isaac

Lifelong Learner
Local time
Today, 03:35
Joined
Mar 14, 2017
Messages
8,738
my system is like a point of sale
the user select the needed items then press "save"
In the database I have two tables one detail and the other is summary of the transaction
when press save the VBA code add the items in the detail table and create the transaction number according to the above function
then VBA add a line in the summary table depending on the transaction number created before
If I depend on auto number, this will not solve the problem
Mr. arnelgp solution I think is good and will solve it but i can't know how to insert it

If you use a Subform setup, properly connected with a linking field that accurately represents keys and foreign keys, then the autonumber - along with carefully coded Event-driven routines - will work for you in order to grab that autonumber which was just added to populate a child table's foreign key. It's worth learning how this works although it may seem a bit tricky at first. This way you don't need to try to reinvent the wheel with dmax which has, as you are finding out, this (rather large) weakness of user conflicts.

Although if you insisted on continuing like this, you could certainly find other ways to make that ID unique...like adding an integer that corresponds to each user and tacking it on to the end. But anyway.
 

Users who are viewing this thread

Top Bottom