Record #'s and testing DB

[DBarker]

I have been working on a budget DB for quite a while now.
It is almost ready to roll out. But I continue to make
copies of the database getting confusing where the changes
are. The purpose is that when I roll out the database I
want the first record to be 1 as the autonumber fields
that I setup so that there will not be any gaps in the
numbering. What is the easiest way to do this. I have
been copying the tables and just deleting the ID fields
and recreating but that requires resetting all the
relationships. There has to be an easier way to do this.

Thanks,
Debbie

 

[Ken Snell]

The easy way is to not use autonumbers as a sequential numbering
system...you're doomed to disappointment because the numbers can develop
gaps (especially if records are partially added and then canceled). If you
want a sequential numbering system, you must set up a field for that purpose
and use code to assign the next number to each record as you create them.

Autonumber is intended as an internal, not usually visible to the user,
method for establishing a unique key for a record in a table.

 

[DBarker]

So would that be like a loop i = 0 +1 or something like
that. I have never written a loop any help would be
appreciated.

 

[Ken Snell]

Not really a loop, but you're correct about incrementing by one.

Let's say that my field is named NumSeq in the table named TableName. On my
form, I want to assign the next value of NumSeq by writing it into a textbox
named txtNumSeq that is bound to that field. I would use a code step similar
to this:

Me.txtNumSeq.Value = Nz(DMax("NumSeq", "TableName"), 0) + 1

The Nz function is used in case this code runs when there is no value in any
record for the NumSeq field. The DMax function returns a Null in that case,
and the Nz function changes it to a zero.