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: Check Existing Data and ask for details for new record

Last updated: before December, 1998

 'Written by Ian Curry <Jist Enterprises Limited>, 1997
     '(C) Copyright 1997 by Ian Curry
     'Permission is granted to freely copy this script in electronic form,
     'or to print for personal use. It may be use in any Approach database,
     'but may not be distributed for profit either by itself or as part of
     'a collection or database.
     'Disclaimer: This script is provided as is without any express or
     'implied warranties. The author assumes no responsibility for errors or
     'omissions, or for damages resulting from the use of the information contained
     'herein, or your own version containing your desires for the usage of the
     'Please mail any improvements to the Approach Users Mailing List

I have created a credit control and diarying database, database names in

There are a number of different accounts (ACCTMAIN) each with a number of
transactions (TRANMAIN), each transaction can have one action (CURRENT) a
current database and then old actions are transferred to history. There
are also notes (CURRNOTE) that can be allocated to an account, action or
transaction and which are also later transferred to history.

The main working screen "Account Action Details" is based on the
ACCTMAINDatabase and has 2 repeating panels - CURRNOTE and TRANMAIN. In
the repeating panel for the TRANMAIN database (transacspanel) users can
enter one action into the CURRENT database.

The CURRENT database has a STD Ref, Action Reference, Diary Date, Raised
Date ans Completed Date.

The following script checks that the diary date is after TODAY() and if it
isn't checks with the user to see if they want to change it.
Then if the standard reference is 13, a new record is created in the
CURRNOTE database and will appear in the other repeating panel.

Sub Lostfocus(Source As Fieldbox)
        Dim DDATE As Variant
        INITRAN=source.transacspanel.Transaction~ Reference.text
        ANUMBER=currentview.body.Account~ Number.text
        DDATE = source.transacspanel.Diary~ Date.text
        AREF= source.transacspanel.Action~ Reference.text
If DDATE="" Then
        Messagebox ("Please complete a Diary Date")
        source.transacspanel.Diary~ Date.setfocus
End If
        If Datenumber(Year(DDATE) , Month(ddate) , Day(DDATE)) < Datenumber(Year(Today()) , Month(Today()) , Day(Today())) Then
                answer% = Messagebox("The Diary Date that you have entered is before today - would you like to change it ?",36,"CCS Warning")
                If answer% = 6 Then
                        source.transacspanel.Diary~ Date.text=""
                        source.transacspanel.Diary~ Date.setfocus
                End If
                If source.transacspanel.STDAction.text="13" Then
                        NEWNOTES = Inputbox$( "Please Enter the Notes","CCS Quick Note Entry",,250,250)
                        If NEWNOTES ="" Then
                                answer2% = Messagebox("Does this note only apply to this transaction?",3+32+256,"CCS")
                                If answer2%=2 Then
                                End If
                                Dim con As New Connection
                                Dim qry As New Query
                                Dim rs As New ResultSet
                                If (con.connectto ("dbase IV")<>False) Then
                                        Set qry.connection = con
                                        Set rs.query = qry
                                        If ((rs.execute)<>False) Then
                                                If answer2%=6 Then NewValue=rs.setvalue(2,INITRAN)
                                                End If
                                        End If
                                End If
                        End If
                End If
        End If
End Sub

[Return to contents]

© Copyright, JohnBrown, Trademarks, Disclaimer, Acknowledgements.