Solved How to step through a table and update select table values

Dell INSPIRON 6000
November 12, 2016 at 18:19:19
Specs: Microsoft Windows 10, 2.40 gigahertz Intel Core i5-4210U/8GB
I have a MSAccess table that has sequential numbers for each row and is sorted by two columns. When a row is deleted, I want to resequence all of the rows with VB code and step through a DAO recordset and update the table column with the sequential numbers so that they are always in the original order but may have a different number.

See More: How to step through a table and update select table values

Report •

#1
November 14, 2016 at 09:14:58
Isn't a formula designation for "current row number"? Then you could simply place that designator as the cell's formula. I'm not at all up to speed with ms-access, although I have worked with it some time ago.

Report •

#2
November 17, 2016 at 15:53:40
✔ Best Answer
Thanks, but your reference to "cell" makes me think that you are thinking of a spreadsheet. Here's the code I was hoping for:

Public Sub Resequence()
Dim strMessage As String, Counter As Integer
Dim dbsLocation As Database
Dim rstTypes As DAO.Recordset

' Open the table and update the HLSeq value
Set dbsLocation = CurrentDb
Set rstTypes = dbsLocation.OpenRecordset("tablename")
Counter = 1
Do Until Counter > 10
rstTypes.Edit
rstTypes!HLSeq = Counter
rstTypes.Update
Counter = Counter + 1
rstTypes.Move 1
Loop
End Sub


Report •
Related Solutions


Ask Question