How to create unique serial number based on todays date in format MMDDYY-XXX in VB?

[Scott Kaempfe]

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!

[Scott Kaempfe]

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

[ADezii]

Can you strip this Vendor Code from the Serial Number if it is present via an Update Query?

[Scott Kaempfe]

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.

[Scott Kaempfe]

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.