Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access - Create s/n from two fields

Posted on 2014-12-11
16
Medium Priority
?
42 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 61

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 46

Expert Comment

by:aikimark
ID: 40494964
@Hainkurt

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

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 61

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 46

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 61

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 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

972 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