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: User selection of database tables within an .apr

Last updated: before December, 1998

In this script, Jerry Sikes demonstrates how to use the ReplaceWithResultSet method to allow the user to select which
database table they want the .apr to access. This is the ultimate work around for people who need to access more than one
database with a single .apr.

Sub DropDownExample
        '-----Jerry Sikes 1998.09.15
        '-----To test, create a unbound fieldbox
        '-----change to dropdown list
        '-----use automatic values
        '-----name the object MyDropList
        Dim lbx As DROPDOWNBOX
        Dim lbxVal() As String
        Set lbx  = CURRENTVIEW.body.MyDropList
        Dim pathName As String, fileName As String
        '----- substitute your path
        pathName$ = "c:\Lotus\work\Approach\*.dbf"
        fileName$ = Dir$(pathName$, 0)
        MyCount% = 0
        '----- First  do loop counts number of qualifying tables, in path
        '----- You must limit the tables presented
        '----- to only those that will map properly
        '----- if more than one type of table exists in this directory,
        '----- try using a common prefix on all qualifying tables
        '----- "x:\path\Z*.dbf" where Z090898.dbf, Z091098.dbf...Zmmddyy.dbf"
        Do While fileName$ <> ""
                MyCount% = MyCount% + 1
                'Print fileName$
                fileName$ = Dir$()
        '-----This sets the number of variables to the correct count
        Redim lbxVal(MyCount%-1)
        '-----Reset the counter
        MyCount% = 0
        '-----Force back to top of list
        fileName$ = Dir$(pathName$, 0)
        Do While fileName$ <> ""
                '----Load the strings
                lbxVal(MyCount%) = fileName$
            '-----get the next file
                fileName$ = Dir$()
                MyCount% = MyCount%+1
        '-----Transfer the entire list to the screen object
        lbx.SetList lbxVal
        lbx.text = ""
End Sub
Sub ReplaceTableExample
        '-----Jerry Sikes 1998.09.15
        '-----This assumes the table selected is of identical
        '-----structure as the original
        '-----no error trapping
        '-----it also assumes the target table is table(0)
        '-----it also assumes, no alias to table(0)
      '-----in production, this script would be launched on the change event
      '-----of the drop down list
        Dim c As New connection
        Dim qu As New query
        Dim rs As New ResultSet
        qu.Tablename = "c:\Lotus\Work\Approach\" & currentview.body.MyDropList.text
        'Print qu.tablename
        Set qu.connection = c
        Set rs.query = qu
        If c.connectto("dBASE IV") Then
                If rs.execute Then
                        RVal = CurrentDocument.tables(0).ReplaceWithResultSet(RS)
                End If
        End If
End Sub

[Return to contents]

© Copyright, JohnBrown, Trademarks, Disclaimer, Acknowledgements.