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 not, I want them to push a button and it takes today's date, serializes it based on the number of parts already existing for that date, and place that unique serial number in the PartID box for them. Then they can proceed to enter the other info.
I am learning Access 2007 and have been doing some Macros but at this point I tend to find things others have done and then learn them enough to modify them. I can figure this out eventually but I don't use Access enough to be efficient and I don't have the time.
Ideally, a VB macro done for me...at least enough to query the existing data and create the number would be great.
Does anyone already have somethign like this or can someone get me started???
Thanks!
13 8027
Just subscribing for now...
Are you always evaluating against the Current Date, or are you reading a Date Value from elsewhere?
Well..the way I had it set up on the form was that the form field had the date. I am currently setting the date by making it TODAY's date. So I have a macro getting the date from this field so it really shouldn't matter if it is TODAY or another date.
If for some reason it matters, I would go with TODAY's date.
Example:
1. John has a new tool that needs a unique ID number
2. It queries the database to determine if there are any tools logged into the database on this same date
3. Returns the highest unique number in MMDDYY-XXX
4. Assigns a new serial number with todays date and the next serial number.
OK - let me preface this by saying that this is a work in progress so ignore how it is formatted etc
Here is my code. The code is bold is where I am having problems. I need to either create a query that only extracts Serial Numbers with todays date OR it just looks for the last unique serial number that starts with Todays date. Not sure of the best approach.
Private Sub Command0_Click()
Dim strCurrentYear As String
Dim strCurrentDay As String
Dim strCurrentDate As String
Dim strStaticValue As String
Dim strSequentialNo As String
Dim strLastSerialNo As String
Dim strLastSequentialNo As String
Dim strNextSequentialNo As String
Dim fGenerateNextSerialNumber As String
Dim strCurrentDateTest As String
strCurrentDate = Format$(Now(), "mmddyy") strLastSerialNo = DLookup("[SerialNo]", "tblTest", "[SerialNo] = " & strCurrentDate & "*")
'get ready to extract the Sequential Number
MsgBox strLastSerialNo
strLastSequentialNo = Right(strLastSerialNo, 3) 'extracts serial number
'Generate the Next Sequential Number
strNextSequentialNo = Format(Val(strLastSequentialNo) + 1, "000") 'produces next serial number
'Generate the next, Unique, Serial #
fGenerateNextSerialNumber = strCurrentDate & "-" & strNextSequentialNo
MsgBox fGenerateNextSerialNumber
End Sub[/indent][/indent]
I have a work in progress, and will probably Post it sometime this evening...
Wow! Really? Thanks! I can send you my whole file if that helps but it is probably a mess in your eyes.
The following Function will: - Search a Table (tblTest) and see if the Date Component of any Serial Number ([Serial#]) is equal to Today's Date:
- 'Are there any Serial Numbers for today's Date?
-
intCount = DCount("*", "tblTest", "Left$([Serial#],6) = " & Format$(Date, "mmddyy"))
- If there is no Serial Number for Today's Date the Function returns:
- Else 'No Serial Number for Today's Date, so set it
-
fGenerateSerialNumber = Format$(Date, "mmddyy") & "-001"
-
End If
in this case: - If a Serial Number(s) exist for Today's Date, then the Code will create a Recordset filtered for Today's Date sorted by the Numeric Component of the Serial Number, namely the XXX in mmddyy-XXX:
- strSQL = "SELECT [Serial#] FROM tblTest WHERE Left$([Serial#],6) = '" & Format$(Date, "mmddyy") & "'" & _
-
" ORDER BY Val(Right$([Serial#],3)) DESC;"
- Notice the Sort Order is Descending on this Numeric Value, so the greatest Value is the 1st Record in the Recordset. The Code does not assume that the greatest Numeric Component of the Serial Number will always be the 'Last' Number for a given Date.
- The Function now returns a New Serial Number, incrementing the Numeric Component by +1:
- If intCount > 0 Then 'Yes there is at least 1, so Increment the Last
-
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
-
fGenerateSerialNumber = Left$(rst![Serial#], 7) & Format$(Val(Right$(rst![Serial#], 3) + 1), "000")
-
Else
- The Function in its entirety is listed below, any questions, feel free to ask.
- Public Function fGenerateSerialNumber() As String
-
Dim intCount As Integer
-
Dim rst As DAO.Recordset
-
Dim strSQL As String
-
-
'Are there any Serial Numbers for today's Date?
-
intCount = DCount("*", "tblTest", "Left$([Serial#],6) = " & Format$(Date, "mmddyy"))
-
-
'Create a Recordset based on all Records having a Serial Number consisting of Today's Date.
-
'Order By the Numeric Component (XXX in mmddyy-XXX) Descending so greatest Value is the 1st
-
'Record in the Recordset
-
strSQL = "SELECT [Serial#] FROM tblTest WHERE Left$([Serial#],6) = '" & Format$(Date, "mmddyy") & "'" & _
-
" ORDER BY Val(Right$([Serial#],3)) DESC;"
-
-
If intCount > 0 Then 'Yes there is at least 1, so Increment the Last
-
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
-
fGenerateSerialNumber = Left$(rst![Serial#], 7) & Format$(Val(Right$(rst![Serial#], 3) + 1), "000")
-
Else 'No Serial Number for Today's Date, so set it
-
fGenerateSerialNumber = Format$(Date, "mmddyy") & "-001"
-
End If
-
-
'Clean Up, if required
-
If Not rst Is Nothing Then
-
rst.Close
-
Set rst = Nothing
-
End If
-
End Function
I will try it soon.
Thank you very much. I really appreciate it!
Hi.
I tried this and it worked fine when there was no record with todays date but when I put a record with today's date into the table, it gave me the following error:
"Run-Time error 3061
Too few parameters. Expected 1."
On the following line:
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Post what you have for strSQL.
Hello. I just got back from vacation and proofed my code and everything seems to be working fine. Thank you.
Now I have a follow-up question. It turns out that some of our old unique serial numbers have a letter in front of the number which represents our vendor.
When I add these numbers to the table, the code to find how many unique serial numbers start with the date crashes.
It says:
"Run-time error '3464':
Data type mismatch in criteria expression"
and it shows that it stops at the line:
intCount = DCount("*", "ToolingID", "Left$([SerialNo],6) = " & Format$(Date, "mmddyy"))
Full code: - Private Sub Command17_Click()
-
Dim intCount As Integer
-
Dim rst As DAO.Recordset
-
Dim strSQL As String
-
Dim fGenerateSerialNumber As String
-
Dim Counttest
-
-
-
'Are there any Serial Numbers for today's Date?
-
intCount = DCount("*", "ToolingID", "Left$([SerialNo],6) = " & Format$(Date, "mmddyy"))
-
MsgBox intCount
-
-
'Create a Recordset based on all Records having a Serial Number consisting of Today's Date.
-
'Order By the Numeric Component (XXX in mmddyy-XXX) Descending so greatest Value is the 1st
-
'Record in the Recordset
-
strSQL = "SELECT [SerialNo] FROM ToolingID WHERE Left$([SerialNo],6) = '" & Format$(Date, "mmddyy") & "'" & _
-
" ORDER BY Val(Right$([SerialNo],3)) DESC;"
-
-
If intCount > 0 Then 'Yes there is at least 1, so Increment the Last
-
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
-
fGenerateSerialNumber = Left$(rst![SerialNo], 7) & Format$(Val(Right$(rst![SerialNo], 3) + 1), "000")
-
Else 'No Serial Number for Today's Date, so set it
-
fGenerateSerialNumber = Format$(Date, "mmddyy") & "-001"
-
End If
-
MsgBox fGenerateSerialNumber
-
Me.inFormat = fGenerateSerialNumber
-
Me.ToolingID = fGenerateSerialNumber
-
-
'Clean Up, if required
-
If Not rst Is Nothing Then
-
rst.Close
-
Set rst = Nothing
-
End If
-
End Sub
Can you strip this Vendor Code from the Serial Number if it is present via an Update Query?
I guess so. But since the code I have is counting the number of times that the first 6 digits of the serial number matches the 6 numbers of teh date in the MMDDYY format, would that mean I need to do an update query that strips the vendor code off of EVERY serial number?
I'm open to it...just not sure how to go about it.
Why am I getting that error? Will it give me the error if the alpha vendor code is at the END of the unique number? I can probably manipulate my codes to do that.
Actually, I just confirmed that if the letter is not in the first 6 digits, it works fine. I will just need to make sure that there are no letters in the first 6 digits.
Is there an easy way to make sure that when an operator enters a unique number that it gives them an error message if the code has a letter in the first 6 digits???
I can make a note on the form but want to mistake proof it.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: weiwei |
last post by:
hi,
is there anyway that I can validate my date format in asp?? I have
tried like date=now(), that gives me like 5/14/2004, the format should
be mmddyy, no space, no dash, and has to include 0 if...
|
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: Arne Beruldsen |
last post by:
I have a windows app using vb.net 2005.
I would like to create a serial number based on a couple of characteristics
of the customers computer (computer name, HD number..etc). I don't need...
|
by: Beckster6701 |
last post by:
I have a MSAccess database that is linked to a SQL server. I cannot
change the SQL server datatype as I am not the only one that uses the
database. I'm trying to convert the text field to a valid...
|
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...
|
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...
|
by: sheperson |
last post by:
Hi,
I have been working for several months on a sofware and now it is
finished.
I have a problem now and it is this:
Because I live in a country which there is no copyright law in it
(Iran!!!!)....
|
by: naaniibabu |
last post by:
I have one number i want to convert in to a date format
the size of the value is =8 Char
20091212
i ant to conver it in to a date format
my sysyem doent understand wether its adate or not its...
|
by: Keshia |
last post by:
I have a database that is for keeping track of inventory. I need to have my form for entering new inventory to automatically make a new serial number based on the category of the new item.
The...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
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,...
|
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...
|
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...
| |