Please Help Increament value in a field

I am in the process of converting an Alpha 5 database.  The counter in that dabtabase allowed duplicates to be created, due to a corruption problems.  I need to renumber the transactions before appending them to my new ms access 2.0 data base.  I have a query that has located 510 duplicate values within my table called NewTran the field is Tran, the query that has located the dups is "Find duplicates for
NewTran" This is the start of my code and I am stuck.

    Dim DB As Database
    Dim rs As Recordset
    Dim intcount As Variant


    Set DB = CurrentDB()
    Set rs = DB.OpenRecordset("Find duplicates for NewTran", DB_OPEN_TABLE)
    DoCmd Hourglass True

    intcount = 15049

   While Not rs.eof

    ' Increment and return the next value
    rs.MoveFirst
    rs.Edit
    rs = ("Trans") = intcount + 1
    rs.Update
    rs.[Trans] = intcount + 1
   Wend
    rs.Close

Please help me fix this.

Thank you.
cremickAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JeroenWCommented:
Declare variables which you know will be whole numbers as Integer or Long to reduce memory-usage.

Open the recordset as a DB_OPEN_SYNASET as it is not allowed to move to through a recordset of the DB_OPEN_TABLE-type. The word for the TABLE-type is Seek while the DYNASET- and SNAPSHOT-type have MoveFirst.

You start a look in which you want to go through all the records in your recordset.
The first line of code in your loop is 'rs.MoveFirst'. This method moves the pointer to the first record of the recordset. The method to go to the next record is MoveNext, to the previous MovePrevious and to the last MoveLast.

In short, you've created an infinite loop.

Furthermore, if you have changed MoveFirst in MoveNext the movement in the loop will cause a runtime error at the end. The pointer doesn't know it has reached the end of file untill he has reached the end. He is very nearsighted. When it stands on the last record, he can't even see he stands on the last record. Beyond the last record lies the Eof of the pointer of the tabel. Whenever you try to edit and update that, it will cause an error. So always put your increment of the pointer-position just before the loop is checked again. So just before Wend.

Then you say you want to edit the current record. This is oke.
After that, you apply a double assignement ( x = y = z ) which could be seen as a boolean-check (true or false). I think you want to increment a counter (intcount). If my assumption is correct, intcount = intcount + 1, would return the appropriate value. Now intcount is not incremented, resulting all of the new numbers to 15050.

In the next line, you update the record, which is like saying, 'i'm done editting this record, remove the lock'.
Then you want to really edit the record by setting rs.[Trans] to intcount, which will not be accepted.

Use edit to access the record and update to release it.

(Tip: use dots for Access-own-things like Eof, MoveNext and stuff; Explanation marks (!) for Fieldnames, controls. The reason for this is that although Access 2.0 eats it, further versions of Access won't, making it harder and less costeffective to upgrade.)

And now for the result:
Dim DB As Database
Dim rs As Recordset
Dim intcount As Long

Set DB = CurrentDB()
Set rs = DB.OpenRecordset("Find duplicates for NewTran",  DB_DYNASET)
DoCmd Hourglass True

intcount = 15049

While Not rs.eof
  ' Increment and return the next value
  rs.Edit
  rs!Trans = intcount
  rs.Update
  rs.MoveNext
  intcount = intcount + 1
Wend

rs.Close


Hope it helps.

Jeroen.







0
JeroenWCommented:
Make that an answer.

Jeroen.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.