Building the transaction form
The Master/detail form
You will have noticed from the model that we've got a Master/detail relationship for the sales tables.
The transaction form used to input the sales data will be more involved than the simple maintenance form because it involves the use of a sub-form, which is always more complicated than a simple form.
It's very important to get this form absolutely right because it's the form that the user will be working with 90% of the time. It must be user-friendly, clear and complete.
Although the Access wizards could help here to build the form, it's not always practical to use them because there's usually some detail that has to be done manually and if you haven't worked-out the form yourself, it's may be difficult to change stuff the wizard has put in.
Step1Create a query for the main form.
The query will use the tables: SalesM, Products.
Step 2Create a query for the subform using tables: SalesD, Reps.
Step 3Use Autoform to create the Sales form.
Edit the form: add a Title, re-position the Text boxes, choose a background.
Note that we created a background picture for this form only, just to show how it would be done. Usually you use some variation of the company logo, muted, as your background picture. You would use the same picture for all the forms to ensure a consistent look throuhout the application.
You would also use Combo boxes for all fields that can be selected from a list. In this case, Product would always be selected from the Products table. You may sometimes have to create a query to et the list to display the way you want it. Note that we display 2 columns for the list because Product and Supplier are always tied together.
Step 4While on the subject of Product, you should have noticed that the fields "Commission" and "OverCommission" are found in both the Products and SalesM tables.
That means that, although the values are associated with the product, you may override them at sales entry time.
So, what we'll do is show the % as default values when the product is selected. Then the user can keep the values or override them if necessary.
To do that we'll write some VBA code that will be executed as an event procedure when the Product field is updated.
The key to this procedure is to use the DLookup( ) function to obtain data from the Products table and insert it into the form.
Step 5Create the subform using Autoform.
Use a Combo box for the Sales rep. Get the names from the Reps table.
Add a footer to th form. The footer will calculate the sum of % entered and we'll use that field as a validation later (a sale should always have a distribution of 100%).
Step 6Create a Subform control on the main form.
Tie the Form and Subform together with the Child / Master properties.
Step 7Create a Total% Textbox on the form.
Get the Total% field from the footer of the Subform using the Expression Builder.
Add an "Update" button and code to validate the Total % field to ensure that it is 100%.