How to prevent duplication of running number (1 Viewer)

luzz

Registered User.
Local time
Yesterday, 23:37
Joined
Aug 23, 2017
Messages
346
Hello all, How do i prevent two user who are accessing the same Access database to have duplicate PO number (Running number) when both of the user click "Save PO" at the same time?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:37
Joined
May 7, 2009
Messages
19,169
if you can show us the function or routine that generate the po.
but basically you need another table with just one field (PO, same fieldtype as with your main table PO).


pseudocode:
lookup PO for the Max PO number.

add 1 to this PO
assign this to variable (poVar)


checkPoint1:
check the table PO again if somebody added this PO (varPO)
if nobody add then we issued Insert SQL to add this PO and goto CheckPoint2

if somebody did added same PO (varPO), increase its number and go back to checkPoint1


CheckPoint2
pass the new PO to the form's textbox
exit routine
 

luzz

Registered User.
Local time
Yesterday, 23:37
Joined
Aug 23, 2017
Messages
346
if you can show us the function or routine that generate the po.
but basically you need another table with just one field (PO, same fieldtype as with your main table PO).


pseudocode:
lookup PO for the Max PO number.

add 1 to this PO
assign this to variable (poVar)


checkPoint1:
check the table PO again if somebody added this PO (varPO)
if nobody add then we issued Insert SQL to add this PO and goto CheckPoint2

if somebody did added same PO (varPO), increase its number and go back to checkPoint1


CheckPoint2
pass the new PO to the form's textbox
exit routine

My code for saving the PO
Private Sub cmdSavePo_Click()
'Insert PO number into MXDPORunningNo table
If Me.txtCode.Tag & "" = "" Then

CurrentDb.Execute "INSERT INTO MXDPORunningNo(CompanyCode,YearCode,PONumber,MRName)" & _
" Values ('" & Me.txtCode & "','" & Me.txtYearCode & "','" & Me.txtRunNo & "','" & Me.txtMRName & "')"

End If
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:37
Joined
May 7, 2009
Messages
19,169
here is an updated code for your save button:


Code:
Private Sub cmdSavePo_Click()
' Insert PO number into MXDPORunningNo table
Dim thisPO as Long
If Me.txtCode.Tage & "" = "" Then
    thisPO = Val(Me.txtRunNo)
    ' check again if somebody has just added
    ' this PO before us
    While Val("0" & DLookup("PONumber", "MXDPORunningNo", "PONumber=" & Chr(34) & thisPO & Chr(34))) <> 0
        'increase PO Number if somebody did added this PO
        thisPO = thisPO + 1
    Wend
    CurrentDb.Execute _
        "Insert Into MXDPORunningNo(CompanyCode,YearCode,PONumber,MRName) SELECT " & _
        """" & Me.txtCode & """" & "," & _
        """" & Me.txtYearCode & """" & "," & _
        """" & thisPO & """" & "," & _
        """" & Me.txtMRName & """"
    If thisPO <> Val(Me.txtRunNO) then
        Me.txtRunNO = thisPO & ""
        ' show message that we used another PO number
        Msgbox "PO: " & Me.txtRunNo & " has been used by another User. " & vbcrlf & _
        "New PO: " & thisPO & " was used instead."
    End If
End If
End Sub
 

luzz

Registered User.
Local time
Yesterday, 23:37
Joined
Aug 23, 2017
Messages
346
here is an updated code for your save button:


Code:
Private Sub cmdSavePo_Click()
' Insert PO number into MXDPORunningNo table
Dim thisPO as Long
If Me.txtCode.Tage & "" = "" Then
    thisPO = Val(Me.txtRunNo)
    ' check again if somebody has just added
    ' this PO before us
    While Val("0" & DLookup("PONumber", "MXDPORunningNo", "PONumber=" & Chr(34) & thisPO & Chr(34))) <> 0
        'increase PO Number if somebody did added this PO
        thisPO = thisPO + 1
    Wend
    CurrentDb.Execute _
        "Insert Into MXDPORunningNo(CompanyCode,YearCode,PONumber,MRName) SELECT " & _
        """" & Me.txtCode & """" & "," & _
        """" & Me.txtYearCode & """" & "," & _
        """" & thisPO & """" & "," & _
        """" & Me.txtMRName & """"
    If thisPO <> Val(Me.txtRunNO) then
        Me.txtRunNO = thisPO & ""
        ' show message that we used another PO number
        Msgbox "PO: " & Me.txtRunNo & " has been used by another User. " & vbcrlf & _
        "New PO: " & thisPO & " was used instead."
    End If
