Custom Alpha/Numeric Autonumber

  • Thread starter Thread starter ADonMS
  • Start date Start date
A

ADonMS

Hi. Im in need of some super advance support with my database. But be gentle
'cause Im not good at coding.
I need to create an autonumber feature that will increment to next
consecutive number based on selection in dropdown box.
I have a table which consists of categories. When one of the values from
the list on my form is selected, it should generate a new number based on
that category.
ex.
IF Apple, then ORD100
IF Apple, then ORD101
IF Table, then ORD200
IF Table, then ORD201
IF Book, then ORD300
and so on...
Please let me know if can be accomplished and best approach to use.
Thanks!
 
ADonMS,

First, let me say, yes it can be accomplished, but need more information...

....Please identify the fields in the table and the table name. Also, from
your description I'm guessing the highest you want to go is 999 OR do you
want it to keep counting? Also, are you only going form 100 to 900 thereby
saying there will only ever by 9 items in that table or will you go to 1000?

--
Gina Whipp


"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Hi. Im in need of some super advance support with my database. But be gentle
'cause Im not good at coding.
I need to create an autonumber feature that will increment to next
consecutive number based on selection in dropdown box.
I have a table which consists of categories. When one of the values from
the list on my form is selected, it should generate a new number based on
that category.
ex.
IF Apple, then ORD100
IF Apple, then ORD101
IF Table, then ORD200
IF Table, then ORD201
IF Book, then ORD300
and so on...
Please let me know if can be accomplished and best approach to use.
Thanks!
Click to expand...

Well... your best (most properly normalized) answer would be Don't Do That.

