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
10 8016
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.
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)
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.
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.
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")
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.
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)?
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.
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?
Sign in to post your reply or Sign up for a free account.
Similar topics |
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
|
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
|
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
|
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 software name?
|
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 table field with "=right(Format(Now(),"yy") & Format(Format(Now(),"y"),"000"),4)" to create the year/day number.----I should be able to add &"-E"& to add the static "-E"----I tried to add this all together with the "autonumber", but that obviously...
| |
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
|
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 entry data person realized he/she made a mistake, they can write a survey number on the cell, and click on the retrieve button to be able to see the data and make the necessary changes overwriting the old entry. Two things are needed:
1. Before the...
|
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 Access
|
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 number in the format:
MMDDYY-XXX where XXX is a unique number that start with 001 for the first item of the day. 002 for the second, etc. but starting over the next day.
When a user enters info in the database, it may already have a number. If...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |