[Return to contents]
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
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