End If
End Sub

I got type mismatch error for this line of code
Private Sub cmdSavePo_Click()
' Insert PO number into MXDPORunningNo table
Dim thisPO As Long
If Me.txtCode.Tag & "" = "" Then
thisPO = Val(Me.txtRunNo)
' check again if somebody has just added
' this PO before us
While Val("0" & DLookup("PONumber", "MXDPORunningNo", "PONumber=" & Chr(34) & thisPO & Chr(34))) <> 0
'increase PO Number if somebody did added this PO
thisPO = thisPO + 1
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:37
Joined
May 7, 2009
Messages
19,169
can you tell me what Datatype is PONumber. If string what is the format, is it AlphaNumeric or just Numeric ?
 

luzz

Registered User.
Local time
Yesterday, 23:37
Joined
Aug 23, 2017
Messages
346
can you tell me what Datatype is PONumber. If string what is the format, is it AlphaNumeric or just Numeric ?

PO number is numeric.

MY PO number looks like this ABC17/100
but is separated into three text box
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:37
Joined
Feb 19, 2002
Messages
42,981
Using a separate table to assign the PO number has the same issue as using an autonumber. You can assign a number and never actually save the record that uses it thereby creating a gap in the numbering. If you don't care about gaps, just go with an autonumber. If you do care about gaps, don't use a separate table.

The PO goes into the record currently bound to the form. It should be the PK of the table which will ensure that it is unique.

You can check for a duplicate prior to saving the record but in a busy environment, this isn't enough. You actually need to trap the error raised when you attempt to save the record. At that point you would generate a new PO and try again to save. You probably want to limit the loop to 20 tries and then stop and let the user request the save again.

I don't have any code handy. I haven't had to do this in quite some time but you will need to trap the error in the Error event. I don't believe trapping the error in the BeforeUpdate event will work due to the nature of the error.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:37
Joined
May 7, 2009
Messages
19,169
MY PO number looks like this ABC17/100
but is separated into three text box


but how does it is being saved in MXDPORunningNo table.
can you show me some data from PONumber field.
 

luzz

Registered User.
Local time
Yesterday, 23:37
Joined
Aug 23, 2017
Messages
346
but how does it is being saved in MXDPORunningNo table.
can you show me some data from PONumber field.

I have attached the image of my running no table.

I did so by applying this code:

Private Sub cmdSavePo_Click()
'Insert PO number into MXDPORunningNo table
If Me.txtCode.Tag & "" = "" Then
CurrentDb.Execute "INSERT INTO MXDPORunningNo(CompanyCode,YearCode,PONumber,MRName)" & _
" Values ('" & Me.txtCode & "','" & Me.txtYearCode & "','" & Me.txtRunNo & "','" & Me.txtMRName & "')"

End If
End Sub

I am thinking if I set the PO number as primary key in my table, and if the PO number is saved by user it will show "This PO number has been saved", then when another user who click on the same PO number will show "Please use the next PO number" Or when the PO number has been saved it will auto increase to the next PO number. What do you think? Do you think that this can solve the issue of having duplication PO number by many users?
 

Attachments

  • running no.png
    running no.png
    7.4 KB · Views: 98
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:37
Joined
May 7, 2009
Messages
19,169
since your PO is numeric we just change the delimiter:

Code:
Private Sub cmdSavePo_Click()
' Insert PO number into MXDPORunningNo table
Dim thisPO as Long
If Me.txtCode.Tage & "" = "" Then
    thisPO = Val(Me.txtRunNo)
    ' check again if somebody has just added
    ' this PO before us
    While Val("0" & DLookup("PONumber", "MXDPORunningNo", "PONumber=" & thisPO)) <> 0
       'increase PO Number if somebody did added this PO
        thisPO = thisPO + 1
    Wend
    CurrentDb.Execute _
        "Insert Into MXDPORunningNo(CompanyCode,YearCode,PONumber,MRName) SELECT " & _
        """" & Me.txtCode & """" & "," & _
        """" & Me.txtYearCode & """" & "," & _
       thisPO & "," & _
        """" & Me.txtMRName & """"
    If thisPO <> Val(Me.txtRunNO) then
        Me.txtRunNO = thisPO & ""
        ' show message that we used another PO number
        Msgbox "PO: " & Me.txtRunNo & " has been used by another User. " & vbcrlf & _
        "New PO: " & thisPO & " was used instead."
    End If
