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

Create Specific Unique Serial Number

P: 6
Using Access 2007, I need to create an automatic Serial Number. I am new to Access and with no knowledege of writing codes.

Example "7235-4300"

"7" will be the current year
"235" is day (Julian Date)
"-43" is static
"01" (SerialNo) is sequential, restarting at "01" each day

I have searched the forum and found a detail explanation of the code and it works. However, the code did not provide how to get the 01 restarted each day.

Code from the previous post:

Expand|Select|Wrap|Line Numbers
  1. Public Function fGenerateNextSerialNumber() 
  2. Dim strCurrentYear As String 
  3. Dim strCurrentDay As String 
  4. Dim strStaticValue As String 
  5. Dim strSequentialNo As String 
  6. Dim strLastSerialNo As String 
  7. Dim strLastSequentialNo As String 
  8. Dim strNextSequentialNo As String 
  9.  
  10. strCurrentYear = Right(Year(Now()), 1) 
  11. strCurrentDay = DateDiff("d", CDate("1/1/" & Year(Now())), Now()) + 1 
  12. strStaticValue = "-43" 
  13.  
  14. 'get ready to extract the Sequential Number 
  15. strLastSerialNo = DLast("[SerialNo]", "tblTest")        'produces 7235-4300 
  16. strLastSequentialNo = Right(strLastSerialNo, 2)            'produces 01 
  17.  
  18. 'Generate the Next Sequential Number 
  19. strNextSequentialNo = Format(Val(strLastSequentialNo) + 1, "00")   'produces "02" 
  20.  
  21.   'Generate the next, Unique, Serial # 
  22.   fGenerateNextSerialNumber = strCurrentYear & strCurrentDay & strStaticValue & strNextSequentialNo 
  23. End Function
Sep 24 '12 #1

✓ answered by TheSmileyCoder

I would not advice combining all of this information into 1 field, as I could imagine it at times could be practical to work with the individual components without having to write code to separate them again.

This may or may not be relevant at this point in time or for you, but have you considered what will happen if you have more then 99 entries in a day? -4399 +1 is what? 4400? 43100?
In general, storing numbers as text is a bad idea as it leads to all sorts of odd results when it comes to sorting.

My suggested approach would be:
If we imagine we keep the date component in 1 field DateCreated, and the DailyNumber in another field, and only combine the 2 when we display it, alot of work gets simpler, and making design changes later is also exponentially easier.

In your forms BeforeUpdate event we need to ensure that the correct values gets assigned, and that can be done like so:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel as Integer)
  2.   'If it is a new record, assign date and DaylyNumber
  3.   If Me.NewRecord Then
  4.     Me.DateCreated=Date()
  5.     Me.DailyNumber=nz(Dmax("DailyNumber","[YourTableNameHere]","DateCreated=Date()"),0)+1
  6.   End If
  7. End Sub
The DMax function finds the maximum used value for all records matching DateCreated=Date(). (Date is a built in access function returning todays date). In case there is no records matching todays date, Dmax returns null, and thats why the Dmax is enclosed in a NZ function call. The NZ function replaces a potential Null with a safe value (In this case I specified a 0). Finally 1 is added to increment it by one.

Finally all you need is to modify the query you are using as recordsource for your form. You can simply add a calculated field:
Expand|Select|Wrap|Line Numbers
  1. Serial: Year(DateCreated) & DatePart("y", DateCreated) & "-43" & Format(DailyNumber,"00")
You can set the primary Key for your table to be the combination of the 2 fields, or you can add a AutoNumber column and let that be the primary key (which is what I would recommend)

Share this Question
Share on Google+
10 Replies


P: 1
One alternative is, where you are generating strLastSequentialNo,
check if strLastSerialNo belonged to the same date (this can be done by checking if strCurrentDay is same as Right(Left(strLastSerialNo, 4), 3). If they are same, that means we have already generated a sequence no for today, and we can continue. If not, make strLastSequentialNo = "01" so that it will restart sequential numbers for today.
Sep 24 '12 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
I would not advice combining all of this information into 1 field, as I could imagine it at times could be practical to work with the individual components without having to write code to separate them again.

This may or may not be relevant at this point in time or for you, but have you considered what will happen if you have more then 99 entries in a day? -4399 +1 is what? 4400? 43100?
In general, storing numbers as text is a bad idea as it leads to all sorts of odd results when it comes to sorting.

My suggested approach would be:
If we imagine we keep the date component in 1 field DateCreated, and the DailyNumber in another field, and only combine the 2 when we display it, alot of work gets simpler, and making design changes later is also exponentially easier.

In your forms BeforeUpdate event we need to ensure that the correct values gets assigned, and that can be done like so:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel as Integer)
  2.   'If it is a new record, assign date and DaylyNumber
  3.   If Me.NewRecord Then
  4.     Me.DateCreated=Date()
  5.     Me.DailyNumber=nz(Dmax("DailyNumber","[YourTableNameHere]","DateCreated=Date()"),0)+1
  6.   End If
  7. End Sub
