Updating value of indexed column

[gershwyn]

I have a table with a composite index on three fields: RDAY, RSM, and RSEQ (each sorted ascending.)

I am trying to renumber the RSEQ field in increments of 5, while preserving the order the records are currently sorted in. Here is the code I am using:

Public Function Resequence(TableName)
  Dim rs As Recordset
  Set rs = CurrentDb.TableDefs(TableName).OpenRecordset
  rs.Index = "SortOrder"
  rs.MoveFirst
  Do While Not rs.EOF
    If dDay <> rs.Fields("RDAY") Or dSM <> rs.Fields("RSM") Then dSeq = 0
    dSeq = dSeq + 5
    dDay = rs.Fields("RDAY")
    dSM = rs.Fields("RSM")
    rs.Edit
    rs.Fields("RSEQ") = dSeq
    rs.Update
    rs.MoveNext
  Loop
End Function

Unfortunately, this code causes an infinite loop. I think because RSEQ is an indexed field, and we're changing its value, the order of the records changes and we never get to EOF. Currently it only changes values for the first value of RSM.

If I remove RSEQ from the index, the loop completes, but the order is not completely preserved.

How can I move through the records in their original order, and change the RSEQ field without reordering the records?

[Stewart Ross]

Hi. Instead of trying to do this in code for such a one-off task I'd generate a temporary table listing the values of all three fields, with a fourth field for the new value of RSEQ which you wish to set, and update the RSEQ field to the new value by joining these tables accordingly.

It should be straightforward to use a query to extract your current composite key values into Excel, say, then create the new key value as a fourth column and paste the four columns back as a new table into Access.

For simplicity I have just given simple table and field names in the example below, with F1 to F3 for your existing fields and F4 for the replacement value of F3:

UPDATE tblTestUpdate INNER JOIN tblUpdateNewKey ON 
 
      (tblTestUpdate.F3 = tblUpdateNewKey.F3) AND 
      (tblTestUpdate.F2 = tblUpdateNewKey.F2) AND 
      (tblTestUpdate.F1 = tblUpdateNewKey.F1) 
 
SET    tblTestUpdate.F3 = [f4];
 

You would have to be careful that in renumbering your existing field you do not create duplicates of some existing values which would violate the integrity of the composite key and therefore not be updateable. For example, you could not renumber a composite value of 1, 1, 1 to 1, 1, 5 if the value 1, 1, 5 already exists in your table.

There are two advantages to this approach. Firstly, in joining to the existing composite key value there are no issues with changing what is already indexed (as long as there are no key violations), and secondly you have a physical mapping table available showing what changes were made which you can use to undo those changes if need be.

-Stewart

[ADezii]

I started to re-write the code to see if it was a Syntax issue, when I realized that you have no closing End If to the If Statement in Line #7. This could definitely be causing the Infinite Loop problem. In any event, I'll post the alternate code which has the single advantage of always being sure that the Records will be in proper order prior to processing. Don't forget to add the End If (LOL).

Public Function Resequence(TableName)
Dim MyDB As DAO.Database
Dim rs As Recordset
Dim strSQL As String
 
'Let's be absolutely sure of the Sort Order
strSQL = "SELECT * FROM " & TableName & " ORDER BY [RDAY], [RSM], [RSEQ];"
 
Set MyDB = CurrentDb
Set rs = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
 
rs.MoveFirst
 
With rs
  Do While Not .EOF
    If dDay <> rs.Fields("RDAY") Or dSM <> rs.Fields("RSM") Then dSeq = 0
      dSeq = dSeq + 5
      dDay = rs.Fields("RDAY")
      dSM = rs.Fields("RSM")
        rs.Edit
          rs.Fields("RSEQ") = dSeq
        rs.Update
    End If
      rs.MoveNext
  Loop
End With
 
rs.Close
Set rs = Nothing
End Function

P.S. - As well as Code Tags, Indenting is always critical in posting code. Had the code been properly Indented, the missing End If would have been quite obvious. Just food for thought.

[Stewart Ross]

Hi ADezii. The If in the OP's first post is an example of a single-line IF statement which does not need an End If. It is an allowed form of syntax, as the MS-Help entry shows (asterisked line below):

Syntax
If condition Then [statements] [Else elsestatements] **

Or, you can use the block form syntax:
If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatements] ...
[Else
[elsestatements]]
End If

-Stewart

[ADezii]

Thanks Stewart, that one went right over my head! (LOL).

[gershwyn]

Stewart: Thanks for answering, I do like your approach. Unfortunately, this is not a one off process, but something we will have to do to three or four tables periodically. (I have no control over the source data, as it comes from another company's software.)

ADezii: I don't know why running off a Dynaset recordset hadn't occured to me - I used to do them for everything until I realized you don't really need to for working directly on a table. Changing the recordset type got around the index problem and the results are perfect.

PS. Sorry about the IF thing. I hate using the block form when the actual code being carried out is so tiny, but I think we've demonstrated that is does add to the code's readability by splitting it out.