Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 46
  • Last Modified:

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:

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
BHForum
Asked:
BHForum
  • 6
  • 3
  • 3
  • +1
1 Solution
 
Nick67Commented:
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
 
Nick67Commented:
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
 
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 & '-%'
)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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.

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
 
Nick67Commented:
Upon further review of your CSV, which I take it came from the table, I'd go this route.
Sample attached
Serial.mdb
0
 
aikimarkCommented:
@Hainkurt

That is T-SQL, a different dialect than MSAccess SQL
0
 
aikimarkCommented:
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
 
Nick67Commented:
@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
 
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

0
 
aikimarkCommented:
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
 
HainKurtSr. System AnalystCommented:
aikimark, not just "-" also I added some ")"  :)
0
 
BHForumAuthor Commented:
@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
 
Nick67Commented:
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
 
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?.
0
 
Nick67Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now