Complex Autonumbering 1

[fiber0pti]

I need to have an auto number field that is sort of complex. I have a Total of 3 fields: CatID (CategoryID), SubCatNum (Sub Category #), and ProdID (Product #). I want the ProdID to be the auto # but I want it to start from 1001 in every category and sub category.
Example
CatID SubCatNum ProdID
2010 101 1001
2020 101 1001

Those would be to seperate Products. Is this possible?

 

[MichaelRed]

See faq700-184 for an example of creating a unique value for a primary key (psseudo AutoNumber). It is NOT exactly what you are after, but it is close enough to get you heasded down the track.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[fiber0pti]

That didn't help at all. I don't see how that is related. Any other suggestions?
Thanks

 

[fiber0pti]

Let me explain a little more. The only field I want to be an autonumber is the ProdID. The CatID and SubCatID are just numbers that I want to enter manually.

 

[MichaelRed]

If you didn't understand the faq, Im probably the wrong one to start giving you advice. Hopefully, someone else has the time.
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[KenG]

This what you want:
CatID SubCatNum ProdID
2010 101 1001
2020 101 1001
It looks like you want ProdID to be non-unique autonumber. That can't happen. An autonumber by definition is unique.

Either break this table into separate tables for each CatID, or SubCatID (depending on your needs), and then use the autonumber. Or, use a query using a clause similar to this (changing the values for CatID and SubCatID as needed);
WHERE CatID=2010 AND SubCatID=101 ORDER BY ProdID;
Then use VBA code to increment the new ProdID and add a new record.

Hope this helps

 

[DougStevens]

Not sure if I understand the question completely.

(1)
It is highly recomended that you never design a table to have the Autonumber field to actually represent any meaningful real world data. Autonumbers help provide a unique KEY for a record.
If you want a field to appear to be automatic you could do this in a form. Have the control's, usually text box, default property set to a function/formula that would calculate the next number in the sequence.
e.g.
Default Value: =DMax("[CatID]", "Category&quot + 1


(2)
There is a technique to have your autonumber to start at a different value other than one. For example, let's you want your autonumber field to start at 1000. Make an append query to append 999 to the field. Then delete the 999 record. The next number in the autonumber sequence should be 1000.

Hope this helps.
-Doug

 

[fiber0pti]

So is this completly impossible? I have an autonumber in the table. That's the ID field. I would like to keep that but is it still impossible to do what I was asking?

 

[DougStevens]

Here's the problem:
You've got a table for your Products
and a field ProdID which you want to be an Autonumber.
You also want the ProdID to start at 1001 for each unique Category and Sub-Category grouping.
Then the data would probably look like:
Cat Sub-Cat Prod
1001 101 1001
1001 101 1002
1001 101 1003
1001 102 1001
1002 101 1001

In this case the ProdID can NOT be a autonumber. Autonumber fields require a unique index. You would be duplicating ProdID numbers for each unique Category and Sub-Category combination.

May I make a suggestion:
Change those fields that would have duplicate values to number fields (long).
Have your form's ProdID text box control's default value property do something like this:
=Nz(DMax("[ProdID]", "ProductTable", "[CatID]=" & CatID & " AND [SubCatID]=" & SubCatID),0) + 1

The DMax() function will return the highest ProdID number for the given CatID and SubCatID; or it will return a NULL if it can not be found. To handle the nulls, the output of the DMax() function is sent as the arguement for the Nz() function, Nulls-to-Zero function. Therefore, you will either get a max number for the ProdID or a zero. Finally add one to that number. (Gotta love those composite function statements.)