[AccessD] Autonumber Corruption ...Help!!!!

...lol...if there is a hard way to do something, Drew will explain it :)

...I really appreciate the effort guy and have added your code to my 
CodeKeeper if this ever comes up again ...:)))))

William






>From: Drew Wutka <DWUTKA at marlow.com>
>Reply-To: accessd at databaseadvisors.com
>To: "'accessd at databaseadvisors.com'" <accessd at databaseadvisors.com>
>Subject: RE: [AccessD] Autonumber Corruption ...Help!!!!
>Date: Wed, 29 Jan 2003 12:58:07 -0600
>
>Okay, the easiest way to update would be an append.  However, I am a code
>head.  So I was hit with this problem about 6 months ago.  They wanted to
>'re-number' an Autonumber field, and the developer had switched to Numbers,
>and made the change, and wanted to put the 'new' numbers into the table.  
>So
>I whipped up a little code for this.  It uses two recordsets.  rsNew and
>rsOld.  rsNew needs to open a blank copy of the table with the Number 
>field,
>but with an AutoNumber field instead.  rsOld opens the number field table.
>If the records are sorted by that Number, use a SQL statement instead of
>opening the table directly.
>
>Last but not least, there are two lines that say
>rsNew.Fields(1).value="Test".  Fields(1) needs to point to a text
>field...any text field will do, so just replace the '1' in both places with
>the first text field in the table.  (remember 0 is the first field...)
>
>Here's the code:
>
>Dim rsNew As Recordset
>Dim rsOld As Recordset
>Dim intCurrentKey As Long
>Dim i As Long
>Set rsNew = CurrentDb.OpenRecordset("Problems", dbOpenTable)
>Set rsOld = CurrentDb.OpenRecordset("Problems-old3", dbOpenTable)
>rsOld.MoveFirst
>Do Until rsOld.EOF = True
>     intCurrentKey = rsOld.Fields(0).Value
>     rsNew.AddNew
>     rsNew.Fields(1).Value = "Test"
>     rsNew.Update
>     rsNew.MoveLast
>     Do Until rsNew.Fields(0).Value = intCurrentKey
>         rsNew.Delete
>         rsNew.AddNew
>         rsNew.Fields(1).Value = "Test"
>         rsNew.Update
>         rsNew.MoveLast
>     Loop
>     rsNew.Edit
>     For i = 1 To rsOld.Fields.Count - 1
>         rsNew.Fields(i).Value = rsOld.Fields(i).Value
>     Next i
>     rsNew.Update
>     rsOld.MoveNext
>Loop
>MsgBox "Done"
>
>-----Original Message-----
>From: William Hindman [mailto:dejpolsys at hotmail.com]
>Sent: Wednesday, January 29, 2003 11:56 AM
>To: accessd at databaseadvisors.com
>Subject: [AccessD] Autonumber Corruption ...Help!!!!
>
>
>...I did something really stupid ...not enough sleep I guess :(
>
>...tblCompany.CompID is an Autonumber PK in a be mdb
>
>...one of clients computers disconnected suddenly and corrupted the mdb
>
>...an entire record in tblCompany was corrupted and repair did not fix it
>...so I went to an archive from December and copied the record and pasted 
>it
>
>into the current table ...all fine except that the AN PK had now changed 
>and
>
>would not let me replace it with the original :(
>
>...so I got stupid and went into design mode and changed the type from AN 
>to
>
>a long ...fixed the PK ...then tried to change the data type back ...and of
>course it won't let me :(((((
>
>...the company owner has last night's server backup tape but is in Miami 
>and
>
>no one can reach him :(((((
>
>...is there any way on god's green earth I can repair this?
>
>William ...doofus extraordinaire :((((
>
>
>
>
>
>
>_________________________________________________________________
>Add photos to your messages with MSN 8. Get 2 months FREE*.
>http://join.msn.com/?page=features/featuredemail
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd


_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*  
http://join.msn.com/?page=features/virus