How can i create a unique serial number in Access 2003?

[NTC2191]

i am using Access 2003 to create a form where i can fill out customer details and the jobs i carry out for them, i want my form to create a unique code in my "job number" field. i want the code to be the date then another number after that which increases by one every record e.g.

if i entered a record on september 12th 2012 i want the job number to be:

12091201

then the next record would be:

12091202


can anybody help me with this please?

[pod]

There is probably a better way to do this, but the following works:

You need to create three fields to do this

date_created : date the record is created
date_id : unique id for each date (duplicates are allowed for different dates)
unique_id : based on the two previous field

So before saving a record, you have to query your table for the last inserted [date_id] of the current day,
build your [unique_id] not forgetting to increment [date_id] by one,
then you could insert your new record with the following data:

[date_created]: 2012-09-12
[date_id] : (SELECT MAX (created_id) FROM tablename WHERE [date_created] = isToday ) + 1 = 73
unique_id : [yyyymmddii] = 2012091273

[TheSmileyCoder]

pod is going in the right direction, so I will just expand a bit on what he has said.

I would create only 2 fields, one to store the date and one to store the ID of the record, and let the primary key be the combination of the two. If you truly need to display the combined ID I would then make a calculated field in the query or the form/report.

In your table, have fields Date_Created and lng_DailyID. In your form, bind these to textboxes txt_DateCreated and txt_DailyID. Ensure that both textboxes are locked (And possibly disabled).

Make the Default Value for the txt_DateCreated be "=Date()".

Select your FORM properties, go the event tab, and select the event Before_Update. In the dropdown, select [Event Procedure] and then click the ... on the right hand side.

Now this will take you the VBA window, and place the cursor inside a piece of text like so:

Private Sub Form_BeforeUpdate(Cancel as True)
 
End Sub

Modify the code to look like so:

Private Sub Form_BeforeUpdate(Cancel as True)
  'Only apply to new records
  If Me.NewRecord Then
    me.txt_DailyID=nz(Dmax("lng_DailyID","[InsertTableNameHere]","DateCreated=#" & me.tb_DateCreated & "#"),0)+1
  End If
End Sub

The Dmax will find the max used ID for that day. In case its the first record of the day it will return null, which is why its encapsualted in a NZ(Dmax,0) which will safely convert the Null to 0, and finally it is incremented by one.



If you need to display your special ID, simply add to your query:

Serial: Format([DateCreated],"yymmdd") + lng_DailyID