How to create a unique serial no. based on item selections

[pbsnow]

How do I create a unique serial number, i.e., TR - "F" for furniture or "M" for Misc, or "V" if Vehicle if selected from a list of categories and a 4-digit serial number for the suffix, to look like this, "TR-F-1004."

TR will not change, only the letter based on the category selected and a 4-digit autonumber field to create a unique label for labeling inventory items.

In the example attached, "55 gallon barrel" was selected, so the
"label number" field populated "TR-55G1035" the 4-digit number comes should come from a auto number field.

Would really appreciate your help on this!

[twinnyfo]

pbsnow,

I am a bit confused. Do you want the 4 digit number to be an AutoNumber or not? If so, then there is no real need to calculate the serial number, as the record itself will have a unique identifier, and you can apply that number to your Serial Number during run time.

However, if this is not the case, but you actually want unique serial numbers for each type of item (e.g.:

TR-F-0001
TR-F-0002
TR-F-0003
TR-M-0001
TR-M-0002
TR-M-0003
TR-M-0004
TR-V-0001
TR-V-0002
TR-V-0003

Then you would need to query all items of that specific type, find the maximum numeric value, increment it by one, then concatenate the new serial number.

Either option is doable, but you must provide us more information on how you are generating this serial number.

Also, keep in mind that when you limit your serial numbers to four digits, you may ultimately run into problems if you have more than 9,999 entries (depending on the size of your operation).

Lots of different options with this, but, again, we need more information to properly advise.

[zmbd]

This topic has been covered many times here at bytes...
Please do a search on this site,
Use the box next to the orange magnifying-glass at the top of the page... enter the search terms:
ms access serial number

The normal advice is to do this in a query using a calculated field and not to store this information:

Allen Browne: Calculated Fields

Calculated Fields MS ACC2003 Although this is for ACC2003 this has not changed in the newer versions.

so in your case in the query...
"TR-" & [catagory_field] & [autonumber_fiel d]

You can get fancier with this and check for null values and other conditions; however, this is the basic method.

[twinnyfo]

@Z,

I think it's earlier where you are....

;-)

[pbsnow]

Yes, I want something like that, and, the autonumber field does not have to start over with each different item, it can, but whatever is easier.

What I do want, however is, if a person selects, i.e., Barrels as a category, how do I best create the items table so that once they select the barrels category, there are different size barrels, i.e., 55G for 55 Gallon Barrel or CB for cardboard barrel, resulting in label number field being "TR-CB-1001" which would represent that it is a cardboard barrel. If they select box, then you have more options for the box category, i.e., LRGB for large box, or SM for small box, etc., resulting in the label number field displaying "TR-LRGB-1001" for large box. There would be three 4 fields, Category, Category Options, if any (since not all cagetories will have additional options, and price.

[NeoPa]

Your original example didn't match your explanation. Your latest post confirms the original explanation as wrong but leaves only a couple of examples of some possible results. You need to redo your explanation properly so that it accurately reflects the question you mean to ask.

Please appreciate that, though the time of people here is offered for free, it is nevertheless of value to them and others they may otherwise be able to help.

[pbsnow]

Thank you for your response. My apologies for confusing anyone. In the next post I will try to give a better example of what I am trying to do.

[pbsnow]

Okay sorry for any confusion. I want to design a form which based on the selections, will generate a serial numbered label for that item. So, if someone selected from the category Barrels, and then chose 55 Gallon Barrel, the serial number would look like this "TR-55G-1001" TR will remain the same (represents company initials), 55G for 55 Gallon Barrel and 1001 from 4-digit autonumber field. I have attached a sample of the items table, for your reference. I need to also somehow have the form populate the fields based on their selections so it is a two part question, I guess.

So, main question is 1) how can I get a serial number field generated based on selection; and 2) {NeoPa - SNIP - Only one question allowed per thread - feel free to post another and link to this one.}

[NeoPa]

It's always difficult to explain technical requirements. Nevertheless very important to do so clearly and accurately, for the reasons explained in my last post. Let's see if we can help you clarify this as your JPEG gives us clues that are absent from your explanation.

The format of the serial number is :

1. "TR-"
2. Either a single character code "F", "M" or "V" as specified in your first post or a string of an indeterminate number of characters from the field [Code].
3. Sometimes a hyphen ("-") but sometimes not (again depending on which posts you go by).
4. Four digits of numbers to differentiate the item from any similarly specified items. Not important whether this is unique globally or just within similar serial numbers.

I was hoping to go forward from here but closer inspection of the information you've provided so far seems to indicate that the requirements have changed dramatically from first post to most recent one. This will need to be clarified in order to continue.

I appreciate you're probably trying to be clear, but would suggest a little more effort is required as, even after my earlier request, there is still much confusion caused by your explanations of the question.

NB. If the [Code] field is ultimately to be used in the determination of your serial number then it would be wise to ensure that what is used is of consistent length. Sections within an identifier, such as the serial number you propose, if of varying lengths, can cause many difficulties when trying to work with it in code. Generally not insuperable, but easily avoided with consistent lengths. That is to say that if one serial number is "TR-55G-1032" and another is "TR-MSCB-1043" then this will cause you problems. Well worth bearing in mind.

[pbsnow]

Thank you very much for your help. I will try what you have suggested and I will break it down to one character. I have tried many things and have not reached the desired result mainly because I am rusty with my programming since I haven't programmed anything in many years. I am better at manipulating things that have already be designed which are similar to what I want.

I would guess the first thing I have to figure out is how to populate the field with combo boxes and/or list boxes when I am inputting the desired information. Thank you very much for your time and your help on this.