IT Services

Microsoft Access 2010™ An Intermediate Guide (Level 2)
Contents
Introduction ..............................................................................................................1 Speeding up Table Creation ................................................................................2 Using Datasheet View ...........................................................................2 Using a Table Template .........................................................................3 Input Masks ...............................................................................................3 Importing Tables .....................................................................................3 Relationships .............................................................................................................4 Finding Unmatched Records ...............................................................................6 The Find Unmatched Query Wizard .................................................7 Action Queries ..........................................................................................................7 Make-Table Queries ...............................................................................7 Delete Queries .........................................................................................8 Append Queries .......................................................................................8 Update Queries ........................................................................................9 Crosstab Queries .................................................................................. 10 Form Design ............................................................................................................ 10 List and Combo Boxes ........................................................................ 10 Check Boxes, Toggle Buttons and Option Buttons ................. 11 Option Groups ...................................................................................... 12 Tab Order ................................................................................................ 13 Setting Controls in Tables ................................................................. 13 Switchboards ......................................................................................................... 13 Command Buttons .............................................................................. 14 Text and Combo Box Buttons ......................................................... 15 Macros ..................................................................................................................... 16 Autoexec ................................................................................................. 17 Macro Security Options ..................................................................... 18

Introduction
This document is a further guide to Microsoft Access 2010. For an introduction to Access see Microsoft Access 2010 - An Essential Guide. If you are still running Microsoft Access 2003, see Microsoft Access 2003 - An Intermediate Guide.
1. Open the Start menu and choose All Programs then Microsoft Office and Microsoft Access 2010 2. Click on Open (or simply press )

3. An Open window appears – scroll down on the left and click on My Computer then on Data (D:) 4. Double click on the folder called Training to open it 5. Click on example2010.accdb from the list which appears and press or click on [Open] Note: For those using these notes on a computer not run by IT Services, the example file can be downloaded from the link provided at step 5 above. Users are welcome to take a copy of the example file if they want to practice.

Speeding up Table Creation
In the Essential Guide, you were shown how to create a new table using Design View. Here, let's see the advantages and disadvantages of the other methods of table creation - Datasheet View and Table Wizard.

Using Datasheet View
New tables can be created in Datasheet View; this gives you a skeleton table, with the fields labelled Field1, Field2 etc. As you type data into the fields, Access automatically sets up each field's data type and properties for you. Try setting up a new table: 1. Move to the Create tab and click on [Table] on the far left Before typing in any information, it's a good idea to rename the fields you want to use so that you know what data to type into each column (though you can do this later, in Design View). 2. Double click on the second column heading and rename the field Surname - press 3. Rename columns 3 to 6, naming them FirstName , Sex , Birthday and Weight 4. Press twice and type in the first record (press to move across the fields): Dixon Paul Male 3 May 75 Access automatically assigns data types and properties to the fields (in a similar way to Excel). You could now go on and type in more records but, when using Datasheet View to create a table, it's always a good idea first to check the table design has been set up as you want it. 5. Click on the [View] button to move to Design View – save the table as Table1 - press 6. Move down through the fields - note how each has been set up Although everything works, there are several improvements which could be made. In particular, the text fields are set to store 255 characters and the weight is stored as a long integer (for numbers >32767). Also, Access has automatically set up an ID field (which started numbering at 6!), which you may not want. 7. Starting with the ID field, click on the [Primary Key] button and then [Delete Rows] – answer [Yes] 8. For both the Surname and FirstName fields, change the Field Size: property to 20 9. Set a Caption of First Name for the FirstName field 10. Drag through Surname and FirstName fields then click on [Primary Key]– a key can be set on 2 fields 11. Move down to the Sex field and set the Data Type to Yes/No 12. To make this read Male or Female set the Format property to ;"Male";"Female" 13. Set a Default Value of -1 (for Male) and, on the Lookup tab, change the Display Control to a Text Box 14. For the Birthday field and change the Format to d mmmm (to display just the day and month) 15. Finally, for the Weight field, change the Field Size property to Byte 16. Click on the [View] button to revert to Datasheet View - press for [Yes] to save the changes to the design If you reduce a field size or set up a validation, a warning message appears: 17. Click on [Yes] to cancel any warning messages which appear and enforce the changes (don't worry too much about these messages as you have yet to type in your data) 2

18. The data is all okay except Paul has changed sex – move across to this field and type -1 to reset it 19. Close the table by clicking on its [Close Window] button then it as you won’t be needing it Typing -1 might seem a funny way to display this data but remember that a Yes/No field is stored as 0 or -1. To display Female in future records, simply the default value (or you can type a 0).

