Field to auto number from the highest plus one

[Guest]

I have a database table populated with a couple thousand records already. The
primary key field is an auto number field. I would like first to create a new
field and copy the auto number data to the new field to retain the same
"record number" for the current items since we started tracking each entry by
this auto number field (wrong idea, I know). But now, I'd like to have this
new field look for the highest number and add one to generate a new number
WITHOUT the use of Access' auto number feature. I'm assuming I could code
this on the form itself each time a user added a new record. Does anyone have
a very simple way to accomplish this?

 

[gibsonsgman]

I have a database table populated with a couple thousand records already. The
primary key field is an auto number field. I would like first to create a new
field and copy the auto number data to the new field to retain the same
"record number" for the current items since we started tracking each entry by
this auto number field (wrong idea, I know). But now, I'd like to have this
new field look for the highest number and add one to generate a new number
WITHOUT the use of Access' auto number feature. I'm assuming I could code
this on the form itself each time a user added a new record. Does anyone have
a very simple way to accomplish this?

this is actually fairly easy to accomplish
use this code:

if isnull(me.count) then
dim rs as dao.recordset
dim x as integer

'points the recordset variable at your table (replace 'tblData' with
your tablename)
set rs = currentdb.openrecordset("tblData")

rs.movelast 'moves to last record of dataset
'below, the value of the count field is assigned to the x integer
variable

'replace 'count' with your primary key field name
x = rs.fields("count").value
x=x+1 'adds one to the x variable for a new record
with rs
.addnew
!count=x 'assigns the new record a number one greater than the
previous
.update
.bookmark=.lastmodified
end with

rs.close
end if

put this code in your Form_Current event.
this should work, but keep in mind this is untested code so there might
be a small error in there. if it doesn't work let me know the problem
and ill hlep you fix it
~~Brian~~

 

[BruceM]

See the link here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

You can change the autonumber field to long integer, then apply the code in
the link. If this is a multi-user database take care to note the extra
instructions.

Similar code can also be applied through VBA after testing for a new record:

If Me.NewRecord Then
Me.YourField = DMax("YourField",YourTable") + 1
End If

The code can be applied in the form's Current event if the user needs to see
it right away, or in the Before Update event if it is a multi-user
environment and the user does not need to see the number when the record is
started. Using the Before Update event will guard against errors from a
duplicated PK in a multi-user environment. If the user needs to see the
number right away and it is a multi-user environment you will need to test
for duplicates before saving the record, probably in the form's Before
Update event.

 

[gibsonsgman]

yes, that seems to be significantly more efficient than my cumbersome
method.