Solved

MS Access - Create s/n from two fields

Posted on 2014-12-11
16
34 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now