Please help - auto number limits

[vicsalt]

I have a database used for producing labels on product. there is a unique 5 diget code we use for our tracibility, however one customer demands a 3 diget max

1 to 999

Thus i would like to create a field which auto fills a number

1st record = 1
2nd record = 2
3rd record = 3
ect.............

once we hit the 999th record it can auto reset to 1

is this possible and some direction would be greatly appriciated

thanks

 

[Isskint]

The purpose of a unique ID is traceability. How would someone know which record ID 54 refers to after 2 or 20 or 200 iterations?? Still some customers can not be told

I would suggest a separate field that is filled by code before a new record is created.
1, Use the DMAX() function to get the current max number based on your ID
2, DLOOKUP() the other 3 digit id, ADD 1 to that
3, Check if >999 and reset to 1
4, Either record this value in a textbox on a new record form so that when the record is created/saved this is added
.....OR add this to the table directly with SQL after the record is created/saved

 

[catalin.petrut]

You can insert a field (name it whatever you want) that will have a validation rule (<1000 & >0) and a default value based on the max value. For the default value expresion, i'm sure that someone with vba experience will help you.
PS: i dont see the logic for a "reset to 1" option. That means that you will have two products with 348 code. That means that you will not have a "unique diget code to use for tracibility".

 

[vicsalt]

Thanks for your responces, However Im not that advanced but will put some time into this.

I fully agree with the traceability issue, however we sell less than 999 parts per year and there is a year id within the table so we still have traceability in that sense

Thanks
If anyone wants to send a demo table that would be greatly appriciated and help my understanding of the suggestions made.

 

[jdraw]

Just a couple of questions -- more curiosity.

Are you identifying Products (independent of year or customer)?
Is this like a Product Serial number?
Perhaps you could expand a little on what a product number means to your business.
If you record Year and productNumber on a label, and record Customer, year and productNumber on Orders/Invoices/Payment related records, it seems you should have all the traceability you might need.

 

[vicsalt]

Just a couple of questions -- more curiosity.

Are you identifying Products (independent of year or customer)? Yes
Is this like a Product Serial number?- No, ID No
Perhaps you could expand a little on what a product number means to your business. - Means nothing to our buisness means everything to customer

If you record Year and productNumber on a label, and record Customer, year and productNumber on Orders/Invoices/Payment related records, it seems you should have all the traceability you might need. - Agree

 

[vicsalt]

Still desperate for some help

 

[Isskint]

Just another thought, you could always format the number at print time to only be the last three digits. Format = 000. This way you keep your 5 digit number but the customer see just the 3.

Equally you could look at a Ranking query to produce these 3 digit numbers.

 

[Mihail]

Modulo function
Result = Number1 MOD Number2
Result = Number1 MOD 999

 

[Pat Hartman]

You can't use an autonumber for this purpose. You will need to create your own sequence number. Leave the autonumber as the PK of the table and use it for ALL relationships. Then, add two additional columns. One to hold the year. Don't call it Year. That is the name of a function. The second to hold the sequence number. Add a unique index on the combination of SalesYear and OrderSeqNum.

Do NOT attempt to reset the number after 999. Relational databases do not allow you to have duplicates in a unique index.

Use DMax() as Isskint recommended.

Me.OrderSeqNum = Nz(DMax("OrderSeqNum", "Yourtable", "SalesYear = " & Year(Date())) +1)

DMax() finds the highest assigned value of OrderSeqNum for a given SalesYear. The Nz() function handles the situation of adding the first sequence number for a year.

 

[vicsalt]

Just another thought, you could always format the number at print time to only be the last three digits. Format = 000. This way you keep your 5 digit number but the customer see just the 3.

Equally you could look at a Ranking query to produce these 3 digit numbers.

HA - Nice - I actually did a very simular thing yesterday and its a solution.
I created a copy of my printed label, blanked out the 2 unwanted numbers.
WIN WIN, customer gets what they want and we maintain our 5 digit tracability number.

Thanks too all, I would have liked a more technical solution but unfortunatly out of my capability, maybe a few course I think