AutoNumber back to 0

[Bob Vance]

I have deleted out all my records and done a compact and repair but when I
entered a new record it is starting off from my old number that was deleted
out

 

[Tom van Stiphout]

On Mon, 19 Jan 2009 12:25:57 +1300, "Bob Vance" <[email protected]>
wrote:

I was under the impression compacting the db would reset the
autonumber. Certainly recreating the table will.
But from your question it appears you care what the value of the
autonumber is. That is always a losing strategy. You should only care
that it is unique. If you want a meaningful number, you should roll
your own.

-Tom.
Microsoft Access MVP

 

[Bob Vance]

Thanks Tom, Created a New Table and Bingo up come [1]
I know made a mistake when the db was created using auto number as a ID
number for Clients and Horses but too late now the whole db revolves around
ClientID and HorseID
Dont think its going to crash my program I hope
Regards Bob Vance

 

[John W. Vinson]

Thanks Tom, Created a New Table and Bingo up come [1]
I know made a mistake when the db was created using auto number as a ID
number for Clients and Horses but too late now the whole db revolves around
ClientID and HorseID
Dont think its going to crash my program I hope

You can change the ID's from Autonumber to Long Integer. You'll need to make
provisions to (manually or automatically) assign new ID's when you add clients
or horses though.

 

[Bob Vance]

Thanks John, I suppose create something like this and add it into my code?
Function NextClientID() As Long
NextClientID = Nz(DMax("ClientID", "tblClientInfo"), 0) + 1
End Function

Thanks Tom, Created a New Table and Bingo up come [1]
I know made a mistake when the db was created using auto number as a ID
number for Clients and Horses but too late now the whole db revolves
around
ClientID and HorseID
Dont think its going to crash my program I hope

You can change the ID's from Autonumber to Long Integer. You'll need to
make
provisions to (manually or automatically) assign new ID's when you add
clients
or horses though.

 

[John W. Vinson]

Thanks John, I suppose create something like this and add it into my code?
Function NextClientID() As Long
NextClientID = Nz(DMax("ClientID", "tblClientInfo"), 0) + 1
End Function

That'll work; you could also put the same statement into the Form's
BeforeInsert event to pop a new number as soon as you "dirty" the form (just
as an autonumber would work).

The disadvantage is that in a multiuser application you could have two users
starting new client records at the same time. There are more elaborate ways to
ensure that you don't get two clients being assigned the same ID, but the
approach above should be fine for a one-user app, or where only one user will
be entering new clients.

 

[Bob Vance]

Thanks John, Only one user would be using the DB at a time, so by keeping
ClientID so all the codes work I should name the AutoNumber ClientAutoNumber
So in AfterUpdate of my form what code should I use to get the text box to
use the NextClientID()
Regards Bob Vance

 

[Bob Vance]

John , Just looked my ClientID AutoNumber is Long Integer,Increment,Yes
(Duplicates OK)
Regards Bob

 

[John W. Vinson]

Thanks John, Only one user would be using the DB at a time, so by keeping
ClientID so all the codes work I should name the AutoNumber ClientAutoNumber
So in AfterUpdate of my form what code should I use to get the text box to
use the NextClientID()

HUH!?

Sorry, we're talking completely at cross purposes here.

You don't NEED an autonumber if you have the Long Integer ClientID. You're
"rolling your own" autonumber, you don't need another one.

You don't need *any* code in the Afterupdate. Either use the code you posted
(the NZ(DMax(...)) code) in the click event of a new ID button, as you
suggested, or in the BeforeInsert event of the form, to have it assigned as
soon as you start entering a new client.

 

[John W. Vinson]

John , Just looked my ClientID AutoNumber is Long Integer,Increment,Yes
(Duplicates OK)

Well... CHANGE IT THEN.

If you don't want an autonumber, don't use an autonumber. It should be just a
Long Integer datatype, not an Autonumber.