Intelligent Part Number Structure?

[AuburnDesigner]

I am a Product designer and not very familiar with Access. I am completely out of my element when it comes to coding, but I am trying to create a Intelligent Part Number Structure. I understand that IPN's are not desired when creating a part number, but I do not have the option in this case, as everyone above me is adamant about an IPN system.
I have searched many topics and I found a DMax function, and thought that would give me what I needed , but It did not return the values I thought. My structure consist of 10 digits and the last 4 are a counter. The first 6 digits are all variables that control the last 4 digits.
Example:
99-10-09-0001
99-10-09-0002
90-10-09-0001
90-10-09-0002
90-11-09-0001
etc. But I will still need to continue with the first example If I ever return to that set. so it would cont.
99-10-09-0003

I have the following tables.
Master = WarrantyT
1st variable = ProdT
2nd Variable = YeT

My Form (WarrantyF) is intended to be "idiot proof" and only has selection options. This form currently fills in the form up to the last 4 digits, but I need advise or a direction to finalize the last 4 digits. There should only be one user on this DB at a time, so dual users will not be able to steal numbers like DMax.

Im sure I let something out, but I will answer any questions.
Thank you in advance for your time,
Adam

 

[boblarson]

It's okay to create those part numbers. The thing that ISN'T okay is using them as a primary key. You should just let Access manage that behind the scenes with autonumbers.

That being a given, you can use a couple of DLookups to find the last numbers entered for a particular structure. So, you can find the max number where the first set of numbers match.

 

[gemma-the-husky]

dmax doesnt "steal a number" it simply asks what the higest number is in the file.

i would be inclined to split your number so you have a prefix (leftpart/base) and a counter

then you can say

highestnumber = dmax("sequencenumber","mytable","[prefix] = " & whatever)

and add 1 to it for the next number.

its a bit trickier to do this, if the counter itself includes the prefix of the number.

---------
secondly do NOT store the dashes. just store 901009 in the prefix, and display the dashes by an display mask where appropriate,

if your prefix numbers, are likely to start with zeroes, then you will need to store them in a text field, not a number field

 

[AuburnDesigner]

"dmax doesnt "steal a number" it simply asks what the higest number is in the file."
With this point I was trying to state that I had read that if two users are using the same database, that DMax will assign the next highest number, but If two users are trying to grab the next highest number at the same time there may be an error due to two users....

"i would be inclined to split your number so you have a prefix (leftpart/base) and a counter"
I currently Have this layout, but It is concatenated w/o the last 4 digits. Is this okay, or is there another way that you would suggest.

"highestnumber = dmax("sequencenumber","mytable","[prefix] = " & whatever)
and add 1 to it for the next number."

As I said I am still new to this, so I will try to put this "Dumb" terms for me.
Highest number = last 4 that I want to find
Sequence number = Im a little confused about this but Im guessing not the first 6 -??? The order in which the first 6 are arranged?
My Table = self explanitory
prefix = first 6
& Whatever = -???? No idea
---------
"secondly do NOT store the dashes. just store 901009 in the prefix, and display the dashes by an display mask where appropriate,"
The Dashes were merely for a visual break for posting, The actual number will not have dashes, spaces, etc.

"if your prefix numbers, are likely to start with zeroes, then you will need to store them in a text field, not a number field"
Currently all fields are stored as Text, except the last 4 digits

Thank you for your time, I greatly appreciate all the help.
-Adam

 

[AuburnDesigner]

How would you set up the highest number? Would it be a previous part number or a new table? I have tried and tried to get this to work, but Im still lost on the lookup and highest number method. Any tips?
Thanks in advance.

 

[ajetrumpet]

I am completely out of my element when it comes to coding

does this mean you are an HTML guy and not a VB guy? LOL.

This form currently fills in the form up to the last 4 digits, but I need advise or a direction to finalize the last 4 digits.

how about piecing the part number together in two as you fill in the control on the form?

"whatever code you use to populate the 1st part now" [B][U][B]&[/B] cstr(
clng(right(me.partnumberbox, 4) + 1))[/U][/B]

make sense?

 

[AuburnDesigner]

Okay, I did some more research and came up with the following code. It appears to work the way I want it to, but I just want to verify to make sure it looks correct to a trained eye. And What issues might I have in the future with the way this code is set up?

    Me.BaseID = Format(Nz(DMax("[WarrantyT].BaseID", "WarrantyT", "Prefix = '" & Me.Prefix & "'"), 0) + 1, "0000")
    PN = Prefix & Format([BaseID], "0000")

As I said... so Far It appears to work correctly, Do you see anything wrong with this?