The DMax function finds the maximum used value for all records matching DateCreated=Date(). (Date is a built in access function returning todays date). In case there is no records matching todays date, Dmax returns null, and thats why the Dmax is enclosed in a NZ function call. The NZ function replaces a potential Null with a safe value (In this case I specified a 0). Finally 1 is added to increment it by one.

Finally all you need is to modify the query you are using as recordsource for your form. You can simply add a calculated field:
Expand|Select|Wrap|Line Numbers
  1. Serial: Year(DateCreated) & DatePart("y", DateCreated) & "-43" & Format(DailyNumber,"00")
You can set the primary Key for your table to be the combination of the 2 fields, or you can add a AutoNumber column and let that be the primary key (which is what I would recommend)
Sep 24 '12 #3

P: 6
WOW! vivekprakash and TheSmileyCoder ya'll are great! I got scorned by a moderator on my first inquiry on this post. However, not a problem as I will take the high road for knowledge and success. I will do my homework a lot better. Thanks again vivekprakash and TheSmileyCoder for your support.
Sep 25 '12 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
I hope you don't take anything I said as scorn. We all start somewhere on the path of learning, myself included.

In the end, an "expert" is just someone who have collected more past mistakes, and learned from them.

Welcome to Bytes.
Sep 25 '12 #5

P: 6
TheSmileyCoder,

I enter the information as you provided. The Transaction_Date posting do return a 1 as visible in a block that I placed on the form named SerialNo...is that what supposed happen?. I attempted modification of the code (as provided below) and received a Run-time error '13': Type mismatch code. What am I doing wrong?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel as Integer) 
  2.   'If it is a new record, assign date and SerialNo 
  3.   If Me.NewRecord Then 
  4.     Me.Transaction Date=Date() 
  5.     Me.SerialNo=nz(Dmax("SerialNo","[Inventory Transactions]","Transaction_Date=Date()"),0)+1
  6.   End If 
  7. End Sub
Expand|Select|Wrap|Line Numbers
  1. Serial: Year(Transaction_Date) & DatePart("y", Transaction_Date) & "-43" & Format(SerialNo,"00") 
Sep 25 '12 #6

P: 6
No I did not find anything you said as scorned. You were not the site moderator who gave the scorn. I am past that. If I can just get the SerialNo incrementing and restaring every day as I move forward and collect on my past mistakes.
Sep 25 '12 #7

P: 6
TheSmileyCoder,

TouchDown! I re-read your postedd information and placed very particular attention to the steps and I was able to put things together. I did not initially understand your mention of modifying the form recordsource. But I figured it out. I have a code I am using that individually changes the Transaction Date to a Julian Date but I do not how to insert into your current code for 2012. I beleive its something that requires right(????, 3)?
Sep 25 '12 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
The format function will take a number and write it according to the format specified. For example:
Expand|Select|Wrap|Line Numbers
  1. format(7,"00")="07"
  2. format(78,"00")="78"
  3. format(983,"00")="983"
You can see that in this case any number will be written with minimum 2 digits, or more if required.

The exception is if there is no number and just null.

While the record is New (not saved first time yet) the SerialNo will be null. and the format function will just return null, which is why you are getting the "2012258-43"

The SerialNo only gets assigned when the record is first saved (That is what goes on in the bit of code for the Forms_BeforeUpdate event.

So you will only actually see the number once you have saved the record once.

There are other ways to do this. You could in theory assign the number in the BeforeInsert event, which takes place at the moment you start a new record. The problem is that if User A starts a record, and user B also starts a record before User A is finished and saves, then both records will receive the same Serial No. Using the BeforeUpdate (Which occurs right before record save) you completely avoid this issue.

To summarize:
Using the BeforeUpdate event to assign the number means you only get the number assigned at the last possible milli-second to avoid 2 records getting the same ID.

Using the Before_Insert event you can get the number immediatly but need to add extra code and possibly tables to ensure that no 2 records get the same ID.

Unless you NEED the ID to be assigned at record creation and not at record Saving, I suggest using the beforeUpdate event. You could possibly modify the formating to make it clearer that a number will be assigned.

Expand|Select|Wrap|Line Numbers
  1. Serial: Year(Transaction_Date) & DatePart("y", Transaction_Date) & "-43" & Iif(Isnull(SerialNo);"??";(Format(SerialNo,"00") 
This should now show a double question mark in place of the last 2 digits until the record is saved, and then properly display the SerialNo.
Sep 25 '12 #9

P: 6
The New SerialNo is not restarting at 0 each New Date. I reviewed your complete posting and find I enter the code exactly as you provided. What am I doing incorrect?
Sep 29 '12 #10

TheSmileyCoder
Expert Mod 100+
P: 2,321
@Post 8, you must have edited after I posted my post 9. I haven't actually checked but I am guessing that julian date simply means the day of the year with 1st of january being number 1 and 31 of december being 365, is this correct?

If so, then DatePart("y", Transaction_Date) should return just that.

I have reviewed my code and can't find anything wrong with it. Mind though, I have not actually tested it live, so no guarantees. Could you post the exact code you have now, as well as relevant field names, and data types of those fields, just to be sure we are not missing something simple.
Sep 29 '12 #11

Post your reply

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