MS Access - Create s/n from two fields

I am building a database to create serial numbers for parts by combining two manually entered fields and one incremented field. The entire serial number will look like this:


The last three digits need to increment for anything with the same PartNo (the location number is not part of the serialization, only the final number itself if that makes sense). So when a user creates a new Full Serial Number, they enter the PartNo and Location into separate fields. The form should then lookup for anything using the same PartNo and then give the next available UniqueID, and then combine all three fields to makeup the Full Serial Number.

I hope that's clear enough. If not, please let me know.

Thanks a bunch!
Who is Participating?
Nick67Connect With a Mentor Commented:
This bit of code pads it out

strIncrement = Me.txtNewIncrement
NumChars = Len(CStr(Me.txtNewIncrement))
For x = NumChars To 3
    strIncrement = "0" & strIncrement
Next x

I misread your CSV when I re-created the table
Change the 3 to a 2
strIncrement = Me.txtNewIncrement
NumChars = Len(CStr(Me.txtNewIncrement)) 'How many digits are there? at least One!
'Three characters this code won't fire
'Two and It will fire one
'One and it will fire twice
'Padding the zeros into the string
For x = NumChars To 2
    strIncrement = "0" & strIncrement
Next x
This happens often enough I should write an article about it.
Can you post a sample with a table that has a bunch of sample data like C2345626-01532-001

Basically, you are going to create a query of SerialNumbers Like "C2345626*"
That gets you the right ones.
Next, in the query in a calculated field you shear off the last three characters and coerce them to integers, order the query by this field descending and adds one
NextUp:CInt(Right([SerialNumber],3)) + 1

Your code that makes the serial number then calls for the Top 1 of this query.
You build your serial number
and then you check that it actually is unique before you slam it into the table
Because in a multi-user environment, that may not always be a given, know how time can lag!
There is of course the problem of a first one--but if you test to see that the query had no records, you know that 001 is going to be what is required.
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

HainKurtSr. System AnalystCommented:
try this (not tested)

select @PartNo & '-' & @Location & '-' & cint(nz(right(lastserial,3),0))+1
from (
select max(serial),3))+1 as lastserial from parts where
serial like @PartNo & '-' & @Location & '-%'
BHForumAuthor Commented:
Nick67: Here is a CSV output of the sample data. The VDFullSerial would be what the query creates. The VDPart would be entered and queried upon to determine what the next VDSerial should be. The VDLocation is irrelevant to the incrementation and is merely added to the VDFullSerial for future reporting. Regarding the environment, there will only be one person creating the serial numbers, all others will be reporting only and even then only a couple of times a week. And yes...if the query for VDPart comes up null, then 001 would be used.


Open in new window

Upon further review of your CSV, which I take it came from the table, I'd go this route.
Sample attached

That is T-SQL, a different dialect than MSAccess SQL
Here is the VBA statement for assigning the value to a control (txtFullSerialNumber in this example)
txtFullSerialNumber.value = txtPartNo.value & txtLocation.value & _
Format(Right("000" & DMax("FullSerialNumber", "PartsTable", "FullSerialNumber Like '" & txtPartNo.value & "*'), 3) )+1, "000")

Open in new window

You didn't!
You naughty boy!
DMax of all things!

That might play nice in a strictly single-user environment, but get a lot of records or a couple of users and it'll be a bug-prone performance pig.
I'll give you that it'll nominally work
YOU wouldn't code it up for yourself that way, would you?

HainKurtSr. System AnalystCommented:
I guess aikimark post should be fixed as this:

txtFullSerialNumber.value = txtPartNo.value & "-" & txtLocation.value & "-" & _
Format(Right("000" & DMax("FullSerialNumber", "PartsTable", "FullSerialNumber Like '" & txtPartNo.value & "*'"), 3) )+1, "000")

Open in new window

Yes.  Forgot the hyphens.  Thanks, Hainkurt.


Given the nature of this question, I wouldn't have any problems using DMax().  It might be required if you needed to write an Update query, since the use of aggregate functions can cause the query to be read-only/non-updatable.  If you have multiple users creating these new accounts, you would want to trap duplicate key errors during the insert operation.
HainKurtSr. System AnalystCommented:
aikimark, not just "-" also I added some ")"  :)
BHForumAuthor Commented:

So that .mdb shows any existing in the pull down. If existing then auto increment the serial. User adds location and press create. What would need to be done to set the field as a text box only and when the user leaves the field it does a lookup, if nothing exists, places a 1 in the New Increment. If it already exists, then places the next available in the New Increment field. Sorry...brain dead today.
The combobox is unbound and has no NotInList event.
So, the combobox will take anything in it -- no need to make it a textbox
when the user leaves the field it does a lookup, if nothing exists, places a 1 in the New Increment.
Private Sub cboPart_AfterUpdate()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("select * from tblVDSerialNumbers where VDFullSerial like " & Chr(34) & Me.cboPart & "*" & Chr(34) & " order by VDIncrement Desc;", dbOpenDynaset, dbSeeChanges)
If rs.RecordCount = 0 Then
    Me.txtNewIncrement = 1
    Me.txtNewIncrement = rs!VDIncrement + 1
End If
End Sub

Open in new window

Updated sample attached
BHForumAuthor Commented:
I like how that works. On this one, the VDFullSerial is adding 4 digit numbers instead of 3 in the VDIncrement portion. But I'm not sure where that is in the code to change to 3. What am I missing in there?.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.