AutoNumber appending

[ddh88]

Hi All,

First off I am a N00b when it comes to Access. I am trying to write an RMA database for my workplace just as a proof of concept before proceeding with it full scale.

I have done a decent amount of looking online but can't seem to find a fit with my situation. Essentially we have a large pre-existing spreadsheet with Unique RMA numbers assigned to RMA cases. These are perfect for my primary key.
However as a result of the pre-existing spreadsheet I need to begin my RMA numbers quite high. The other issue is that the numbers are pre-fixed with SI. So our numbers look like SI00235.



I can get the number to start at 235 using a query to append the index, but I cannot use an appended set of letters on this as when I append the number from the query it deletes the Prefix added under the Autonumber. I also cannot use the source index from the query as an Autonumber as this prevents the query appending successfully.

Can anyone please help me out with this?

Thanks so much for your time and assistance.

[Micron]

You can't use an autonumber field this way even though you can tell it to start at a particular number. You can only do this with code or sql to write to a text field if you want the entire value stored in it. Otherwise, you'd have to use vba or a calculated form field to concatenate your prefix and the autonumber, but only for display. If it's possible that you'd have more than one prefix in the future, I would have a table for these and do as I mentioned, but grab the prefix from tblPrefix. What I would not do is have an autonumber field, a concatenation process AND store the concatenated value.

[ddh88]

Hi Micron,

Thanks for your time!

I hate to be that guy but I'm not really any clearer on my way forward :-/ sorry. I'll always only have the SI prefix. We're currently around SI00235. Essentially I want the next RMA number to autopopulate in the form when the customer service person is entering the information for the record.

Is it possible to have the unique number (Id) and a prefix table and add these two values together to form the rma number?

Dave Hayden

[Micron]

First make sure you understand the process as you want it to work and the business needs. For example I now presume we are talking mainly about data entry. There are two points where this number could be created. One is at the end of the process, the other at the beginning. If having consecutive numbers is important, it's usually better to do it at the end since at the moment of commitment, you want to ensure the next person who commits gets the next number. Doing it at the beginning means the next slot is created, but if this process is then cancelled and another user commits, expect gaps in the sequence. However, if sequential numbering is important, an autonumber field is NOT the way to go. Once you understand the requirements, you seek the means to the end (perhaps you've already done this but just haven't elaborated here).

The short answer to your question is yes, you could. I wouldn't bother with a prefix table if there will ever only be one. If doing this by code, you build the number and write it to the table, perhaps as text and not a number (as long as there's no need to do arithmetic on it). If using only queries and forms, you'd get the DMax value of a number field (not text) but make that invisible on the form. The form would use a visible calculated control that concatenates the value of the hidden number on to your prefix. Hope that's not too confusing. Again, ensure you have thought out the requirements of the process before going too deep.

[ssanfu]

Maybe this will help you:
This sample illustrates a number of ways to simulate your own Autonumber primary key.
http://www.rogersaccesslibrary.com/forum/topic395.html

[NTC]

As you are finding, and I see it all the time - humans seem to be accustomed to reference IDs that today make no sense. For instance in your case "SI" - if you want that in all cases then just put that as a default value in its own field. Let's call that field F1

As to stuffing preceding 0s - that of course adds no intelligence whatsoever and is totally arbitrary. Also of course a number field type cannot have preceding 0s so it makes no logical sense in today's technology.

Finally you have your real ID which is generating via an Autonumber field - let's call that F2

Don't confuse presentation with table data storage. You can always & anywhere present F1 & F2

Dropping the preceding 0s is your best idea - but if you can't bring yourself to do that then you have to count the F2 characters and subtract that number from 5 and stuff the 0s into its own field - let's call that F0.

Then you present F1 & F0 & F3 anywhere you need it to be seen by users.