Calculating in Approach files
How Access opens forms
Q I work for a nonprofit organisation that holds a number of workshops each year. In Lotus Approach, I created a file called WORKSHOP to hold information about each workshop, and a file called PARTICIP that lists all the participants in each workshop. These files are linked via a field called Workshop Number. A third file, MEMBER, contains information about participants in all the workshops and includes a Member? field that's left blank for nonmembers. This file is linked to PARTICIP via a field called Member Number.
The fee we charge for a workshop depends on whether the participant is a member of our organisation. I need Approach to display the appropriate fee from one of two fields in WORKSHOP, Mem_Fee and Non_Mem_Fee, which depends on whether the Member? field in the MEMBER file is blank or contains an entry. What's the best way to do this?
- Nick Caruso
A You can add a calculated field to the PARTICIP file. For this to work, your files must be joined, as shown below.
Since your files are already joined, I won't discuss how to do that. First, open PARTICIP and choose Create-Field Definition from the menu. On the first blank line at the end of the Field Name column, enter Fee or some other name for the new calculated field. Then change the Data Type to Calculated. In the Formula box that appears at the bottom of the screen, enter the following formula:
If(IsBlank(MEMBER.Member?),WORKSHOP.
Non_Mem_Fee,WORKSHOP.Mem_Fee)
When you're finished, click OK to go back to viewing PARTICIP. The new Fee field can be added to any form or report that includes PARTICIP.
Q Some forms in my MS-Access database contain macros attached to push buttons that open other forms. Sometimes I want the forms to fill the screen; other times I'd like them to be smaller. Can I customise the macros to set the size of a form as it opens?
- Jay Ginsberg
A You can add the MoveSize action to a push-button macro to tell Access how big to make the form and where to place it. Just follow these steps:
1. Open your form in design mode. Right-click the push button
that has the macro you want to edit, and choose Properties from
the resulting menu.
Scroll down to the "On Click" line. If you created your
push button using the Command Button Wizard, you'll see "[Event
Procedure]" on that line. Highlight it and press <Delete>.
We'll replace the event with a macro.
2. Click the E button at the end of the same line, and double-click Macro Builder in the Choose Builder dialogue box. Enter something like Open form with set size for the new macro's name.
3. In the Macro Builder dialogue box, you need to enter two actions. On the first line in the Action column, enter OpenForm, or select it from the drop-down menu. Then open the Form Name drop-down menu in the Action Arguments section of the dialogue box, and select the form you want to open.
4. On the line after the OpenForm action, enter MoveSize, and specify your form's size and position in the Action Arguments section, as in the figure below.
The numbers you enter are in either inches or centimetres, depending on the International settings in the Windows Control Panel. For example, to place your form's upper-left corner just under the Access toolbar, enter 0 after "Right" and "Down". Specify the form's width and height, then close the dialogue box.
If you already have a macro attached to your push button, click the E button at the end of the "On Click" line to open the Macro Builder dialogue box. Add a MoveSize action after the OpenForm action, as described in step 4, then close the dialogue box.
To test this, click the Form View button on the toolbar, then click the push button you customised.
- Celeste Robinson