[Lecture 5] Creating A Sub Form in MS Access
CREATING THE SUB FORM
[IMG]file:///C:/Users/Indus/AppData/Local/Temp/moz-screenshot.png[/IMG]Now that we have created a main form for recording customer information we now
need to show sales information. We could do it by creating a sub form to hold sales
information and link this sub form to our main customers form.
Firstly we need to create the table to store the sub form data. I have created a basic
sales info table as follows:
Notice I have created a CustomerID field. We also created one earlier in our
customers table. This is the field that links both tables. The data type was set as an
auto number earlier in out customers table and for this new sales table we should set
the CustomerID field to have a data type of Number. In the properties we should then
set the field size to be long integer.
We do not have to name the linking fields with the same name. We could call one
CustomerID and the other xyz if we wanted, but it makes things clearer if we keep to
something which makes sense.
Recall we set the CustomerID field in the customer table to be a primary key. The
CustomerID field in the sales table is what is known as a foreign key. We do not need
to set it though. Just creating it and naming it is enough.
There is a way of manually linking the tables using what is known as relationships,
but I will leave that for another day. Let’s just get it up and running.
Go back to the database window and select the forms tab. Select ‘New’ and then
select the AutoForm: Datasheet option that appears.
Select the table ‘tblSales’ to be the source of the form and click ok
You should now see a datasheet as follows:
Now we need to wire the forms up so that we can see which customers have made
To do this right click the frmCustomers form and select design view.
You will need to bring the database window into focus so that you can see it
alongside the form. Pressing the F11 key will show the database window.
Select the Sales form and keep the mouse button pressed while you drag it to the
main Customers form. Only release the mouse button when it is in position at the
bottom of the Customers form. It should look like the following:
You can now drag the sub form into position. Click on the sub form so that it is
highlighted. You will see small colored squares around the edges showing that your
have selected it.
Move the mouse to the small square at the top left and hold and drag the form into
If you right click the sub form and select properties you will notice that Access has
understood that you want to link the forms together via the CustomerID fields that we
set up in the customer and sales tables. The properties are Link Child Fields and Link
Master Fields. If the field names were different we may have to link them manually in
Run this form to check out how it looks. To do this go to the ‘View’ menu and select
Try entering an order number, date etc into the fields of the sub form. Don’t enter
anything into the CustomerID field. Access will update this field automatically.
You can move to the next line after each entry is complete
Obviously the form needs some tidying up as we can’t see all of the sub form at
present, but you get the idea of how to create a simple system.
Now save your work
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)