Changing the Value of an Autonumber

[Guest]

Hi Im a new user to MS Access. I'm designing a database that requires an
Autonumber to be sequential and start from the value of 40000 (QS NUMBER) -
This Autonumber is set to the primary Key. At the moment it starts
incrementing at the value of 1. I've tried the approach of making a
temporary table an numbering the same named field to 39999 - then running the
append query to.......append the original table. I'm getting the error msg of
'Duplicate output destination 'QS NUMBER'

Can anyone give me advice as to where Im going wrong?

 

[Naresh Nichani MVP]

Hi:

You could try this in a VBA Module --
Click Tools | References to make sure you have a reference to Microsoft DAO
3.6 Object Library
I assume ID is your AutoNumber field and MyTable is your table name

Sub SetAutoNumber()
Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordSet("Select [ID] from [MyTable]",dbOpenDynaset)
rs.AddNew
Rs.Fields("ID") = 39999
rs.Update
rs.Close
Set rs = Nothing
Set Db = Nothing
End Sub

Click F5 inside sub to run it once. Next record your add to table should
have ID of 40000.

Regards,

Naresh Nichani
Microsoft Access MVP

 

[Guest]

Naresh Problem Solved!! - Thankyou very much for your quick response!!

Hi:

You could try this in a VBA Module --
Click Tools | References to make sure you have a reference to Microsoft DAO
3.6 Object Library
I assume ID is your AutoNumber field and MyTable is your table name

Sub SetAutoNumber()
Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordSet("Select [ID] from [MyTable]",dbOpenDynaset)
rs.AddNew
Rs.Fields("ID") = 39999
rs.Update
rs.Close
Set rs = Nothing
Set Db = Nothing
End Sub

Click F5 inside sub to run it once. Next record your add to table should
have ID of 40000.

Regards,

Naresh Nichani
Microsoft Access MVP

Hi Im a new user to MS Access. I'm designing a database that requires an
Autonumber to be sequential and start from the value of 40000 (QS NUMBER) -
This Autonumber is set to the primary Key. At the moment it starts
incrementing at the value of 1. I've tried the approach of making a
temporary table an numbering the same named field to 39999 - then running the
append query to.......append the original table. I'm getting the error msg of
'Duplicate output destination 'QS NUMBER'

Can anyone give me advice as to where Im going wrong?

 

[Lynn Trapp]

Sammy,
You may have gotten your number to start at 40000, however if you are
counting on it to be perfectly sequential then the AutoNumber will not work
for you. AutoNumber fields will, eventually and inevitably, develop gaps in
the sequence and there isn't anything you can do about that. To do that you
will need to create your own method.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Naresh Problem Solved!! - Thankyou very much for your quick response!!

Hi:

You could try this in a VBA Module --
Click Tools | References to make sure you have a reference to Microsoft
DAO
3.6 Object Library
I assume ID is your AutoNumber field and MyTable is your table name

Sub SetAutoNumber()
Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordSet("Select [ID] from [MyTable]",dbOpenDynaset)
rs.AddNew
Rs.Fields("ID") = 39999
rs.Update
rs.Close
Set rs = Nothing
Set Db = Nothing
End Sub

Click F5 inside sub to run it once. Next record your add to table should
have ID of 40000.

Regards,

Naresh Nichani
Microsoft Access MVP

Hi Im a new user to MS Access. I'm designing a database that requires
an
Autonumber to be sequential and start from the value of 40000 (QS NUMBER) -
This Autonumber is set to the primary Key. At the moment it starts
incrementing at the value of 1. I've tried the approach of making a
temporary table an numbering the same named field to 39999 - then
running the
append query to.......append the original table. I'm getting the error
msg of
'Duplicate output destination 'QS NUMBER'

Can anyone give me advice as to where Im going wrong?