Unique Identifier resets every year in MS Access (1 Viewer)

Alhakeem1977

Registered User.
Local time
Today, 14:09
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:

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
 

Micron

AWF VIP
Local time
Today, 07:09
Joined
Oct 20, 2018
Messages
3,476
The simplest approach would be to create a composite index on the 2 fields. In table design view, Ribbon>Design tab>Show/Hide > indexes button is there. Then you can allow Access to prompt if a duplication of a field pair is attempted. You can use the built in error message, or trap the error and generate your own. Or you will have to first test in code that the value pair that's going to be added exists or not. Rather than 2 DLookups or combined lookup criteria, I'd lean towards creating a recordset on the two values and if the count is zero, it doesn't exist. My preference would be the index.
 

Alhakeem1977

Registered User.
Local time
Today, 14:09
Joined
Jun 24, 2017
Messages
308
The simplest approach would be to create a composite index on the 2 fields. In table design view, Ribbon>Design tab>Show/Hide > indexes button is there. Then you can allow Access to prompt if a duplication of a field pair is attempted. You can use the built in error message, or trap the error and generate your own. Or you will have to first test in code that the value pair that's going to be added exists or not. Rather than 2 DLookups or combined lookup criteria, I'd lean towards creating a recordset on the two values and if the count is zero, it doesn't exist. My preference would be the index.
Thanks for your reply, it came my notice that because the database is used by multi users (Split database) the form in some cases will not catch the previous ID utilized by the other user to generate the next one.
As you explain I should do it in the table itself instead in the form, but how can I trap the error to view my custom error message for the user?

Sent from my HUAWEI NXT-L29 using Tapatalk
 

June7

AWF VIP
Local time
Today, 03:09
Joined
Mar 9, 2014
Messages
5,423
I do have multiple users, however, usually only 1 is entering new record at a time.

Duplicate ID cannot be 100% prevented but risk is greatly reduced. Duplicate has never happened with my setup even when there are multiple users entering new records.

User clicks button for "Log New Sample". Code generates the ID and immediately saves record then opens form for input of remaining data. If for some reason they decide should not enter this record, there is an Abort button. The record is saved with data removed except for the ID value. Next "Log New Sample" will find this 'blank' record and use the number, a date field is populated and committed so another user cannot grab the record. All numbers are accounted for.

ID is generated upfront because user needs to write it on submittal paperwork and it was too difficult to generate and display at end of data input.
 
Last edited:

Alhakeem1977

Registered User.
Local time
Today, 14:09
Joined
Jun 24, 2017
Messages
308
My aim is how to achieve having a custom unique ID reset every year for multi users environment but it should examine the duplicate entries prior.

Thanks in advance!

Sent from my HUAWEI NXT-L29 using Tapatalk
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:09
Joined
Feb 28, 2001
Messages
26,996
First, the total pain-in-the-toches way to do this is to try to do the ID as a single string when in fact, you would do FAR better breaking it apart. If you store the year number in one field and the unique number in another, a simple query will concatenate the two numbers as strings any time you need them together. With Access, your forms and reports and queries don't give a rat's patootie about whether the unerlying recordset came from a table or a query.

Let's face it, the year is a no-brainer. Just a DatePart("yy", Now() ) and you have the year portion. The tougher part is to find the highest number unused this year. But with that split field, it would be something like 1 + DMax( "[UniquePart]", "table-name", "[YearPart]=" & DatePart("yy", Now() ) - assuming you left the year as numeric. You might want to also test for having ANY records for the current year in the first place, in which case your first number is 1.

I think the best way to do this is to make a simple-minded best-guess attempt to compute your new ID. BUT in the table, put two fields together - the year number and the unique-per-year part - as a compound unique key. You do NOT want to bother with making either component individually unique. So you take your best guess at the number and try to store the combination. IF you got it right, nothing much happens. You go on about your business.

BUT... if you guessed wrong and it duplicates some extant key, you get a "key violation" error which you can catch in an error trap. If you get error 3022, you had a key violation. Other errors are something else and there, you are on your own.

So IF you get the 3022 error when you try to store that record with the supposedly unique key, you use the "RESUME xxx" syntax to resume your routine at the point where you start over again to compute a new index. If this loops, something else is wrong, so it wouldn't hurt to test this via single-stepping with the debugger. That's because trap-loops are notoriously difficult to break out.
 

Users who are viewing this thread

Top Bottom