Creating a form
What is a form used for?
Entering data directly into tables can only be achieved if you have the necessary knowledge to do so. Forms are used as an interface between the user and the database tables.
Forms are created to enable novice users to use a database with relatively little knowledge of how the system actually works.
Microsoft Access does a very good job of creating a form using the wizard.
Design considerations
When designing your form you need to consider who the audience are i.e. the users. All the forms that you create within your database need to be consistent.
Consistency
When considering consistency you need all your forms to have the same layout i.e. buttons are all located in the same place and the same use of colours and logos etc.
This will enable the user to know what to expect when opening a different form from the one they are currently using. This method promotes confidence, usability and overall a more successful database management system.
What problems do you think could occur if the user entered data directly into the tables? (Think back to your own experience of doing this)
Before Creating the Forms, Lets create a query.
A (select) query enables us to retrieve data from the database that meets a certain criteria. This enables us to search for specific records.
We will be discussing queries in a lot more detail in the next lesson.
Step One
Select the Queries tab, and then double click on ‘Create query in Design View’
Step Two
You should be now confronted with the query builder, see below.
Add the following tables: OrderItem, Item and click ‘Close’
Step Three
Your tables should look like the ones above. You should have a relationship between the two tables.
Double Click on the following Fields:
OrderID (From OrderItem), ItemID (From OrderItem), Description, ItemPrice, and Qty.
Step Four
Your Query design should look like the one above.
You are now required to create a Calculated Field. This field will be used to calculate the cost of each item that has been ordered. (ItemPrice*Qty)
You need to type in the following formula into the query design below:
LineCost: [ItemPrice]*[Qty]. The ‘LineCost’ will be the name of the calculated field and the value will be the ItemPrice * Qty.
Step Five
Ok, let’s run the query and look at the results
Notice the LineCost Field. This has multiplied the Item price by the Qty.
You should save this query as ‘qryLineCost’. You should now close this query.
This query will be needed for the Forms exercise.
Creating Forms
Step one – Create the Order form
(You need to relate all tables together using the ERD diagram that you created last week)
Click Forms on the main menu, once selected double click on “Create form by wizard”
Step Two
You should be now confronted with the following window:
Select “Table: Order” and then click “>”. Finally select “Query: qryLineCost” and then click “>”
Now click Next.
Step Three
This form allows you to select how you want to view your form.
Select “by Order”.
If your form does not offer a sub form you have probably related the tables incorrectly.
Select “Form with subform(s)” and click “Next”
Step Four
You are now required to select what style you want your form to appear as.
You can choose any style you like, please remember to use the same style on each of the forms you create to promote consistency. Once you have selected the style you want click “Next”.
Step Five
The final step is to give your forms an appropriate name i.e. “Order”, “Line Cost”.
Once you are happy with your form names click “Finish”.
Your form should look similar to the form below:Note: you should put your form in design view and resize the sub form to make it fit as shown below.
You will notice the Record bar at the bottom of the form; this is a navigation tool to allow you to display each of the order records held within your database, alternatively you could make use of command buttons to do the same job but in a more user friendly way.
From here you will be able to add, delete and update records.
Step Six
Design View
To make changes to your form you need to click“Design view”, See below:
- Enlargement of the design view button.
This will place the form in a design environment to enable you to make changes to it.
All the tools you will need to make changes to the form are available in the toolbox – see below:
Click here to display the toolbox.
Add a label to the form
To add a label to your form you need to select label from the toolbox.
Label
You then need to draw on the label onto your form and enter in some text i.e. Order Details. You should use the Form Header to insert your label into.
Please make sure that your form looks similar to the one above.
Step Seven
Close and save this Form, you should notice that you have two forms created, as shown below. The Order form is the main form and the LineCost is the sub form of the order form.
Step Eight – Create the Customer Form.
Click on ‘Create form by using wizard.
Step Eight – Create the Customer Form.
Select “Table: Customer” and then click “>”.
Now click Next.
Complete the rest of the steps required to create the ‘Customer’ form. – See below:
To make your form more user friendly you need to add a logo to the form and labels.
To place a logo on your form simply get a picture from the internet and copy and paste it to your form.
Try to make your form look like the one below:
Step Nine – Create the Customer Form – sub form.
Now we need to create a sub form on the customer form to show what Orders have been made by that Customer.
Put the Customer form in design view, and display the toolbox.
Click on the Subform/Subreport tool on the Toolbox.
Step Ten – Create the Customer Form – sub form.
Click and drag the sub form across the bottom of the Customer form.
Make sure you select ‘use an existing form’ and click on the ‘Order’ form. Now click ‘Next’.
Step Eleven
Make sure you select ‘Show Order for each record in Customer using Customer_ID’.
This will only show orders that are related to the customer that is currently being displayed on the Customer form. Now Click ‘Next’.
Step Twelve
At this stage click ‘Finish’
Step Thirteen
Your form should now look similar to the one below:
Design View
Form View
Notice how the Customer details are on the main form, this is because it is the One side of the relationship and the Order the Many side. The Line Cost sub form will show what Items are attached to a particular order. “A customer can make many orders. One order may have many items attached to it.”
Try adding Item number 4 to this order.
Other features
Some other features that could be added to the form are buttons, these are available within the toolbox.
Drag a button to the form, you will then be presented with the command button wizard - see below:
Select “Record Navigation”, followed by “Go To Next Record”, then click “Next”.
The next step is to give your button a user friendly description or picture. For simplicity I have selected the default picture. Once you have added your text or picture select “Finish”.
Notice the button on the form; this will enable the user to go through each of the customer records.
Try adding the following buttons to the form: “Go to previous”, “Add new”, “Delete record”. – Do the same for the Order form.
For more help feel free to discuss issues with your tutor, you can also get extra help from an on-line tutorial:
Create a list:
A list box / combo box is a form of validation as it allows a user to select a Staff member from a list as apposed to typing in the data.
Put the form in design view as shown below:
Right Click on the Staff_ID textbox and select, ‘Change To’“Combo Box”.
Once the Staff_ID textbox is changed to a Combo box. Right Click again on the Staff_ID textbox and select “Properties”.
You should now have the following window open:
On Row Source “Click on …”
The following windows should be presented:
Because we want to display a list of available Staff Members we need to add the Staff table, once added close the ‘Show Table’ window.
We need to add the StaffId field because we need to store the ID of the Staff member asa foreign key field inthe Order table. We also need the StaffName field because the user will not necessary understand what a number represents.
Computers tend to work with numbers and humans tend to work with descriptions/labels.
Add the fields to the query builder by double clicking on the relevant fields in the Staff table.
Once these fields are added close the query builder by clicking “x”, you should be then confronted with the following message:
Click “Yes” this will insert the SQL (Structured Query Language) into the row source of the combo box.
Select the “Format” tab and change the column count to “2”. This is because you selected two fields within the query builder therefore we need to display two columns.
Change the widths of both columns to “1cm;2cm”.Close this window once this data is entered.
Run your form:
Notice the drop down list of StaffIds and Names.
Self assessment questions
What is the main purpose of a form?
What should you consider when designing and implementing your form?
What view should you select if you want to add extra controls and change the design of your form?
What box do you need to display to get the controls from i.e. buttons, labels etc?
Did you have any difficulties completing this exercise, if so what were they?
What areas do you feel you need extra help with?
BTEC National Extended DiplomaPage 1 of 25Database Design