Create Specific Unique Serial Number

[Wishbone1]

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:

Public Function fGenerateNextSerialNumber() 
Dim strCurrentYear As String 
Dim strCurrentDay As String 
Dim strStaticValue As String 
Dim strSequentialNo As String 
Dim strLastSerialNo As String 
Dim strLastSequentialNo As String 
Dim strNextSequentialNo As String 
  
strCurrentYear = Right(Year(Now()), 1) 
strCurrentDay = DateDiff("d", CDate("1/1/" & Year(Now())), Now()) + 1 
strStaticValue = "-43" 
  
'get ready to extract the Sequential Number 
strLastSerialNo = DLast("[SerialNo]", "tblTest")        'produces 7235-4300 
strLastSequentialNo = Right(strLastSerialNo, 2)            'produces 01 
  
'Generate the Next Sequential Number 
strNextSequentialNo = Format(Val(strLastSequentialNo) + 1, "00")   'produces "02" 
  
  'Generate the next, Unique, Serial # 
  fGenerateNextSerialNumber = strCurrentYear & strCurrentDay & strStaticValue & strNextSequentialNo 
End Function

[vivekprakash]

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

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

Private Sub Form_BeforeUpdate(Cancel as Integer)
  'If it is a new record, assign date and DaylyNumber
  If Me.NewRecord Then
    Me.DateCreated=Date()
    Me.DailyNumber=nz(Dmax("DailyNumber","[YourTableNameHere]","DateCreated=Date()"),0)+1
  End If
End Sub

The DMax function finds the maximum used value for all records matching DateCreated=Dat e(). (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:

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)

[Wishbone1]

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.

[TheSmileyCoder]

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.

[Wishbone1]

TheSmileyCoder,

I enter the information as you provided. The Transaction_Dat e 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?

Private Sub Form_BeforeUpdate(Cancel as Integer) 
  'If it is a new record, assign date and SerialNo 
  If Me.NewRecord Then 
    Me.Transaction Date=Date() 
    Me.SerialNo=nz(Dmax("SerialNo","[Inventory Transactions]","Transaction_Date=Date()"),0)+1
  End If 
End Sub

Serial: Year(Transaction_Date) & DatePart("y", Transaction_Date) & "-43" & Format(SerialNo,"00")

[Wishbone1]

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.

[Wishbone1]

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

[TheSmileyCoder]

The format function will take a number and write it according to the format specified. For example:

format(7,"00")="07"
format(78,"00")="78"
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_BeforeUpd ate 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.

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.

[Wishbone1]

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?

[TheSmileyCoder]

@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_Dat e) 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.