473,325 Members | 2,828 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Create Specific Unique Serial Number

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)

10 7979
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
2,322 Expert Mod 2GB
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
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
2,322 Expert Mod 2GB
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
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
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
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
2,322 Expert Mod 2GB
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
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
2,322 Expert Mod 2GB
@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

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

Similar topics

15
by: tom | last post by:
Hi, How do I get the serial number of the harddisk in .NET? I want this to be the same number even if the user has reformatted, so I do not want the volume serial number. Thanx, t
5
by: | last post by:
Hi, Do memory sticks have serial numbers like harddrives? If so how can I get this, I want to uniquely identify a memory stick (removable drive) for authentication. Thanks
1
by: Paul | last post by:
Hi Guys, I have written an application in Visual C#, I wish to add serial number validation dll to the installer, how do I go about doing this. Thanks in advance. Paul Mathebula
5
by: Jassim Rahma | last post by:
my CPU mac address is BFEBFBFF000006F6 and my software name is : shefa is there any function or encryption way in C# to generate a 24 digits (alpha numeric) from both CPu MAC address and...
16
by: malteselemon | last post by:
Using Access 2003, I need to create an automatic Serial Number. Example "7235-E001" "7" is current year "235" is day "-E" is static "001" is sequencial, restarting at "001" each day I set a...
2
by: Lit | last post by:
Hi, How can I get the CPU serial number via C# How can I get a screen shot via C# Thank you, Lit
0
by: BT08 | last post by:
I am creating an automated questionnaire (form) in excel to be used to entry data. I need to create a unique survey number. The survey number should be linked to a retrieve button. The goal is if...
12
by: Ivan Popov | last post by:
I need to generate a unique serial number for each field in the table. The serial number shall consist of two letters and six numbers, for example AA123456. Someone can suggest how to do it in MS...
13
by: Scott Kaempfe | last post by:
I need to identify parts we buy at our plant. I want to give each a unique serial number that is based on the date. We may have multiple parts on a given date so I want to have the unique serial...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.