A basic principle of relational database design is that the value of a field
should not depend on any other field. What you're doing is storing the fact
that a given order is for a Table in two different places - in the Category
field, and redundantly in the fourth character of the order number. What if
you have a record in the table for a Table, ORD213, and then realize that
somebody goofed and this order was actually for a Chair, in the 600 series? Do
you leave it as ORD213, causing a contradiction? Change it to ORD613 (oops,
can't do that, somebody else ordered a different chair)? Change it to ORD628,
the next available 600, leaving a gap?

STOP. This kind of Intelligent Key is simply a Bad Idea and will cause far
more hassles than benefits!

Perhaps you could explain the purpose of this from your point of view; there's
almost surely a better way to do it.
 
Hi John.
I thought this would be a awesome way to track my resources, since I am
trying to use a naming convention for the auto ids in my inventory database.
However, I understand that this is not practical use of how the autonumbering
was intended to work, and if it causes more headache than necessary then, I
guess I will have to use a different method. Any ideas on how to go about
setting this up? Basically, I have a table of resources based on specific
catagories. I want new records with ids that fall under the category of that
resource.
So, if I see ids with preceding numbers 100, 200, 300, I know what series or
category that particular record belongs to.
Thanks for the response.
 
Hi John.
I thought this would be a awesome way to track my resources, since I am
trying to use a naming convention for the auto ids in my inventory database.
However, I understand that this is not practical use of how the autonumbering
was intended to work, and if it causes more headache than necessary then, I
guess I will have to use a different method. Any ideas on how to go about
setting this up? Basically, I have a table of resources based on specific
catagories. I want new records with ids that fall under the category of that
resource.
So, if I see ids with preceding numbers 100, 200, 300, I know what series or
category that particular record belongs to.
Click to expand...

In general, an ID is one thing... and a Category is a different kind of thing.
You're using a *database* - you can display any field you want, when you want.
When you were using file cabinets then you did have to put a single label on
each file folder... but you're not!

That said... if you really want to do this, and are prepared to put up with
the multitudinous hassles that it will bring, you'll certainly NOT want to use
an Autonumber (it's not controllable at all). Instead you could use a Long
Integer field. You would need to ensure that a Form is the *ONLY* way that
users can update the table, and you'll need VBA code in either the AfterUpdate
event of the Category combo box, or (probably better) in the Form's
BeforeUpdate event, which will assign the ID at the last possible minute. You
could use code like:

Me!ID = NZ(DMax("[ID]", "[tablename]", _
"Category = '" & Me!cboCategory & "'"),
Me!cboCategory.Column(1)) + 1

to look up the current maximum ID for the current record's category. The
category combo box should have two columns, one being the category name (I'm
assuming it's of text type) and the second being the "seed" number for that
category - 100, 200 and so on.

You're of course going to be in trouble with the 100th sale in any category
since the numbers won't fit. Maybe you don't plan on expanding your business
to that level... but if you hit your 50th sale before the end of the fiscal
year, you might want to rethink your self-imposed limitations.
 
Gina,
I just starting from scratch, again. however, for my former database, my
resources used autonumbering. After receiving the information from John, I
thought about creating the exisitng resources usng the naming convention ids
from an unbound text box that would pop up after the category control is
selected. Then the user can select the next consecutive sequence number for
that category. If is doable, I would just need to know exactl coding for
this process.
Thanks.
 
ADonMS,

Okay, I think there's a little confusion here...

1. What does "...creating the exisitng resources using the naming convention
ids..." mean? Naming convention is to follow some convention *rule* for
naming objects, see
http://www.regina-whipp.com/index_files/NamingConventions.htm So you can
see how the above statement is a bit confusing.

2. Then you go on to talk about "...from an unbound text box that would pop
up after the category control is selected." You in one sentence you have
moved from database design to form set-up. You should focus on getting the
tables correct (that would be the foundation to the house) before worrying
about where to hang the drapes.

3. Have a look at...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


http://www.databasedev.co.uk/table-of-contents.html

4. What you want is doable but perhaps NOT the way you described. No one
can provide coding until the tables are created. It would be like buying
the cabinet for your kitchen that is not yet built, we'd be guessing.

So let's start from the beginning and get the tables built first. If you
want help with that then you would to describe what it is you are wanting to
accomplish. Are you tracking Orders? Are you tracking Resources (and what
resources)? This will be the most time consuming part but the part that
pays for itself in the long run. Oh, and it would help to know what version
of Access.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

ADonMS said:
Gina,
I just starting from scratch, again. however, for my former database, my
resources used autonumbering. After receiving the information from John,
I
thought about creating the exisitng resources usng the naming convention
ids
from an unbound text box that would pop up after the category control is
selected. Then the user can select the next consecutive sequence number
for
that category. If is doable, I would just need to know exactl coding for
this process.
Thanks.
Click to expand...
 
Gina, I do have all of my tables created. With that said, it could be a
little confusing in what I am trying to do and/or say. My apologies. After
more research, I think my issue is simpler than I am expressing, that I know.
I appreciate the links as I will follow through to see if something works.
Thanks, again for the support.
 
John,
I am trying a number of things, one being your suggestion to see if this
works. I will let you know what worked for me. Thank a bunch to you and
Gina for assisting with this issue.

John W. Vinson said:
Hi John.
I thought this would be a awesome way to track my resources, since I am
trying to use a naming convention for the auto ids in my inventory database.
However, I understand that this is not practical use of how the autonumbering
was intended to work, and if it causes more headache than necessary then, I
guess I will have to use a different method. Any ideas on how to go about
setting this up? Basically, I have a table of resources based on specific
catagories. I want new records with ids that fall under the category of that
resource.
So, if I see ids with preceding numbers 100, 200, 300, I know what series or
category that particular record belongs to.
Click to expand...

In general, an ID is one thing... and a Category is a different kind of thing.
You're using a *database* - you can display any field you want, when you want.
When you were using file cabinets then you did have to put a single label on
each file folder... but you're not!

That said... if you really want to do this, and are prepared to put up with
the multitudinous hassles that it will bring, you'll certainly NOT want to use
an Autonumber (it's not controllable at all). Instead you could use a Long
Integer field. You would need to ensure that a Form is the *ONLY* way that
users can update the table, and you'll need VBA code in either the AfterUpdate
event of the Category combo box, or (probably better) in the Form's
BeforeUpdate event, which will assign the ID at the last possible minute. You
could use code like:

Me!ID = NZ(DMax("[ID]", "[tablename]", _
"Category = '" & Me!cboCategory & "'"),
Me!cboCategory.Column(1)) + 1

to look up the current maximum ID for the current record's category. The
category combo box should have two columns, one being the category name (I'm
assuming it's of text type) and the second being the "seed" number for that
category - 100, 200 and so on.

You're of course going to be in trouble with the 100th sale in any category
since the numbers won't fit. Maybe you don't plan on expanding your business
to that level... but if you hit your 50th sale before the end of the fiscal
year, you might want to rethink your self-imposed limitations.
Click to expand...
 
If you feel _compelled_ to have a composite key where the numbers are
meaningful as to the item for display to your users, then I would suggest
you have multiple fields which you concatenate for display or printing.
From your example, it would appear that everything begins with "ORD" -- if
so, that could just be a constant; but if there are multiple values, you can
assign the proper one (use a lookup table to limit them, if it would be
helpful); then you appear to have a numeric value to identify some category
of item -- set Category as a separate field; then you seem to want a
sequential number (but maybe only two digits?) -- autonumbers are not
guaranteed to be sequential, so an autonumber are not a good choice for
this. There are a number of posted algorithms for creating
monotonically-increasing sequential numbers.

However, for internal use, joining from table to table, I'd suggest you use
an Autonumber Primary Key as a surrogate to uniquely identify the record, so
your joins can be on only one field -- just remember, that Autonumber is
intended to be unique, intended for such internal use only, not have any
other characteristic, so is not suitable for display to or printing for, a
user.

You can group the earlier separate fields into an index, and not allow
duplicates, to use as a "natural key" to prevent entering the same item
twice.

Larry Linson
Microsoft Office Access MVP
 
Back
Top