Please Help  Increament value in a field

Posted on 1998-03-01
Last Modified: 2008-03-03
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 = ("Trans") = intcount + 1
    rs.[Trans] = intcount + 1

Please help me fix this.

Thank you.
Question by:cremick
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 1969653
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!Trans = intcount
  intcount = intcount + 1


Hope it helps.



Accepted Solution

JeroenW earned 150 total points
ID: 1969654
Make that an answer.


Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity dbnull syntax 1 59
Close gap between sub forms on a main form 5 28
Access 2003 query, remove dupes 17 25
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question