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
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
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
can you tell me what Datatype is PONumber. If string what is the format, is it AlphaNumeric or just Numeric ?
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.
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
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
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
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
is your form Bound now?
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
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