Me.Undo leave autoincrement !?!

[maikon]

Hi there,

I've a button that position the table on a new record:

DoCmd.GoToRecord , , acNewRec

And I create a button to cancel that action:

Me.Undo

But the code field (autoincrement) continues autoincrementing! Explaning:

1. I click on "Add" button
2. The table's positioned on autoincrement code #1
3. I click on "Cancel" button
4. The register is cancelled, it isn't includded on table
5. I click on "Add" button
6. The table's positioned on autoincrement code #2 and the #1 isn't exists...

How it's happen? How I solve it?

Thanks

 

[Rich]

You'll have to create your own custom autonumber function if you want the number to be sequential with no gaps

 

[mresann]

The AutoNumber field is only used to create unique record ID's without having to code for it. Generally speaking, though, records should not fields specific to sequential numbering. If you need to number records sequentially, it's much more efficient and, in fact, much less likely of error, to create a query that returns the record index number based on an ascending sort on the Autonumber. This index number is a sequential number.

If I may ask, what purpose do you need the sequential number for?

 

[maikon]

Frankly, I don't need accurately sequential number, but I thought I didn't need to waste that numbers that autoincrement waste everytime I locate the table in a new record...

 

[Pat Hartman]

Frankly, I don't need accurately sequential number, but I thought I didn't need to waste that numbers that autoincrement waste everytime I locate the table in a new record

The Autonumber isn't assigned until you start typing in some control so it would only be wasted if you started creating a record and then cancelled it. If you have some code that is causing the Autonumber to be generated as soon as you position to a new record, move the code to the form's BeforeUpdate event.

 

[Oldsoftboss]

The Autonumber isn't assigned until you start typing in some control so it would only be wasted if you started creating a record and then cancelled it. If you have some code that is causing the Autonumber to be generated as soon as you position to a new record, move the code to the form's BeforeUpdate event.

Pat,

I use the auto number as a job no in our service division. I had the same problem as maikon and gave up trying to find a solution. Let me explain (If I can)

I have a form based on a query from 2 tables, tblClients and tblService. Pretty standard.

One of the fields in the tblclients is a check box I called "BlackListed". Basically if the client is a bad payer or just a plain pain in the but the check box is ticked.

Now in my service form I have a combo box with the clients and bound to ClientID. In the before update event i put:

If Me.BlackListed = True then
Me.undo
End If

The code works and the record is not updated but the auto number is lost. It looks a bit messy to see a missing job no every now and then.

I have never been able to over come this issue so am interested in your (or anybodies) response or imput.

Dave

 

[Oldsoftboss]

Just re-read your response:

move the code to the form's BeforeUpdate event.

The Forms BeforeUpdate Event.

Will give it a go,

Dave

 

[Oldsoftboss]

No, still loose the auto number. Is it because I start to type in the combo box ? I presume Yes.

The only other way I thought of just now is to have a pop up form of clients open when "Add New Job" is clicked. That way the client would be selected and analysed before the Service Form is updated. Sort of defeats the purpose of having a combo box on the Service Form to select the client.

Dave

 

[Pat Hartman]

Read my response again. I did not say that moving the code to the BeforeUpdate event would ALWAYS elminate the "wasted" number. It only eliminates it IF you are dirtying the record rather than the user. You said you got a new number as soon as you moved to a new record. That wasn't accurate. You actually only get the number generated when someone TYPES something.

Don't waste so much effort on attempting to eliminate the unnecessary numbers. There can't be that many of them and the autonumber shouldn't have any meaning at all. Its only purpose is to provide a unique identifier.