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_field]

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.

[pbsnow]

Hello Neo,
It is not so much that my explanation has changed. I was trying to clarify that my design has 2 parts. The design is for a small shipping company which ships,barrels, furniture and sometimes vehicles to other places.

All I am trying to do is help a friend to organize/be able to keep track of what is being shipped, by creating some type of label to place on each item.

Based on the selections made by the data entry person, it should generate a label which will make it easier to find once the item reaches its destination.

I am having issues in 2 areas:\

1) with trying to design an order form which allows populates the fields for the label based on the selections. I want a type of "If/Then" type of form where if I enter, i.e. "barrel" for category, the Code on the label will have a "B" for barrel, but there are different size barrels to choose from, just like there are different size cars, boxes, etc. And the prices vary for shipping those items.

Initially, I did just want to be shown how to create the serial number labels for the database, but now I am also having issues just trying to create a form with list boxes and combo fields for the selection process.

[NeoPa]

Let me help you here by making very clear that you will not benefit from trying to skip over that necessary portion of the process. It'll make it harder for us to help you, of course, but what I'm saying is that it makes it much harder for you especially.

That first step is to analyse exactly what it is that you want. I'm still not clear on that point, even from your latest post. I may be wrong, but I suspect you aren't clear either.

The TR- part is simple enough. No ambiguity there. The last part is equally set and clear. Maybe you should clarify whether or not it needs the hyphen separator, but other than that it's clear.

Where the difficulty comes for all of us is that you haven't yet decided on what is required in the middle. You have a fuzzy idea of what it needs to do for you (or your friend in this case) but not what you need there. This step should be taken first, before you start asking for help to produce something you're unable to specify.

To specify what you need you must first ask yourself some basic questions. For simplicity I'll refer to this middle section of the serial number as part B :

