Hi Guys,

In my table design view I've set my primary key to auto number and the 'New Values' to random. The problem is, sometimes the numbers generated are too long or negitive. Is there any way I can specify the limit of the numbers to be generated and keep them positive numbers?

Why does it matter if they are negative or not as it is an internal number and should only be used for linking. If however you are exposing the autonumber to a user and this is a big No No, then generate your own PKs.

Hi Dennisk,

Well what I want to do is generate random numbers for invoices. So the PK will be on the Inovice form, which is why I am asking. Do you have any other suggestions ?

I certainly would not use an auto number as an invoice number. Golden rule, Never expose an auto number to a user.

The reason for this is that they are erratic and sometimes increment by alarming amounts. If your db becomes well and truly corrupted and you require a recovery program then auto numbers are converted to numbers. And when you reapply the auto number you can bet your life that will not match up with the previously generated numbers. The are two tried and tested method for generating your own numbers and this question is answered at least once a week. So either create a table to hold the last number generated or use dmax(YourInvoiceNumber)+1 to generate the next number in sequence.