Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Please Help  Increament value in a field

Posted on 1998-03-01
2
Medium Priority
?
312 Views
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.MoveFirst
    rs.Edit
    rs = ("Trans") = intcount + 1
    rs.Update
    rs.[Trans] = intcount + 1
   Wend
    rs.Close

Please help me fix this.

Thank you.
0
Comment
Question by:cremick
  • 2
2 Comments
 
LVL 1

Expert Comment

by:JeroenW
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.Edit
  rs!Trans = intcount
  rs.Update
  rs.MoveNext
  intcount = intcount + 1
Wend

rs.Close


Hope it helps.

Jeroen.







0
 
LVL 1

Accepted Solution

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

Jeroen.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

926 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