Approach User Support homepage Approach User Support
Answers to Frequently Asked Questions about Lotus Approach
Examples LotusScripts
Example databases
Links to other Lotus Approach support services

[Return to contents]

Example LotusScript: Fill Field No.2

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
        For C = 1 To RS.NumRows
                RS.SetValue("PersonID"), Format$(CurVal, "000000") 'Replace PersonID with your field name
                CurVal = CurVal + IntVal
        Msgbox Trim$(Str$(RS.NumRows)) & " records have been updated with new ID numbers.", 64, "Update Completed"
        Goto ExitSub
        Msgbox "Error " & Str$(Err) & " - message: " & Error$, 16, "System Error"
        Resume ExitSub
        Exit Sub
End Sub

[Return to contents]

© Copyright, JohnBrown, Trademarks, Disclaimer, Acknowledgements.