Generating Unique ID in form of AB-YY-xxxx

[njitter]

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):

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

 

[ZmrAbdulla]

Do you have a reference to DAO xxx library?

________________________________________________________
Zameer Abdulla
Help to find Missing people
Even a thief takes ten years to learn his trade.

 

[njitter]

I have a Reference to the DAO 3.6 library. This was the first thing i checked when it did not work..



---
It's never too late to do the Right thing

 

[CautionMP]

Yeah, what Zameer said.

I have been working on projects across Access 2k, '02, and '03 and have had more headaches resulting from the Microsoft DAO x.x Object Library. I have found that if I add a reference to this library in the newer versions of Access I start getting errors in strange (and somewhat un-elated) areas.

A couple of recomendations:[ul][li]Change your DAO code to ADO code, it's a pain but ADO is the 'go forward' recordset model.[/li][li]If you decide to add the DAO object Library be sure to fully qualify all your calls.

Dim Db As [b]DAO.[/b]Database
Dim Cust As [b]DAO.[/b]Recordset

[/li][li]Make sure that the Reference to the DAO Objcet Library is assigned a low priority in your project.[/li][/ul]

Hope this helps,
CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[CautionMP]

Something like this should work:

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
    
 '[red][s]Dim Db As Database[/s][/red]
 '[red][s]Dim Cust As Recordset[/s][/red]
 [b]Dim Cust As New ADODB.Recordset[/b]
 Dim oud As Double
 Dim temp, jaar As String
 Dim nieuw As Integer

 '[red][s]Set Db = CurrentDb[/s][/red]     'Openen huidige database
 '[red][s]Set Cust = Db.OpenRecordset("volgnummers", DB_OPEN_TABLE)[/s][/red] 'Openen volgnummers tabel
 '[red][s]Cust.Index = "primarykey"[/s][/red]'Op welke index wordt gezocht
 [b]Cust.Open "volgnummers", CurrentProject.Connection, adOpenDynamic, adLockOptimistic[/b]
  
 temp = Date$  'Opvragen systeemdatum
 '[red][s]jaar = Mid(temp, 7, 4)[/s][/red]
 [b]jaar = CStr(Year(Date))[/b]   'Filteren jaartal (4-cijfer)

 [b]Cust.Filter = "job_jaar=" & jaar[/b]
  
 '[red][s]Cust.Seek "=", jaar[/s][/red]       'Zoek op jaartal
  '[red][s]If Cust.NoMatch = False Then[/s][/red]         'Jobnummer gevonden
    '[red][s]oud = Cust!volgnummer[/s][/red]
    '[red][s]Cust.Edit[/s][/red]
    '[red][s]Cust("volgnummer") = oud + 1[/s][/red]     'Retourneren laatste nummer
    '[red][s]Cust.Update[/s][/red]'Update record
  '[red][s]Else[/s][/red]
    '[red][s]Cust.AddNew[/s][/red]  'Aanmaken nieuw record
    '[red][s]Cust("job_jaar") = jaar[/s][/red] 'Invullen nieuw jaartal
    '[red][s]Cust("volgnummer") = 1[/s][/red]  'Initialiseren nieuw volgnummer
    '[red][s]Cust.Update[/s][/red]  'Update record
  '[red][s]End If[/s][/red]

  '[red][s]Cust.Close[/s][/red]    'Tabel sluiten
  '[red][s]Db.Close[/s][/red]

 [b]If Cust.BOF And Cust.EOF Then
    With Cust
      .AddNew
      .Fields("job_jaar") = jaar
      .Fields("volgnummer") = 1
      .Update
    End With
 Else
   Do
      If oud < Cust.Fields("volgnummer") Then
          oud = Cust.Fields("volgnummer")
      End If
      Cust.MoveNext
   Loop Until Cust.EOF
   oud = oud + 1      'Database sluiten
 End If[/b]
[green]'...[/green]

This is untested but it should be pretty close,
CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[njitter]

Thanks for the ADO code.
I got the DAO code working but i will look into it..

This did the trick:

Dim Db As DAO.Database
Dim Cust As DAO.Recordset

---
It's never too late to do the Right thing