creating a transaction formIn the previous lesson we created several maintenance forms, one for every table, except the transaction tables: Invoice and Invoice details.
Creating the transaction form - the Invoice - is a big job.
There are several ways that could be done. We'll follow a step-by-step approach that doesn't call too much on the Assistants to do things automatically. By building every object from the ground up we keep control on all parts of the final result and we can easily make all the changes we need at any time.
Once the transaction form is in place we'll be able to start processing customers through the garage. The other forms must be in place before this so that we have access to customers, employees, parts, etc. to create the invoice.
steps in building a transaction formBefore you start the process you should have an idea of what you expect to get as a result. In other words, you should design the form that you want to produce. Draw a sketch of it on paper.
What should the invoice show? Do you need the customer's address, all his phone numbers? Do you need the employee's job title on the invoice? And so on.
Here are the steps. We'll cover them all individually in this lesson.
- Create a query from table Invoice
- Create a query from table Invoice details
- Create a form from query InvoiceQuery
- Create a form from query InvoiceDetailsQuery
- Attach the InvoiceDetails form as a subform in the Invoice form
- Add all Totals calculations to the Invoice form
step 1 - create the invoice queryCreate a query in Design mode using all the tables shown.
You know from the design what fields you should include in the query.
But there are a few rules that you must follow:
- include all the fields from the Invoice table (use the *)
- include only the fields you need from the other tables such as phone numbers, etc.
- never include the same field twice; for example Owner in table Vehicles is the same field as ID in table Customers (it's the customer number), so if you have to have the customer number, use Owner but not ID
- simplify the form by concatenating in the query: customer first and last name, employee first and last name, maybe even address
step 2 - create the invoice details queryThis one looks really simple but it's a bit more complicated than it looks.
Like the Invoice query there are basic rules:
- include all the fields from the Invoice details table (use the *)
- include only the fields you need from the other tables - in this case, we only need Part name from Parts
- do all calculations in the query
The important work here is in the calculations. In Invoice details you have a Sell price, Quantity and Discount %.
You must calculate the Extended price = Sell price * Quantity, calculate Discount amount = Extended price * Discount/100 and finally, calculate Net price = Extended price - Discount amount.
Note that when calculating the Discount amount, we have to account for the fact that Discount may be Null (not the same as 0). We could have set its default value to 0 in the table definition, but we didn't. So now, if there is no Discount entered, we must set it to 0 using the iif() function.
Calculate the Discount amount. We can change the format to "currency" later, in the form that will be created.
The Net amount is what the customer pays, after Discount.
step 3 - create the invoice formFrom the Invoice query, create the Invoice form.
In Design view you can now format the form. You move the fields about, resize and color, etc.
There is one suggestion to keep in mind here: avoid scrolling. The whole form, including the subform in the following step, should fit in one screen. It's a pain when the user has to scroll up and down and even worse, left and right, to see all the information contained. Group the fields tight together, shorten them when you have to and you'll be amazed at the quantity of information you can show in one screen.
step 4 - create the invoice details subformFrom the InvoiceDetailQuery, create a new form.
You don't have to worry about the formatting of the fields in this one because you'll view the form in "Datasheet format" which you'll set in the Property sheet of the form.
The reason for the datasheet is that it looks like a regular invoice that everyone is used to. If you left the subform in Form view and you had 10 items purchased on the invoice, you'd just get a long list of boxes.
You should also create the SubTot text box at this time. You do this in the Footer area of the form. This field will be used later to calculate the total amount due on the invoice.
step 5 - attach the details subform to the invoice formIn the Invoice form created in step 3, add a Subform control. The subform control is basically a window to let you view another form inside the main form. When you create it it will be empty (turn off the wizard or ignore the question that asks for properties). You will have to tie the subform to the main form through the appropriate properties, as you see in the diagram.
The Source is obviously the name of the subform.
The Link Master and Link Child fields are the fields common to both forms and they are the primary and foreign keys defined in the relationships.
Now we'll take a commercial break for a word from our sponsors.
When we return we'll look at the final steps to produce a beautiful form.
step 6 - test it and refine itMaybe we should have said this earlier.
Before you do too much work on the format, colors, size, etc. of the form and subform, test it to make sure everything works. You should now be able to create an invoice for a customer, billing 3 or 4 items. The extended amount, the discount and the net amount should all calculate correctly.
And if you open the Invoice and Invoice details tables, all the information should be in there, without the calculated fields, obviously.
If the test is not successful you have to fix the problems before continuing. At worst it may involve scrapping the forms and starting over. Hint: the error is often in the query, so look at that first.
Now, if evreything works you can go back to the forms and put all the effort in making it pretty. Or you can wait after step 7 and do it then.
step 7 - calculate invoice totalsAt this point, if you've done everything correctly, you have a working invoice form. You can use it to enter the data into the Invoice and Invoice details tables.
But before you can collect money from the customer you have to know the total amount for the parts, the taxes and the final total payable. You don't have that on the form yet.
Under the subform (after, not inside the subform control) create 3 new text boxes for Sub-total, Taxes and Final total. Give each box a Name because you'll need it in the next steps.
Then, to transfer the sub-total amount from the footer of the subform to the stot text box, use the Expression Builder as shown.
The other amounts, Taxes and Final total are calculated directly in the text boxes:
After some editing and formating you have a final, working invoice form that you can use to bill customers.
There may be some more refining to do on the invoice form. For example, there may be fields that need to be validated and some may need a default value. For example, [Invoice date] should always default to Date(). In a future lesson we'll look at a function that will make [Sell price] default to [List price] from the Parts table.
And, in a while we'll look at reports and we'll see how we can print the invoice.