Download Access PDF

TitleAccess
File Size1.7 MB
Total Pages154
Table of Contents
                            1. The hotel system
2. 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
3. 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
4. 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
5. 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
		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
                        
Document Text Contents
Page 1

Microsoft-Access Tutorial
Soren Lauesen

E-mail: [email protected]


Version 2.4a: August 2007



Contents
1. The hotel system................................................... 4
2. Creating a database ............................................. 6

2.1 Create a database in Access ............................. 6
2.2 Create more tables ......................................... 10
2.3 Create relationships ....................................... 12
2.4 Look-up fields, enumeration type .................. 14
2.5 Dealing with trees and networks.................... 16

3. Access-based user interfaces ............................. 18
3.1 Forms and simple controls ............................. 18

3.1.1 Text box, label and command button...... 18
3.1.2 Adjusting the controls............................. 20
3.1.3 Cleaning up the form .............................. 20
3.1.4 Shortcut keys for the user ....................... 22
3.1.5 Lines, checkbox, calendar....................... 22
3.1.6 Combo box - enumeration type .............. 24
3.1.7 Combo box - table look up ..................... 26
3.1.8 Control properties - text box................... 28

3.2 Subforms........................................................ 30
3.2.1 Subform in Datasheet view..................... 31
3.2.2 Adjust the subform ................................. 34
3.2.3 Mockup subform..................................... 36
3.2.4 Subform in Form view............................ 36
3.2.5 Summary of subforms............................. 38
3.2.6 Prefixes ................................................... 38

3.3 Bound, unbound and computed controls........ 40
3.3.1 Showing subform fields in the main form42
3.3.2 Variable colors - conditional formatting. 42

3.4 Tab controls and option groups...................... 44
3.5 Menus ............................................................ 46

3.5.1 Create a new menu bar............................ 46
3.5.2 Add commands to the menu list ............. 48
3.5.3 Attach the toolbar to a form.................... 48
3.5.4 Startup settings - hiding developer stuff . 48

3.6 Control tips, messages, mockup prints .......... 50
4. Queries - computed tables................................. 52

4.1 Query: join two tables.................................... 52
4.2 SQL and how it works ................................... 54
4.3 Outer join ....................................................... 56
4.4 Aggregate query - Group By.......................... 58
4.5 Query a query, handling null values .............. 62
4.6 Query with user criteria ................................. 64
4.7 Bound main form and subform...................... 66

4.7.1 Editing a GROUP BY query................... 67
5. Access through Visual Basic ............................. 68

5.1 The objects in Access .................................... 68
5.2 Event procedures (for text box) ..................... 72

5.2.1 More text box properties......................... 72

5.2.2 Computed SQL and live search...............74
5.2.3 Composite search criteria........................76
5.2.4 Event sequence for text box ....................78

5.3 Visual Basic tools...........................................80
5.4 Command buttons ..........................................84
5.5 Forms .............................................................86

5.5.1 Open, close, and events...........................86
5.5.2 CRUD control in Forms..........................87
5.5.3 The OpenForm parameters......................89
5.5.4 Multi-purpose forms (hotel system)........90
5.5.5 Dialog boxes (modal dialog)...................92
5.5.6 Controlling record selection....................93
5.5.7 Column order, column hidden, etc. .........94
5.5.8 Area selection, SelTop, etc......................94
5.5.9 Key preview ............................................97
5.5.10 Error preview ........................................97
5.5.11 Timer and loop breaking .......................98
5.5.12 Multiple form instances.........................99
5.5.13 Resize..................................................100

5.6 Record sets (DAO).......................................102
5.6.1 Programmed record updates..................102
5.6.2 How the record set works......................104
5.6.3 The bound record set in a Form ............106
5.6.4 Record set properties, survey ................108

5.7 Modules and menu functions .......................110
5.7.1 Create a menu function .........................110
5.7.2 Define the menu item............................112
5.7.3 Managing modules and class modules ..112
5.7.4 Global variables ....................................114

6. Visual Basic reference......................................116
6.1 Statements ....................................................116
6.2 Declarations .................................................120
6.3 Constants and addresses...............................122
6.4 Operators and conversion functions .............124
6.5 Other functions.............................................128
6.6 Display formats and regional settings ..........132

7. Access and SQL................................................134
7.1 Action queries - CRUD with SQL ...............134

7.1.1 Temporary table for editing ..................134
7.2 UNION query...............................................136
7.3 Subqueries (EXISTS, IN, ANY, ALL . . .) ..138
7.4 Multiple join and matrix presentation ..........140
7.5 Dynamic matrix presentation .......................142
7.6 Crosstab and matrix presentation .................144

