Changing the Value of an Autonumber

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
Naresh Problem Solved!! - Thankyou very much for your quick response!!

Naresh Nichani MVP said:
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

Sammya66 said:
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?
Click to expand...
Click to expand...
 
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


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

Naresh Nichani MVP said:
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

Sammya66 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top