Increment Auto-Number?

[layman]

Respected senior members,

I am a newbie in access. I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records. Thanks in advance.

 

[plog]

Re: restrict autonumber increment

No. You can create a regular numeric field and then populate it however you want by building a custom VBA function to generate the correct next number, but autonumber cannot do this.

However, when you ask questions like this it makes it seem like you do not know what an autonumber primary key is for. It is strictly for ensuring that every record in a table has a unique identifier. It seems in your system it will be doing more than that which can cause issues.

Why does your autonumber need to do this?

 

[layman]

Re: restrict autonumber increment

Thanks for your help. But please tell me the exact procedure step by step.Let me explain my problem in brief -I have developed a db for issuing sales voucher. It should show a Voucher Book Number and Voucher Serial Number. The voucher book number should remain same for 50 records and then should increase by 1 (one voucher book should contain 50 records). The voucher serial number will be simple auto number.
Thanks in advance again for your kind help.

 

[plog]

Re: restrict autonumber increment

You've got some conflicts in your description. If voucher serial number is a simple auto number, then just make it an autonumber. If it needs to go from 1-50 then reset, then it isn't a simple autonumber. If it is the latter, you will need to build a custom VBA function to determine what the next serial number is and assign it.

Exactly how will the table be populated?

 

[layman]

Re: restrict autonumber increment

Thanks Plog,
I am attaching a sample table screen shot. If this number is to be supplied through any query, then how it is to be done. If possible please help with a sample db.

Thanks again for your kind help.

 

[plog]

Re: restrict autonumber increment

Records are not inherently numbered, nor can you trust an autonumber field to be consecutive. Also, what happens if record with vo_ID=35 gets deleted? Do you need to recalculate vo_BookNo for every record where vo_ID is greater than 35?

 

[layman]

Re: restrict autonumber increment

Respected Sir,
Once book number allotted to any record should be remain there, even if any record gets deleted. No re-calculation.

Thanks in advance for your kind help.

 

[plog]

Re: restrict autonumber increment

That makes vo_BookNo a calculated value and those you don't store in your table. Instead you would calculate it whenever you needed to use it. This would produce the value you wanted in a query:

vo_BookNO: Int(([vo_ID]-1)/50)+1

 

[layman]

Re: restrict autonumber increment

Many Many Thaks Sir,
I think that your suggestion will solve my problem.