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:
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
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?
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.