Full Version: how to autonumber per category
UtterAccess Forums > Microsoft® Access > Access Forms
i need to make autonumber for each category...
Omean ....
ag001, mag002....... etc for magazine
bk001, bk002..... etc for book
cm001, cm002... etc for comic
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.....
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.
still confuse......
He need 2 field and not autonumber......???
first is category field...
second is number.....??? (did we need a query....???)
can you give example of the code....?
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.
oh.... i see.....
i'll try it.
Maybe i'll add more field for the combined value.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.