Solved

Please Help  Increament value in a field

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

Jeroen.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

772 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