Changing the Value of an Autonumber

microsoft.public.access.gettingstarted

25 Feb 2005 11:03 AM

Sammya66

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?

25 Feb 2005 11:38 AM

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

Show quote

"Sammya66" <Sammy***@discussions.microsoft.com> wrote in message
news:5E186E41-8C29-43F0-9414-AE26BD87DFD7@microsoft.com...
> 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?

25 Feb 2005 12:09 PM

Sammy

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

Show quote

"Naresh Nichani MVP" wrote:

> 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" <Sammy***@discussions.microsoft.com> wrote in message
> news:5E186E41-8C29-43F0-9414-AE26BD87DFD7@microsoft.com...
> > 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?
>
>
>

25 Feb 2005 3:15 PM

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

Show quote

"Sammy" <Sa***@discussions.microsoft.com> wrote in message
news:D127F25D-9763-42C7-A268-A930A162A41A@microsoft.com...
> Naresh Problem Solved!! - Thankyou very much for your quick response!!
>
> "Naresh Nichani MVP" wrote:
>
>> 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" <Sammy***@discussions.microsoft.com> wrote in message
>> news:5E186E41-8C29-43F0-9414-AE26BD87DFD7@microsoft.com...
>> > 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?
>>
>>
>>