more on transaction forms

In the previous lesson we created an Invoice form for the Garage application

Now we're going to create the transaction form for the Financial application.

This one is not as complicated as the Garage form because there is a lot less information to enter and not very many calculations to do. So we're going to add some functionality to make it more user-friendly. The functions we add here could also be added to the Garage Invoice form or to any other transaction form. Once you know how to do one or two you can apply the techniques to any number of similar projects.

steps in building the sales reporting form

Basically we follow the same steps as in the previous lesson.

  • Create a Master query.
  • Create a Details query.
  • Create the transaction form from the Master query.
  • Create the subform from the Details query.
  • Insert the Details subform into the Master form.
  • Finalize the form.

steps 1 & 2 - create the queries

There's nothing very complicated here.

Just make sure you take everything from the Master and Details tables.

step 3 - create the master form

Again, this is straightforward. The form is created from the master query from step 1.

Note that the illustration shows the form after a considerable bit of editing. We'll cover that in the final step. At this time you should concentrate on getting the form to work. As mentioned in the previous lesson, you may have to scrap the whole thing a few times, so don't put too much work into it yet.

Adding a combo box and a background are part of the editing at the end.

After the commercial break we'll create the subform that will complete our design for this transaction processing.

step 4 - creating the details subform

The details form is created from the details query.

There are no calculations to do here. However, we have added a combo box to choose the sales rep from a list. That will be covered too in the last step.

In the footer we create a text box to sum the percentages. For every transaction the sum of the distributions should be 100%. So, even if I have 5 reps, all the shares of commissions must add-up to 100%.

step 5 - insert subform into main form

So, create the subform control then specify the subform name and links in the subform's properties.

step 6 - finalize the form

Now that everything works we can edit the form to make it more efficient and attractive.

To create a background for the form, sometimes called a watermark, you need a picture in .gif or .jpeg format. You would usually use a company logo with very low contrast - it has to be barely visible.

Then, you simply insert the filename in the form's "Picture" property. Then you can experiment with "Tiling" and "Alignment", etc.

The Total % field is transfered from the footer area of the subform the same way as the sub-total in the Invoice form - use the Expression builder. In the next lesson we'll start to use VBA code to do specific jobs. In this case we'll code a simple module to validate that the total does add up to 100% like it's supposed to.

To make the form more efficient we will use combo boxes or lists whenever we can.

For example, to specify a Product in the form, we will select it rather than enter the name. Whenever we type things there is a chance of making a mistake, a keying error. Selecting the name from a list avoids that.

To create a list:
  • right-click on the text box and select "Change to ... Combo box";
  • specify the source of the list in Properties - a query or a table, the name of the query or table, the number of columns to show
  • specify the position of the Bound column (the column that is kept);

The Product field is easy. TheProducts table is already in alphabetical order on Product name in column 1 and that is also the key. So we'll display the first 2 columns but column 1 is the Bound column.

The Reps table has "Rep number" as primary key and column 1. We want to display the list on "Rep name" in the subform, so we might want to create a query to change the order of columns.

Then you have to work with both the Format tab and the Data tab in the properties of the Combo box. Set Column count to 1 to display only one column and set Bound column to 2 to keep Rep number in the file. You can also adjust Column width to change the size of the combo box.

The changes that we made to this form could also apply to the Garage invoice from the previous lesson.

Where you enter Part number in the subform, it would be much better to select the part from a list of part names. That way you avoid a lot of errors.

You've probably seen the technique used when you fill-in a form on the Internet. All the information that can be put in a list will be: Country, State/Province, Credit card name, etc.

You should do the same for all the forms that you build.