8. References.........................................................148
Index......................................................................149

Page 2

2 Preface

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. 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.



© Soren Lauesen, 2007
Permission is granted to make paper copies of the file on a non-profit basis as long
as the source is clearly stated.

Page 77

5. Access through Visual Basic 77

Now we would get this fragment:
" AND arrival = #21-10-07#

Looks all right, but unfortunately, SQL expects dates in
the US format. In this case it would reject 21 as a non-
existing month, in other cases produce a wrong result.

Fortunately, the Access database engine can treat dates
as double numbers, but other databases may not. In
such cases you would have to generate the dates with
an explicit US-format, for instance in this way:
" AND arrival = #" & Format(txtArrival, "mm/dd/yyyy") & "#"

Empty texts
An empty field in the database has the value Null for
all types of fields. The same applies for the Value in an
empty text box. To test for an empty field , we would
ask for IsNull(f). In a VBA string variable, an empty
text is a zero-length text with the value "". Since
copyName is a string variable, we ask whether the text
is different from "".

Null values
Notice how the program deals with Other stays, i.e.
stays that are neither booked nor checked-in. These
stays include those with state > 2 and those without a

state (a NULL state). Stays with a NULL state are not
real stays, but records generated by the outer join for
guests that don't have a stay.

Initialize the form
A final touch is to make the Find Guest screen
initialize itself properly:

Let the three checkboxes have a mark initially.
Otherwise, the stay list will be empty initially and
the user may panic. To do this, set the initial value
of the boxes through their property sheet.

Let the Form's Load event procedure call the
search procedure in order to make the stay list
match the initial criteria:

Private Sub Form_Load()
Call search
End Sub

Try it out
You should try to make the solution work in practice. It
is a challenge, but fun. Make a copy of frmFindStay
and use it for the experiment.

Fig 5.2C Composite criteria

txtName

Dim copyName As String, copyStreet As String, copyPhone As String

Private Sub search()
Dim s As String

s = "SELECT * FROM qryStayList WHERE TRUE "
If copyName <> "" Then s = s & " AND name Like( '*" & copyName & "*' ) "
If copyStreet <> "" Then s = s & " AND address1 Like( '*" & copyStreet & "*' ) "
If copyPhone <> "" Then s = s & " AND phone Like( '*" & copyPhone & "*' ) "
if txtArrival > 0 Then s = s & " AND arrival = " & CDbl(txtArrival)
s = s & " AND (FALSE "
If chkBooked Then s = s & " OR state = 1 "
If chkCheckedIn Then s = s & " OR state = 2 "
If chkOther Then s = s & " OR state > 2 OR ISNULL(state) "
s = s & ");"
Me.subStayList.Form.RecordSource = s

End Sub

Private Sub txtName_Change()
copyName = txtName.Text
Call search

End Sub

Private Sub txtArrival_AfterUpdate()
Call search

End Sub

Private Sub chkBooked_AfterUpdate()
Call search

End Sub

frmFindStay3

txtStreet

txtPhone

chkBooked

chkCheckedIn

chkOther

Sub search doesn't know whether criteria
are in Value or Text. So they are always here.

SELECT * FROM qryStayList WHERE
TRUE AND name Like('*joh*') . . .

AND (FALSE OR state = 1 . . .

. . . );

txtArrival

Page 78

78 5. Access through Visual Basic

5.2.4 Event sequence for text box
We have now looked at two events for the text box,
Change and AfterUpdate. However, there are many
more, as you can tell from the Event tab in the property
box. (Select the event procedure and click F1 to get an
explanation of the event.) Figure 5.2D shows typical
event sequences for a text box. We will explain what
happens.

User clicks in the text box. First, the text box's Form
object may get a Current event. This happens if the
text box is in a record that wasn't selected before.

Next, the text box receives two events, Enter and
GotFocus. Enter signals that now the control is
active. Before calling the Enter event-procedure,
Access creates the Text property and sets it to the
current Value of the text box. GotFocus signals that
typing will now go to this control.

Then the textbox receives this series of events:
MouseDown, MouseUp, Click and maybe also a
DblClick.

User types an Ascii character. The text box receives
four events: KeyDown, KeyPress, Change, KeyUp
(plus mouse events if the mouse is used). KeyDown
occurs when the user depresses any key. The event
procedure has parameters that give details of the
physical key and other keys depressed at the same
time, for instance Shift and Ctrl. KeyPress occurs
when the key has generated an Ascii character, for
instance a letter, digit, space, tab or backspace. All
of these correspond to characters in the Ascii al-
phabet.

