Solved

MS Access - Create s/n from two fields

Posted on 2014-12-11
16
37 Views
Last Modified: 2016-07-12
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:

PartNo-Location-UniqueID
C2345626-01532-001

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!
0
Comment
Question by:BHForum
  • 6
  • 3
  • 3
  • +1
16 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40494520
Fun!
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!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40494530
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.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40494536
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 & '-%'
)
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:BHForum
ID: 40494680
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.

VDFullSeriall,VDPart,VDLocation,VDSerial
C143291-0123-001,C143291,0123,001
C143291-0429-002,C143291,0429,002
C168225-0032-001,C16822,0032,001
C143291-0123-003,C143291,0123,003
C152899-0429-001,C152899,0429,001

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 40494779
Upon further review of your CSV, which I take it came from the table, I'd go this route.
Sample attached
Serial.mdb
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40494964
@Hainkurt

That is T-SQL, a different dialect than MSAccess SQL
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40494989
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

0
 
LVL 26

Expert Comment

by:Nick67
ID: 40495004
@akimark
<tongue-in-cheek>
You didn't!
You naughty boy!
DMax of all things!
</tic>

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?

Nick67
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40495026
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

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40495337
Yes.  Forgot the hyphens.  Thanks, Hainkurt.

============
@Nick,

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.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40496348
aikimark, not just "-" also I added some ")"  :)
0
 

Author Comment

by:BHForum
ID: 40501248
@nick67

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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40501394
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
Else
    Me.txtNewIncrement = rs!VDIncrement + 1
End If
End Sub

Open in new window


Updated sample attached
Serial-v1.mdb
0
 

Author Comment

by:BHForum
ID: 40501581
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?.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40501662
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
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question