Hello,
i would like to generate an unique ID in the form of
CS-06-0001 (where CS is a prefix, 06 is the current year and 0001 is a counter).
I had this working in a 97 database but for some reason that code doesn't work anymore in Access 2002 (not my own code).
It uses a table with two fields.
Year 2006
Number the current available number
The code (it's had some dutch variables but i think you will get the point):
---
It's never too late to do the Right thing
i would like to generate an unique ID in the form of
CS-06-0001 (where CS is a prefix, 06 is the current year and 0001 is a counter).
I had this working in a 97 database but for some reason that code doesn't work anymore in Access 2002 (not my own code).
It uses a table with two fields.
Year 2006
Number the current available number
The code (it's had some dutch variables but i think you will get the point):
Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Err_add_New_job_Click
Dim new_number As Double
Dim Code, Volgnummercode As String
Dim Db As Database
Dim Cust As Recordset
Dim oud As Double
Dim temp, jaar As String
Dim nieuw As Integer
Set Db = CurrentDb 'Openen huidige database
Set Cust = Db.OpenRecordset("volgnummers", DB_OPEN_TABLE) 'Openen volgnummers tabel
Cust.Index = "primarykey" 'Op welke index wordt gezocht
temp = Date$ 'Opvragen systeemdatum
jaar = Mid(temp, 7, 4) 'Filteren jaartal (4-cijfer)
Cust.Seek "=", jaar 'Zoek op jaartal
If Cust.NoMatch = False Then 'Jobnummer gevonden
oud = Cust!volgnummer
Cust.Edit
Cust("volgnummer") = oud + 1 'Retourneren laatste nummer
Cust.Update 'Update record
Else
Cust.AddNew 'Aanmaken nieuw record
Cust("job_jaar") = jaar 'Invullen nieuw jaartal
Cust("volgnummer") = 1 'Initialiseren nieuw volgnummer
Cust.Update 'Update record
End If
Cust.Close 'Tabel sluiten
Db.Close 'Database sluiten
Volgnummercode = Trim(Str(oud))
If Len(Volgnummercode) = 1 Then
Volgnummercode = "000" + Volgnummercode
ElseIf Len(Volgnummercode) = 2 Then
Volgnummercode = "00" + Volgnummercode
ElseIf Len(Volgnummercode) = 3 Then
Volgnummercode = "0" + Volgnummercode
End If
DoCmd.GoToRecord A_FORM, "onderwerp", A_NEWREC
Code = "LAS" + jaar + Volgnummercode
Forms!Onderwerp!Job.Locked = False
Forms!Onderwerp.Job = Code
Forms!Onderwerp!Job.Locked = True
Exit_add_New_job_Click:
Exit Sub
Err_add_New_job_Click:
MsgBox "Nieuw jobnummer kan niet aangemaakt worden. Huidige job nog niet opgeslagen, fout in database of jobnummer niet uniek!", 48, "Waarschuwing"
Resume Exit_add_New_job_Click
---
It's never too late to do the Right thing