Having an issue and don't know how to solve. Please Help. (1 Viewer)

Jon123

Registered User.
Local time
Today, 16:59
Joined
Aug 29, 2003
Messages
668
I'm trying to track a small set of part numbers, 13 different parts. 10 of the 13 parts have serial numbers and I have my table to not allow duplicates. Works fine for the 10 parts. So for the 3 parts that don't have a serial number I want to make a serial number. Something like NM-0001 and for every time one of these parts are entered I would like it to automatically update to the next number. Ex - NM-0002 and so on. I cant figure out how to find the last number entered.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 16:59
Joined
Apr 27, 2015
Messages
6,286
Simplest way to do,this is to have a table that holds the serial numbers. The primary field would be an autonuber; it would automatically increment, eleimanating the need for code to determine the next sequential number.
When it came time to assign a new S/N you would simply use string concatenation to add you NM prefix. Easy day...
 

Jon123

Registered User.
Local time
Today, 16:59
Joined
Aug 29, 2003
Messages
668
I think I'm getting close
So I have my table for the serial number NM-00001 and NM-00002
I have this code on my form
SNupdate = DMax("[SN]", "Tble-SN")
Me.[Serial Number] = (Me.[SNupdate] + 1)
This kind of works but I can't use the NM- infront of the number and if I have 00002 and add 1 to that it becomes 3 and not 00003.
So If I can get the number I need how do I add the NM-00003
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 16:59
Joined
Apr 27, 2015
Messages
6,286
Use the format feature in Design View to add leading zeros. For example if you want a 4 digit number, put 0000 in the format property.
 

Jon123

Registered User.
Local time
Today, 16:59
Joined
Aug 29, 2003
Messages
668
How do I add the NM- to the begging?
 

Jon123

Registered User.
Local time
Today, 16:59
Joined
Aug 29, 2003
Messages
668
I got it I think [SerialNumber] = "NM-" & [SerialNumber]
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 16:59
Joined
Apr 27, 2015
Messages
6,286
Make sure SNupdate is a text field in the table and then use this code:
SNupdate = "NM-" & DMax("[SN]", "Tble-SN")
Me.[Serial Number] = (Me.[SNupdate] + 1)
 

Jon123

Registered User.
Local time
Today, 16:59
Joined
Aug 29, 2003
Messages
668
Sorta. I need the format to be 0000-0000 and I cant get 0000-0001 to add 1 no matter what I do.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 16:59
Joined
Apr 27, 2015
Messages
6,286
Jon123,

Rather than walk you through this one step at a time, I made a demo DB for you. Download it and see how it works.

Keep in mind it is very crude and would need some bells and whistles if you plan on using this in a multi-user environment.

My intent was to give you an idea on how to go about what you are trying to achieve.
 

Attachments

  • SerNumDemo.accdb
    476 KB · Views: 62

Jon123

Registered User.
Local time
Today, 16:59
Joined
Aug 29, 2003
Messages
668
I need to convert it, error I need a newer access to open?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 16:59
Joined
Apr 27, 2015
Messages
6,286
It was done in 2016. What version are you using?
 

Users who are viewing this thread

Top Bottom