The Change event occurs when the visible content
of the control has changed, for instance that a char-
acter was added to the text or a character was de-
leted. Before calling this event procedure, Access
updates the Text property so that it contains what
the user sees.

The KeyUp event occurs when the user releases the
key. If the user keeps the key down to generate for
instance a whole sequence of x's, each x generates
KeyDown, KeyPress and Change. The KeyUp oc-
curs only when the user releases the key.

User types a non-Ascii character, for instance Arrow
left or F6. This generates only the KeyDown-event
(and the KeyUp). Nothing happens to Text or
Value, and no Change event is generated.

User types Delete. Delete is not an Ascii character, so
no KeyPress-event occurs. However, one or more
characters may be deleted, and then a Change event
occurs. KeyDown and KeyUp occur too.

User tabs to the next field. Access generates several
events. KeyDown occurs since the user pressed the

Tab key. BeforeUpdate shows that the user has fin-
ished the field, but it is not yet accepted. Before
calling the event procedure, Access has copied Text
to Value. BeforeUpdate may check the value, and
in case something is wrong reject the update.
Rejecting the update means that focus remains on
the text box and no AfterUpdate event is generated
yet. The user may edit the field or click Esc to set
the value back to its old value.

If it is an unbound control (not connected to a
database record), Access will also copy Text to
OldValue before calling the procedure. This is quite
illogical because OldValue is intended for letting
the program restore an erroneous field. This is not
possible for unbound controls.

Next comes the AfterUpdate-event. The value has
been checked and the procedure may act on it. In
the example above we used this opportunity to re-
compute the stay list. If it is a bound control, the
value will not yet be stored in the database, nor will
OldValue be changed. Storing the value in the
database doesn't happen until the user moves to
another record or explicitly saves the record with
Shift+Enter (or Records -> Save Record).

Next comes the Exit event. It signals that the field is
not active anymore. Finally, LostFocus occurs and
signals that typing will go to another control. When
both events have occurred, the Text property disap-
pears.

What about KeyUp? It happens in the next field,
which accordingly receives the KeyUp event.
Before that, the next field receives Enter and
GotFocus - in the same way as if the user clicked in
the next field.

What about errors in what the user has typed, for
instance an incorrect date? Access shows an error
message to the user instead of calling Before-
Update, and the cursor remains in the field. Can't
the program interfere before this error message?
Yes it can. The form receives a Form_Error event,
which may take action and cancel the error message
that Access was about to show (see section 5.5.10).

User clicks in next field. Access does almost the same
as when the user tabs to the next field. The only dif-
ference is that there is no KeyDown event.

User moves to next record. First the text box in focus
will receive the same events as if the user moved to
another control in the same record.

Next, the Form object receives three events.
BeforeUpdate signals that the record is about to be
saved and that the program may check that values
are correct and consistent with each other. The Be-

Page 153

Index 153

Q
queries, 52–67
- action, 134
- aggregate, 58

- in subform, 66
- alias (AS), 58
- ALL, 138
- all fields (*), 52
- ANY, 138
- average (AVG), 60
- Cartesian product, 54
- computed fields, 58
- computed SQL, 74, 76, 102
- correlated, 138
- COUNT, 60
- Crosstab, 144
- CRUD, 134
- DELETE, 134
- DISTINCT, 138
- dynaset, 52

- group by, 61, 134
- editing the data, 53, 134
- EXISTS, 138
- FIRST/LAST, 60
- grid, 52
- GROUP BY, 54, 58

- editing the data, 67, 134
- in subform, 66