1. Must this part B value uniquely reflect the item (identified by [ItemID] in your table) or is it adequate to reflect simply the [Category]?
2. Is the numeric [ItemID] value ok for it or must it be alpha to give a clue as to the item for humans?
3. Are you going to worry about the number of characters in part B? Do you believe it's important that serial numbers are all of the same length?
   Bear in mind here that you have already been advised by an experienced designer of databases (That'd be me.) that values of different lengths are likely to cause you problems of one sort and another.
4. If you would like to use the [Code] value what are you going to do about the fact that someone has designed the system to allow such values to be of varying lengths?

When you have answered all these questions you will be in a position to ask the question clearly and in a way that makes sense.

NB. I invite you to reread your responses in here. You'll notice that, even though you've put some effort into responding, which I recognise and appreciate, you have only very rarely actually dealt directly with the points raised. I suggest you focus carefully on trying to be more thorough in dealing with these responses. It is very difficult to deal with someone who isn't. I appreciate that it can all be quite confusing until you get more used to it, but if you read things through more than once you are less likely to miss important points or fail to respond to a suggestion. As it's your question we're all trying to help with I would suggest it's worth the extra effort required.

[pbsnow]

First to answer your question, I reaslly don't care about the hyphens or separators. I can do a custom mask for that. Let me say first, thank you for helping me. And let me go on to explain that I have always been good at manipulating an existing database, but not good at the visual basic programmming part of it, as I am only self taught (a novice). Again, what I am trying to come up with is the best way to create a serial number with meaning, whereas, if someone is shipping a barrel, the label will have be consistent with the fact that it is a barrel, I could leave out the extra characters for small, medium, large, etc, and could live with just the letter "B" for barrel, V for vehicle, X for box, etc.

I am, however struggling with the list boxes which answer the questions about the items and then generating the label

[pbsnow]

I will try to attach the code of what I have so far

[pbsnow]

Okay, I see your point. I will review all of your responses carefully before I attempt to respond, again...

[NeoPa]

PBSnow:
First to answer your question, I really don't care about the hyphens or separators. I can do a custom mask for that.

I hear you and understand your point of view. Just take a second though, to consider how confusing it can be for someone reading your question when it changes as we proceed. We don't know what's in your mind unless you express that in writing. In truth, it's not a difficult thing to handle at all, as long as it's clear what is required.

Now, even before worrying about your existing code, it seems we've made some real progress on the most critical part of the specification - That part which has been at the root of most of the delays so far - which is the middle part. You say you have some experience and comfort where you're dealing with databases so I expect you'll know that if we decide to take the first character position to put in that part then we'll need a value to get that first character from. Can I assume that you would be happy with the first character of the field [Category]? If so then we can actually proceed onto the main point of the question.

What I'll do for now is to assume that the answer to that is yes and proceed accordingly. If it proves an unfounded assumption then we can deal with that later. We want to show that we're really here to answer the question, and only delay with explaining how to ask it when that proves unavoidable.

As it happens, I just recently started playing with a different approach that appears to be simpler and better than the one I've previously used for such situations. We can try that out and you can say if it suits. I expect you'll be happy to hear it is less VBA based but relies more on SQL (or a query if you prefer). It's fundamentally what Zmbd was saying in post #3.

Before we start I will give names to the items I need to reference. They may be different from those in your database. Change the suggested query to match the names in your database before you test it of course. I'll assume, for want of any information on this, that the table we are working on is as displayed in your post #8 and is called [Items]. I'll further assume the name of the field to be created is [SerialNo] and that [ItemID] is the AutoNumber field.

First create a query which selects all the fields in the table but add a new, calculated, field as :

NewID:'TR-'+Left([Category],1)+Right(Format([ItemID],'0000'),4)

Next, build a form that sets the value of the control from [NewID]. More on that later if you need it. Start with this and see how far you get (Not forgetting to answer any outstanding questions still of course).

[pbsnow]

Thank you much. You are right on in your assumptions, so I will do exactly what you ask and get back to you.

[pbsnow]

Wow, that was amazing. It actually worked, generating a number "TR-10001", with the first 1 representing the order the "category" items. I would prefer that it be a letter instead or taken from the "Code" field instead of the category field... So I will work on that! But that you very much. Very impressive and simple.

Now I just need to either eliminate the category field and just focus on the "Code" field, because I don't really know how to invoke an "if/then" type query to say: If it is a barrel, then which size barrel? type of question/answer

[pbsnow]

I did edit your query, and I received the result that I wanted... thank you so much... decided to go with the code and 4 characters... thank you.....

Now I am working on Part 2:

Here I am attaching the category table and the category form...

Problem is This works well if each category had one item (or size). I select the category, populates the code and the price, however what is the best way to create the form so that the category once selected can have multiple items, i.e., sizes

Or, not multiple items, but how can I, e.g., if the category is box, the right size of the box so that it populates the correct price for the box?

In the category table would I have to list each item with price and then create a query that filters your selection?

[NeoPa]

Let me correct my earlier formula by adding the separator that I should have included. This makes working with numerals so much easier as it clearly differentiates between each section of the code :

NewID:'TR-'+Left([Category],1)+'-'+Right(Format([ItemID],'0000'),4)

That said, we need to review how you post again I'm afraid. {Added later: Just to be very clear, this is not something you'd be expected to know. This is just how it is here. I reread and realised this sounds like I'm criticising, which would have been somewhat unfair.} Information posted in pictures will not work well for you as a general rule. It is a massive amount of data for a very small amount of info. Much better and more clearly posted as text in the post. When it makes sense to post pictures then it's important to select only as much of the picture as shows what you want to show. Otherwise it's just too small when displayed to impart any information. Post #8 was a good example how to do it well. Post #19 an example of a waste of space because no-one can read it.

From the info in post #8 it seems all the category values are alpha, so I'm surprised your value came up as a '1'. It was part of the reason why I believed that [Category] was a sensible field to use. Whatever you use is up to you, but you really need to go through the process I outlined in post #12 prior to deciding. This should be done before ever posting the question. It makes little sense to ask for help with a question you haven't even outlined to yourself yet. No-one can know what you need if you don't even know yourself. Asking for help trying to work out what it is you're thinking really makes very little sense. This is fundamentally what you're doing here.

To try to help in this area though, if you're considering [Code] or [Category] or even some other field we know nothing about, Remember that :

1. You're not limited to a single character for any field.
2. It's easier to reduce to a common width than expand into one. IE. If the minimum width of the [code] field must be three characters but some are up to five, then using three will always be easier than trying to use five.

I cannot stress enough that you need to clarify the question in your own head before you start to ask for help. Largely, this process will help you avoid the need for asking - which is satisfying in itself. Also, I'm sure you can imagine how people feel when asked a question where the basics clearly haven't been done yet.

[pbsnow]

I did crop the tables, but I guess depending on what I cropped them in, it still displayed the entire screen image.

I actually found a way to do Part 2 of what the selection process, still working on it, as it is still not doing exactly what I would like.

I did have a question, your solution seems to work, however, I haven't put enough sample data in to see if it is going to do exactly what it should as I add more items.

I thought about all of your positive feed back, so I am working on the database to get it exactly where I want it to be before I start asking more questions.

Thank you for all your help thus far, but I will have more questions tomorrow... for sure... As I am still testing the results and other items for generating the serial numbers....

[NeoPa]

I look forward to some more questions from you :-)

[pbsnow]

Hi NeoPa

I worked this over and over and decided to go with the Northwind Database sample because it has most of what I need. Now, once again, you have shown me how to create this serial number in a previous message - "TR-55GB-1001." I am guess that I need to create an "items" Table to keep track of the individual items in the shipment. The items table will contain the last 4 digits of the serial number. I know how to do that, but based on the image of the order screen attached, how do I tie in the quantity of the items being shipped with the serial number label so that each item will have a customized label, so that if I run a report, I can generate shipping labels for all items being shipped by the customers sorted by ship date