Hello Experts.
I have a sub given below. In this sub I am sorting and renumbering the rows of a matrix which is 5 columns wide and has KMAX rows in it. The column number is input by a form. Now I want to make the active form invisable and after renumbering make the data available to the calling form, which has nearly the same structure as the hidden form. How can I do this.
[/CODE]
I have a sub given below. In this sub I am sorting and renumbering the rows of a matrix which is 5 columns wide and has KMAX rows in it. The column number is input by a form. Now I want to make the active form invisable and after renumbering make the data available to the calling form, which has nearly the same structure as the hidden form. How can I do this.
Code:
[CODE]Sub RowNumberResequence()
'-----------------------------------------------------------------------------------
'This sub resequences RowNumbers belonging to a ColNumber after sorting by ColNumber
'-----------------------------------------------------------------------------------
Dim rf As DAO.Recordset
Dim F As Form
Dim I, J, KMAX As Integer
I = 0
' Identify the active form and clone form as recordset
Set F = Screen.ActiveForm
'MsgBox "Active form " & F.Name
Set rf = F.RecordsetClone
'get row count
KMAX = rf.RecordCount
' move the cursor to the first record on the screen
rf.MoveFirst
'Execute the loop until it reaches the end of the screen
While Not rf.EOF
rf.Edit
I = I + 1
rf("CellNumber").Value = 5 * (I - 1) + rf("ColNumber").Value
rf("RowNumber").Value = I
rf.Update
rf.MoveNext
Wend
'MsgBox "number of updated records on form= " & I
rf.Close
Set rf = Nothing
End Sub