Auto Increment

[jimsterman82]

Hello,
I am looking to auto-increment a part number every time the New Part number form is opened. I am trying to use the default value of the Part Number field in my form for this.

Will an input mask interfere with this?

We are moving data over from an old spreadsheet and so many part numbers exist. This has caused the autonumber option to be out of the question.

My challenge is: our part numbers include letters (ex. SM-004112)

Out of desperation to get things moving I have set up a query to show the last drawing number entered and have included an instruction on the table to type in the number after the one shown (The number from the query is shown in text next to the data entry field).

I have been reading articles for several days no to no avail. It has been many years since I set up a database and have lost much of my original knowledge.

Please help!

 

[isladogs]

Welcome to AWF

I would avoid using an input mask

The best approach will depend on your part numbering system
If your part numbers always start with SM or 2 letters, I suggest you move that part to a separate field (PartLetter?) or possibly add it in formatting.

The first thing is to get the next number value
For example, you can extract the current highest number value in various ways e.g. use Abs(Val(SM-004112)) = 4112, add 1 then format with additional zeroes => 004113 & prefix with SM- ... or concatenate with the separate PartLetter field if used

If you can provide a few more part number values to indicate the general pattern, someone can provide clearer advice than I have done above

 

[June7]

Generating custom unique identifier is a common topic. Suggest search forum.

That Abs(Val()) is a new trick for me. Thanks, Colin.

 

[isladogs]

You're welcome.
That particular example would have worked just using Abs

 

[jimsterman82]

Thank you for the reply.

The numbering system is generic. So all numbers follow the same format of SM-000000.

So: SM-004112, SM-004113, SM-004114, etc...

 

[isladogs]

In that case, I would use a number or autonumber field but format the field to display SM- at the start together with leading zeroes to make up 6 digits
So the value 4112 would be displayed as SM-004112 etc.

If unsure how to do this, use Access help or a Google search for formatting fields

 

[AccessBlaster]

The prefix can reside in a query as an alias only, and then concatenated with "format" prior to viewing or printing. You avoid storing redundant information.

 

[jimsterman82]

Thanks again,

Unfortunately I am unable to use the autonumber field. Some of the older numbers were duplicated and some had -1, -2, etc added to them.


If I add the SM- as formatting or as an alias is it still searchable as the whole number (ex. SM-004112)? Or would I have to search for only the number portion to find existing data (ex. 004112)?

 

[isladogs]

You would search for the saved value e.g. 4112.

 

[Wayne]

I see you have both an alpha and a numeric component to your part number. Considering ONLY the numeric portion of it, is it possible that there could be duplicate numbers? (for example: SM-000125 and DC-000125)

Wayne

 

[gemma-the-husky]

Even if you store the prefix and item number separately, you may still have an issue

if you store these parts separately, then you can easily increment the part number.
(This also allows you to have prefixes other than SM)
SM
4114

However, the part number is now an integer, not a string, and it's easy to increment.
As long as you always need the same length of partnumber, you can say

displaynumber = right("000000" & partnumber, displaylength)

so 41114 will display as 004114.

However note that you won't be able to manage a partnumber above a million (although you could easily make the expression produce 7 digit numbers). Also you wouldn't be able to have part numbers of both "04114" AND "004114", because the part number in both cases is actually just 4114.

 

[gemma-the-husky]

@OP

autonumber. You definitely do not want an autonumber. It's not guaranteed to maintain a sequence, and it won't deal with gaps. You need a managed sequence.


Nextnumber = (highest number in the table) plus 1

(highest number in the table) needs to work on an integer, not a string, so STORING the number with a SM- prefix turns it into a string, and makes this much harder. At some point you will almost certainly decide you want a different prefix for some parts, and then you will be stymied. If you design it so it already manages a different prefix (which is actually easier than manipulating a string), then you don't even have an issue.

You get highest number by using a dmax function. Finally note that if 2 users may be adding parts at the same time, you need to be carefully about the possibility of both users getting the same "next number".

 

[jimsterman82]

I didn't realize from the earlier messages but we have some older numbers with -01 and -02 on them (A number data type wont allow the -01, -02). Am I able to auto increment a text field if it has only numbers including the -01?

Ex. SM-004112-01

This happens rarely, but it does happen.

 

[June7]

Yes, the DMax() will still find the ID as long as the string pattern is consistent (leading zeros). However, code to increment value will be more complicated. This is string manipulation and will have to test for presence of the suffix and deal with it.

Then there is figuring out where and when to call procedure. Is this a multi-user db?

Review https://www.access-programmers.co.u...highlight=generating+custom+unique+identifier

 

[jimsterman82]

Yes it is a multi-user database.

 

[AccessBlaster]

If I add the SM- as formatting or as an alias is it still searchable as the whole number (ex. SM-004112)? Or would I have to search for only the number portion to find existing data (ex. 004112)?

You could save the concatenated string as a composite key or field, but I think most experts on here would say it's bad practice.