Previous
Contents
Next
Lesson 9 for
Creating a Master/Detail Form for transactions
Creating a Master/Detail Form for transactions
|
For more information and special deals related to any of the issues on this page, place your cursor over the double-underlined links.
All information supplied by Kontera.com. |
Transaction processing
So far we've created several tables, forms and queries. We've input data into the tables and displayed it in several ways. All the work we've done so far has been what we call maintenance. You must be able to add, change and delete all the data that you have in the tables.
Now we come to the money-making part of the operation. We will look at transaction processing. A transaction is recorded when money is exchanged for goods or services. In our business it happens when a customer rents a movie.
Before getting into the details, let's look at what we want to accomplish. When a customer rents one or more movies, we want to record the transaction in an Invoice form that will look something like this:
For future reference note that that type of form will occur in all kinds of situations: invoices, customer orders, work requisitions, purchase orders, expense reports, timesheets and on and on ...
There is probably not a single business application that doesn't use several of these types of forms.
Without going into all the theory behind it, in relational databases, of which Access is one, there is a standard table structure that is used to record this type of transaction. It's called Master/Detail.
What happens is that you use one table, the Master, to record the general part of the transaction: the transaction number, the customer ID, the date, shipping information, etc.
The other table, the Detail, will hold the particulars of each line in the transaction: the item number, the quantity, the price, etc.
The actual structure of the tables will vary slightly depending on the application. In our case, for example, if a customer rents 5 movies the "Out" date and time is the same for all the movies but the return date and time may be different for each: some may be 1-day rentals and others 7-day. So, we keep the Out date in Master but the Return date is in Detail. And we don't have a quantity since there is always only one of each movie.
Now, if you take another look at the tables you should have in the database it should start to become clear.
A few points to note when building the tables:
The Northstar application comes with Access. It is the standard demo for relational database construction. You should normally find it in the folders where you installed Access.
Northstar is a wholesaler. Therefore it has all the standard tables and forms we've been talking about here: Master/Detail, customer order, invoice, etc. Take the time to study the application and to compare it with what you want to do with MikeVideo.
The Invoice form at the top of this page is a rough draft of what we really need. If you use the AutoForm option that's what you get. But it's far from complete: it doesn't show the customer's name and address, the employee's name, the invoice total, taxes, etc.
We can fix all that but it will require some work.
This lesson is probably the most difficult in the tutorial, so pay close attention!
There are several ways to proceed. We could use a Form Wizard, or do the whole thing manually or a combination of both.
We'll use the combination approach. This way, you'll understand all the little tricks and you should then be able to reproduce the technique in all kinds of applications.
Here is the procedure, step-by-step:
Next week: The Application Menu. See you then!
A few points to note when building the tables:
- td_number and tm_number contain the same value since they keep the details with the master; since tm_number is AutoNumber, td_number must be Number, Long integer.
- If a transaction consists of 5 movies there will be 5 lines in Detail with the same td_number; that can't happen with a primary key. Therefore, we create a concatenated key: the primary key is: (the transaction number + the movie number) and that is always unique. However, the individual parts of the key are not unique. If there are 5 movies in a transaction, the same td_number will appear 5 times. And the same movie can be rented many times by many customers. It's important to make sure that the Duplicates OK property appears, especially when you've played with the keys several times, turning them on and off. To create a concatenated key, select both lines with ctrl-click before you hit the key symbol.
- All the other linked columns, customer_id, employee_id, movie_number must respect the relationship rules we saw in a previous lesson.
The Northstar application comes with Access. It is the standard demo for relational database construction. You should normally find it in the folders where you installed Access.
Northstar is a wholesaler. Therefore it has all the standard tables and forms we've been talking about here: Master/Detail, customer order, invoice, etc. Take the time to study the application and to compare it with what you want to do with MikeVideo.
Building the form
The Invoice form at the top of this page is a rough draft of what we really need. If you use the AutoForm option that's what you get. But it's far from complete: it doesn't show the customer's name and address, the employee's name, the invoice total, taxes, etc.
We can fix all that but it will require some work.
This lesson is probably the most difficult in the tutorial, so pay close attention!
There are several ways to proceed. We could use a Form Wizard, or do the whole thing manually or a combination of both.
We'll use the combination approach. This way, you'll understand all the little tricks and you should then be able to reproduce the technique in all kinds of applications.
Here is the procedure, step-by-step:
- Create a query for the Master form and one for the Detail form. Each of these will have all the fields you will want to see on the Invoice.
- Using AutoForm, start creating the Master form from the MasterQuery. You will get a very basic form which you will then modify:
- Using AutoForm again, create the Detail form from DetailQuery. Change its Display property to Datasheet. Don't bother with alignment and color, etc. because that doesn't matter in datasheet view.
Then, from View in the Menu, add a Header/Footer. This won't appear in datasheet view but we'll use it to calculate our Invoice total later. In the Footer, create a TextBox for the subtotal amount and give it the properties as shown.
- In the Master, create a Subform object. The subform is like a window in which you will display another object, in this case the Detail form.
- In the Master, add TextBoxes for the totals after the Subform. Use the properties appropriately.
This is tricky! Be careful!
- Finally, go through the Master carefully and fix the Tab Order.
Also, for all the fields that are displayed but you don't have to enter, chnage their TabStop property to 'No'. This means that when you tab through the form you enter a customer ID and Name and Address are displayed automatically, the cursor won't stop on those fields.
Do the same for Trx number and Title in the Detail form.
Next week: The Application Menu. See you then!