Full Version: how to autonumber per category
UtterAccess Forums > Microsoft® Access > Access Forms
bayfx
i need to make autonumber for each category...
Omean ....
ag001, mag002....... etc for magazine
bk001, bk002..... etc for book
cm001, cm002... etc for comic
etc...
i had see the example... but its password protected... i want to know how to create it
this image is that example....... (i edited because it not in english)

can anyone help me.....
thanks....
MrSiezen
I'm pretty sure and also hope that this autonumber is stored in two fields, and merged for the form.
o in your source table there will be two fields for the autonumber (which technically won't be an autonumber):
category - text field (contains 'mag', 'bk', 'cm', etc)
cat_incr - numeric field (1,2,3,etc...)
So when you need to store a magazine, you set the category control or field to 'mag'.
Then you find the highest 'mag' number with a DLookup() and add +1.
Two important notes:
You do NOT want the complete 'mag001' in one field. This will make life so much harder afterwards...
Add a real autonumber field in the table as well for the primary key.
bayfx
still confuse......
He need 2 field and not autonumber......???
so..
first is category field...
second is number.....??? (did we need a query....???)
can you give example of the code....?
thanks...
rsindle
To clarify, what MrSeizen is saying (and I totally agree with him) is that you'll want your data to look SOMETHING like this:
D (autonumber), Category, NumberWithinCategory, Other fields out here....etc.
------------------- ----------- --------------------------- -------------- ------------- ------------ etc.
1 mag 1
2 mag 2
3 cm 1
4 mag 3
5 cm 2
6 cm 3
7 cm 4
8 mag 4
9 cm 5
etc, etc,
You can ALWAYS "put the values together" on forms and reports to show "Mag.001" or "Mag001" or whatever for display purposes. But store the values separately...Trust us... (or trust Codd).
The ID will populate itself. The user can select the Category.
YOU can generate the [NumberWithinCategory] value by:
dim lngNumber as long
lngNumber = nz(Dmax("NumberWithinCategory","yourtablename","Category = '" & frmYourForm.cboCategory & "'"),0) + 1
Me.NumberWithinCategory = lngNumber
(untested code)
This example assumes that you have a combo box on your form called cboCategory where the user selects the category. Once that is done, you call the code above to generate the "Next number" for THAT category.
Hope this gets you started.
Rob
bayfx
oh.... i see.....
i'll try it.
Maybe i'll add more field for the combined value.
thanks
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.