Filetype PDF | Posted on 31 Jan 2023 | 2 years ago
Authentication
digital resources
162x Filetype PDF File size 2.12 MB Source: www.itu.dk
Microsoft-Access Tutorial
Soren Lauesen
E-mail: slauesen@itu.dk
Version 2.4b: July 2011
Contents
1. The hotel system................................................... 4 5.2.2 Computed SQL and live search...............74
2. Creating a database............................................. 6 5.2.3 Composite search criteria........................76
2.1 Create a database in Access.............................6 5.2.4 Event sequence for text box....................78
2.2 Create more tables ......................................... 10 5.3 Visual Basic tools...........................................80
2.3 Create relationships ....................................... 12 5.4 Command buttons..........................................84
2.4 Look-up fields, enumeration type..................14 5.5 Forms.............................................................86
2.5 Dealing with trees and networks....................16 5.5.1 Open, close, and events...........................86
3. Access-based user interfaces.............................18 5.5.2 CRUD control in Forms..........................87
3.1 Forms and simple controls.............................18 5.5.3 The OpenForm parameters......................89
3.1.1 Text box, label and command button......18 5.5.4 Multi-purpose forms (hotel system)........90
3.1.2 Adjusting the controls.............................20 5.5.5 Dialog boxes (modal dialog)...................92
3.1.3 Cleaning up the form..............................20 5.5.6 Controlling record selection....................93
3.1.4 Shortcut keys for the user .......................22 5.5.7 Column order, column hidden, etc..........94
3.1.5 Lines, checkbox, calendar.......................22 5.5.8 Area selection, SelTop, etc......................94
3.1.6 Combo box - enumeration type ..............24 5.5.9 Key preview............................................97
3.1.7 Combo box - table look up .....................26 5.5.10 Error preview........................................97
3.1.8 Control properties - text box...................28 5.5.11 Timer and loop breaking.......................98
3.2 Subforms........................................................ 30 5.5.12 Multiple form instances.........................99
3.2.1 Subform in Datasheet view.....................31 5.5.13 Resize..................................................100
3.2.2 Adjust the subform ................................. 34 5.6 Record sets (DAO).......................................102
3.2.3 Mockup subform.....................................36 5.6.1 Programmed record updates..................102
3.2.4 Subform in Form view............................36 5.6.2 How the record set works......................104
3.2.5 Summary of subforms.............................38 5.6.3 The bound record set in a Form ............106
3.2.6 Prefixes................................................... 38 5.6.4 Record set properties, survey................108
3.3 Bound, unbound and computed controls........40 5.7 Modules and menu functions.......................110
3.3.1 Showing subform fields in the main form42 5.7.1 Create a menu function.........................110
3.3.2 Variable colors - conditional formatting.42 5.7.2 Define the menu item............................112
3.4 Tab controls and option groups......................44 5.7.3 Managing modules and class modules..112
3.5 Menus............................................................ 46 5.7.4 Global variables....................................114
3.5.1 Create a new menu bar............................46 6. Visual Basic reference......................................116
3.5.2 Add commands to the menu list .............48 6.1 Statements....................................................116
3.5.3 Attach the toolbar to a form....................48 6.2 Declarations .................................................120
3.5.4 Startup settings - hiding developer stuff.48 6.3 Constants and addresses...............................122
3.6 Control tips, messages, mockup prints ..........50 6.4 Operators and conversion functions.............124
4. Queries - computed tables.................................52 6.5 Other functions.............................................128
4.1 Query: join two tables.................................... 52 6.6 Display formats and regional settings..........132
4.2 SQL and how it works...................................54 7. Access and SQL................................................134
4.3 Outer join....................................................... 56 7.1 Action queries - CRUD with SQL...............134
4.4 Aggregate query - Group By..........................58 7.1.1 Temporary table for editing ..................134
4.5 Query a query, handling null values..............62 7.2 UNION query...............................................136
4.6 Query with user criteria ................................. 64 7.3 Subqueries (EXISTS, IN, ANY, ALL . . .)..138
4.7 Bound main form and subform......................66 7.4 Multiple join and matrix presentation..........140
4.7.1 Editing a GROUP BY query...................67 7.5 Dynamic matrix presentation.......................142
5. Access through Visual Basic.............................68 7.6 Crosstab and matrix presentation.................144
5.1 The objects in Access .................................... 68 8. References.........................................................148
5.2 Event procedures (for text box).....................72 Index......................................................................149
5.2.1 More text box properties.........................72
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
Preface
This booklet shows how to construct a complex appli- 6. Visual Basic reference. A reference guide to the
cation in Microsoft Access (MS-Access). We assume Visual Basic language for Applications (VBA).
that the user interface has been designed already as a 7. Access and SQL. An overview of the remaining
paper-based mockup (a prototype). How to design a
good user interface is a separate story explained in parts of SQL, for instance how to update the
User Interface Design - a Software Engineering Per- database through SQL. We also explain how to
spective, by Soren Lauesen. generate matrices of data with dynamically chan-
ging headings.
After design, development continues with constructing
the database, constructing the user interface, binding Using the booklet for teaching
the user interface to the database, and finally develop We have experimented with using the booklet for
the program. This is what this booklet is about. teaching. First we tried to present part of the material
with a projector, then let the students try it out on their
The reason we illustrate the construction process with own, next present some more, etc. Although the
MS-Access is that it is a widely available tool. Any- students listened carefully, it turned out to be a waste
body who has Microsoft Office with MS-Word, also of time, partly because the students worked with vastly
has Access and the programming language Visual Ba- different pace.
sic behind Access.
Now we give a 15 minute introduction to the main
MS-Access is also a good illustration of many princi- parts of Access: the database window, the tables, the
ples that exist on other platforms too, for instance a re- forms - and how they relate to what they have learned
lational database, a Graphical User Interface (GUI), in user interface design. Then the students work on
event handling, and an object-oriented programming their own. We have instructors to help them out when
language. MS-Access contains all of these parts - co- they get stuck.
operating reasonably smoothly.
The hotel system
Organization of the booklet We have chosen to illustrate the construction process
The chapters in the booklet are organized like this: with a hotel example, because most people have an
1. An introduction to the hotel system that is used as idea what it is about, yet it is sufficiently complex to
an example throughout the booklet. show typical solutions in larger systems. Some of the
complexities are that a hotel has many types of rooms
2. Creating a database. Construct a database that cor- at different prices; a guest can book several rooms,
responds to the data model behind the design. The maybe in overlapping periods; a room may need reno-
user will only see the database indirectly - through vation or repair, making it unavailable for a period; the
the screens we construct. hotel keeps track of regular guests and their visits over
time.
3. Access-based user interfaces. Construct the screens Simplifications
and menus that the user will see. We follow the pa- However, we have simplified the system in many other
per-based mockup designed in User Interface De- ways to shorten the discussion. For instance we ignore
sign. You can use the result as a tool-based that in most hotels, rooms are not booked by room
mockup. number, but by room type; hotels usually overbook, i.e.
4. Queries - computed tables. Connect the screens to book more rooms than they have, expecting that some
the database, usually by means of queries - com- customers will not turn up. We also ignore all the other
puted data tables. The result will be a partially aspects of operating a hotel, for instance keeping track
functional prototype. of when rooms are cleaned and ready for the next
guest, purchasing goods, planning who is to be on duty
5. Access through Visual Basic. Program what the for the next weeks, payroll and general accounting. In
buttons and menus will do when the user activates spite of these simplifications, the example still shows
them. The result will be a fully functional prototype the structure of larger systems.
and later the final system to be delivered to the On-line resources
customer. The first part of the chapter is tutorial - A demo-version of the hotel system, a VBA reference
mandatory reading if you want to work with Visual card, etc. are available from the authors's web site:
Basic and Access. The rest of the chapter is for Comments are welcome.
looking up various subjects. We assume you know www.itu.dk/people/slauesen.
a bit of programming already. Soren Lauesen, slauesen@itu.dk
Preface
3
1. The hotel system
In this booklet we illustrate MS-Access by means of a record services that the guest has received. The system
system for supporting a hotel reception. The system is uses the term Stay to mean a booking or a guest who
used as the main example in User Interface Design - a has checked in.
Software Engineering Perspective, by Soren Lauesen.
If you know the book, skip this section and go straight Breakfast list. The Breakfast screen shows the break-
to Chapter 2. fast servings for a specific date. It handles just two
kinds of breakfast: self-service breakfast in the restau-
Screens rant (buffet) and breakfast served in the room. The
The hotel system consists of the screens shown in Fig- waiter in the restaurant has a paper copy of the list and
ure 1A. records the servings here. Later the receptionist enters
the data through the Breakfast screen.
Find guest. The Find guest screen allows the recep- Service list. The Service list shows the price for each
tionist to find a guest or a booking in the database. The kind of service. Hotel management uses this list to
receptionist may enter part of the guest name and click change service prices or add new kinds of service.
the Find guest button. The system then updates the
lower part of the screen to show the guests or bookings Database
that match. The receptionist may also find the guest by The system uses a database with several tables. They
his phone number, room number, or stay number (also are shown as an E/R data model on Figure 1B.
called booking number).
The receptionist can select the guest from the list and tblGuest has a record for each guest with his address
click the buttons to see details of the booking or create and phone number.
a new booking for the guest. tblStay has a record for each stay (booking or checked
Room Selection. The Room Selection screen gives an in) with a booking number (stay number) and the pay
overview of available rooms in a certain period. Avail- method.
ability is shown as IN when the room is occupied, tblRoom has a record for each room in the hotel.
BOO when it is booked, etc. The receptionist may
specify the period of interest and the type of room, then tblRoomState has a record for each date where a room
click the Find room button. The system updates the ta- is occupied. It connects to the room occupied and the
ble at the bottom of the screen to show the rooms of stay that occupies it. If the room is occupied for repair,
interest. The receptionist can then choose a room and it doesn’t connect to a stay.
book it for the guest – or check a guest into the room.
Stay. The Stay screen shows all the details of a book- tblRoomType has a record for each type of room (room
ing, for instance the guest and his address, the rooms class) with a short description of the room type, the
he has booked and the prices. When the guest is number of beds, and the prices.
checked in, the Stay screen also shows breakfast and tblService has a record for each type of service with its
other services he has received. The system shows these name and price per unit.
details on the Services tab. Here the receptionist can
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-
Fig 1B. Tables as E/R model voice for each stay).
tblGuest
tblStay tblServiceReceived tblServiceType
tblRoomState
tblRoom tblRoomType
4 1. The hotel system
no reviews yet
Please Login to review.
