Apr-Dialogs
Sample Approach application from XpertSS.com.
Demonstrates the use of Lotus Dialogs and LotusControls in an Approach application.
Author: Paul Bent. 10-Sep-2002


---------------------------------------------
Support and Contact Details
---------------------------------------------

Free support is available in the Approach - Scripts discussion folder at www.xpertss.com. You must register (free) to post a question.

If you wish to contact the author regarding application development or consultancy services, please visit my web site at www.northwindit.co.uk.

---------------------------------------------
System Requirements
---------------------------------------------

Windows 95, 98, ME, NT, 2000 or XP
Approach 9.5 or later. If Approach 9.5 in SmartSuite 9.5, patch C is recommended. It is available in the Free Downloads section at www.xpertss.com.

---------------------------------------------
Installation Instuctions
---------------------------------------------

1. Create a new folder and unzip Apr-Dlgs.zip into it.

2. Before opening the Approach file, check if MSCOMCTL.OCX exists in your Windows, System folder. If you don't have a copy, it's supplied in the zip. Copy it into the System folder then register it with the Run command:

regsvr32 mscomctl.ocx

This is a Microsoft ActiveX control that contains a ListView control used on sone of the sample dialogs. Please note that you do not have a licence to distribute MSCOMCTL.OCX to your own end-users. I am able to distribute it with a Lotus Dialog because I have a Visual Basic developers licence. However, you can expect to find this control already present on most Windows PCs.

---------------------------------------------
Usage Notes
---------------------------------------------

The sample Approach file is intended for developers with an intermediate to advanced knowledge of LotusScript and the Approach object model. You must also be familiar with the Dialog Editor and LotusControls.

The apr opens four tables, Customers, Order Header, Order Items and Products. It opens on the Customers form from which all the dialogs can be displayed.

The scripts behind the dialogs are extensively commented so these notes are limited to an outline explanation and some pointers to look out for when examining the code in the Script Editor.

All Lotus Controls are unbound controls and in many cases need to be populated with data at run-time. If the data is from the current record when the dialog is loaded, it can be stored in Global variables and transferred to the controls in the dialog's Load event. If the data is a list, to populate a combobox or listview control for example, it's returned in a ResultSet by a direct connection and a SQL query. Similarly, any data entered by the user on a dialog that needs to be written back to the database is updated through a ResultSet.

Note that the Globals object and every individual control has an Options section. It's necessary to put Option Explicit in every object to ensure strict type and variable checking at compile time. It's about time Lotus changed this; it's a real chore and a bad design choice on their part.

Execution of subs and functions is always initiated by an event firing. You'll see that every event sub has an error handler. This is a belt and braces strategy that ensures no unhandled run-time error can occur. Most times a general handler in each event will suffice; if the event sub calls other subs and functions then errors that occur in these will be passed up the call stack to the general handler if no local handlers exist. The Contact Notes dialog (dlgCustLog) has a function named fUpdCustNotes in its Global object. This contains an example of a local handler to trap error 13009 (Cannot perform UpdateRow) when writing changes back to the database table. If 13009 occurs, the function tries the update three times then returns False. Any other errors that occur locally are bufferred while connections are closed then raised again just before the function ends. Raising the error in this way reports it up the call stack to the event sub which traps it and reports it to the user.

There are four sample dialogs, two of which open a second dialog, making a total of six.

1. Contact Notes.

This dialog can be opened by clicking the "Properties" icon in the repeating panel. The panel contains a memo field to enter conversation notes. There is insufficient room to display the memo field fully and a multi-line fieldbox in a repeating panel doesn't display a scroll bar. The dialog allows the memo field contents to be viewed/edited in a larger textbox control.

 - The sub that displays the dialog first stores data for the current record in Global variables. The repeating panel contains an invisible auto-serial field which is stored and later used to select the correct record to update if the user edits the data in the dialog. It also sets a Global variable named gintDlgLoad to True, see below.

 - The dialog Load event contains the code to initialize the controls for first time use. The dialog caption, sone labels and the memo text are transferred from the Global variables to the dialog. When controls are initialized, it may cause some events to fire with undesired results. For example, transferring the memo field data from the variable to the textbox causes its Change event to fire. Code in the Change event sets the "dirty" flag (see below) and enables the Save button. We want this to happen when the user edits the data but not when it's first loaded into the textbox. Events that fire like this check gintDlgLoad and if True set it to False then exit. If gintDlgLoad is False, the event executes the procedure intended when the user fires it.

 - The dialog's Global object contains module level variables named mintDirty and mintSaved. mintDirty is set to True when the Change event fires in the textbox. The dialog's Closing event, checks mintDirty and if True, prompts the user to save changes before closing the dialog. mintSaved is set to True when changes have been saved at least once. When the dialog closes and mintSaved is True, the script refreshes the Customer form to display the changed data. The "saving" is done with a ResultSet object and would not show in the repeating panel until the data is refreshed.

2. Order History

Click the Order History button. Shows how to use a MS ListView control to display orders for the current customer. Double click an order in the ListView to open a second dialog opens showing the order lines.

The ListView control was added at design-time but you can't access its Properties sheet (or even resize it) in the Dialog Editor. Hence all relevant properties are set at run-time in the dialog Load event.

3. Orders Report

Click the Orders Report button. This dialog shows how to get find criteria from the user and run a named find. It shows how to use Option (Radio) buttons and hide/display controls depending on the option selected. It demonstrates how to initialize a Lotus combobox by creating a list from Approach data and how to synchronise two comboboxes containing id numbers and names so the user can select from either. This provides the functionality of a "description" field which is available in an Approach DropDownBox but not a Lotus ComboBox.

Note when selecting a date range that the sample data has orders dated 1996 to 1998.

Nine named finds were created at design-time to cover the combinations of options that can be selected on the dialog. The named finds reference conditions in variable fields. The variable fields were put on the Customer form and made invisible at design-time by running a one-off Global sub named sHideCtl. When the OK button is clicked on the dialog, the user specified options are transferred to the variable fields prior to running the named find. The point of using a named find is that error 11022 is raised if no records are found which can be trapped and handled. It would be easier to use a Find object and the FindSort method but this doesn't return anything to indicate no records were found.

4. Change Product Prices

Click the Price Change button. Shows how to use a spinbutton and slider control to increment a value (percentage change) in a textbox. The OK button is disabled when the percentage change is zero.

Note the slider control doesn't work correctly with mouse or keyboard on systems tested to date. I'll report the problem to Lotus. When the slider has focus, a left/right arrow press should decrement/increment the value by 1%. What actually happens is that a series of arrow keypresses change it by 1, 3, 7, 15, 31, 63 then to 100%. Dragging with the mouse is erratic and the drag direction is sometimes ignored. 

It shows how to use a spinbutton or slider (if it worked) to change the value in a textbox. The slider is also synchronised with the value in the textbox.

When the OK button is clicked, a second dialog opens to display the price changes in a ListView control. Clicking OK on this dialog updates the Products table with the new prices.

Paul Bent
Northwind IT Systems
www.northwindit.co.uk