Solved

Please Help  Increament value in a field

Posted on 1998-03-01
2
301 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
[X]
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
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 150 total points
ID: 1969654
Make that an answer.

Jeroen.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

615 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