Using a Table Template
Microsoft Access has a wide range of sample tables (both for business and personal applications) which can be invoked from a Template. When you first load up Access, you are asked whether you want to use one of these templates to let Access create your database for you. Some of these are very good but you do lose control of your database. The templates can also be used to create a table in your own database: 1. Move to the Create tab and click on [Table Templates] in the Tables group on the left 2. Choose any of the templates on offer – a new table appears 3. Move back to the Home tab and click on the [View] button to move to Design View 4. Press or click on [OK] to save the table as Table1 5. Move down the fields and note how each has been set up You could now reset the properties and delete the fields not needed, but here: 6. Close the table by clicking on its [Close Window] button then it - you don’t need it

Input Masks
An Input Mask can be set on a field whereby only certain characters are permitted in a particular part of the field. For example a userid here at the University must start with 3 letters (indicating faculty, department and status) followed by 2 numbers (year of entry) followed by either 3 more letters or 2 and a number (for the user's initials). Input masks can also be used to add extra characters automatically, for example a currency symbol, or to translate a lower case letter to upper case (or vice-versa). To learn more about input masks 1. Right click on the students table and choose Design View 2. Move down to the Surname field and click in the Input Mask property 3. Press for Help then scroll down to see the effect of the special characters A > sign translates any following letter into UPPER CASE; < makes following letters lower case. Characters are specified by one of several codes: L stands for a required letter; 0 stands for a required number; A stands for a required letter or number. Optional letters are denoted by l; numbers by 9; letters or numbers by a. A \ indicates that the following character should be displayed as it is. For example, a British Post Code could be entered using an input mask of >LA09\ 0LL, a 4-digit phone extension by \x0000. Set an input mask on the Surname field to translate any data entered into a Capitalised name. 4. For the Surname field, set up an Input Mask of >A] 10. Choose your own primary key using the Name field - press for [Next >] 11. Import to Table Halls2010 - press for [Finish] 12. Press or click on [Close] to end the Import Wizard 13. Double click on the new table in the Objects list to open it - note that the Meals field has values of -1 (Yes) and 0 (No), which needs to be changed 14. Move to the Home tab then click on the [View] button to move to Design View 15. Move down to the Meals field and reset the Format Field Property to Yes/No 16. Click on [View] to move back to Datasheet View, saving the changes to the design of the table 17. Next, move to the Create tab and click on [Form] – a new form appears 18. Close the form by clicking on its [Close Window] button – save the form as Halls2010 19. Finally, close the Halls2010 table by similarly clicking on its [Close Window] button Tables (queries, forms, reports etc) can also be transferred directly from one database to another - or you can link two or more databases together. The instructions below import copies of three new tables, including all the data required for the exercises which follow. 1. Move to the External Data tab and, in the Import group, click on [Access] 2. [Browse] for the file extra.accdb (in the Training folder) then press to [Open] it 3. Click on [OK] to Import tables, queries, forms, reports, macros, and modules into the current database 4. Finally, click on [Select All] then on [OK] to import them – press for [Close]

Relationships
Relationships:

Having set up various tables in your database, you need a way to tell Access how the information in the tables relates to each other. You saw how to join two tables together in a query in the Essential Guide. You can do a similar thing for the whole database by defining relationships between your tables. This will then automatically draw the join lines when you have more than one table in a query. Further, you can create forms and reports to display information from several tables at the same time. 1. Move to the Database Tools tab then click on the [Relationships] button 2. Next, click on the [Show Table] button 4

3. Double click on the Halls, Options, students and Tutors tables to [Add] them to Relationships 4. Press or click on the window's [Close] button to [Close] the Show Table window 5. Drag down the bottom border to enlarge each table so that you can see all its fields 6. Using drag and drop, create a join line linking the Name field in the Halls table to the Hall field in the students table 7. The Edit Relationships window appears - click on [Create] to confirm the type of join

Note: In this window you can modify the Join Type (either to select only records where the join field is equal, or to include all records from one of the tables) and can Enforce Referential Integrity (eg a student's tutor must be in the Tutors table). Once Referential Integrity is turned on you can Cascade Update/Delete Related Records. This automatically changes data in a linked table (eg if a tutor leaves and a new member of staff takes his/her place) or deletes records from a database (eg if a customer dies or no longer wishes to use your services). 8. Set up a join line from the students table to the Options table, linking via the Option field 9. Turn on Enforce Referential Integrity then click on [Create] 10. Repeat step 8 linking the LastName in the Tutors table to the Tutor field in students 11. Turn on Enforce Referential Integrity and Cascade Update Related Fields for this link – click on [Create] 12. Drag the tables around to show the join links clearly - move the Options table below Halls 13. Close Relationships by clicking on the window's [Close] button - press or click on [Yes] to save the new design Once relationships have been set up, various additional features automatically emerge. For example, if you set up a form for one of the new tables then a subform giving a list of appropriate students is added. Try this next for the Halls: 1. Click on the Halls table in the Objects list to select it (there’s no need to open it) 2. Move to the Create tab then click on the [Form] button in the Forms group Note how, for each tutor, a list of students with that tutor appears. You can use or the record indicator at the bottom to move between tutors. 3. [Close] the form by clicking on the window's [Close] button - save it as Halls 4. Repeat steps 1 to 3 to create new forms for the Options and Tutors tables You can also see related information when viewing a table: 5. Double click on the Halls table in the Objects list to open it 6. Click on the + sign on the far left of a record to see a list of students in that hall 7. Finally, click on the - sign to close the list then [Close] the Halls table 5

A second additional benefit of relationships is that the links are automatically shown whenever you use more than one table in a query. As an example, regenerate the query for the students' addresses (used for mailing labels in the Essential Guide): 1. Move to the Create tab then click on the [Query Design] button 2. [Add] the Halls and students tables - press to [Close] the Show Table window Note that the join line is automatically drawn between the two tables. 3. Double click on the FirstName, Surname and Hall fields in the students table to fill the Field in the first three columns 4. Double click on the Road and Town fields in the Halls table to fill Field in columns 4 and 5 5. Click on the [Run] button to run the query You will find that only 231 records (out of the 390) are being displayed - almost half are missing! The reason for this is that there is an inconsistency between the two tables on the linked field. You will see next how to identify such problems. 6. Click on the [Save] button, saving the query as Addresses - press for [OK]

Finding Unmatched Records
There are two ways to identify inconsistencies between data in two or more tables - you can either modify the join line or use a special Query Wizard. First, try modifying the join line: 1. Click on the [View] button to move to Design View 2. Right click on the join line and select Join Properties (or use single left click to select it, then double click to show the properties)

You will see that the current join only selects records from both tables where the fields are equal 3. Change the property to show ALL the records in the students table (here, option 3) - press for [OK] 4. Add the Name field from the Halls table to the Query Design by double clicking on it 5. Click on [Run] to run the amended query You now have all 390 records. Note which have empty fields - those for students living in Wessex, Windsor or Private accommodation. If you remember, you had to type in the Wessex and Windsor records yourself on the Essential course and would have to do so again to complete the table here. 6. Close the query by clicking on the window's [Close] button - don't save the changes to the Query Design (you only modified it to identify the problem) - click on [No] 6

The Find Unmatched Query Wizard
The alternative way to find unmatched records is to use the special wizard: 1. Move to the Create tab then click on the [Query Wizard] button 2. Choose Find Unmatched Query Wizard then press for [OK] Note: There is also a Find Duplicates Query Wizard to identify duplicate records. 3. Choose students as the table containing the records you want - press for [Next>] 4. Choose Halls as the table containing the related records - press for [Next>] 5. Match the fields in the tables by selecting Hall in the students table and Name in the Halls table (Access has already done this for you) - click on [] then on [Next>] 6. Move across the Surname and Hall fields to the query results by clicking on them - press for [Next>] 7. Press for [Finish] to end the wizard and run the query The unmatched records are now displayed - again this shows that students living in Wessex, Windsor or Private accommodation are not properly matched. To view the design of the query: 8. Right click on the query’s tab and choose Design View to move to Query Design Note the notation Is Null on the Criteria line - this is what you use to identify an empty field. 9. Close the query by clicking on the window's [Close] button

Action Queries
In the Essential Guide you were introduced to queries. There are however two main sorts of queries - select queries and action queries. What you have seen so far have all been select queries (they select records from tables based on some criteria); action queries, on the other hand, are used to perform one of the following tasks:     Copy records into a new table - a Make Table query Delete records from a table - a Delete query Append records to an existing table - an Append query Update the values in one or more fields in a table - an Update query

A third type of query, a Crosstab query, can be used to summarise the data in one or more tables. Let's look at each of these in turn.

Make-Table Queries
A good example of a Make Table query on the students database would be to archive the records for those students graduating at the end of the year. First you create a select query which identifies them; then you turn it into an action query: 1. Move to the Create tab then click on the [Query Design] button 2. [Add] the students table - press to [Close] the Show Table window 3. Double click on * to set the first Field to students.* 4. Double click on EntryYear to set the second Field 5. For this field, turn off Show then for the Criteria: type 2009 Tip: To set this up as a parameter (ie for any year) set the criteria to [Which Year] 6. Click on the [View] button - you should have 128 records selected (always check your results first) 7. Move back to Query Design by clicking on the [View] button 7

8. On the Query Tools Design tab click on the [Make Table] button 9. When asked, set a new Table Name of OldStudents - press for [OK] A message now appears, indicating that you can’t include a calculated field in this query.

This is really annoying as there isn’t an easy way of omitting the (calculated) email field. It’s much better not to have such fields in a table but to use a query instead. To sort out this problem: 10. Remove students.* from the query by selecting the column and pressing 11. You now have to add all the fields (except email) individually by double clicking on them 12. Click on the [Run] button - press for [Yes] to verify you want the records in the new table 13. [Save] the query as MakeTable Note that the new table has been added to the Tables in the Objects list.

Delete Queries
The same query can be used as the basis of a Delete Query - now that the old students have been copied into a new table, their records can be removed from the table of current students: 1. On the Query Tools Design tab, click on the [Delete] button in the Query Type group 2. Click on the [Run] button - choose [Yes] to permanently delete the records 3. Click on the [Office Button] and use Save As... to rename the query DeleteRecords 4. Close the query by clicking on the window's [Close] button 5. Double click on the students table in the Objects list - you should find only 262 records remain 6. Close the table by clicking on the window's [Close] button Warning: A Delete Query can be a very dangerous thing - it's very easy to accidentally delete the wrong records as it runs as soon as it is opened. It is advisable to save it as a Select Query and then, once you are sure the correct records are being selected, turn it into a temporary Delete Query just before you run it.

Append Queries
As an example of an Append Query, let's put the old students back into the students table - normally you would append the OldStudents table to an accumulative table of PastStudents: 1. Move to the Create tab then click on the [Query Design] button 2. [Add] the OldStudents table - press to [Close] the Show Table window 3. Double click on * to set the first Field to OldStudents.* 4. In the Query Type group click on the [Append] button 5. Set the Append To Table Name: to students - press or click on [OK] 6. Click on [Run] then press for [Yes] to complete the query 7. Close the query, using the window's [Close] button, saving it as AppendRecords If you like, check the students table again has 390 records.

8

Update Queries
Update queries are often used in businesses, for example to raise wages or prices by a given percentage or to reflect a change in tax rates. Here, let's make use of the OldStudents table to update a text field - you're going to add the word Hall to each student's hall of residence (where appropriate). 1. Move to the Create tab then click on the [Query Design] button 2. [Add] the OldStudents table - press to [Close] the Show Table window 3. Double click on Hall to set the first Field: to Hall 4. In the Query Type group, click on [Update] 5. Set the new Update To: row to [Hall] & " Hall" - don't forget the space in " Hall" Note: The square brackets here denote the current field. 6. In the Criteria: row type Not Private 7. In the Or: row type Not Student Village You can also use the notation to signify not equal - students not living in a hall do not need the extra word added. Before you run the update, it’s always good idea to check you have set the criteria correctly: 8. Click on the [View] button and note the results As you can see, the query is not correct – it includes all the students!. If you think about the logic of the criteria, a student living in the Student Village would satisfy the criterion Not Private, while another in Private accommodation would similarly be covered by Not Student Village. In this case you need an AND logical test, not an OR: 9. Click on the [View] button again to return to Design View 10. Change the Criteria: to read NOT "Private" AND NOT "Student Village" 11. Click on [View] to check the criteria is now correct (and again to return to Design View) 12. Now click on [Run] - press for [Yes] to update 77 of the 128 records 13. Close the query, saving it as UpdateRecords 14. Double click on the OldStudents table in the Objects list to open it You will find that the OldStudents table has been updated, but not always successfully. This is because the Hall field has a Field Size of only 15 characters. You should have increased this to 20 to accommodate the extra letters. Fortunately, it doesn't matter here as you are only learning, but you need to be aware of potential problems such as this as updates may not be easily reversible. 15. Close the OldStudents table by clicking on the window's [Close] button Another way of updating data is provided through the join options set up in Relationships. In the earlier section on this you set up Cascade Update Related Fields on the link between a student's Tutor and the LastName field in the Tutors table. Try out this next exercise to see the effect: 1. Double click on the Tutors table in the Objects list to open it 2. Change the LastName of one of the tutors to your_own_name (or type xxxxx if your name is already a tutor) 3. Close the Tutors table by clicking on the window's [Close] button 4. Now open the students table You should find that several students have your name showing as their tutor instead of the original name - the related records have been automatically updated. 5. End by closing the students table - click on the window's [Close] button

9

Crosstab Queries
Crosstab queries are used to summarise data in tables. As an example, let's count up each tutor's number of overseas and non-overseas students: 1. Move to the Create tab then click on the [Query Wizard] button 2. Choose the Crosstab Query Wizard - press for [OK] 3. Base your query on the OldStudents table - click on [Next >] 4. Move Tutor from the Available to the Selected Fields by clicking on [>] to set up the row heading - click on [Next >] 5. Click on Overseas to set up the column heading - click on [Next >] 6. Summarize each row using Count from the list of Functions - click on [Next >] Note that you could also find the maximum/minimum values, the sum or average of any of the available fields. 7. Press to [Finish] the query, accepting the default query name Note that Yes/No field column headings appear as -1 (Yes) or 0 (No) in the summarized table 8. Close the query by clicking on the window's [Close] button

Form Design
In this next section you are going to learn more about the design of forms and, in particular, how to use some of the controls provided. 1. Double click on the Options form in the Objects list to [Open] it Several improvements could be made to the form produced by AutoForm. Some of the boxes (eg option) are too large; some fields (eg tutor and day) could be filled in from a list; some fields (eg term and assessed) could appear as check boxes. In order to have these you must change the form design manually. 2. Right click on the form background (or the form’s tab) and choose Design View 3. Try altering the width of the option Text Box (the box on the right) – click to select it then move the mouse cursor to the far right border, hold down the mouse button, and drag the border to the left You’ll find that you are changing the size of all the boxes – by default, Access 2010 links all the controls in a pre-defined tabular format. To unlink them: 4. First, press to [Undo] the resizing then click on the table indicator just above the option label 5. Next, move to the Form Design Tools Arrange tab and click on the [Remove] button 6. Now, click on the form background (to release the selection) then on the option Text Box again 7. Move the mouse over the right middle handle and drag this left to shrink the box to one unit wide 8. You could repeat step 7 to resize some of the other boxes, if you wanted to

List and Combo Boxes
Combo Box: List Box:

Next you are going to replace the existing Tutor box with a special type of list box, called a combo box. Whereas a list box displays all the possible values on several rows, a combo box has a list arrow attached to a single row. 1. Click on the tutor Text Box (on the right) to select it then press to get rid of it 2. Move to the Form Design Tools Design tab then click on the [Combo Box] tool in the Controls group 3. Move the mouse onto the form to the location of the original box and click once 10

A Combo Box Wizard will start automatically. 4. Press or click on [Next >] for I want the combo box to look up the values in a table or query. 5. Select the Tutors table - press or click on [Next >] 6. Set LastName as the Selected Field by clicking on [>] - click on [Next >] to move on 7. Set the Sort order to LastName - click on [Next >] 8. Adjust the width of the column if necessary (double click on the column heading right border) - press or click on [Next >] 9. Store the value in the tutor field using the list arrow - press or click on [Next >] 10. Type tutor: as the label to appear on the form - press or click on [Finish] Don't worry about lining up the boxes properly - you will do that later. Combo (and List) Box lists can either be looked up in a table (as above) or typed in manually. For the days of the week: 11. Click on the day Text Box ( on the right) to select it then press to get rid of it 12. Again, click on the [Combo Box] tool in the Controls group on the Ribbon 13. Move the mouse onto the form to the location of the original box and click once 14. Select I will type in the values that I want. then press or click on [Next >] You now have to type the values you want into the column provided: 15. Click in the first cell and type Monday then press or to move to the second row 16. Continue typing Tuesday, Wednesday, Thursday, Friday into the rows (double click on the column border to fit) then press or click on [Next >] 17. Store the value in the day field using the list arrow - press or click on [Next >] 18. Type day: as the label you want to appear on the form - press or click on [Finish]

Check Boxes, Toggle Buttons and Option Buttons
Check Box: Toggle Button: Option Button: Property Sheet:

Access provides you with three very similar objects which can be placed on a form where you want to be able to select from a very limited number of alternative values, namely check boxes, toggle buttons and option buttons. All three perform in much the same way but their appearance differs. For a Yes/No data type, for example, you can have a single button or box which can be turned on (for Yes) or off (for No). Here, replace the existing Assessed box by a check box: 1. Click on the assessed Text Box (on the right) to select it then press to get rid of it 2. Click on the [Check Box] tool in the Controls group on the Ribbon 3. Move the mouse to the position of the original box and click once Sadly, there is no wizard to help you create the check box - you have to do the work yourself: 4. Click on the [Property Sheet] button in the Tools group on the far right of the Ribbon You have yet to meet Properties, which at first sight seem very complicated but which in fact aren't that bad. Access allows you to set up all sorts of things here and there isn't time to cover everything in detail, so try changing just a couple of settings: 5. Click on the Data tab to view some of the properties 6. Change the Control Source to assessed by picking from the list provided by the list arrow - this sets up the field in which the data is stored 7. Set a Default Value of Yes 11

8. Click on the All tab to view all the properties 9. Scroll down to get an idea of what else can be set then click on the pane’s [Close] button To reposition and rename the default label: 10. Click on the label (Check 19) to select it 11. Move the mouse to its top left handle 12. Hold down the mouse button and drag the label to line up with the other labels on the left 13. Click inside the box then the existing name and type in assessed: instead 14. Press when you have finished typing

Option Groups
Option Group:

When you have more than two alternatives, you cannot use a single button or box but instead have to create an Option Group. Try this for the Term field: 1. Click on the term Text Box (on the right) to select it then press to get rid of it 2. Click on the [Option Group] tool in the Controls group on the Ribbon 3. Move the mouse onto the form into the space at the top to the right of option/tutor and click once A Wizard is provided to help you. First you need to supply labels for the various options: 4. Type Autumn as the first Label Name, press or and type Spring () then Summer - press or click on [Next >] 5. Set up the default value, if you want (eg Autumn) - press or click on [Next >] 6. There's no need to change the values here - press or click on [Next >] 7. Store the values in the term field using the list arrow - press or click on [Next >] 8. Choose which sort of box/button you require (eg Toggle Buttons) - click on [Next >] 9. Type term: as the caption for the option group - press or click on [Finish] You can now move the component parts of the option button around, if you want. For example, you could line the label up with the other labels on the left hand side of the form, or you could have the toggle buttons across the form in a row rather than a column. You may also want to realign all the objects and make them evenly spaced: 10. Click on the Autumn toggle button and drag it to the space to the right of the assessed check box 11. Repeat step 10 to place Spring and Summer to the right of Autumn 12. Hold down and click on the Spring and Autumn buttons – all three should now be selected 13. Move to the Format Design Tools Arrange tab and, click on [Top] in the Control Alignment group 14. Next, click on the [Make Horizontal Spacing Equal] button in the Position group – your buttons should now be properly aligned and spaced 15. Click on the border which surrounded the group and resize it to the new position using the handles 16. Finally, click on the term Label and, using the handle, drag it to its original position between the time and assessed labels Another way to select the controls on a form is to drag through them: 17. Position the mouse cursor to the left of the Table.students sub-form then hold down the mouse button and drag the rectangle which appears so that it also touches the assessed control 18. Let go of the mouse (both controls are selected) then use to move the controls down 19. Repeat steps 17 and 18 to select the term toggle buttons and move them to the left 20. Now, drag the mouse down the left-hand ruler to select all the controls except the sub-form 12

21. With all the controls selected, click on [To Grid] in Control Alignment then on [Make Vertical Spacing Equal] in the Position group – other buttons here [Increase/Decrease Vertical Spacing] 22. Click in the background to release the selection then drag through the Label controls (the ones on the left) to select just them and then click on [Right] in the Control Alignment group 23. Finally, use the [Format Painter] on the Design tab to copy the font settings from the option box to the boxes with black text (ie tutor, term, day and assessed) – double click on the button to turn this on/off 24. Click on the [View] button to see the redesigned form 25. Press to move through the form and watch what happens

Tab Order
You will find that the order of data entry has changed as you through the fields. Whenever you replace an existing box with a new one, the new one is added to the end of the list. You can set up the Tab Order for a particular box (it's one of its Properties) or you can set it up for the whole form: 1. Right click on the form background and choose Design View 2. Right click anywhere in the Detail background and select Tab Order... You can now set up the order to whatever you want by dragging the fields up and down the list. More simply, if you want the order to move from the top of the form to the bottom, simply: 3. Click on Auto Order to automatically reset it then click on [OK] to close the Tab Order window 4. Click on the [View] button and through your data again - it should now work properly 5. Close the form using its [Close] button - save the changes to the design

Setting Controls in Tables
In the above example, changes to the controls were made on a form, but you can also do so in a table: 1. Right click on Options in the Tables list and choose Design View 2. Move down to the tutor field then click on the Lookup tab in the Field Properties 3. Change the Display Control to a Combo Box 4. Change the Row Source to the Tutors table 5. Repeat steps 2 and 3 on the day field, setting the Row Source Type to a Value list 6. For the Row Source type in Mon, Tues, Wed, Thur, Fri 7. Press to save the changes to the design of the table then click on the [View] button to move to Datasheet View - note that you now have combo boxes for the tutor and day fields 8. Next, move to the Create tab and click on [Form] 9. Note how combo boxes appear automatically on the form then [Close] it - there's no need to save it 10. End by closing the Options table – save the changes to the design Though the combo boxes do work in the table itself, it's always better to use a form for data entry.

Switchboards
Image: Unbound Object: Bound Object:

There's a special type of form, called a Switchboard, which you can set up such that anybody can use your database even if they know nothing about Access. A switchboard is essentially a blank form covered by buttons which open forms, run queries or produce reports. All the user has to do is simply click on the buttons provided. 13

1. Move to the Create tab and click on the [Form Design] button You are now presented with a blank form, which you will have to design from scratch yourself. 2. Drag the Detail borders out to fill the whole screen 3. Right click anywhere on the Detail and select a Fill/Back Color 4. Click on [Label] in the Control group then click on the form where you would like a title to appear 5. Type in a title for your form (eg Department Student Records) then press 6. Change the [Font], [Font Size] and [Font/Fill Colour] to whatever you require 7. Finally, right click on the title and choose Size then To Fit If you would like a picture as part of the background: 8. Click on the [Image] button Note: An Image control is normally used for adding a picture to a form. An Object Frame is mainly used for adding other objects, such an Excel spreadsheet or Word document, but it can be used for a picture. Unbound Objects are not linked to any data held in the tables of the database. If you want to include a picture on a form which is held in a table (eg a student's photograph) then you use a Bound Object Frame. 9. Move the mouse into the Detail where you would like the picture to appear then hold down the mouse button and draw out a rectangle to the size required 10. You are now asked to Insert a Picture - choose your own, if you have a suitable file, or use one from the Sample Pictures folder (in My Documents) – choose it then press for [OK] 11. Resize the picture, if necessary, using the handles provided 12. Move the picture to exactly where you would like it on the form either using the arrow keys or by holding down the mouse button inside the frame and dragging it to its new position

Command Buttons
Command Button:

To add command buttons to the switchboard: 1. Click on the [Button] button in the Controls group on the Form Design Tools Design tab 2. Move the mouse cursor to where you would like the button to appear on the Switchboard and click once - a Wizard starts up to help you set up the necessary instructions:

14

Try creating some buttons to open your forms: 3. In Categories: choose Form Operations and in Actions: select Open Form - press for [Next >] 4. Select students from the available forms - press for [Next >] 5. Press for [Next >] to Open the form and show all the records. 6. Choose Text: for your button but modify it to read Students - press for [Next >] 7. Name the button OpenStudentForm - press for [Finish] 8. Repeat steps 1 to 7 but this time for opening the Options form Next, add a command button to run a query: 9. Repeat steps 1 and 2 10. In Categories: choose Miscellaneous and in Actions: select Run Query - press for [Next >] 11. Select Addresses from the available queries - press for [Next >] 12. Choose Text: for your button but modify it to read Addresses - press for [Next >] 13. Name the button RunAddressesQuery - press for [Finish] You also need a button to close the Switchboard and Quit Access: 14. Repeat steps 1 and 2 15. In Categories: choose Application and in Actions: select Quit Application - press for [Next >] 16. Keep your button as a Picture (the Stop sign is fine) - press for [Next >] 17. Name the button ExitDatabase - press for [Finish] Finally, try using the Switchboard: 18. Right click on the Switchboard and choose Form View (or use the [View] button on the Home tab) 19. Try out the various buttons (except Stop) to see what they do You will soon realise that you need to add corresponding command buttons to Close your forms (instead of having to click on the window's [Close] button). 20. Click on your Students button then on [View] to switch to Design View 21. Click on the [Button] button then click on the form where you would like the control to appear 22. In Categories: choose Form Operations and in Actions: select Close Form - press for [Next >] 23. Amend the Text: to read Close - press for [Next >] 24. Name the new button CloseStudentsForm - press for [Finish] 25. Repeat step 21 but this time choose Record Operations and note what's available 26. Under Actions: select Print Record - press for [Next >] 27. Keep the Text: as Print Record - press for [Next >] 28. Name the button PrintForm - press for [Finish] 29. Click on the [View] button to see the amended layout then try out your new [Close] button - save the changes to the design of the form when asked 30. Press to save your changes – call the form Switchboard

Text and Combo Box Buttons
As an alternative to command buttons on the Switchboard, you can provide boxes (or combo boxes) for users to fill in (or select from). Once the user has provided this information, it can be passed to a query which runs automatically. Try this next: 1. Right click in the background of the Switchboard and choose Design View 2. Next, click on the [Text Box] button in the Controls group then on the Switchboard where you want the box to appear 15

A Text Box and Label appears – note that the former is Unbound. 3. Click on the Label on the left and replace the current text with Name of Student 4. Now move to the Create tab and click on [Query Design] 5. [Add] the students table then press to [Close] the Show Table dialog box 6. Double click on the * to set the first field in the query to students.* 7. Double click on Surname to set the second field in the query to Surname and turn off Show 8. Move down to the Criteria: in the second column then click on [Builder] in the Query Setup group 9. Double click on Forms then Loaded Forms and select Switchboard 10. Finally, double click on Text3 to set up the criteria Forms![Switchboard]![Text3] – then click on [OK] Note: there should only be the one Text box but it may not be called Text3 on your switchboard. 11. [Close] the query, saving it as LookupStudent 12. Select the new query in the Objects list then move to the Create tab and click on [Form] 13. [Close] the form, saving it as LookupStudent You will need a macro to open this form – as you’ll see later. Next, try creating a combo box: 14. Move to the Design tab and click on the [Combo Box] button in the Controls group 15. Now click on the Switchboard where you want the box to appear – the Wizard starts up 16. Run through the wizard, as earlier in the course, picking up the values from the Halls table and using the Name field (in Ascending order) – as a final label for the button type Which Hall? 17. Now create a new query, as in steps 4 to 10 above, based on the Halls table with Name as the second field and set the Criteria: to Forms![Switchboard]![Combo9] (again, it might not be Combo9) 18. Repeat steps 11 to 13, saving the query and form as LookupHall Tip: Another useful control on a Switchboard is a Tab Control. This provides you with a set of tabs, which you can use to group particular controls together (eg a different tab for first, second and final year students).

Macros
A macro holds a series of instructions (commands) such that all the instructions can be carried out simply by running the one macro. Once a macro has been defined it can be assigned to a button on a form so that the user just clicks on that button to carry out the instructions held in the macro. Macros are not covered in detail in this course but, to demonstrate what they are and how they work, you are going to create a couple of simple examples. The command buttons you just created on the Switchboard could have been made by writing your own macros. Indeed, if you want slightly more sophisticated buttons, you will need to use a macro anyway. Let's create a new macro to open the Tutors form: 1. Move to the Create tab and click on the [Macro] button on the right – the following screen appears:

16

2. Click on the [Add New Action] button to see the list of available actions 3. Chose MessageBox from the list 4. Type a Message saying You are about to look at the Tutor's details! 5. Set the Type to Warning! and add a Title of BEWARE 6. Click on the [Add New Action] button again and choose OpenForm 7. Set the Form Name to Tutors 8. Set the Data Mode to Read Only - this prevents anyone accidentally changing the data 9. Close the macro by clicking on the window's [Close] button - save it as OpenTutorsForm Now you need to add the macro to your Switchboard: 10. Click on the [Button] button on the Form Design Tools Design tab then click on the Switchboard where you would like the button to appear 11. Under Categories: choose Miscellaneous and under Actions: select Run Macro - press for [Next >] 12. Select OpenTutorsForm as the macro to be run - press for [Next >] 13. Choose Text: for your button but modify it to read Tutors - press for [Next >] 14. Name the button OpenTutorsForm - press for [Finish] 15. Click on the [View] button and try out the new button - note that you can't change the data 16. Add a Close button, as before, then [Close] the Tutors form - save the changes to the design Next, set up macros to open the LookupStudent and LookupHall forms 1. Move to the Create tab and click on the [Macro] button 2. Click on [Add New Action] then choose Open Form and set the Form Name to LookupStudent 3. Set the Data Mode to Read Only then [Close] the macro - save it as OpenLookupStudent 4. Repeat steps 1 to 3 for the LookupHall form, saving the macro as OpenLookupHall To activate the macros: 5. Right click on the switchboard background and choose Design View 6. Right click on the Unbound Name of Student text box and choose Properties 7. Click on the Event tab and set After Update to OpenLookupStudent using the arrow provided 8. Next click on the Unbound Which Hall? combo box and set On Change to OpenLookupHall 9. Click on [Property Sheet] to [Close] the Property Sheet task pane then press to [Save] the changes to the Switchboard 10. Click on the [View] button to move to Form View and try out the new controls 11. Type Smith for the student name and press then try out the hall combo box Note: you’ll need to add [Close] buttons to the forms to make them easier to use.

Autoexec
To finish, you are going to create a special macro called Autoexec which is run automatically whenever the database is opened: 1. Move to the Create tab and click on [Macro] for a new macro 2. Click on the [Add New Action] button and choose OpenForm 3. Set the Form Name to Switchboard 4. Click on the [Add New Action] button again and set the action to Maximize Window 5. Close the Macro Window by clicking on the window's [Close] button 17

6. Press for [Yes] to save the macro; call it Autoexec Now test out the macro: 7. Move to the File tab and choose Close Database 8. Now click on Recent and select it from the list of Recent Files You should find that the Switchboard is automatically displayed. If you wanted to hide the Navigation Pane and tabs on the Ribbon, you can do so via Options on the File tab. Go to Current Database and scroll down to Navigation (turn off Display) and Ribbon and Toolbar Options(here, turn off Allow Full Menus). 9. Finally, click on the [Stop] button to close the database and Exit Access

Macro Security Options
Sometimes when you open a database with macros, a warning message appears:

1. Press or click on [OK] This is followed by a second Action Failed dialog box with a button to [Stop All Macros] – you have no other choice. The database will then open but with the macros disabled. A Security Warning appears on the Message Bar immediately below the Ribbon:

To activate the macros: 2. Click on the [Options…] button on the Message Bar – another dialog box appears 3. Choose Enable this content then press for [OK] The macros should now be enabled. Though it is not recommended (by Microsoft) you can disable this security setting so that the macros run automatically for any database you open. This should not be a problem here at the University (or at home for your own databases). To change the security settings: 4. Move to the File tab then click on Options at the foot of the list 5. Next, choose Trust Center on the left then click on [Trust Center Settings] on the right 6. Choose Macro Settings on the left then Enable all Macros (not recommended …) 7. Click on [OK] twice to close the dialog boxes

™ Trademark owned by Microsoft Corporation. © Screen shot(s) reprinted by permission from Microsoft Corporation. Copyright © 2011: The University of Reading Last Revised: December 2011 18