 |
 |
 | |
 | |
 | |
 |
|
|
| |
| |
| |
|
Last updated: before December, 1998
This script was sent to the mailing list by Paul Bent (with some comments added by John Brown). It does approximately the same thing as Fill Field No.1, expect that it demonstrates different techniques and has different features: Paul writes:
"The table's already opened by the apr so I've used a Table object and the CreateResultSet method instead of a
Connection object.
I've used a For Next loop instead of a Do loop
I made the ID a text field, length 6 and the ID must be 6 long, zero filled from the left - just for fun!
I put in the statements to get the start & step values from the user.
In real life I would validate the user input but didn't bother for this demo."
Sub FillField
Dim Tbl As Table, RS As New ResultSet, C As Long, CurVal As Long, IntVal As Long
Dim ConfMsg As String, MR As Integer
On Error Goto ErrTrap
CurVal = Clng(Inputbox$("Please enter the starting ID number:", "Enter ID", "1", 180, 180))
IntVal = Clng(Inputbox$("Please enter the value to increment each ID number by:", "Enter Step", "1", 180, 180))
ConfMsg = "Start value: " & Format$(CurVal, "000000")
ConfMsg = ConfMsg & Chr$(10) & Chr$(13)
ConfMsg = ConfMsg & "Increment: " & Cstr(IntVal)
ConfMsg = ConfMsg & Chr$(10) & Chr$(13)
ConfMsg = ConfMsg & Chr$(10) & Chr$(13)
ConfMsg = ConfMsg & "Update all records with the new ID numbers ?"
MR = 2
MR = Msgbox(ConfMsg,33,"Confirm Update")
If MR = 2 Then
Goto ExitSub
End If
Set Tbl = CurrentDocument.Tables(1) 'Use appropriate Table number. Tables are listed in the
'FILE / APPROACH FILE PROPERTIES menu item.
'The first table listed is Table (0), the second is Table (1),...
Set RS = Tbl.CreateResultSet
RS.FirstRow
For C = 1 To RS.NumRows
RS.SetValue("PersonID"), Format$(CurVal, "000000") 'Replace PersonID with your field name
RS.UpdateRow
CurVal = CurVal + IntVal
RS.NextRow
Next
CurrentApplication.ActiveDocWindow.Refresh
Msgbox Trim$(Str$(RS.NumRows)) & " records have been updated with new ID numbers.", 64, "Update Completed"
Goto ExitSub
ErrTrap:
Msgbox "Error " & Str$(Err) & " - message: " & Error$, 16, "System Error"
Resume ExitSub
ExitSub:
Exit Sub
End Sub