Incremental Number

[Raul Sousa]

I have a table with a field called Number. I need this
field to be incremental. On record one this field must be
1 on record two, it must be 2, etc.
Is it possible?

 

[Karen Skipper]

I have a form that increments a number as records are added (using a button
on a form).

Here's the code. It may be crude, I'm not an expert (not even close).
You might be able to modify it to fill in the NUMBER field on existing
records.


Private Sub newform_Click()
'event for newform button

Dim refvar As Integer

DoCmd.Echo False
DoCmd.GoToRecord , , acLast 'go to last record in table
refvar = NUMBER + 1 'add 1 to NUMBER
DoCmd.GoToRecord , , acNewRec 'append new record to table
NUMBER = refvar 'fill in NUMBER

DoCmd.Echo True


End Sub

Karen Skipper

 

[JohnFol]

Why not use an AutoNumber?

 

[Steve Shin]

-----Original Message-----
I have a table with a field called Number. I need this
field to be incremental. On record one this field must be
1 on record two, it must be 2, etc.
Is it possible?

.

Raul,

This is possible, but what are you using the number for?
The Autonumber is a possibility, however, you must
understand that it simply provides for a UNIQUE record
identity. It is not the best solution if a "gap" in your
sequence is unacceptable. For example, if you are using
this number as an Invoice No, you may get gaps for records
that have been started, but not committed. As a result,
your number sequence may "skip". If having a gap in your
numbering sequence is unacceptable, email me and I will
send you the code that I use to insure that a all record
numbers are properly sequenced and accounted for.
Otherwise, simply using the Autonumber field should work
fine.

 

[Raul Sousa]

Hi Steve,
You are rihght. Having a gap in my numbering sequence is
unacceptable. That's why i can´t use auto number.

 

[Raul Sousa]

thank you i will try it

-----Original Message-----
I have a form that increments a number as records are added (using a button
on a form).

Here's the code. It may be crude, I'm not an expert (not even close).
You might be able to modify it to fill in the NUMBER field on existing
records.


Private Sub newform_Click()
'event for newform button

Dim refvar As Integer

DoCmd.Echo False
DoCmd.GoToRecord , , acLast 'go to last record in table
refvar = NUMBER + 1 'add 1 to NUMBER
DoCmd.GoToRecord , , acNewRec 'append new record to table
NUMBER = refvar 'fill in NUMBER

DoCmd.Echo True


End Sub

Karen Skipper




.

 

[Larry Linson]

Because AutoNumbers are not guaranteed to be sequential. They are intended
for internal use... surrogate keys, joining tables, and the only requirement
is that they be unique. There are a number of situations that will leave
"gaps" in the sequence.

Larry Linson
Microsoft Access MVP