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

 

[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