Download Microsoft Access Tutorial: A Comprehensive Guide to Database Design and Development and more Lecture notes Computer Applications in PDF only on Docsity!
Microsoft-Access Tutorial
Soren Lauesen
- Version 2.4b: July E-mail: [email protected]
- The hotel system................................................... Contents
- Creating a database
- 2.1 Create a database in Access
- 2.2 Create more tables
- 2.3 Create relationships
- 2.4 Look-up fields, enumeration type
- 2.5 Dealing with trees and networks....................
- Access-based user interfaces
- 3.1 Forms and simple controls.............................
- 3.1.1 Text box, label and command button......
- 3.1.2 Adjusting the controls.............................
- 3.1.3 Cleaning up the form
- 3.1.4 Shortcut keys for the user
- 3.1.5 Lines, checkbox, calendar.......................
- 3.1.6 Combo box - enumeration type
- 3.1.7 Combo box - table look up
- 3.1.8 Control properties - text box...................
- 3.2 Subforms........................................................
- 3.2.1 Subform in Datasheet view.....................
- 3.2.2 Adjust the subform
- 3.2.3 Mockup subform.....................................
- 3.2.4 Subform in Form view............................
- 3.2.5 Summary of subforms.............................
- 3.2.6 Prefixes
- 3.3 Bound, unbound and computed controls........
- 3.3.1 Showing subform fields in the main form
- 3.3.2 Variable colors - conditional formatting.
- 3.4 Tab controls and option groups......................
- 3.5 Menus
- 3.5.1 Create a new menu bar............................
- 3.5.2 Add commands to the menu list
- 3.5.3 Attach the toolbar to a form....................
- 3.5.4 Startup settings - hiding developer stuff
- 3.6 Control tips, messages, mockup prints
- Queries - computed tables.................................
- 4.1 Query: join two tables....................................
- 4.2 SQL and how it works
- 4.3 Outer join
- 4.4 Aggregate query - Group By..........................
- 4.5 Query a query, handling null values
- 4.6 Query with user criteria
- 4.7 Bound main form and subform
- 4.7.1 Editing a GROUP BY query...................
- Access through Visual Basic
- 5.1 The objects in Access
- 5.2 Event procedures (for text box)
- 5.2.1 More text box properties......................... - 5.2.2 Computed SQL and live search............... - 5.2.3 Composite search criteria........................ - 5.2.4 Event sequence for text box .................... - 5.3 Visual Basic tools........................................... - 5.4 Command buttons .......................................... - 5.5 Forms ............................................................. - 5.5.1 Open, close, and events........................... - 5.5.2 CRUD control in Forms .......................... - 5.5.3 The OpenForm parameters...................... - 5.5.4 Multi-purpose forms (hotel system)........ - 5.5.5 Dialog boxes (modal dialog)................... - 5.5.6 Controlling record selection.................... - 5.5.7 Column order, column hidden, etc. ......... - 5.5.8 Area selection, SelTop, etc...................... - 5.5.9 Key preview ............................................ - 5.5.10 Error preview ........................................ - 5.5.11 Timer and loop breaking ....................... - 5.5.12 Multiple form instances......................... - 5.5.13 Resize.................................................. - 5.6 Record sets (DAO)....................................... - 5.6.1 Programmed record updates.................. - 5.6.2 How the record set works...................... - 5.6.3 The bound record set in a Form ............ - 5.6.4 Record set properties, survey ................ - 5.7 Modules and menu functions ....................... - 5.7.1 Create a menu function ......................... - 5.7.2 Define the menu item............................ - 5.7.3 Managing modules and class modules .. - 5.7.4 Global variables .................................... - 6. Visual Basic reference...................................... - 6.1 Statements .................................................... - 6.2 Declarations ................................................. - 6.3 Constants and addresses............................... - 6.4 Operators and conversion functions............. - 6.5 Other functions............................................. - 6.6 Display formats and regional settings .......... - 7. Access and SQL................................................ - 7.1 Action queries - CRUD with SQL ............... - 7.1.1 Temporary table for editing .................. - 7.2 UNION query............................................... - 7.3 Subqueries (EXISTS, IN, ANY, ALL .) .. - 7.4 Multiple join and matrix presentation .......... - 7.5 Dynamic matrix presentation ....................... - 7.6 Crosstab and matrix presentation ................. - 8. References......................................................... - Index......................................................................
Printing instructions
Print on A4 paper with 2-sided printing so that text and associated figures are on opposing pages.
Version 1 : October 2004.
Version 2.1 : November 2004. Changes: a. Restructured section 3.2 with small additions. b. Section 7.1 on action queries added. c. Small changes and additions to Chapter 6 with corresponding changes in the Reference Card. d. Index provided
Version 2.2 : April 2004. Changes: a. SQL HAVING introduced in section 4.2 and the example in section 4.4. b. More on aggregate functions in section 4.4. c. ColumnOrder, ColumnWidth discussed in section 5.5.7. d. Selection of an area in the datasheet is discussed in section 5.5.8. e. Section 5.7 (action queries) now moved to Chapter 7. f. Action queries, Union, Subqueries, Crosstab, etc. discussed in Chapter 7 (a new chapter). g. Various small changes and improved explanations here and there.
Version 2.3 : September 2006. Changes: a. Access 2003 dialog when opening a database changed (page 8). b. Look-up fields for foreign keys deleted (last part of section 2.4). Access's automatic creation of relationships caused too much confusion. c. Combo boxes described in sections 3.1.6 and 3.1.7. d. More events explained in section 5.2.3. e. Various misprints corrected.
Version 2.4 : August 2007 and July 2011. Changes: a. Partial integrity (page 12). b. Adding a label to a control (page 20). c. DateTime Picker (page 22). d. More Null rules (page 62, 77, 124). e. Access data model and experiments improved (page 68-70). f. Composite search criteria, more computed SQL, date comparison (page 76-77). g. Event sequence for textbox: small corrections, e.g. OldValue (page 78). h. Improved area selection (page 95-96). i. Error handling, user errors (page 97-98). j. Timer and loop breaking (page 98-99). k. Managing modules and class modules (page 112). l. Error handling, VBA errors, Err object (page 117). m. Enum type (page 121). n. Partition operator (page 124). o. Week number in the Format function (page 126). p. Dynamic matrix simplified (page 136). q. Minor corrections and improvements in many places. r. Version 2.4a: Note on AutoNumber added to Figures 2.1C and 2.4. s. Version 2.4b: Copyright notice more liberal. Misprint corrected (page 65, step 14 and 15). Figure 52B (page 75) shows quote-stuff more clearly. SendKeys on page 99 elaborated.
© Soren Lauesen, 2007 Permission is granted to use, print and copy the file on a non-profit basis as long as the source is clearly stated. The document is available on the author's web site on these conditions.
2 Preface
1. The hotel system
In this booklet we illustrate MS-Access by means of a system for supporting a hotel reception. The system is used as the main example in User Interface Design - a Software Engineering Perspective , by Soren Lauesen. If you know the book, skip this section and go straight to Chapter 2.
Screens
The hotel system consists of the screens shown in Fig- ure 1A.
Find guest. The Find guest screen allows the recep- tionist to find a guest or a booking in the database. The receptionist may enter part of the guest name and click the Find guest button. The system then updates the lower part of the screen to show the guests or bookings that match. The receptionist may also find the guest by his phone number, room number, or stay number (also called booking number ).
The receptionist can select the guest from the list and click the buttons to see details of the booking or create a new booking for the guest.
Room Selection. The Room Selection screen gives an overview of available rooms in a certain period. Avail- ability is shown as IN when the room is occupied, BOO when it is booked, etc. The receptionist may specify the period of interest and the type of room, then click the Find room button. The system updates the ta- ble at the bottom of the screen to show the rooms of interest. The receptionist can then choose a room and book it for the guest – or check a guest into the room.
Stay. The Stay screen shows all the details of a book- ing, for instance the guest and his address, the rooms he has booked and the prices. When the guest is checked in, the Stay screen also shows breakfast and other services he has received. The system shows these details on the Services tab. Here the receptionist can
record services that the guest has received. The system uses the term Stay to mean a booking or a guest who has checked in.
Breakfast list. The Breakfast screen shows the break- fast servings for a specific date. It handles just two kinds of breakfast: self-service breakfast in the restau- rant (buffet) and breakfast served in the room. The waiter in the restaurant has a paper copy of the list and records the servings here. Later the receptionist enters the data through the Breakfast screen.
Service list. The Service list shows the price for each kind of service. Hotel management uses this list to change service prices or add new kinds of service.
Database
The system uses a database with several tables. They are shown as an E/R data model on Figure 1B.
tblGuest has a record for each guest with his address and phone number.
tblStay has a record for each stay (booking or checked in) with a booking number ( stay number ) and the pay method.
tblRoom has a record for each room in the hotel.
tblRoomState has a record for each date where a room is occupied. It connects to the room occupied and the stay that occupies it. If the room is occupied for repair, it doesn’t connect to a stay.
tblRoomType has a record for each type of room (room class) with a short description of the room type, the number of beds, and the prices.
tblService has a record for each type of service with its name and price per unit.
Fig 1B. Tables as E/R model
tblStay
tblRoomState
tblRoom
tblServiceReceived (^) tblServiceType
tblGuest
tblRoomType
tblServiceReceived has a record for each delivery of service to a guest. It connects to the type of service and to the stay where the service is charged (there is an in- voice for each stay).
4 1. The hotel system
Fig 1A. Hotel system screens
- The hotel system 5
Fig 2.1B Define a table (design view)
Primary key. Right click
Possible data types
Field properties. Also use F1 - Help.
Table name. Access asks for it the first time you close the window.
One line per field
different). We have selected the Tables tab, but there are no tables or other things in the database as yet. However, you see three icons that can create tables for you. When you have created a table, it will appear in the table window and you can then Open it and enter data into it, or you can Design it, i.e. change the defi- nition of it. (In Access 97 the database window looks like a traditional tab form. There are no create-icons, but function buttons for the same purpose.)
Define a table
- Double click on Create table in Design view.
Now you get a window as shown on Figure 2.1B. Here you define the fields (attributes) of the table. The list of fields runs downwards with one line per field. Initially there are only empty lines. The table hasn't got a name yet. Access asks for the name when you close the win- dow.
The figure shows the finished guest table. You see the field names to the left. In the middle column is the type of the field - Data Type. The figure shows all the pos- sible types as a combo box. The most important data types are Text, Number, Date/Time, and AutoNumber. An AutoNumber is a counter that Access increases for each new record, so that it serves as a unique key. The value is a Long Integer (32-bit integer). We explain more about data types in the next section.
- Fill in all the field lines according to the attributes in the guest table (see the figure). All the fields are
of type Text, except the guestID which is of type AutoNumber.
Note that although we say phone number and passport number , these fields are texts because the "numbers" contain parentheses, dashes and maybe letters.
When you have chosen a data type, you can choose a number of other field properties. They are in the lower part of the window. On the figure you can see that the name field is a text field with space for 50 characters. You can also see that the user doesn't have to enter anything in the name field ( Required =No). You should change this to Yes since it doesn't make sense to have a guest without a name.
Try to use Access's help to find more information about the data types and their properties. For instance, put the cursor in the Data Type of a field and click F1. Or point at one of the properties and click F1.
Lookup Wizard is not a field type. If you select Lookup Wizard, it makes the field into a combo box where the user can select a value instead of typing it into the field. We will look closer at Lookup in section 2.4.
Key fields Often you have to define a key field so that other tables can refer to this one. In our case, guestID must be the key field:
- Right-click somewhere in the guestID line. Then select Primary Key. Access now shows that the field is the key.
- Creating a database 7
You can remove the key property again by once more selecting Primary Key. If the key consists of more than one field, you first select all the fields by clicking on their left-hand marker with Ctrl down. Then select Primary Key by right-clicking inside one of the field lines.
- Close the window. Access asks you for the name of the table. Call it tblGuest. (The prefix tbl will help you remember that it is a table. As the system grows, there will be guest windows, guest buttons and many other things. Without discipline on your part, it becomes a mess.)
If you have not defined a primary key, Access will warn you and suggest that it makes one for you. Don't let it - do it yourself. Or at least check what Access makes in its excessive helpfulness.
Enter data After these efforts, it is time to record some guests. Fortunately it is easy:
- Select the guest table in the database window. Click Open or just use Enter.
Now the system shows the table in user mode ( Da- tasheet view ) so that you can enter guest data.
- Enter the guests shown on Figure 2.1C. You add a new guest in the empty line of the table - the one marked with a star. Notice that as soon you start entering something, the record indicator changes to a pencil and a new star line appears. The pencil shows that you are editing the record, and the record you see is not yet in the database.
On Figure 2.1C we originally entered a guest that got guestID 4, later deleted this guest. Access will never reuse number 4 for a guest.
Close and reopen the database To feel confident with Access, it is a good idea to close and open the database now.
- Close the large Access window. (Not the small database window inside the Access window.)
Notice that Access doesn't ask whether you want to save changes. Access saves them all along, for instance when you define a table or when you enter a record in the table.
- Find your database file ( hotel.mdb ) in the file fold- ers. Use Enter or double click to open it.
Access 2003 is very security concerned and asks you several questions when you open the file. The dialog may vary from one installation to another, but is something like this:
- The file may not be safe. Do you want to open it? Your database is safe, so answer Open.
- Unsafe expressions are not blocked. Do you want to block them? You want full freedom, so answer No.
- Access warns you one more time whether you want to open. Say Open or Yes. (In some versions the question is a very long text box, and you cannot understand it. Say yes anyway.)
As an alternative, you may say yes to blocking the unsafe expressions. This will save you some questions when you open the file in the future. However, some installations don't allow you to block expressions.
Note that Access 2003 shows that your database is in Access 2000 format. This is all right. It allows you to use it also from Access 2000. You can convert it to other formats with Tools -> Database Utilities -> Con- vert Database.
Undo. Use Esc to undo the changes you have made to the current record.
- The first Esc undoes changes to the field where the cursor is.
- The second Esc undoes all changes to the record where the cursor is.
As soon as you move the cursor to the next line, Access stores the record in the database and you cannot make an automatic undo anymore. However, you can manually edit the stored record. Notice that the pencil disappears when the record is stored in the database.
Shortcut keys for data entry F2: Toggles between selecting the entire field and se- lecting a data entry point. Shift+F2: Opens a small window with space for the entire field. Useful for entering long texts into a field that is shown only partly in the table. How- ever, the text cannot be longer than you specified in the table definition. Alt+ArrowDown: Opens a combo box. Choose with the arrows and Enter.
Shortcut keys for navigation Tab and Shift+Tab: Moves from field to field. Ctrl+Tab: Moves from one tab form to the next, for in- stance in the lower part of the table definition win- dow. F6: Moves between upper and lower section of a win- dow, for instance in the table definition window. Ctrl+Enter: Opens the table in design mode (in the da- tabase window).
See also shortcuts on the reference card
8 2. Creating a database
2.2 Create more tables
You should now create the remaining tables for the hotel. The data model on Figure 2.2 shows the tables we will use. To simplify your job, we have shown all the keys, including the foreign keys and the artificial keys.
- Close the guest table.
- Create all the remaining tables in the same way as you created the guest table (from the Tables tab use Create table in Design view - or click New ).
Make sure you define all the fields. Otherwise you will get stuck when later constructing the user interface. Here are a few notes about the various tables:
tblStay: stayID is the primary key of tblStay. Make it an Auto- Number. guestID is a foreign key that refers to the AutoNumber in tblGuest. The foreign key must have a matching data type - a long integer. Choose Data Type = Number and Field Size = Long Integer. Warning: Don't make the foreign key an AutoNumber. This would cause Access to fill in the foreign key fields automatically, and you cannot change the numbers so that they point to the primary keys in the guest table. paymethod is an enumeration type. Make it an integer (a 16-bit integer, not a long integer). Choose Data Type = Number and Field Size= Integer. We will use the value 1 to denote Cash, the value 2 to de- note Visa, etc. We will look closer at this in section 2.4. state must also be an enumeration type. Make it an in- teger. Here the value 1 will denote booked , 2 in , etc.
tblRoomType: Contains one record for each type of room, for instance one for double rooms, one for single rooms, etc. (In the book User Interface Design , we added this table late in the design process to illustrate the normalization con- cept.)
roomType is an artificial key. An AutoNumber is okay. description is a short text, for instance "double room, bath". bedCount is the number of beds in the room, including temporary beds. price1 and price2 are the standard price and a possible discount price. The price should be a decimal num- ber. Choose Data Type= Number, Field Size= Sin- gle , Decimal Places =2.
tblRoom: roomID is a natural key - the number on the door. So don't use an AutoNumber. Use an integer. roomType is a foreign key that refers to tblRoomType. (You should by know how to deal with it.)
tblRoomState: stayID and roomID are foreign keys. Ensure their types match what they refer to. Notice that roomID refers to a natural key, not to an AutoNumber. date should be a Date/Time field with Format = Short Date. personCount is the number of persons staying in the room. An integer should suffice. state is similar to state for tblStay, although the values are slightly different. The key consists of two fields: roomID and date. It is a bit tricky to specify this: select both fields by clicking on the left-hand marker (hold down Ctrl while selecting the second field). Then right-click somewhere on the text inside the line.
Optional tables
The following two tables are needed for the full sys- tem. However, you don't need to create them in order to follow the tutorial.
tblServiceType: serviceID is an artificial key. Should be an Auto- Number. name and price should be obvious. The price should be a decimal number. Choose Data Type= Number, Field Size= Single , Decimal Places =2.
tblServiceReceived: stayID and serviceID are foreign keys that refer to AutoNumbers. The foreign keys must thus be long integers. roomID is an optional reference to a room. An integer should suffice. (This reference is needed when a waiter records a service for a specific room and the guest has more than one room.) date should be a Date/Time field. Choose Format = Short Date. quantity is the number of items the guest has got - an integer should suffice.
Data types
Data is stored in the computer according to its type. Here is a description of the most important types in the data base. Visual Basic deals with almost the same types (see section 6.2 and the reference card under Declarations ).
Text. The field can contain any characters. The Field Size property defines the maximum number of charac- ters. The maximum cannot be above 255 characters.
Memo. Like a text field, but the maximum number of characters is 65,535. Access takes more time to process a memo field, so use text fields if adequate.
Number. The field can contain a number. The Field Size property defines what kind of number:
10 2. Creating a database
- Integer. A small integer. It must be in the range - 32,768 to +32,767 (a 16-bit integer).
- Long Integer. It must be in the range from around -2,140 million to +2,140 million (a 32-bit integer).
- Single. A decimal number in the range from -3.410 38 to +3.410^38 with an accuracy of 6 or 7 significant digits (a 32-bit floating point number).
- Double. A decimal number in the range from -1.810 308 to +1.810^308 with 14 significant digits (a 64-bit floating point number).
- Decimal. A very long integer with a decimal point placed somewhere. Intended for monetary calcula- tions where rounding must be strictly controlled. In the book we use Single or Double instead.
Numbers can be shown in many ways depending on the format property of the field. You may for instance show them with a fixed number of decimals, with a currency symbol, etc.
Some formats show data in a way that depends on the regional settings of the computer. If you for instance specify the format of a number as Currency , the number will show with a $ on a US computer and with a £ on a British computer.
Date/Time. The field gives a point in time. In the computer it is stored as the number of days since 30/12-1899 at 0:00. It is really a Double number, so the number of days may include a fraction of a day. In this way the field specifies the date as well as the time with high precision. As an example, the number 1 corre- sponds to 31/12-1899 at 0:00, the number 1.75 to 31/12-1899 at 18:00 (6 PM).
Usually we don't show a date field as a number, but as a date and/or a time. The format property specifies this.
Also here you can choose a format that adapts to the regional setting.
Yes/No. The field contains a Boolean value shown either as Yes/No, True/False, or On/Off. The format property specifies this.
AutoNumber. The field is a long integer (32 bits) that Access generates itself as a unique number in the table. Access numbers the records 1, 2,... as you enter the records. However, you cannot trust that the sequence is unbroken. For instance when you add a record and undo the addition before having completed it, Access uses the next number in the sequence anyway.
A foreign key is a field (or several fields) that refer to something unique in another table - usually the primary key. Be careful here. The foreign key and the primary key must have the same type. However, when the primary key is an AutoNumber, the foreign key must be a long integer.
Changing a data type. Access is quite liberal with changing a data type to something else - even if there are data in the records. It can also change an Auto- Number field to a number field, but not the other way around. If you need to change field B to an Auto- Number, create a new field C and make it an Auto- Number. Then delete field B and rename field C to B.
If you for some reason want to store a record with an AutoNumber of your own choice (for instance create a stay with stayID=728), you need to append the record with an INSERT query (see section 7.1). You cannot just type in the stayID.
Fig 2.2 Create remaining tables
tblStay
tblRoomState
tblRoom
tblServiceReceived tblServiceType
stayID, roomID, date, personCount, state (booked | occupied | repair)
guestID, name, address1, address2, address3, phone, passport
roomID, roomType
stayID, serviceID, serviceID, name, price roomID, date, quantity
tblGuest
stayID, guestID, paymethod (cash | visa ...), state (booked | in |out | canceled)
tblRoomType
roomType, description bedCount, price1, price
Optional tables
- Creating a database 11
Fig 2.3 Create relationships
Right-click in database window. Select Relationships
Choose tables to show in the ER diagram
Create a relationship: Drag 1-side field to m-side field (or opposite). Edit the relationship - Referential integrity!
Right-click in ER diagram. Select Show Table
Primary key
Foreign key
- Creating a database 13
2.4 Look-up fields, enumeration type
Your next task will be to fill in some data in all the ta- bles. However, some of the fields are cumbersome to fill in correctly. As an example, the pay method field is a code where 1 means Cash, 2 Visa, etc. The user should not have to remember these codes, so we will let the user choose the value from a list. It is an enumeration-type field:
paymethod(Cash | Visa |... )
Figure 2.4 shows what we want when the user fills in the paymethod field. We want the field to be a combo box where the user can select the mnemonic text while Access stores the number code. Here is how to do it:
- Open tblStay in design view. (Select it and click Design or use Ctrl+Enter).
- Select the paymethod field and the data type Lookup Wizard.
- Access asks whether you (as a user) want to select the values from a table or from a list of values that you (as a designer) type in. Choose to type them in. Then click Next.
- Access asks how many columns your combo box should have. Choose two and fill in the columns as shown on the figure. Then click Next.
- Access asks which column holds the value to store in the table. In our case it is column 1.
- Finally, Access asks for the column name that the user will see. In our case, paymethod is okay. Click Finish.
Fill in some stay records You are now going to create some stay records and connect them to a guest.
- Close the table design window and open it in user mode.
- Also open tblGuest in user mode. Keep the two tables side by side so you can see both. Make sure you have created some guests. Otherwise do it now.
- Fill in a stay record using the combo box for paymethod. Notice that what you see as a designer, is the number stored in the database. The user should not see the number, but the text. We can arrange for this when the field becomes a text box in the user window (see section 3.2.2).
- Also fill in the foreign key guestID so that it refers to one of the guests. Since there is referential integrity, Access won't let you store the stay record without a proper guestID. If you get into real trouble, use Esc twice (see the Panic box for the explanation).
- Fill in a few more stay records in the same way.
How the look-up field works Open tblStay in design mode and study the Lookup tab for paymethod (bottom of Figure 2.4). The display control property is Combo Box. It means that when the user is to fill in the paymethod, he sees a combo box.
- For ordinary fields Display Control is Text Box. A text box shows texts, numbers, etc. as a string of characters. If you want to change the field back to an ordinary field, just set Display Control to Text Box.
The values the user can choose between are listed in Row Source. You may edit the values here. Column Count shows that these values are to be displayed as two columns. Notice that Limit to List is No. It means that the user can enter other values than those in the list. In our case, it is not desirable, so set the property to Yes. Sections 3.1.6 and 3.1.7 explain more about combo boxes.
Undo the Lookup Wizard? How do you make the field an ordinary field rather than a lookup field? It doesn't help to make it an integer or a text. Choose the Lookup tab at the bottom of the table design window. Change Display Control to Text Box. (See bottom of Figure 2.4.)
Panic? Undo data entry When you enter data into the tables, Access checks against the rules you have defined for the tables and the relationships. For instance, when you enter the guestID in tblStay, this ID must correspond to a guest in the guest table. Access doesn't allow you to leave the record before this is fixed. The reason is that Access stores the record in the database as soon as you move the cursor away from the record. And the database must meet all the rules you have stated.
Sometimes you may not know what to type to satisfy Access, and on the other hand you cannot leave the re- cord to look at what to type. Many users panic here and even switch off the power to close down the system. The solution is to use Esc twice :
- First Esc: Undoes the correction you made in the field where the cursor is.
- Second Esc: Undoes all the changes you made to the record where the cursor is. This means that the database returns to a consistent state where all the rules are met.
14 2. Creating a database
2.5 Dealing with trees and networks
E/R models can neatly describe complex relationships, for instance as we saw it for the flight routes in User Interface Design. Figure 2.5 shows the E/R model, but Access cannot show such a model directly.
The problem is that Access identifies a relationship by means of the two tables it connects. This means that Access cannot have two connectors between the same two tables. Also you cannot have a self-referential con- nector. In the flight route model we need both of these.
As a compensation, Access offers shadow copies of a table. The table and its shadow copies are the same ta- ble, but they have different names. You can now create connectors to the shadow copies and thus indirectly create multiple connectors between the same two ta- bles.
Figure 2.5 shows how to handle the flight routes in Access by means of shadow copies.
- Create a new database, FlightRoutes. Create the tables City, Leg and Route in the usual way.
- Open the relationship window and add all three ta- bles to the relationship window. Then add City and Leg once more. The relationship window should now contain also a City_1 and a Leg_1 as shown on the figure.
- Drag the connectors as shown. You now have two connectors between City and Leg. One is deter- mined by City and the foreign key from. The other is determined by City_1 and the foreign key to. You also have a self-referential connector from Leg to itself. It is determined by Leg_1 and the foreign key next.
- Try to fill in data for AA331 according to the fig- ure. Note that there are only one City table and one Leg table to fill in. The shadow tables are not real tables.
16 2. Creating a database
Fig 2.5 Flight routes - shadow tables
Route
City^ Leg
next
from
to
routeID, mon, tue, wed, thu, fri, sat, sun
legID, route, next, from, to deptTime, arrTime,
cityID, name
Route: AA331. Mon, Wed
Arr Dep
Chicago 10:
Columbus 11:40 12:
Washington 13:30 14:
New York 15:
Relationship between Leg and Leg : Shadow copy of Leg****.
Two relationships between City and Leg : Shadow copy of City****.
Right-click -> Show table****. Show City once more.
A leg
- Creating a database 17
Fig 3.1A Create a Form - a user window
Create a Form
List of forms (empty initially)
The Forms (user windows) of the database
Wanted:
Tool-based
mockup
The Form
ControlsControlsControls
Property box for the Form.
Set grid size to 5 units per cm.
Toolbox: Click Text Box control. Draw a box on the Form.
Click for Form properties
- Access-based user interfaces 19
Using the fields You may wonder why Access writes unbound inside all the boxes. It means that the box is not bound to any record in the database. The user may enter something but it is not automatically stored in the database. Try this:
- Close the form. Access asks for its name. Call it frmFindStay. (We use the prefix frm for forms.)
- Open it again in user mode. It should now look like the bottom of Figure 3.1B. This is how the user would see the form.
- Try to enter something in the fields. It stays on the screen, but is it persistent data?
- Close the form and open it again (in user mode). All the fields are blank - no data was saved. It was just dialog data - not persistent data. Click the command button - nothing happens. It is just a mockup we have made. (In the next chapters we will add real data and functionality.)
3.1.2 Adjusting the controls
- Close the form and open it in design mode. Select one of the text boxes. Notice the two black han- dles, one on the label part and one on the text box.
- Moving and sizing. Point the mouse at the label handle. The cursor changes to a finger. Try to drag the label part around. The text box itself doesn't move. Point at the text box handle and use the fin- ger to drag it around.
- Point at the border of the text box. The cursor changes to a hand. Drag it - both label and text box should move.
- Point at one of the sizing handles in the corners or on the middle of a side. Drag here and the box changes size.
- Deleting a control. Click on the text box and click Delete. Oops - both box and label disappeared! Undo it using the Undo button or Ctrl+Z.
- Click on the label part. Notice that now the sizing handles are on the label part. Click Delete. The la- bel part disappears.
If you want a label without the text box, select the label tool from the toolbox and draw a label control.
If you want to add a label to a label-less text box, select some label, copy it (Ctrl+C), select the text box and paste the label (Ctrl+V).
- Moving and sizing with the keyboard. Select a control, then try moving it around with Ctrl+up, Ctrl+down, etc. Try moving it with Shift+up, etc. Now it changes size. This is one way to fine-tune the positions and sizes. There is no way to enlarge the picture as you can do in Word and many other programs.
- Select several controls at the same time. Either hold Shift down while clicking on the controls one by one, or drag a rectangle around them. (All con- trols touching the rectangle will be selected.) Now
try to move and resize the controls with the key- board, or drag them with the mouse.
- Undo. You can undo your last operation with the Undo button or Ctrl+Z. But only the last! You can undo all changes since you last opened the form by closing the form and saying No to saving the changes. Try it now - you don't want to save the last adjustments.
Make sure you see all menu items Access 2000 and 2003 have an annoying feature where it shows only the last menu items you have used. It makes it difficult to follow the procedures below. Get rid of this feature:
- Right-click anywhere in the menus. Select Cus- tomize->Options. Access 2000: Look at the checkbox "Menus show recently used commands first". Make sure that there is no check mark here. Access 2003: Look at the checkbox "Always show full menus". Make sure there is check mark here.
The Format menu and the grid Open the form in design mode and select a control. Now look at the Format menu at the top of the Access window. There are several things here that can help you design the form:
- Snap to Grid. If you check this box, all controls you draw or move with the mouse will snap to the grid in all four corners. If the grid points are closely spaced - more than 9 per cm - Access doesn't show the grid, but snaps to it anyway.
- Align. You can align the selected controls to the left, right, etc. or you can align them to the grid. Only their top-left point is aligned. They don't change size.
- Size. You can change the size of the selected con- trols so that they just fit the data in the control (matching the chosen font size). Or you can make them fit the grid in all four corners. Finally, you can give all the selected controls the same width or height.
- Change to ... You can transform the selected con- trol to another one - with reasonable limitations. For instance you can turn a text box into a combo box or vice versa.
3.1.3 Cleaning up the form
You may notice that the form has things in the corners that we don't want in the final user window (bottom of Figure 3.1B). The title bar, for instance, holds our pro- grammer-oriented form name, frmFindStay. It should be Find Guest. There are also record selectors, naviga- tion buttons, and space for a scroll bar that we don't need in the final window. We can correct all of this by setting properties of the form:
- Make sure the form is open in design mode. Show the property box for the form (double-click the lit- tle square where the rulers meet).
20 3. Access-based user interfaces
