Alhakeem1977
Registered User.
- Local time
- Tomorrow, 01:27
- Joined
- Jun 24, 2017
- Messages
- 308
Hi All,
I Have an issue with below code that is supposed to generate a unique identifier concatenated sequential ID represents the year and incremental number resets every year for the column named [DocID], unfortunately, I noticed that it create a duplicate sequential number as the below which highlighted in red:
DocID
00001-18
00002-18
00003-18
00001-19
00002-19
00003-19
00003-19:banghead:
I do not know how to figure it out, appreciate your earliest suggestions, please find below my codes one in the field and the other one in the form before update event:
******************************************************
I Have an issue with below code that is supposed to generate a unique identifier concatenated sequential ID represents the year and incremental number resets every year for the column named [DocID], unfortunately, I noticed that it create a duplicate sequential number as the below which highlighted in red:
DocID
00001-18
00002-18
00003-18
00001-19
00002-19
00003-19
00003-19:banghead:
I do not know how to figure it out, appreciate your earliest suggestions, please find below my codes one in the field and the other one in the form before update event:
Code:
Private Sub AccountNo_NotInList(NewData As String, Response As Integer)
On Error Resume Next
If MsgBox("Sorry, file not received ! *" _
, vbCritical, "A/C Not Listed") = vbCritical Then
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If
Me.AccountNo = 4590
Me.ActualAC.Visible = True
Me.ActualAC.SetFocus
End Sub
******************************************************
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler
Dim strLinkCriteria As String
Dim strMessage As String
Dim DocID As Integer
If DontPromptUser = True Then Exit Sub
If Len(Trim(Me!AccountNo & vbNullString)) = 0 Then
strMessage = strMessage & " You must provide an Account Number!" & vbCrLf
Me.AccountNo.SetFocus
End If
If Len(Trim(Me!DocumentDate & vbNullString)) = 0 Then
strMessage = strMessage & " You must provide a Document Date!" & vbCrLf
Me.DocumentDate.SetFocus
End If
If Len(Trim(Me!DocumentName & vbNullString)) = 0 Then
strMessage = strMessage & " You must provide a DocumentName!" & vbCrLf
Me.DocumentName.SetFocus
End If
If Len(strMessage) > 0 Then
MsgBox strMessage, vbCritical
Cancel = True
Else
strLinkCriteria = "[AccountNo] = " & Me!AccountNo & " AND " & _
"[ActualAC] = '" & Me!ActualAC & "' AND " & _
"[DocumentDate] = " & Format$(Me!DocumentDate, "\#mm\/dd\/yyyy\#") & " AND " & _
"[DocumentName] = " & Me!DocumentName & " and " & "[Notes] = '" & Me!txtNotes & "'"
Dim varID
varID = DLookup("DocID", "tblFacilityRegister", strLinkCriteria)
If DCount("*", "tblFacilityRegister", strLinkCriteria) > 0 Then
Me.img1.Visible = True
MsgBox "Duplicate document !" & vbCrLf & _
"Please write the Doc ID *( " & varID & " )* at the top and send it to DCD. ", vbCritical, "Duplicate Entry"
Cancel = True
Me.Undo
Me.txtCustomer = ""
img1.Visible = False
DoCmd.GoToRecord , "", acNewRec
Me.txtDocsToBeSent.Requery
Else
Me.SeqNo = Nz(DMax("[SeqNo]", "tblFacilityRegister", "Year([SentDate]) = " & Year(Me.[SentDate])), 0) + 1
Me.DocID = Format([SeqNo], "0000") & "-" & Format([SentDate], "yy")
Me.img1.Visible = False
Me.DocID.BackColor = vbRed
Me.Detail.BackColor = vbGreen
Me.New_Record.SetFocus
End If
End If
Cleanup:
Exit Sub
ErrorHandler:
MsgBox Err.Number & ": " & Err.Description
Beep
MsgBox "You must enter a value in one of the following:" & vbCrLf & _
vbCr & "Account Number, DTD or Document Name.", vbOKOnly, "Empty Fields"
Resume Cleanup
End Sub