Microsoft Access is a popular database platform, used to provide an introduction to databases. As part of the Microsoft Office package, there are a lot of people who have access to Access. This guide focuses on how to create forms, make those forms usable, ensure the integrity of the form data and test the forms. These activities can be performed on a previously created database with sample data. This guide is intended for students studying the BTEC National in Information Technology and addresses Part B ofthe Unit 2 examination.
How to create a form
Activity 6 will ask you to create forms based upon a set scenario. Don’t worry about making the database for this section, you will be provided with one. When creating an Access form, it is important to decide what sort of form you are wanting to create. Is it a columnar, tabular, justified or datasheet form. All of these forms have a different appearance, but will contain the data from your database. Forms are created using the form wizard, by following these steps, as seen in this video:
Open the Form Wizard
Use the drop down box to select the table you want your form to interact with
Use either the >> button to let all of the fields from that table display on the form or use the > button to select certain fields to display on your form.
Once you are happy with the fields that will be on your form, click the next button
Select whether you want a tabular, columnar, justified or data sheet view (these options change how the form looks but doesn’t change
Select finish
One thing to note: if you are combining fields from multiple tables, then make sure you select the option to have your form as a single form, otherwise you will end up with a form within a form).
Form usability
The forms that you create need to be usable. This means that any person that uses your system needs to be able to both understand your form, and use your form. You cannot assume any prior knowledge of your user. You need to make sure that no IDs can be changed, no associative IDs are shown (even if your tables are joined) and that no field labels are shown, instead you should have text that would explain what the field is. For example: instead of RoleType you could have Person’s role.
Navigation is also important. You should ensure that a user can navigate through all required elements of the system. You can do this by creating buttons and using macros. To do this, you have to complete the following steps, seen in this video:
Open your form in design view
Look at the design tab at the top of the screen
Select a button from the elements pane, and use your mouse to create the button on your form, put a word or phrase on your button (by double clicking it) that describes where the button will take you to.
Right click your new button and click properties
Go to the ‘Event’ tab on the properties pane
Locate the onClick event, and click the … on the field adjacent to the one with onClick in it.
Select macro builder
In the macro builder, select the action ‘Open Form’
Select the form you want to open
Save your macro and close the macro builder
One last thing to remember is to make sure any data you are displaying is not truncated. Truncated data is when data is cut off, not displayed correctly or only partially shown. When your data is being displayed in Access Forms make sure the entirety of your record is shown in the field, no data should be cut off. If it is cut off, then increase the size of the field to prevent this truncated data.
Ensuring the integrity of data
How to write validation rules
In addition to having validation forms in your database, you can have validation rules on your form. Your exam script may specify that you cannot change the database and this is not validating your database. This is validating the values within the fields of your form. Validation rules for this are written in the same way as those for the database, but instead of applying the validation to the database table, you do the following:
Open your form in design view and select the field you want to validate using your mouse.
Right click it, and select properties
Go to the ‘Data’ tab on the properties pane
In the ‘Validation Rule’ field, type in your validation rule
In the ‘Validation Text’ field, type in the text that you want to appear when the user doesn’t enter the correct data.
For more information on how to create access forms, check out Microsoft’s validation guide here [1].
Ensuring all fields are filled before saving a record
One elements you may have to incorporate, is a check that makes sure that all of the fields in your form have values in them before you try to save a record. That makes sure that you aren’t saving any blank fields in your database, and that any required fields are filled. To create this, you can complete the following steps:
Open your form in design view
Right click your form and select Form Properties
In the Form Properties pane, select ‘event’
Find ‘Before Update’ and click on the drop down arrow for ‘Before Update’
Select ‘[Event Procedure]’ and then click the three dots.
In the code window, between Private Sub,... and End Sub… you will see a blank line, this is where you need to type the following:
If IsNull( field_name) Then
MsgBox(‘Could not save record because…..’)
Cancel = True
End IfIn the code above, change field_name to the field you’re checking, the one that you don’t want to be blank. This field name must copy the field name in your database, including any punctuation or capitalisation. Additionally, the error message (currently ‘Could not save record because’) should reflect the reason why the record could not be saved.
Once you have typed this, save your code and close it. Then save your form and open it in Form View. It should now prompt you if you try to save a record with blank fields.
Testing forms
Testing forms is an important component of the part B assessment, and makes up Activity 7. In the exam, you are recommended to have 20 minutes to fill out this question. You will be given a series of tests to complete on your part B database. You will need to provide evidence for the tests’ completion and state whether any further action will be required post-test. There are three main types of tests you can do to ensure that your database is well-created and those are tests using Normal Data, Erroneous Data, and Boundary Data:
Normal Data - Normal data is the data that you would expect to have in your database. If your database is meant to be a certain value e.g Student, Teacher or Administrator, to test a database using normal data, you would use one of those three values (with the correct capitalisation). Testing of this type should require no action, and any tests should be documented with a screenshot showing that the data has saved correctly.
Erroneous Data - This is data that is most definitely wrong and should cause an error (hence erroneous). To look at our Student, Teacher or Administrator example, a test using erroneous data could be the number 42, or the date 01/01/2021. Erroneous data should ideally be of a different data type, definitely not one of the set values required for that field. My advice is think of the most incorrect answer a user could give, and put that. Testing of this type should have the evidence of a screenshot showing that an error had occurred.
Boundary Data - This is data on the edge of being wrong, but also on the edge of being incorrect. For example, if you had to choose between the numbers 1 and 3, boundary tests would be 0, 1, 3, and 4. A boundary test for our Student, Teacher and administrator example would be ‘student’ with no capital S at the start.
Evaluation
The final thing you will need to do, is evaluate your interface. This will be evaluating it against the points provided in the examination brief and against your form’s usability, accessibility and functionality. When completing this section don’t forget the importance of including a point, the evidence of where it has occurred and an evaluation of why it works/doesn’t work/any notable features.
References:
[1] Microsoft [2021] Restrict Data Input by Using Validation Rules. Available at: https://support.microsoft.com/en-us/office/restrict-data-input-by-using-validation-rules-b91c6b15-bcd3-42c1-90bf-e3a0272e988d (Accessed: 21/05/21)
Comments