- HAVING, 54, 60
- IN, 138
- INSERT, 134
- JOIN, 52, 54
- JOIN (multiple), 140
- live search, 74
- MIN/MAX, 60
- named (stored), 140, 142
- ORDER BY, 54, 62
- OUTER JOIN, 56
- SELECT, 54, 55
- SQL, 54
- SQL-engine, 55, 68
- StDev, 60
- subquery, 138
- SUM, 60
- UNION, 136
- UPDATE, 134
- user criteria in grid, 64
- Var, 60
- WHERE, 54
QueryDefs (VBA), 142
quick info (Visual Basic help), 82
quotes (nested "), 74

R
radio buttons (option group), 44
record
- saving, 8
- saving (VBA), 88
record selector (on form), 21
RecordCount (recordset), 109
recordset, 68, 102–9

- bound to form, 106
- clone, 104, 106
- CRUD control, 102
- DAO 3.6, 102
- properties, 104, 108
rectangle (on form), 22
reference card (VBA), 116
referential integrity, 12
regional settings, 132
relationships, 12
- referential integrity, 12
repair database, 15
Requery (recordset), 109
Resize (form event), 86, 100
role
- developer, 6, 132
- user, 6, 132
room grid (hotel system), 4, 94,

140
row source (in Combo Box), 26

S
save
- form, 22
- module (class), 110
- record, 8
- record (VBA), 88
- Visual Basic code, 81
Screen (VBA object), 110
Scroll Bars (on form), 21
Scroll Bars (on text box), 28
searching
- live search, 74
- many criteria, 76
- user criteria in grid, 64
security (when opening a

database), 8
SELECT (query), 54, 55
Select Case (VBA), 116
self-reference (in computed

expressions), 40
self-reference (in database), 16
SelTop, SelHeight, SelWidth,

SelLeft (datasheet, VBA), 94
SendKeys, 98
Set statement (VBA), 116
SetFocus (VBA), 86
shadow table, 16
shared procedure, 76
Shift (KeyCode, VBA), 97
shortcut keys
- data entry, 8
- for change of view, 22
- navigation, 8
- underlined letter on control, 22
shortcut menu (pop-up menu), 46
size unit (twips), 100
sizing and moving
- controls, 20
- forms, 100
sorting

- datasheet rows, 34
- records in query, 55, 62
Space function (VBA), 128
SQL. See also queries
- computed, 74, 76, 102
- how it works, 54
SQL engine (database), 55, 68
square brackets [name

parenthesis], 40, 54, 91
startup settings (menus etc.), 48
statements (VBA), 116
Static (declaration, VBA), 122
StDev (query), 60
stop program, 98
StrComp (VBA), 128
string functions (VBA), 128
strings ", 122
strings (nested), 74
strings, multi-line, Chr(10), 92
style (on form), 21
subform, 30–39
- columnar, 31
- datasheet versus form view, 38
- datasheet view, 31
- Form property, 71, 122
- form view, 36, 38
- Parent property, 97, 122
- properties, 38, 70
- subform control, 32
subquery (SQL), 138
subroutine (procedure, VBA),

120
SUM (query), 60

T
Tab Index (cursor movement), 28
tab sheet (control), 44
table
- as subform, 36
- creation, 7
- data entry, 8
- shadow copy, 16
Text (text box property), 72
text box, 18
- AfterUpdate (event), 72, 78
- BeforeUpdate (event), 78
- Change (event), 73, 78
- Control Source, 28
- ControlTip, 28
- Enabled, 28
- Enter (event), 78
- event sequence, 78
- events, 72
- Format, 28
- GotFocus (event), 78
- KeyDown (event), 78
- KeyPress (event), 78
- KeyUp (event), 78
- Locked, 28
- MouseDown, Click, etc., 78
- OldValue, 72

Page 154

154 Index

- properties, 28, 72
- Scroll Bars, 28
- Text (property), 72
- Text Align, 28
- Value, 72
text comparison (Like), 64, 124
text field (in table), 10
time/date field (in table), 11
time/date functions (VBA), 130
Timer event, 98
TimerInterval, 98
toolbars. See menus
toolbox (for drawing), 18
tools (Visual Basic), 80–83
Top (position property), 28, 100
tree structure (in database), 16
trim functions (VBA), 128
twips (size unit), 100
type check functions (VBA), 130
type conversion (VBA), 125
type declaration (VBA), 120
types of data. See data type

U
UBound (array bound, VBA),

128
Ucase function (VBA), 128
unbound controls, 20, 40
undo data entry, 8

undo drawing, 20
undo lookup-Wizard, 14
undo update (VBA), 79, 84
UNION (SQL), 136, 138
Unload (form event), 86
unsafe expressions (when

opening a database), 8
update (record), 8
UPDATE (record, SQL), 134
update (record, VBA), 88
Update (recordset), 109
user role, 6, 132
user windows (forms), 18

V
Value (text box), 72
Var (query), 60
variables (declarations, VBA),

120
variables (initial values), 120
variant data type (VBA), 120
VarType (VBA), 130
vbKey . . . (VBA), 97
view (of form)
- datasheet advantages, 38
- form view advantages, 38
- shortcut keys for changing, 22
Visual Basic, 68–115, 116–34
- breakpoints, 82

- debug command, 81
- help, 83
- object browser, 83
- objects in Access, 68–71
- pop-up help, 82
- project explorer, 80
- reference card, 116
- saving the code, 81
- tools, 80–83

W
wait for event, 98
week number (Format function),

126
WHERE (query), 54
While statements (VBA), 118
Width (size property), 28, 100
wildcarding (text comparison),

64, 124
windows (forms), 18
With-End (VBA), 123
Wizard
- form, 31
- lookup, 14

Y
yes/no field, 11

Similer Documents