By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,571 Members | 3,168 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,571 IT Pros & Developers. It's quick & easy.

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

P: 1
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:


then the next record would be:


can anybody help me with this please?
Sep 12 '12 #1
Share this Question
Share on Google+
2 Replies

P: 298
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
Sep 12 '12 #2

Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel as True)
  3. End Sub
Modify the code to look like so:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel as True)
  2.   'Only apply to new records
  3.   If Me.NewRecord Then
  4.     me.txt_DailyID=nz(Dmax("lng_DailyID","[InsertTableNameHere]","DateCreated=#" & me.tb_DateCreated & "#"),0)+1
  5.   End If
  6. 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:
Expand|Select|Wrap|Line Numbers
  1. Serial: Format([DateCreated],"yymmdd") + lng_DailyID
Sep 12 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.