End If
End Sub
 

luzz

Registered User.
Local time
Yesterday, 23:37
Joined
Aug 23, 2017
Messages
346
since your PO is numeric we just change the delimiter:

Code:
Private Sub cmdSavePo_Click()
' Insert PO number into MXDPORunningNo table
Dim thisPO as Long
If Me.txtCode.Tage & "" = "" Then
    thisPO = Val(Me.txtRunNo)
    ' check again if somebody has just added
    ' this PO before us
    While Val("0" & DLookup("PONumber", "MXDPORunningNo", "PONumber=" & thisPO)) <> 0
       'increase PO Number if somebody did added this PO
        thisPO = thisPO + 1
    Wend
    CurrentDb.Execute _
        "Insert Into MXDPORunningNo(CompanyCode,YearCode,PONumber,MRName) SELECT " & _
        """" & Me.txtCode & """" & "," & _
        """" & Me.txtYearCode & """" & "," & _
       thisPO & "," & _
        """" & Me.txtMRName & """"
    If thisPO <> Val(Me.txtRunNO) then
        Me.txtRunNO = thisPO & ""
        ' show message that we used another PO number
        Msgbox "PO: " & Me.txtRunNo & " has been used by another User. " & vbcrlf & _
        "New PO: " & thisPO & " was used instead."
    End If
End If
End Sub

Thank you! How can I automatically display concatenated PO in my textbox after clicking on SAVE PO?
As of now, I have to click on the textbox to display the concatenated PO by using the following VBA Code:

Code:
Dim CompanyCode As String
CompanyCode = Me.txtCode
Dim YearCode As String
YearCode = Me.txtYearCode
Dim PONumber As Integer
PONumber = Me.txtRunNo
Dim MXDPO As String
Me.txtMXDPO = Me.txtCode & Me.txtYearCode & Me.txtRunNo
 

Attachments

  • Untitled.png
    Untitled.png
    1.9 KB · Views: 41

luzz

Registered User.
Local time
Yesterday, 23:37
Joined
Aug 23, 2017
Messages
346
since your PO is numeric we just change the delimiter:

Code:
Private Sub cmdSavePo_Click()
' Insert PO number into MXDPORunningNo table
Dim thisPO as Long
If Me.txtCode.Tage & "" = "" Then
    thisPO = Val(Me.txtRunNo)
    ' check again if somebody has just added
    ' this PO before us
    While Val("0" & DLookup("PONumber", "MXDPORunningNo", "PONumber=" & thisPO)) <> 0
       'increase PO Number if somebody did added this PO
        thisPO = thisPO + 1
    Wend
    CurrentDb.Execute _
        "Insert Into MXDPORunningNo(CompanyCode,YearCode,PONumber,MRName) SELECT " & _
        """" & Me.txtCode & """" & "," & _
        """" & Me.txtYearCode & """" & "," & _
       thisPO & "," & _
        """" & Me.txtMRName & """"
    If thisPO <> Val(Me.txtRunNO) then
        Me.txtRunNO = thisPO & ""
        ' show message that we used another PO number
        Msgbox "PO: " & Me.txtRunNo & " has been used by another User. " & vbcrlf & _
        "New PO: " & thisPO & " was used instead."
    End If
End If
End Sub

I think there is some error here.
The PO that was being use should be 103 instead of 104 right?
How do i do that?
 

Attachments

  • Untitled.png
    Untitled.png
    12 KB · Views: 86

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:37
Joined
May 7, 2009
Messages
19,169
is your form Bound now?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:37
Joined
May 7, 2009
Messages
19,169
OK, i see it now, change this portion:

Code:
    If thisPO <> Val(Me.txtRunNO) then
        Me.txtRunNO = thisPO & ""
        ' show message that we used another PO number
        Msgbox "PO: " & Me.txtRunNo & " has been used by another User. " & vbcrlf & _
        "New PO: " & thisPO & " was used instead."
    End If

to:
Code:
    If thisPO <> Val(Me.txtRunNO) then
        ' show message that we used another PO number
        Msgbox "PO: " & Me.txtRunNo & " has been used by another User. " & vbcrlf & _
        "New PO: " & thisPO & " was used instead."
        Me.txtRunNO = thisPO & ""
    End If
 
Last edited:

Users who are viewing this thread

Top Bottom