How to prevent duplication of running number

L

luzz

Registered User.
Local time
Today, 09:57
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?
 
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
 
arnelgp said:
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
Click to expand...

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

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
 
can you tell me what Datatype is PONumber. If string what is the format, is it AlphaNumeric or just Numeric ?
 
arnelgp said:
can you tell me what Datatype is PONumber. If string what is the format, is it AlphaNumeric or just Numeric ?
Click to expand...

PO number is numeric.

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


but how does it is being saved in MXDPORunningNo table.
can you show me some data from PONumber field.
 
arnelgp said:
but how does it is being saved in MXDPORunningNo table.
can you show me some data from PONumber field.
Click to expand...

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: 145
Last edited:
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
 
arnelgp said:
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
Click to expand...

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

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: 132
is your form Bound now?
 
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:
You must log in or register to reply here.

Similar threads

P
Long-running delete of duplicates
2
Replies
23
Views
2,892
CJ_London
C
J
New Form Clearing Old Form For PO's
Replies
12
Views
1,419
jillnoble
J
S
Batch file for updating local frontend Access database copy
2
Replies
26
Views
3,936
arnelgp
arnelgp
N
Solved How to filter a specific column in MS Access Query
Replies
12
Views
959
nector
N
N
Solved How to run an append query to append Five lines same data in the same table
Replies
6
Views
469
Ken Sheridan
Ken Sheridan

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom