Reusing generated ID numbers in the event of record deletion. 1

[BajanPOET]

My main form in my application generates sequential IDs for requisitions using an Autonumber. The problem that has been reported to me by my users is that requisitions that have been assigned an ID number sometimes get deleted or aborted by the user or the system during routine contraction of the dbase, but the numbers can't be reused. This leaves huge gaps of unused numbers. I need to be able to generate IDs for the requisitions but allow for reuse of an ID number if the requisition is deleted.

Any ideas as to how I can go about it?

What? Who? ME????

 

[Remou]

An autonumber is merely a unique number, it is not an ID for users. If you want a number that means something and does not have gaps etc, create one. Autonumber is not suitable for this.

 

[BajanPOET]

ok cool. understood.


What? Who? ME????

 

[BajanPOET]

I've changed the field to a number rather than an autonumber. To automatically generate a new requisition number I now have to create code to check the last Req. number in the table and increment it. How do I reference the requisition number in the last record from VBA (I want to check for the last record, increment the number and store it as the Requisition # for the new record in the Form_Load event.)

What? Who? ME????

 

[Remou]

You can use DMax, but there may ne problems with this method in a multi-user environment. If two users are creating a new record at the same time, it is posible that they will end up with the same number, you may prefer to store the incrementing number in a 'system' table that can be locked until a number is assigned.

 

[BajanPOET]

I'm trying to figure out how to stop two requisitions from being added at the same time with the same number. I liked the Autonumber - if two staff members tried to add requisitions at the same time each would get a different number. How do I get around this issue?

I created a new table NextReq that only as ReqNo as a field; I was thinking that I would increment the number in the Form_Add() of the Add Requisition form. I've gotten as far as incrementing the number and storing it in the table NextReq; how do I use that to make sure that 2 users don't get the same number?

What? Who? ME????

 

[PHV]

Have a look here: faq700-184

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

Thanks PHV...
Excellent - with some modifications I got it to work! Definitely a code snippet worth keeping... lol.

What? Who? ME????

 

[BajanPOET]

Hey PHV,

I modified the code that I saw in the FAQ you sent me by removing the requirements for the date. It's been working mostly, but recently I've seen a trend where somehow instead of there being only one record in the table I created, I see others. I just deleted two extra records to leave the most current one. How do you force the table to only keep one record?

What? Who? ME????