Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010

    Unanswered: VBA to change field values in a table

    I have a table where each record is a step for a test case. There are maybe 500 test cases and about 20 to 30 steps in each test case. Some of these test cases need new steps inserted. This will require step numbers to be renumbered. All steps that have been added will be a decimal, like 7.1 and 7.2 which means that between steps 7 and 8 two new steps will be added. The decimal numbers and all succeeding steps will need to be renumbered appropriately. I have gotten to the point of getting the recordset, have code that will identify numbers that are not integers, and will renumber in a successive fashion. However, I am noticing that the recordset is not in the correct order. Steps from the same test case are not grouped together and when the first record is loaded it is not the first record I see when I just open the table and view the records. I tried a sort command but that did not get all the records in the right order. I want them to be sorted by test case first, then by step #, then my code can just go through and do the renumbering.

    Here is my code:

    Sub Renumber_Test_Case_Steps()

    Dim db As DAO.Database
    Dim rstReNumTable As DAO.Recordset
    Dim sTable As String
    Dim vValue As Variant
    Dim lCurrentRecord As Long
    Dim lCurrentStep As Long
    Dim lNextStep As Long

    sTable = "tbl 080"
    Set db = Access.Application.CurrentDb

    Set rstReNumTable = db.OpenRecordset(sTable, dbOpenDynaset)
    rstReNumTable.Sort = ("[Test Case ID], [Step #]")
    Set rstReNumTable = rstReNumTable.OpenRecordset

    rstReNumTable.FindFirst "[Step #] <> Int([Step #])"


    rstReNumTable.Move (-1)
    lCurrentStep = rstReNumTable.Fields("Step #")
    lNextStep = lCurrentStep + 1
    rstReNumTable.Move (1)

    rstReNumTable.Fields("Step #") = lNextStep

    lCurrentStep = rstReNumTable.Fields("Step #")
    lNextStep = lCurrentStep + 1

    rstReNumTable.Move (1)

    Loop While rstReNumTable.Fields("Step #") <> 1

    lCurrentRecord = rstReNumTable.AbsolutePosition
    rstReNumTable.FindNext "[Step #] <> Int([Step #])"

    Loop While rstReNumTable.EOF = False And rstReNumTable.AbsolutePosition <> lCurrentRecord


    Set rst = Nothing
    Set db = Nothing
    Exit Sub

    Resume Exit_Renumber_Test_Case_Steps

    End Sub

    I don't know how clean it is so ask any questions you need to. The real problem here is that the recordset does not seem to load the records in the same order that I see when I just open the table. Any ideas as to why?


  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    From Access Help (about the Sort property): "When you define this property for an object, the sort is made when another Recordset object is created from the first object".
    Dim strSQL as String
    strSQL = "SELECT * FROM [tbl 080] ORDER BY [Test Case ID], [Step #]"
    Set rstReNumTable = db.OpenRecordset(strSQL, dbOpenDynaset)
    Have a nice day!

  3. #3
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    Instead of opening the recordset on the table directly, open it on an SQL statement that includes your desired sorting:

    Set rstReNumTable = db.OpenRecordset("SELECT * FROM TableName ORDER BY Field1, Field2", dbOpenDynaset)

  4. #4
    Join Date
    Oct 2010

    All set now

    thank you for the SQL suggestion. I found that my problem was not that but rather a table with two fields that started with the same name and caused me confusion. This is a database created by someone else so I am learning their design quirks as I go. Still, the SQL suggestion is good and works well. It is a better way to do things in my opinion also. Thank you both for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts