SETEC University
Management Information System
Table of Contents Chapter 1 ............................................................................................................................................. 1 Introduction to MS-Access 2007......................................................................................................... 1 1.1. របបៀបបបើកកម្មវ ិធី MS-Access 2007: ................................................................................... 1 1.2.
របបៀបបិទកម្មវ ិធី MS-Access 2007 ...................................................................................... 3
1.3.
របបៀបបប្កើត Database ថ្មី ..................................................................................................... 3
1.4. របបៀបបបើក Database ដែលមានស្រាប់ ................................................................................ 5 Chapter 2 ............................................................................................................................................. 6 Creating and Using Table .................................................................................................................... 6 2.1. របបៀបបប្កើត Table ថ្មីែំបូ្.................................................................................................. 6 2.2.
របបៀបបប្កើត Table ថ្មីបដនែម្ ................................................................................................ 7
2.3.
របបៀបបប្កើត Table ថ្មីបដនែម្ បោយប្បើ Table Templates................................................... 7
2.4.
របបៀបបដនែម្ Field/Column ចូ លកនុ្ Table ......................................................................... 7
2.5. 2.6. 2.7. 2.8. 2.9.
របបៀបបដនែម្ Field/Column ចូ លកនុ្ Table បោយប្បើ Field Templates ............................ 7 របបៀបបដនែម្ Field/Column ពី Table បផេ្បទៀតកនុ្ Datasheet View: ............................... 8 របបៀបលប់ Field/Column បចញពី Table កនុ្ Datasheet View ............................................ 8 របបៀបបប្កើត Table កនុ្ Design View.................................................................................. 9 Primary Key: ...................................................................................................................... 14
2.10. Import Table: ...................................................................................................................... 14 2.11. Export Tables ..................................................................................................................... 21 2.12. ភ្ជាប់បៅកាន់ Table (Link Table): ...................................................................................... 22 2.13. បប្កើត Lookup Wizard ....................................................................................................... 24 2.14. Relationships: ..................................................................................................................... 30 2.15. ការកំនត់ោក់ Password: .................................................................................................... 33 Chapter 3 ........................................................................................................................................... 36 Creating and Using Query ................................................................................................................. 36 3.1. បប្កើត Select Query បោយប្បើ wizard: ............................................................................. 37 3.2.
របបៀប Run query: .............................................................................................................. 39
3.3.
បប្កើត Select Query កនុ្ Design View:. ............................................................................ 40
3.4.
ការ Backup Database ទុក: ................................................................................................. 50
3.5.
បប្កើត Delete Query: .......................................................................................................... 51
3.6.
បប្កើត Update Query: ......................................................................................................... 53
3.7.
បប្កើត Append Query:........................................................................................................ 55
3.8.
បប្កើត Make-table Query: .................................................................................................. 57
3.9.
បប្កើត Parameter Query: .................................................................................................... 60
3.10. បប្កើត Crosstab Query: ...................................................................................................... 62 3.11. Structure Query Language (SQL): ..................................................................................... 67 Table of Contents
I
Management Information System
SETEC University
Chapter 4 ........................................................................................................................................... 71 Creating and Using Form .................................................................................................................. 71 4.1. បប្កើត Form បោយប្បើ Form tool: .................................................................................... 71 4.2.
របបៀប View form:.............................................................................................................. 72
4.3.
បប្កើត Form បោយប្បើ Split Form tool ............................................................................ 74
4.4.
បប្កើត Form បោយប្បើ Multiple Items tool: ..................................................................... 76
4.5.
បប្កើត Form បោយប្បើ Wizard ......................................................................................... 77
4.6.
បប្កើត form បោយប្បើ Blank Form tool: .......................................................................... 79
4.7.
ការរ ំកិល control បលើ form: ............................................................................................... 81
4.8.
ការប្បើ្ាស់ form : .......................................................................................................... 82
4.9. បប្កើត Main form និ្ Subform ......................................................................................... 83 4.10. Visual Basic for Application(VBA): .................................................................................. 89 4.11. Introduction to Variables: .................................................................................................. 92 4.12. Data Type: .......................................................................................................................... 93 4.13. Introduction to Procedures: .............................................................................................. 100 4.14. Conditional Statements .................................................................................................... 106 4.15. Counting and Looping Structures: ................................................................................... 107 4.16. Data Access Objects(DAO) ............................................................................................. 109 Chapter 5 ......................................................................................................................................... 114 Creating and Using Report .............................................................................................................. 114 5.1. បប្កើត Report បោយប្បើ Report tool: ............................................................................. 114
II
5.2.
របបៀប View Report.......................................................................................................... 114
5.3.
បប្កើត Report បោយប្បើ Report Wizard ......................................................................... 117
5.4.
បប្កើត Label បោយប្បើ Label Wizard ............................................................................ 121
5.5.
បប្កើត Report បោយប្បើ Blank Report tool: ................................................................... 124
5.6.
អំពី Report Sections: ........................................................................................................ 125
5.7. 5.8.
បប្កើត Grouped Report ថ្មី បោយប្បើ Report Wizard: ..................................................... 126 Homeworks ...................................................................................................................... 132
Table of Contents
SETEC University
Management Information System
Chapter 1 Introduction to MS-Access 2007 Introduction Microsoft Access 2007
Computer Application
desktop computer
computer network ។
។ MS-Access 2007 -
Table:
-
Query:
-
Form:
-
Report:
៖
(Store) Tables Table ជាដ ើម
1.1.
MS-Access 2007: -
១:
Start (All) Program Microsoft Office Microsoft Office
Access 2007 ។ -
២: File
-
Directory : C:\Program Files\Microsoft Office\Office12 msaccess.exe
៣:
Double Click
Start Run
Open:
។ msaccess
Enter
OK ។
Chapter 1: Introduction to MS-Access 2007
Page 1 of 140
Management Information System
SETEC University
MS-Access 2007
-
Title bar: Button
Office Quick Access Toolbar
Quick Access Toolbar
។ Title bar
Customized Button Office Button
-
Office Button:
Button
(Save)
Database -
។
Quick Access Toolbar: Save, Undo Toolbar
Redo
Icons ។
Customized Button
Quick Access Icons
។ Page 2 of 140
Chapter 1: Introduction to MS-Access 2007
SETEC University
-
Management Information System
The Work Area:
MS-Access 2007
: ១ : Template Categories:
(Categori s
(Template)
។ ២:
: -
(New Blank Database)
-
។
(Featured Online Templates): (Template)
Database
៣: 1.2.
Database
Microsoft ។ Databases
។
MS-Access 2007 -
Office Button : :
1.3.
Close Button
Exit Access Title bar
Alt + F4 Datab s
-
Office Button :
១ (Template Categories)
Featuring -
Blank Database
File Name:
-
New
Database
Folder Save as type
Microsoft Access 2007 Database(*.accdb)
Create ។
Chapter 1: Introduction to MS-Access 2007
Page 3 of 140
Management Information System
Program Window
SETEC University
MS-Access 2007
Ribbon
Tabbed Documents
Navigation Pane
View Button
Status bar -
The Ribbon:
Program Window
Commands
។
-
Navigation Pane:
objects
Database ។ -
Tabbed Documents:
Tables, Queries, Forms, Reports
Macro
Tab ។
Page 4 of 140
Chapter 1: Introduction to MS-Access 2007
SETEC University
-
Management Information System
Status bar:
Program Window Buttons
1.4.
View ។
Databas -
Office Button Open :
-
៣ (Open recent document):
Browse
Database Double-Click
Database
Open ។
***End of Chaptert 1***
Chapter 1: Introduction to MS-Access 2007
Page 5 of 140
Management Information System
SETEC University
Chapter 2 Creating and Using Table អ្វដី ៅជា Table? Table
object
MS-Access ។
2.1.
T 1.
l
Database
2.
Program Window
:
Field/Column Headers
Row/Record Headers Tabbed document
3.
Tabbed document, double-click : Right-click
4.
Field/Column Headers Column Field
5. Page 6 of 140
Save
Field/Column Headers
…
Rename column
Tab Enter
Table
OK Chapter 2: Creating and Using Table
SETEC University
Management Information System
2.2.
T
l
1.
Ribbon,
Create
2.
Tabbed document, double-click : Right-click
3.
Icon: Table
Field/Column Headers Column Field
4.
…
Save
2.3.
T 1.
Ribbon,
Field/Column Headers Rename column
Tab Enter
Table
l
OK
Table Templates Create
Icon: Table Templates
Templates 2.
Save
2.4.
Fi l
Table olumn
OK
Table
១: 1.
Table
2. Right-click 3.
Datasheet View Field/Column Header
Column
Insert Column ២:
1. 2.
Field/Column Header Ribbon,
2.5.
Fi l
Column
Datasheet olumn
T
Fields & Column, l
Icon: Insert
Field
Templates 1. 2.
Table
Datasheet View
Field/Column Header Column
Chapter 2: Creating and Using Table
Page 7 of 140
Management Information System
3.
Ribbon,
4.
Field Templates,
SETEC University
Datasheet
Fields & Column,
Icon: New Field
Fields
Table ។ 2.6.
Fi l 1.
Table
2.
olumn
T
l
Datasheet View:
Datasheet View
Field/Column Header Column
3.
Ribbon,
Datasheet
Fields & Column,
Icon: Add Existing Field 4.
Field List,
Fields Table
2.7.
Fi l
olumn
T
l
Datasheet View
១: 1.
Table
2. Right-click 3.
Datasheet View Field/Column Header
Field
Delete Column
YES
២: 1. 2. 3.
Table
Datasheet View
Field/Column Header Ribbon,
Field
Datasheet
Fields & Column,
Icon: Delete
YES
Page 8 of 140
Chapter 2: Creating and Using Table
SETEC University
2.8.
Management Information System
T 1.
l
Design View
Ribbon,
Create
Icon: Table Design
2. Field Name:
Field
3. Data Type:
user
-
Text:
-
Memo:
Field
:
២៥៥ ៦៣
support Rich Text Formatting E.g:
Table
។
។
Property: Text Format = Rich Text)
-
Number:
-
Date/Time:
-
Currency:
Chapter 2: Creating and Using Table
១
Page 9 of 140
Management Information System
-
SETEC University
AutoNumber:
user
record -
Yes/No:
user
(Yes/No,
True/False, or On/Off) -
OLE Object: Object
(Files)
word, excel, graphics,
sound -
Hyperlink: Web Page
-
Text
(Path/URL)
Website
Attachment:
(attach)
images, word, excel records
-
Lookup Wizard: List
Table
4. Description: 5. Field Properties:
Page 10 of 140
File,
Field
Database user
List box
Combo box
Field
Status bar Fields
Chapter 2: Creating and Using Table
SETEC University
Management Information System
Field Properties Data Type Text
Data Type Field Properties
-
Field Size:
-
Format:
user
Field Datasheet View
>: <: @[color]: E.g : @[red]/[black]/[blue]/[green]/[cyan]/[magenta]/[yellow]/[white] -
Input Mask:
: + -
0 (zero): 9 (nine): +
-។ +
#: -។
: Space
user
។
A
L:
A
? (Question mark): A: a: &:
sp
C:
sp
<: >: - Caption:
Label
Column Heading
Datasheet view
- Validation Rule: - Validation Text: user
Chapter 2: Creating and Using Table
Form Field
Text
Message box Validation Rule
Page 11 of 140
Management Information System
Data Type
SETEC University
Field Properties - Required: - o
Field
- Yes: -
Field
Allow Zero Length: - No: - Yes:
String
Null string
String
Null string
- Indexed: - No: The field is not indexed (default).
Number
index
- Yes(Duplicates OK):
index
Field
user
- Yes(No Duplicates):
index
Field
user
Field
- Field Size: -
Byte: 1 byte of memory or disk space, holds from 0 to 255
-
Integer: 2 bytes of storage, holds from -32,768 to 32,767
-
Long Integer: 4 bytes of storage, holds from -2,147,483,648 to 2,147,483,647
-
Single: 4 bytes of storage, holds from -3.402823x1038 to 3.402823x1038
-
Double: 8 bytes of storage, holds from -1.79769313486232x10308 to 1.79769313486232x10308
- Format:
Date/Time
Page 12 of 140
-
000: to put 00 left number E.g.: 001
-
#,##0.00R: format Riel E.g.: 10,000.00R
- Format: -
d: Display the day of the month as one or two digits.
-
dd: Display the day of the month using two digits. Chapter 2: Creating and Using Table
SETEC University
Management Information System
Data Type
Field Properties -
ddd: Display the first three letters of the day of the week.
-
dddd: Display the full name of the day of the week.
-
m: Display the month of the year as one or two digits.
-
mm: Display the month of the year using two digits.
-
mmm: Use the first three characters of the month.
-
mmmm: Use the full name of the month.
-
yy: Use the last two digits of the year.
-
yyyy: Use the full year.
-
h: Display the hour using one or two digits.
-
hh: Display the hour using two digits.
-
n: Display the minute using one or two digits.
-
nn: Display the minute using two digits.
-
s: Display the second using one or two digits.
-
ss: Display the second using two digits.
- Input Mask: -
Date: 00/00/00
-
Time: 00:00:00
E.g. 1 : Input mask
Sample values
(000) 000-0000
(206) 555-0248
(999) 999-9999
(206) 555-0248 ( ) 555-0248
(000) AAA-AAAA
(206) 555-TELE
#999
-20 2000
>L????L?000L0 Chapter 2: Creating and Using Table
GREENGR339M3, MAY R 452B7 Page 13 of 140
Management Information System
SETEC University
>L0L 0L0
T2F 8M4
00000-9999
9811598115-3007
>L
Maria Brendan
SSN 000-00-0000
SSN 555-55-5555
>LL00000-0000
DB51392-0493
E.g. 2: ValidationRule Property
ValidationText Property
<> 0
Entry must be a nonzero value.
> 1000 Or Is Null
Entry must be blank or greater than 1000.
Lik “A????"
Entry must be 5 characters and begin with the letter "A".
2.9. Primary Key: Primary Key
Field
(Records)
Table ។ Field
1.
Table
Primary Key: Design View
Field
Primary
Key 2.
Ribbon,
Tab: Design
Primary Key
1.
Table
2.
Ribbon,
Icon: Primary Key Field:
Design View Tab: Design
Field
Primary Key
Icon: Primary Key
2.10.Import Table:
Import Table
1.
Page 14 of 140
Database
Database Import Table
Chapter 2: Creating and Using Table
SETEC University
2.
Management Information System
Ribbon, Window
3. 4.
t
“Ext rn l D t ” Import: Access :
Browse
Database “Import T
l s, qu ri s, forms, …”
Window
Chapter 2: Creating and Using Table
Tables OK
:
Page 15 of 140
Management Information System
5.
“Import O j
SETEC University
t”,
tab: Tables
Import
6. 1. 2.
OK
“S v import st ps” Import Table
File
Excel
Page 16 of 140
Close
MS-Excel
Database Ribbon,
Tables
Import Table t
“Ext rn l D t ” Import: Window
:
Chapter 2: Creating and Using Table
SETEC University
Management Information System
Browse 3.
File MS-Excel “Import th sour
Window “Import Spr
sh
Chapter 2: Creating and Using Table
Tables
t …”
t Wiz r ”
OK :
Page 17 of 140
Management Information System
4.
“Show Worksh Import
Page 18 of 140
SETEC University
ts” Next
List window
sheet :
Chapter 2: Creating and Using Table
SETEC University
Management Information System
5.
“First Row Column heading
6.
ont ins
olumn H
Next
Field
ing” window
row ១ :
:
Field Name:
Field
Data Type: Indexed:
Index “Do not import fi l
Skip ”
Import
Field 7.
Next
window
Chapter 2: Creating and Using Table
:
Page 19 of 140
Management Information System
8.
SETEC University
: Let Access add primary key:
Access
Choose my own primary key: No primary key: 9.
Page 20 of 140
Next
primary key Field
primary key
primary key window
:
Chapter 2: Creating and Using Table
SETEC University
Management Information System
10.
Table
11.
Import
“Import to T
l ”
Finish
Close
2.11. Export Tables 1.
Export table
File MS-Excel:
Navigation bar,
table
Export 2.
Ribbon,
t
“Ext rn l D t ” Export:
Excel
Chapter 2: Creating and Using Table
Window
:
Page 21 of 140
Management Information System
3.
SETEC University
Browse options
Export table
:
Export data with formatting and layout: Export format
layout
Open the destination file after the export operation is complete: export
export
Export only the selected records: Export 4.
OK
2.12.
file
records
Close
Table (Link Table):
Link
1. 2.
Table
Database
Database Ribbon, Import,
Page 22 of 140
:
Link table tab: External Data Icon: Access
Window
:
Chapter 2: Creating and Using Table
SETEC University
3.
Management Information System
Browse
4.
Database “Link to th
Window
5.
t sour
…”
Link tables OK
:
“Link T
l s”,
Tables
Link
OK Chapter 2: Creating and Using Table
Page 23 of 140
Management Information System
2.13.
SETEC University
Lookup Wizard Lookup Wizard
1.
table
2.
:
Design View Data type: Lookup Wizard
Field
Lookup
Wizard (E.g.: Sex)
3.
Page 24 of 140
win ow “Lookup Wiz r ”
:
Chapter 2: Creating and Using Table
SETEC University
Management Information System
4.
“I will typ in th v lu s th t I w nt” window
:
Number of columns: 5.
6.
columns
Col1,
Next
7.
Next
Lookup column (E.g.:
window
lookup column
:
Finish
8. Save table
Chapter 2: Creating and Using Table
Page 25 of 140
Management Information System
SETEC University
Lookup Wizard
1.
table
2.
Tables/Queries
Design View Data type: Lookup Wizard
Wizard
3.
Field
win ow “Lookup Wiz r ”
“I w nt th lookup olumn to look up th v lu s in qu ry”
Page 26 of 140
:
Lookup :
t
l or
Next
Chapter 2: Creating and Using Table
SETEC University
Management Information System
4.
table column
5.
query
Next
Fields
“Av il
l Fi l s”
Lookup column
6.
Lookup
Field
Next
sort order (Ascending/Descending)
Fields
Next
Chapter 2: Creating and Using Table
Page 27 of 140
Management Information System
7.
SETEC University
column
mouse “Hi
(drag)
k y olumn r
omm n
column ”
key column 8.
Next
9.
10.
lookup column
+ Yes: + No:
Page 28 of 140
save table
Finish
relationships
relationships Chapter 2: Creating and Using Table
SETEC University
Management Information System
11. Save table
lookup column
1.
table
2.
Field
Design View
Field Properties, ontrol”,
Field
tab: Lookup
lookup column
“Displ y
Text Box
3. Save table
Chapter 2: Creating and Using Table
Page 29 of 140
Management Information System
SETEC University
2.14. Relationships: Relationships:
1.
Ribbon, Show/Hide,
tab: Database Tools Icon: Relationships Relation tab
2.
:
tab: Design
Icon: Show Tables
: Right-click
Relationship Window “Show T
Page 30 of 140
l ”
Show Tables :
Chapter 2: Creating and Using Table
SETEC University
3.
Management Information System
tab: Tables
tables
relationship
Add 4.
Close
5.
Primary key Relationships
Chapter 2: Creating and Using Table
(drag)
Foreign key
Edit
:
Page 31 of 140
Management Information System
6.
SETEC University
Create
7.
tab: Design
Relationship:
1.
tab: Design
Icon: Close
Relationship tab
Icon: Relationship
Relationship tie 2. Double-click : Right-click
Page 32 of 140
Relationship tie Relationship tie
“E it R l tionship…”
Chapter 2: Creating and Using Table
SETEC University
3.
Management Information System
Relationship
:
Enforce Referential Integrity: Relationship
Tables ។
Cascade Update Related Fields:
rim ry
Foreign Key
y
។
Cascade Delete Related Records: Primary table
Foreign table ។
4.
1. 2.
OK
Relationship: tab: Design
Icon: Relationship
Relationship tie : Right-click
3. 2.15.
key: Delete
Relationship tie
Delete
Yes Password:
1.
Password database
Database:
Exclusive Mode:
Chapter 2: Creating and Using Table
Page 33 of 140
Management Information System
-
Office Button
-
SETEC University
Open
Database Password Open
button: “Op n
Ex lusiv ”
Page 34 of 140
Chapter 2: Creating and Using Table
SETEC University
2.
Management Information System
Ribbon,
tab: Database Tools
Database Tools,
Icon:
Encrypt with Password 3.
Password: Password: Verify:
4. 1. 2.
Password Password
OK Password database Ribbon,
Exclusive Mode tab: Database Tools
Database Tools,
3.
Database
Icon: Decrypt Database
Password
Chapter 2: Creating and Using Table
OK Page 35 of 140
Management Information System
SETEC University
Chapter 3 Creating and Using Query I.
Query?:
Query
object
MS-Access table
tables
។
Query ។ Query
Form, Report Access Page Query
Data
។ MS-Access
1. Select
:
Query:
u ry
table ។ Select Query : Sum, Count, Average, Max 2. Action Query:
។
Query
t ៤
in
l s
។ Action Query
:
-
Delete Query :
-
Update Query :
table table
។
-
Append Query :
table
table
។
-
Make-table Query:
table
t
l
។ 3. Parameter Query:
Query ។
4. Crosstab Query:
Query ។
Page 36 of 140
sum,
average,
Chapter 3: Creating and Using Query
SETEC University
Management Information System
ount,… ។ 5. Structured Query Language (SQL):
query
SQL Statement UPDATE
SQL Commands (SELECT,
DELETE) , SQL Clauses (WHERE
ORDER
SQL
relational databases
Microsoft Access
II.
។
។
Query:
3.1. 1.
S l Ribbon,
t
u ry
tab: Create
Wizard
2.
wizard:
Win ow “
“Simpl
Other, w
u ry Wiz r ”
Chapter 3: Creating and Using Query
Icon: Query
u ry”
:
OK
Page 37 of 140
Management Information System
3.
“T List “Av il
l s
u ri s”,
Table
l Fi l s”, List “S l
4.
SETEC University
Fields t
Query
Fi l s”
Next
5.
Page 38 of 140
“D t il shows v ry fi l of v ry r
or ”
Next
Chapter 3: Creating and Using Query
SETEC University
Management Information System
6.
query
“Op n th qu ry to vi w inform tion”
Finish Lab 1: Create select query by using wizard: 1. 2.
t
s “ orthwin .
Select Query
” table Products
: ProductID, ProductName,
UnitPrice, UnitsInStock. 3. Save query
“qry ro u ts”
Lab 2: Create select query by using wizard: (Modify the query design) 1. 2.
t
s “ orthwin .
Select Query
” table Customers
: CustomerID, ContactName,
Address, City, Phone 3. S v th qu ry s “qry ustom r ont 3.2. 1.
t” n
hoos “ o ify th qu ry
sign”
Run query: Ribbon,
Query Tools: Design
Chapter 3: Creating and Using Query
Result,
Icon: Run
Page 39 of 140
Management Information System
3.3.
S l
1.
t
u ry
Ribbon,
2.
Design View:.
tab: Create
Design
Add
3. Double-click : Fi l
-
T
Other,
Win ow “Show T
tab: Tables
-
SETEC University
Icon: Query l ”
:
Tables Close
Query Window
:
Field
mouse
Field
(drag) table
l
Page 40 of 140
tables Chapter 3: Creating and Using Query
SETEC University
-
Sort
-
Show
-
Criteria:
Management Information System
(Ascending/Descending).
។ : >,
<, <=, >=, Is Null, Is Not Null, Between…An …, In, ** Like:
(wildcards)
: ១
?
ot In, Lik , An , Or,<>,… ។ E.g. Lik “sm ” E.g. Lik “sm?th”
h r
t r
E.g. Lik “sm iy th” E.g. Lik “sm i th”
E.g. Lik “ -
”
* Disable the output of all fields in query design:
uncheck
Office Button Access Options Object Designer
“ u ry D sign”,
Output all fields
Chapter 3: Creating and Using Query
Page 41 of 140
Management Information System
Lab:
Select Query
1.
SETEC University
Design View:
database: Northwind.accdb
2.
Select Query
table: Customers
:
a.
(Sort: Ascen ing CompanyName ។
b.
London
c. 3.
Fr ។ Select query
table Products
:
a.
៥ ។
b.
៣ t goryID=8 S
foo
។
c.
Chang
Unit ri
$18.00 ។ 4.
Select query
table Orders
:
a.
12/11/1996 20/04/1997
ShipCountry=Germany ។
b.
(ShipRegion)
c. 5.
WA, CA, SP ។
(ShipRegion) ។ Select query
t
l
ustom rs
Orders
។ Homework 1.
ProductName, UnitPrice (Categories)
2.
Seafood ។
ProductName, UnitPrice ១
3.
CategoryName CategoryName
(Categories)
CategoryID, ProductName, UnitPrice “r”
Seafood ។ UnitsInStock ៣ E.g: Norwood, Carnarvon,
Sir Ro n y, … ។ Page 42 of 140
Chapter 3: Creating and Using Query
SETEC University
Management Information System
E.g: > 234
Returns all numbers greater than 234. To find all numbers less than 234, use < 234.
>= "Cajhen"
Returns all records from Cajhen through the end of the alphabet
Between #2/2/2007#
Returns dates from 2-Feb-2007 through 1-Dec-2007 (ANSI-89). If
And #12/1/2007#
your database uses the ANSI-92 wildcard characters, use single quotation marks (') instead of pound signs (#). Example: Between '2/2/2007' And '12/1/2007'
Not "Germany"
Finds all records where the exact contents of the field are not exactly equal to "Germany." The criterion will return records that contain characters in addition to "Germany," such as "Germany (euro)" or "Europe (Germany)".
Not "T*"
Finds all records except those beginning with T. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).
Not "*t"
Finds all records that do not end with t. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).
In(Canada,UK)
In a list, finds all records containing Canada or UK.
Like "[A-D]*"
In a Text field, finds all records that start with the letters A through D. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).
Like "*ar*"
Finds all records that include the letter sequence "ar". If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).
Chapter 3: Creating and Using Query
Page 43 of 140
Management Information System
Lik “
ison
SETEC University
Finds all records that begin with "Maison" and contain a 5-letter
Dewe?"
second string in which the first 4 letters are "Dewe" and the last letter is unknown. If your database uses the ANSI-92 wildcard character set, use the underscore (_) instead of the question mark (?).
#2/2/2007#
Finds all records for February 2, 2007. If your database uses the ANSI-92 wildcard character set, surround the date with single quotation marks instead of pound signs ('2/2/2007').
< Date() - 30
Uses the Date function to return all dates more than 30 days old.
Date()
Uses the Date function to return all records containing today's date.
Between Date() And
Uses the Date and the DateAdd functions to return all records
D t A
between today's date and three months from today's date.
“ ", 3,
Date()) Is Null
Returns all records that contain a null (blank or undefined) value.
Is Not Null
Returns all records that contain any value (that are not null).
“"
Returns all records that contain a zero-length string. You use zerolength strings when you need to add a value to a required field, but you don't yet know what the actual value is. For example, a field might require a fax number, but some of your customers might not have fax machines. In that case, instead of entering a number, you enter a pair of double quotation marks with no space between them “" . 3.3.1.
Alias field name: :
Field
query
NewFieldName : OldFieldName
E.g 1. : Name : FirstName Page 44 of 140
Chapter 3: Creating and Using Query
SETEC University
E.g 2. Full
Management Information System
m
First
m
& “ “ & L st
m
E.g 3. : Sub Total : [UnitPrice]*[Quantity]
3.3.2. 1. 2. 3.
Aggregate function: Select Query Ribbon, Show/Hide,
Design View
Query Tools: Design Icon: Total
Total grid
:
Total grid
4.
Total grid,
Aggregate function
Group y, Sum, ount, Avg,
Chapter 3: Creating and Using Query
x,
fields
:
in, …
Page 45 of 140
Management Information System
5.
SETEC University
Run
3.3.3.
Expression Builder:
1.
Select Query
2.
Field
3.
Ribbon, : Right-click
Page 46 of 140
Design View
Query Tools: Design Field
Query Setup,
Icon: Builder
Builder
Chapter 3: Creating and Using Query
SETEC University
Management Information System
4. Double-Click
folder: Function
functions Double-click
5.
Built-In Function
function
function
OK
function
6. Run query Lab1:
Select Query
1.
Aggregate function
database Northwind.accdb
2.
Select Query
table Products
a. Group by CategoryID b. Sum on UnitsInStock 3. Save query
“qryUnitsInSto k y
t gory”
4. Run the query Lab2: Calculation using Expression Builder 1.
database Northwind.accdb
2.
Select Query
table OrderDetails
fields: OrderID,
ProductID, UnitPrice, Quantity 3.
field
:
-
SubTotal Before Discount: [UnitPrice]*[Quantity]
-
Discount Price: [UnitPrice]*[Quantity]*Discount
Chapter 3: Creating and Using Query
Page 47 of 140
Management Information System
-
SETEC University
SubTotal After Discount: [SubTotal Before Discount] – [Discount Price] : SubTotal After Discount: [UnitPrice]*[Quantity]*(1-[Discount]) : SubTotal After Discount$: Format([UnitPrice]*[Quantity]*(1- Dis ount ,” urr n y”
4. S v qu ry s “qrySu Tot l” 3.3.4.
Build-in Function - IIF:
Function ។
:
IIf(expression, truepart, falsepart) expression: truepart: falsepart:
E.g.1 R sult IIf S or >=50,”
ss
”,”F il
”
E.g.2 : Mention: IIf([TotalScore] Between 450 And 500,"Excellent",IIf([TotalScore] Between 350 And 449,"Very Good",IIf([TotalScore] Between 200 And 349,"Good","Poor")))
E.g. 3 L ngu g
IIf
ountryR gion =“It ly”, “It li n", “Som oth r l ngu g "
E.g. 4: Status: IIf([Due Date]
Lab 1: 1. 2.
Build-in function IIF database Northwind.accdb Select Query
table Products
: ProductID, ProductName,
UnitInStock 3.
field
StockStatus
UnitsInStock=0
“ ro u t out of sto k” “ ro u t not out of sto k”
4. Save query Page 48 of 140
“qrySto kSt tus” Chapter 3: Creating and Using Query
SETEC University
Lab:
Management Information System
nested function IIF
1.
database Northwind.accdb
2.
Select Query
table Products
: ProductID, ProductName,
UnitsInStock, UnitPrice 3.
field
UnitPrice 1 - 20
ntion “V ry h
UnitPrice 21 - 50
ntion “ h
UnitPrice 51 - 70
ntion “Exp nsiv ”
UnitPrice > 70
ntion “V ry Exp nsiv ”
4. Save query 3.3.5.
Mention
“qry ro u t
Build-In-Function
p”
p”
ntion” :
Abs(Number): Absolute value
Chr(charcode): Convert ascii to character
Avg(expr): Average
Asc(Strexpr): Convert character to ascii
CBool(expr): Convert to Boolean
Date(): Display current date
CByte(expr): Convert to byte
DateAdd(Interval,Number,Date): Add to
CCur(expr): Convert to currency
date by interval
CDate(date): Convert to date
DateDiff(Interval,Date1,Date2): Date2
CDbl(expr): Convert to double
minus Date1 by interval
CInt(expr): Convert to integer
DatePart(Interval,Date): Select date by
CLng(expr): Convert to long integer
interval
* Interval :
Year
yy, yyyy
Quarter
q, q
Month
mm, m
Chapter 3: Creating and Using Query
Page 49 of 140
Management Information System
* Interval :
SETEC University
Day
dd, d
Week
wk, ww
Hour
hh
Minute
mi, n
Second
ss, s
Milisecond
ms
Ex D t
rt “yyyy”,D t
* Build-in functions - Date/Time
-
DateAdd(Interval,Number,Date): interval E.g. : D t A
-
“yyyy”,10, Hir D t
DateDiff(Interval,Date1,Date2):
Date2
Date1
interval E.g. : D t Diff “yyyy”, irthD t , ow
-
DatePart(Interval,Date): interval E.g. : D t
rt “ ”,
irthD t
D t
rt “m”,
irthD t
D t
rt “yyyy”,
irthD t
* Build-in functions - Format() E.g. : Form t
irthD t , “yyyy”
Form t Amount , “ urr n y” Format([Amount], “៛ #,##0.00") 10 Year Aniversary: Format(DateAdd("m",10,[HireDate]),"dd/mmm/yy") 3.4. 1. 2.
kup D t
s
Database file Office button
Page 50 of 140
: Back up Manage
Back Up Database Chapter 3: Creating and Using Query
SETEC University
3.
Save As dialog box, browse
4. 5. 3.5.
Management Information System
File name,
Database file Database file
Back up
Save Delete Query: table
1.
S l
t
u ry
2.
Ribbon,
3.
Query Type,
។
Design View
Query Tools: Design Icon: Delete
Delete grid
:
Chapter 3: Creating and Using Query
Page 51 of 140
Management Information System
SETEC University
Delete grid
4.
field
-
Field:
-
Table:
-
Delete:
record: field table + From: + Where:
field
table
record
field
5. Run the query
Page 52 of 140
Chapter 3: Creating and Using Query
SETEC University
6. L
Management Information System
Yes
records
D l t 1.
u ry
:
records
table: Order Details
(UnitsInStock)
30 ។ 2.
table: Orders
3.
table: Orders
OrderID
10500 ។
ID = “ LAUS” ។ 4.
table: Products
(CategoryName)
Seafood ។ 3.6.
Update Query: table
1.
S l
t
u ry
2.
Ribbon,
3.
Query Type,
។
Design View
Query Tools: Design Icon: Update
Chapter 3: Creating and Using Query
Update grid
Page 53 of 140
Management Information System
SETEC University
Update grid
4.
fields
-
Field:
-
Table:
-
Update To:
-
Criteria:
fields field
Update record
Update record
table
5. Run the query
Page 54 of 140
Chapter 3: Creating and Using Query
SETEC University
6.
Management Information System
Yes
Lab:
Update query
1.
Country=“
2.
Update
xi o”
“
m o i ”
table: Customers
Unit ri
t
l
ro u ts
១
stock
២ ។ 3.7.
Append Query: record
1.
S l
t
2.
Ribbon,
3.
Query Type,
table
u ry
Design View
។ Source table
Query Tools: Design
4.
Icon: Append
:
-
T
-
Current Database: Database
-
Anoth r D t
l
m
Destination table s
Chapter 3: Creating and Using Query
record
record table
database
Page 55 of 140
Management Information System
5.
OK
-
Field:
-
Table:
-
Append To:
-
Criteria:
SETEC University
fields
Field
fields
Append
Source table
Source table Field
Destination table
6. Run the query
7.
Yes
Page 56 of 140
Chapter 3: Creating and Using Query
SETEC University
L
r
Management Information System
t App n
1.
u ry
:
table
Clients
2.
table: Customers
table Customers
ountry=”
xi o”
table Clients ។ 3.
table Customers
fax
table Clients ។ 4.
table Customers Germany
table Clients ។
5.
table Customers
Canada
UK
table Clients ។ E.g.: < Date() - 30 Betw
nD t
Returns all dates more than 30 days old. An D t A
“ ", 3, D t
Returns ll r
or s
tw
n to
y‘s date
and three months from today‘s date. 3.8.
Make-table Query: table
t
l
។ 1.
S l
t
u ry
2.
Ribbon,
3.
Query Type,
Design View
tables
Query Tools: Design
4.
Icon: Make-table Query
:
:
-
T
l
m
table
Chapter 3: Creating and Using Query
record Page 57 of 140
Management Information System
5.
SETEC University
-
Current Datab s
table
Database
-
Anoth r D t
table
database
s
OK
6.
fields
fields
table
7. Run the query
8.
Yes
Page 58 of 140
Chapter 3: Creating and Using Query
SETEC University
Lab: 1. 2.
Management Information System
Make-table query : database Northwind.accdb t
l
CustomerAG
Address
ity A
3.
t
table: Customers
l
ustom rLon on London
t UnitPrice
l
ID
G។
ContactName, Address
4.
fields: CustomerID, ContactName,
ity
ri
fields: CustomerID,
table: Customers
Madrid ។ ro u t otSE
UnitsInStock
fields: ProductID, ProductName,
table: Products “s”
Chapter 3: Creating and Using Query
“ ”។
Page 59 of 140
Management Information System
3.9.
SETEC University
Parameter Query: Query ។
1.
S l
2.
t
u ry
Design View
fields
tables field
Parameter
Query
3.
Parameter Query
Criteria
field
E.g: Between [Start Date] And [End Date] 4. Run the query
Page 60 of 140
Parameter
Chapter 3: Creating and Using Query
SETEC University
Management Information System
Lab: Create Parameter query 1.
query
table: Orders
(OderDate) 2.
query
(Begin Date table: Products
stock 3.
End Date) ។ ។
query
table: Customers (CompanyName) ។
4.
query
table: Customers omp ny
Chapter 3: Creating and Using Query
m
។
Page 61 of 140
Management Information System
3.10.
SETEC University
Crosstab Query: Query ។ Column Heading
Row Heading
1.
Ribbon,
tab: Create Win ow “
2.
Page 62 of 140
“ rosst
w
Other,
Icon: Query Wizard
u ry”
u ry Wiz r ”
:
OK
Chapter 3: Creating and Using Query
SETEC University
3.
Management Information System
table
query
Crosstab Query
Next
4.
field
Chapter 3: Creating and Using Query
Row Heading
Next
Page 63 of 140
Management Information System
5.
6.
field
interval
Page 64 of 140
field
SETEC University
Column Heading
Next
Date/Time
Chapter 3: Creating and Using Query
SETEC University
Management Information System
7.
field
function
field
Next
8.
query
Finish
Chapter 3: Creating and Using Query
Page 65 of 140
Management Information System
Lab: 1.
SETEC University
Crosstab query: database Northwind.accdb
2.
crosstab query
table Customers
ountry
3.
(City)
crosstab query
customers ។
table Orders ។
4.
Wizard,
crosstab query (Freight)
(CompanyName)
Page 66 of 140
table: Orders
Shippers
(ShipCountry) ។
Chapter 3: Creating and Using Query
SETEC University
Management Information System
3.11. Structure Query Language (SQL): query
SQL Statement
SQL Commands (SELECT, UPDATE ORDER
។
SQL
relational databases a)
DELETE), SQL CLAUSES (WHERE
Microsoft Access
។
SQL: 1.
Ribbon,
2.
Other,
3.
Show Table,
4.
tab: Create Icon: Query Design
Close
Query Tools: Design
Chapter 3: Creating and Using Query
Result,
Icon: SQL
Page 67 of 140
Management Information System
b)
SETEC University
Run SQL:
-
Ribbon,
Query Tools: Design
Result,
Icon:
Run c)
View SQL:
-
Ribbon,
tab: Home
View SQL View
*** SELECT Statement Instructs the Microsoft Jet database engine to return information from the database as a set of records. Syntax : SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]} FROM tableexpression [, ...] [IN externaldatabase] [WHERE... ] [GROUP BY... ] [HAVING... ] [ORDER BY... ] [WITH OWNERACCESS OPTION]
Page 68 of 140
Chapter 3: Creating and Using Query
SETEC University
Management Information System
The SELECT statement has these parts: Part
Description
Predicate
One of the following predicates: ALL, DISTINCT, DISTINCTROW, or TOP. You use the predicate to restrict the number of records returned. If none is specified, the default is ALL.
*
Specifies that all fields from the specified table or tables are selected.
Table
The name of the table containing the fields from which records are selected.
field1, field2
The names of the fields containing the data you want to retrieve. If you include more than one field, they are retrieved in the order listed.
alias1, alias2
The names to use as column headers instead of the original column names in table.
tableexpression
The name of the table or tables containing the data you want to retrieve.
externaldatabase The name of the database containing the tables in tableexpression if they are not in the current database. Lab:
queries
1.
SQL Statement:
database Northwind.accdb
2.
SQL a. b.
: ustom rID, ont
m
ro u t
m , Unit ri
Or
rID, Or
L st Descending
m
t
l
ustom rs ។
UnitsInStock
UnitsInSto k ៥
rD t , Fr ight (Freight)
e.
r ss
១
table: Products ។
stock d.
A
ProductID, ProductName, UnitPrice UnitsInStock
c.
t
First
(UnitPrice) Ship ៣
m
table: Products
m ៤
២
។
table: Orders ។
table: Employees
sort
LastName ។
Chapter 3: Creating and Using Query
Page 69 of 140
Management Information System
SETEC University
f.
២ table: [Order Details] ។
g.
t
h.
l
Or
rs ។
OrderID, ១
Group by: OrderID
t
l
Or
i.
r D t ils ។
Sort: Ascending CompanyName ។
j.
ustom rID, ont
t
m
omp ny
m
Subquery ។ k.
ContactName, Address
Country
table: Customers, order by
Country, ContactName ។
*****End of Chapter 3*****
Page 70 of 140
Chapter 3: Creating and Using Query
SETEC University
Management Information System
Chapter 4 Creating and Using Form អ្វដី ៅជា Form? Form
Object
MS-Access table
4.1.
query
Form
។
Form tool:
1.
Navigation Pane,
2.
Ribbon,
3. Access
interface
table
tab: Create
form
Chapter 4: Creating and Using Form
query
Form,
Layout view
Form
icon: Form
:
Page 71 of 140
Management Information System
4.
Save
5.
OK
4.2. 1.
SETEC University
save form
View form: Navigation Pane, Double-click view
Page 72 of 140
form
form
Form View:
Chapter 4: Creating and Using Form
SETEC University
2.
Management Information System
Ribbon,
: -
tab: Home
icons
Views,
icon: View
Status bar
Form View:
Chapter 4: Creating and Using Form
user ។
Page 73 of 140
Management Information System
-
Layout View:
SETEC University
form
(formatting) ។
-
Design View:
form
(formatting)
Layout View
4.3. 1.
Form Navigation Pane,
Page 74 of 140
។
Split Form tool table
query
Form
Chapter 4: Creating and Using Form
SETEC University
2.
Ribbon,
3. Access
Management Information System
tab: Create
form
Forms,
Layout View
icon: Split Form
:
Form View
Datasheet View
Chapter 4: Creating and Using Form
Page 75 of 140
Management Information System
4.4.
Form
Multiple Items tool:
1.
Navigation Pane,
2.
Ribbon,
3. Access
Page 76 of 140
SETEC University
table
tab: Create
form
query
Form,
Layout View
Form
icon: Multiple Items
:
Chapter 4: Creating and Using Form
SETEC University
Management Information System
4.5.
Form
1.
Ribbon,
Wizard tab: Create
Form,
More Forms
Form Wizard
2.
Wizard
3. 4.
“T List “Av il
l s
:
u ri s” ,
l Fi l s”,
t fi l s
l
query form
Next
Chapter 4: Creating and Using Form
Page 77 of 140
Management Information System
5.
layout
form
SETEC University
olumn r, T
ul r, D t sh
t, …
Next
6.
style
form
Access 2003, Access 2007, Apex, ...
Next
Page 78 of 140
Chapter 4: Creating and Using Form
SETEC University
Management Information System
7.
form information.”
4.6.
form
1.
Ribbon,
title bar
“Op n th form to vi w or nt r
Finish
Blank Form tool: Create
2.
Forms,
icon: Blank Form
Layout View
Field List pane
:
Chapter 4: Creating and Using Form
Page 79 of 140
Management Information System
3.
SETEC University
Field List pane,
(+)
table
fields
table 4. Double-click
field
form ។
fields
key: Ctrl +
fields ។
5.
tools
Control
tab: Format
Logo,
,
form
Page 80 of 140
Chapter 4: Creating and Using Form
SETEC University
Management Information System
6.
control -
4.7. 1.
Design view Ribbon,
2. Right-click
Right-click
tab: Design
control form
form: form tools
Design View Controls
form:
Design View
control
control
Layout
Remove
3.
Mouse pointer
Chapter 4: Creating and Using Form
Controls
Page 81 of 140
Management Information System
4.
SETEC University
Control
Mouse pointer
Controls
4.8.
form : Cursor
-
Tab:
-
Shift + Tab:
-
Ctrl + Home:
-
Ctrl + End:
Page 82 of 140
field
Cursor
field field
Cursor Cursor Cursor
record ១ record
Form Form
Chapter 4: Creating and Using Form
SETEC University
4.9.
Management Information System
Main form
Main form
Subform
Subform
tables
One-to-Many ។ Main form table table
1.
Columnar Form
Subform
Tabular
Datasheet Form form
Subform
Many ។
Main form Form
One
Subform
Ribbon,
Form Wizard
tab: Create
Forms,
icon: More Forms Form
Wizard 2.
3.
window
Form Wizard
Tables/Queries,
4. Double-click 5.
table/query fields
Wizard table/query
:
Main form
Main form Tables/Queries,
Subform
6. Double-click
Chapter 4: Creating and Using Form
fields
Subform
Page 83 of 140
Management Information System
7.
SETEC University
Next
8.
“Form
with su form s ” 9.
Page 84 of 140
Next
Chapter 4: Creating and Using Form
SETEC University
10. 11.
12.
Management Information System
layout
subform
: Tabular
Datasheet
Next
style
Chapter 4: Creating and Using Form
Next
Page 85 of 140
Management Information System
13.
Main form
SETEC University
Subform
“Op n th form to vi w
or nt r inform tion” 14.
Finish
Subform
1.
Page 86 of 140
Main form
form Columnar
Chapter 4: Creating and Using Form
SETEC University
2.
Management Information System
Subform
Tabular Form
3. View main form
4.
Ribbon,
Datasheet Form
Design View
tab: Design
Controls,
icon: Use Control
Wizards
5.
Navigation Pane,
Subform
Detail
Main
form design
Chapter 4: Creating and Using Form
Page 87 of 140
Management Information System
6. Access
SETEC University
Subform
Main form
Relationship
Database ។ ***
Access 1.
Subform
Main form
Navigation Pane, Right-click
2.
form design,
3.
Ribbon, Tools,
4.
Source Object:
Page 88 of 140
Design
Subform tab: Design
Form Design Tools
icon: Property Sheet
Property Sheet, -
Main form
:
tab: Data
Properties form
Subform
Chapter 4: Creating and Using Form
SETEC University
-
Management Information System
Linked Master Field:
Field (Primary key)
Main form
Subform -
Linked Child Field:
Field (Foreign key)
Subform
Main form 5. View form
Form View
Lab: Create Main form and Subform 1.
Main form
2.
Subform
3.
Subform
table: Customers table: Orders Main form
4.10. Visual Basic for Application(VBA): V A
Microsoft Visual Basic
code
Windows Application
Microsoft ។
a. What is Object? Object
… MS-Access Form, Object
Chapter 4: Creating and Using Form
។
Control
Page 89 of 140
Management Information System
-
Text Box :
SETEC University
Control
keyboard -
Label :
Control
Control
-
Command Button :
Control
button
event -
h
k
ox
Control
check
uncheck -
Option Button :
Control
ន។ a.1. What is Property? Property
Object
Access ។ rop rti s .Left
Control ontrols
:
Form
MS-
form
form
.Top :
form
.Height:
Control
.Width:
Control
a.2. What is Method? Method
object
។
ObjectName.MethodName E.g. : txtFirstName.SetFocus Me!txtLastName.Undo rs.MoveNext rs.MoveFirst a.3. What is Event? Event
(Procedure) user
program code ។
E.g.: Private Sub btnOK_Click() Statements… End Sub Page 90 of 140
Chapter 4: Creating and Using Form
SETEC University
Management Information System
Private Sub Form_Load() Statements… End Sub Private Sub txtFirstName_GotFocus() Statements… End Sub b.
form
Design view
Ribbon, c.
Tab: Create
Code View form
vi w o
Design View
Form Design
form Form Design Tools,
Tab: Design
View Code d.
event Click
control
event
1.
Property windows,
tab: Event
button
: Right-click Lab:
control
Form
control
uil Ev nt… Code Builder
OK
Code
Form
txtFirstName txtLastName txtAddress btnClear btnOK 2.
Code Editor,
code
Private Sub btnOK_Click() txtFirstName.Value = "Sabay" txtLastName.Value = "Sok" txtAddress.Value = "Bokor mountain" End Sub Private Sub btnClear_Click() txtFirstName.Value = "" txtLastName.Value = "" txtAddress.Value = "" txtFirstName.SetFocus End Sub Chapter 4: Creating and Using Form
Page 91 of 140
Management Information System
SETEC University
4.11. Introduction to Variables: Variable
memory application ។ variable memory ។
E.g.1: Private Sub Detail_Click() SomeColor = vbRed Detail.BackColor = SomeColor End Sub -
(declare) variable variable
Dim
។ Dim VariableName
E.g.: Private Sub Form_Load() Dim thisVar thisVar = 15 MsgBox thisVar End Sub -
Option Explicit:
variable (Error)
។
E.g.: Private Sub Form_Load() Dim thisVar thisVar = 15 MsgBox thisInt
'<-- Note the misspelled "thisInt"
End Sub Option Explicit Code Builder variable E.g. :
variable ។
Option Explicit Private Sub Form_Load() ..... End Sub
Page 92 of 140
Chapter 4: Creating and Using Form
SETEC University
-
Management Information System
Option Explicit 1.
menu
2.
Code Builder:
Microsoft Visual Basic,
tab: Editor R quir V ri
l D
Tools Option…
Code Settings,
check
h
k ox “
l r tion”
4.12. Data Type: Data Type
variable
។
Dim VariableName As DataType Or: Dim VariableName1 As DataType1, DataType2 Dim VariableName3 As DataType3 E.g. :
VariableName2
As
Dim StudentName As Byte Dim CountryName As String
-
: Data Type
(Range)
Description
(0 to 255)
Byte
1-byte binary data
Integer
2-byte integer
(– 32,768 to 32,767)
Long
4-byte integer
(– 2,147,483,648 to 2,147,483,647)
Single
4-byte floatingpoint number
Double
8-byte floatingpoint number
Currency
8-byte number with fixed decimal point
String
Date
-
(– 3.402823E38 to – 1.401298E – 45 (negative values)) 1.401298E – 45 to 3.402823E38 (positive values)
(– 1.79769313486231E308 to – 4.94065645841247E – 324 (negative values)) 4.94065645841247E – 324 to 1.79769313486231E308 (positive values)
(– 922,337,203,685,477.5808 to 922,337,203,685,477.5807)
(Zero to approximately two billion
String of characters
characters)
8-byte date/time value
(January 1, 100 to December 31, 9999)
variable
(Prefix)
variable Chapter 4: Creating and Using Form
Page 93 of 140
Management Information System
SETEC University
Data Type
Prefix
Example
Boolean
bln
blnFound
Byte
byt
bytTracks
Date/Time
dtm
dteStartOfShift
Double
dbl
dblDistance
Error
err
errCantOpen
Integer
int
intNbrOfStudents
Long
lng
lngPopulation
Object
obj
objConnection
Single
sng
sngAge
String
str
strCountryName
Currency
cur
curHourlySalary
Variant
var
varFullName
- Functions CBool():
convert
CStr():
Boolean
convert
CByte():
convert
Lng
convert
Long
String
CSng
convert
Single
Byte
CDbl:
convert
Double
ur
convert
Currency
(Small number) Int
-
convert
Integer
D t
Constant:
convert
variable
Date
។
Const VariableName = Value
E.g. :
Const conPI = 3.14159265359
vbCrLf (Carriage Return-Line Feed Constant) : text -
Static
constant
។ keyword
sub procedure
variable ។
Static
variable
function keyword:
memory function
Page 94 of 140
sub procedure
។ Chapter 4: Creating and Using Form
SETEC University
E.g. :
Management Information System
Sub Calculate() Static intVar As Integer intVar = intVar + 1 MsgBox intVar End Sub Private Sub btnCalculate_Click() Call Calculate End Sub
Lab:
Data Type:
1.
form
2.
form “On
3.
li k”
Design View Property windows,
tab: Event
event
button
code
Private Sub Detail_Click() Dim strName As String,intYearBorn As Integer Dim intAge As Integer strName = InputBox("Your name: ") intYearBorn = InputBox("Year borned: ") intAge = Format(Now(), "yyyy") - intYearBorn MsgBox "Hello " & strName & vbCrLf & _ "you are " & intAge & " years old." End Sub 4.
form
Design View A
-
Caption: Calculate
-
Name: btnCalculate
5.
ontrol
omm n
utton
:
code
Sub Calculate() Static intVar As Integer intVar = intVar + 1 MsgBox intVar End Sub -
event: On Click
button: Calculate
code:
Private Sub btnCalculate_Click() Call Calculate End Sub 6. Save form
“frmSt ti V ri
Chapter 4: Creating and Using Form
l ” Page 95 of 140
Management Information System
***
SETEC University
(See help: MsgBox Function)
MsgBox function: message
dialog box constant
button
click user
click
button
user
។
MsgBox(prompt[, buttons Or icon] [, title]) title prompt
icon
buttons -
prompt:
string
expression
message
dialog
box ។ -
button:
buttons Constant
prompt Value
Description
vbOKOnly
0
Display OK button only.
vbOKCancel
1
Display OK and Cancel buttons.
vbAbortRetryIgnore
2
Display Abort, Retry, and Ignore buttons.
vbYesNoCancel
3
Display Yes, No, and Cancel buttons.
vbYesNo
4
Display Yes and No buttons.
vbRetryCancel
5
Display Retry and Cancel buttons.
vbCritical
16
Display Critical Message icon.
vbQuestion
32
Display Warning Query icon.
vbExclamation
48
Display Warning Message icon.
vbInformation
64
Display Information Message icon.
vbDefaultButton1
0
First button is default.
vbDefaultButton2
256
Second button is default.
vbDefaultButton3
512
Third button is default.
vbDefaultButton4
768
Fourth button is default.
Constants
VBA code
Page 96 of 140
constants ។
Chapter 4: Creating and Using Form
SETEC University
Management Information System
If the user click
-
titl
The function returns
Numeric Value
vbOK
1
vbCancel
2
vbAbort
3
vbRetry
4
vbIgnore
5
vbYes
6
vbNo
7
string
Title bar
Message Box ។
E.g. : Private Sub Detail_Click() MsgBox "Do you really want to delete this record?", _ vbYesNoCancel Or vbQuestion, "Confirmation" End Sub
-
buttons MsgBox Dim x As Integer x = MsgBox("Do you really want to delete this record?", _ vbYesNoCancel + vbQuestion, "Confirmation") code Yes, No
MsgBox Cancel ។
constant vbYes=6 ***
button: Yes x។
InputBox Function: message
button
buttons
user
dialog box string
text
click
text box ។
InputBox(prompt[, title] [, default]) Chapter 4: Creating and Using Form
Page 97 of 140
Management Information System
-
prompt
-
f ult
title: text
SETEC University
Message Box text box
។ user
។ E.g. : Private Sub Form_Load() Dim x As Integer, y As Integer Dim sum As Integer x = InputBox("Please enter value of x", "Enter value of x") y = InputBox("Please enter value of y", "Enter value of y") sum = x + y MsgBox "The sum of " & x & " and " & y & " is " & sum, ,"Result" End Sub
Page 98 of 140
Chapter 4: Creating and Using Form
SETEC University
Lab :
Management Information System
Form
1.
Code:
form
-
-
Add buttons
click
properties Name
Caption
Events
btnSum
Sum
On_Click
btnSubtract
Subtract
On_Click
btnMultiply
Multiply
On_Click
btnDivide
Divide
On_Click
buttons (x
2.
, access
InputBox
user
y) ។
code
Option Compare Database Option Explicit Private Sub btnDivide_Click() Dim x As Integer, y As Integer Dim divide As Integer x = InputBox("Please enter value of x", y = InputBox("Please enter value of y", divide = x / y MsgBox "The division of " & x & " and " divide, , "Result" End Sub Private Sub btnMutiply_Click() Dim x As Integer, y As Integer Dim multiply As Integer x = InputBox("Please enter value of x", y = InputBox("Please enter value of y", multiply = x * y MsgBox "The multiplication of " & x & " multiply, , "Result" End Sub Chapter 4: Creating and Using Form
"Enter value of x") "Enter value of y") & y & " is " & _
"Enter value of x") "Enter value of y") and " & y & " is " &
Page 99 of 140
Management Information System
SETEC University
Private Sub btnSubtract_Click() Dim x As Integer, y As Integer Dim subtract As Integer x = InputBox("Please enter value of x", "Enter value of x") y = InputBox("Please enter value of y", "Enter value of y") subtract = x - y MsgBox "The subtract of " & x & " and " & y & " is " & subtract, , "Result" End Sub Private Sub btnSum_Click() Dim x As Integer, y As Integer Dim sum As Integer x = InputBox("Please enter value of x", "Enter value of x") y = InputBox("Please enter value of y", "Enter value of y") sum = x + y MsgBox "The sum of " & x & " and " & y & " is " & sum, , "Result" End Sub 4.13. Introduction to Procedures: Procedure
code ។
program ro
ur s
Procedure
Function Procedure ។
Procedure
return (Call)
4.13.1.
VBA
Sub procedures
Sub Procedure
Function return
។ Sub Procedure:
Sub ProcedureName([Arguments]) Statements… End Sub E.g. : Sub Multiply() Dim x As Integer, y As Integer Dim result As Integer x = txtValue1 y = txtValue2 result = x * y txtResult = result End Sub -
(Call) Sub Procedure Call ProcedureName([Arguments]) ProcedureName [Arguments]
Page 100 of 140
Chapter 4: Creating and Using Form
SETEC University
Management Information System
E.g. : Private Sub btnCalculate_Click() Call Multiply End Sub -
Sub Procedure
Arguments
Sub Multiply(x As Integer, y As Integer) Dim result As Integer result = x * y txtResult = result End Sub Private Sub btnCalculate_Click() Call Multiply(txtValue1, txtValue2) End Sub 4.13.2.
Function Procedure:
Function FuntionName([Arguments]) As DataType Statements… End Function -
Function Procedure As
Sub Procedure
return
DataType
return ។ E.g. : Function Divide() As Integer Dim x As Integer, y As Integer x = txtValue1 y = txtValue2 Divide = x / y End Function -
Function Procedure statements Procedure variable
។
Sub Procedure
។
Sub return
text box
។
E.g. : Private Sub btnCalculate_Click() txtResult = Divide End Sub
Chapter 4: Creating and Using Form
Page 101 of 140
Management Information System
Lab: 1.
SETEC University
Sub Procedure: form
2.
Sub Procedure
3.
Sub Procedure
4.
click
button: Calculate
5.
click
button: New
។ clear
text box
។
Sub SquareSolution() Dim dblSide As Double Dim dblPerimeter, dblArea As Double dblSide = txtSide dblPerimeter = dblSide * 4 dblArea = dblSide * dblSide txtSqPerimeter = dblPerimeter txtSqArea = dblArea End Sub Private Sub RectangleSolution() Dim dblLength, dblHeight As Double Dim dblPerimeter, dblArea As Double dblLength = txtLength dblHeight = txtHeight dblPerimeter = (dblLength + dblHeight) * 2 dblArea = dblLength * dblHeight txtRecPerimeter = dblPerimeter txtRecArea = dblArea End Sub Page 102 of 140
Chapter 4: Creating and Using Form
SETEC University
Management Information System
Private Sub btnCalculateRec_Click() RectangleSolution End Sub Private Sub btnCalculateSqare_Click() Call SquareSolution End Sub Private Sub btnSqNew_Click() txtSide = "" txtSqPerimeter = "" txtSqArea = "" txtSide.SetFocus End Sub Private Sub btnRecNew_Click() txtLength = "" txtHeight = "" txtRecPerimeter = "" txtRecArea = "" txtLength.SetFocus End Sub Homework: Lab 4.13.3.
Function Procedure: Function Procedure
Procedure
។
Arguments:
Sub ProcedureName(Var1 As Datatype, Var2 As Datatype,…) Statements End Sub Function FunctionName(Var1 As Datatype, Var2 As Datatype,…) _ As Datatype Statements End Function E.g. 1
Function Procedure
។
Function CubeArea(Side As Double) As Double CubeArea = Side * Side * 6 End Function Function CubeVolume(Side As Double) As Double CubeVolume = Side * Side * Side End Function -
Call Function ro
ur
Arguments
Private Sub btnCalculate_Click() txtArea = CubeArea(txtSide) txtVolume = CubeVolume(txtSide) End Sub Parameter Chapter 4: Creating and Using Form
Page 103 of 140
Management Information System
Lab: 1.
SETEC University
Procedures with Arguments: Sub Procedure Sub SolveEllipse(SmallRadius As Double, LargeRadius As Double) Dim dblCircum As Double Dim dblArea As Double dblCircum = (SmallRadius + LargeRadius) * 2 dblArea = SmallRadius * LargeRadius * 3.14159
‘2R
txtEllipseCircumference = dblCircum txtEllipseArea = dblArea End Sub 2.
Function Procedures Function BoxArea(dblLength As Double, _ dblHeight As Double, _ dblWidth As Double) As Double Dim Area As Double Area = 2 * ((dblLength * dblHeight) + _ (dblHeight * dblWidth) + _ (dblLength * dblWidth) _ ) BoxArea = Area End Function Function BoxVolume(dblLength As Double, _ dblHeight As Double, _ dblWidth As Double) As Double Dim Volume As Double Volume = dblLength * dblHeight * dblHeight BoxVolume = Volume End Function
Homework HW1:
Sub Procedure ១
HourW g
។
txtHours txtHWage txtResult btnE&xit
Page 104 of 140
Chapter 4: Creating and Using Form
SETEC University
Management Information System
Private Sub txtResult_GotFocus() Dim dblHours As Double Dim dblHWage As Double dblHours = txtHours dblHWage = txtHWage CalcAndShowSalary dblHours, dblHWage End Sub Sub CalcAndShowSalary(Hours As Double, HWage As Double) Dim dblResult As Double dblResult = Hours * HWage txtResult = dblResult End Sub Private Sub btnExit_Click() DoCmd.Close End Sub HW2:
Function Procedure (Marked Price)
discount (Discount Rate) ។
Function CalculateNetPrice(DiscountRate As Double) As Currency Dim OrigPrice As Double OrigPrice = CCur(txtMarkedPrice) CalculateNetPrice = OrigPrice - OrigPrice * DiscountRate End Function Private Sub cmdCalculate_Click() Dim dblDiscount Double dblDiscount = CDbl(txtDiscountRate) txtNetPrice = CalculateNetPrice(dblDiscount) End Sub
Chapter 4: Creating and Using Form
Page 105 of 140
Management Information System
SETEC University
4.14. Conditional Statements -
-
Here is a summary table of the logical operators we have studied: Operator
Meaning
Example
Opposite
=
Equality to
a=b
Not
Not
Not equal to
12 <> 7
=
<
Less than
25 < 84
>=
<=
Less than or equal to
Cab <= Tab
>
>
Greater than
248 > 55
<=
>=
Greater than or equal to
Val1 >= Val2
<
The If…Then Statement If Condition Then Statement : If Condition Then Statement1 Statement2 ……………… End If
-
The If…Then…Else Statement If Condition Then Statement1 Else Statement2 End If
-
The If…Then…ElseIf Statement If Condition1 Then Statement1 Else If Condition2 Then Statement2 Else If Condition3 Then Statement3 Else Statement4 End If
Page 106 of 140
Chapter 4: Creating and Using Form
SETEC University
Lab:
Management Information System
Conditional Statements:
1.
Password Form
frmLogin
Textboxs: -
txtUserName
-
txtPassword, Inputmask: Password
Command buttons:
2.
-
btnLogin, Caption: L&ogin
-
btnClose, Caption: &Close
code
event On_Click
button: Login
Private Sub btnLogin_Click() If txtUserName = "Sabay" And txtPassword = "1234" Then MsgBox "Welcome to my application" DoCmd.OpenForm "frmHourWage" Else txtPassword = "" txtUserName = "" txtUserName.SetFocus End If End Sub 3.
code
event On_Click
button: Login
Private Sub btnClose_Click() DoCmd.Close End Sub 4.15. Counting and Looping Structures: Looping Structures (Statements) -
computer ។
Th Do Whil …Loop Do While Condition Statement(s) Loop
Chapter 4: Creating and Using Form
Page 107 of 140
Management Information System
SETEC University
E.g.: Private Sub cmdCounter_Click() Dim Number As Integer Do While Number < 46 MsgBox CStr(Number) Number = Number + 4 Loop MsgBox "Counting Stopped at " & CStr(Number) End Sub -
Th Do…Loop Whil Do Statement(s) Loop While Condition
E.g.: Private Sub cmdCounter_Click() Dim Answer As String Do Answer = CStr(InputBox("Are we there yet(1=Yes/0=No)?", "Counter", "1")) Loop While Answer <> "1" MsgBox "Wonderful, we have arrived" End Sub -
Th Do Until…Loop Do Until Condition Statement(s) Loop
E.g.: Private Sub cmdCounter_Click() Dim Answer As String Do Until (Answer = "1") Answer = InputBox("Are we there yet (1=Yes/0=No)?", _ "Counter", "1") Loop MsgBox "Wonderful, we have arrived" End Sub -
Th Do…Loop Until Do Statement(s) Loop Until Condition
-
Th For… ext: For Counter = Start To End Step Increment Statement(s) Next Counter
Note: To stop an endless loop, press ESC or CTRL+BREAK. Page 108 of 140
Chapter 4: Creating and Using Form
SETEC University
Management Information System
4.16. Data Access Objects(DAO) 1. What is DAO? DAO
Programming interface
database
Microsoft
Microsoft Visual Basic
Microsoft Access database
។
2. Recordset object query
DAO
records
database
3.
table
។
Database Object
:
Dim DatabaseName AS DAO.Database E.g. :
Dim db AS DAO.Database
4.
Recordset Object : Dim RecordsetName AS DAO.Recordset E.g. :
Dim rs AS DAO.Recordset
5.
Database Object : Set db = CurrentDb() :
Database Object ដ ើមបីភ្ជាប់
Database
។ Set db = DAO.OpenData Database
s “
th\Fil
m .m
”
Database Object
។
E.g. : Set db = DAO.OpenDatab s “D \My Databases\StudentDB.acc 6.
”
Recordset Object S t rs =
.Op nR
or s t “T
l |qu ry”, R
or Typ
*RecordType: -
dbOpenDynaset: Open in Dynaset
-
dbOpenTable: Open in table (have index)
-
dbOpenSnapShot: Open in snapshot (read only)
Chapter 4: Creating and Using Form
Page 109 of 140
Management Information System
Lab1:
DAO
SETEC University
Form :
Form
-
:
txtSubjectID txtSubjectName txtDescription
btnLast btnNext btnPrevious Database
-
Recordset
:
btnFirst
Option Explicit Dim db As DAO.Database Dim rs As DAO.Recordset Sub showDT() txtSubjectID.Value = rs.Fields("SubjectID") txtSubjectName.Value = rs.Fields("SubjectName") txtDescription.Value = rs.Fields("Description") End Sub -
Database
Recordset
Form_Load() :
Private Sub Form_Load() Dim strSQL As String strSQL = "Select * from tblSubjects" Set db = CurrentDb Set rs = db.OpenRecordset(strSQL) Call showDT End Sub -
code
btnFirst (event: On_Click)
record ១:
Private Sub btnFirst_Click() rs.MoveFirst Call showDT End Sub -
code
btnLast (event: On_Click)
record
:
Private Sub btnLast_Click() rs.MoveLast Call showDT End Sub
Page 110 of 140
Chapter 4: Creating and Using Form
SETEC University
-
Management Information System
code
btnNext (event: On_Click)
record
:
Private Sub btnNext_Click() rs.MoveNext If Not rs.EOF Then Call showDT Else MsgBox "No more record" rs.MovePrevious End If End Sub -
code
btnPrevious (event: On_Click)
record
:
Private Sub btnPrevious_Click() rs.MovePrevious If Not rs.BOF Then Call showDT Else MsgBox "First record reached" rs.MoveNext End If End Sub Lab 2:
records :
btnSearch btnClear
-
code
btnSearch (event: On_Click)
records:
Private Sub btnSearch_Click() Dim strCriteria As String strCriteria = "Select * from tblSubjects Where _ SubjectName='" _ & InputBox("Input SubjectName:") & "'" Set db = CurrentDb Set rs = db.OpenRecordset(strCriteria) If rs.EOF Then MsgBox "No record found!", vbInformation Else Call showDT End If End Sub Chapter 4: Creating and Using Form
Page 111 of 140
Management Information System
Lab 3:
Add record
SETEC University
:
btnAdd
-
code
btnAdd (event: On_Click)
Add records:
Private Sub btnAdd_Click() Dim intSubjectID As Integer Dim strSubjectName As String Dim strDescription As String intSubjectID = InputBox("Input Subject ID:") strSubjectName = InputBox("Input Subject Name:") strDescription = InputBox("Input Description:") With rs .AddNew !SubjectID = intSubjectID !SubjectName = strSubjectName !Description = strDescription .Update .Close End With End Sub Lap 4:
record :
btnDelete
-
code
btnDelete (event: On_Click)
Delete records:
Private Sub btnDelete_Click() If MsgBox("Are you sure you want to delete this record?", vbYesNo, "Delete record") = vbYes Then rs.Delete Call btnPrevious_Click Else Exit Sub End If End Sub Page 112 of 140
Chapter 4: Creating and Using Form
SETEC University
Lab 5:
Management Information System
Update record:
btnUpdate
-
code
btnAdd (event: On_Click)
Add records:
Private Sub btnUpdate_Click() With rs .Edit !SubjectID = txtSubjectID !SubjectName = txtSubjectName !Description = txtDescription .Update End With End Sub
*****End of Chapter 4*****
Chapter 4: Creating and Using Form
Page 113 of 140
Management Information System
SETEC University
Chapter 5 Creating and Using Report Report? Report
Object
MS-Access
query 5.1.
ដេញពី table
។ R port
1.
Report tool:
Navigation Pane,
Table
Query
Report 2.
Ribbon,
tab: Create
Reports,
icon: Report Report
3. 5.2. 1.
Save
Layout View
:
Save report
View Report Navigation Pane, Double-click view
Page 114 of 140
Report
Report
Report View
Chapter 5: Creating and Using Report
SETEC University
2.
Management Information System
Ribbon,
: -
tab: Home
icons
icon: View
Status bar
Report View: Report
Views,
Report
Print
Clipboard
Chapter 5: Creating and Using Report
Page 115 of 140
Management Information System
-
Print Preview:
-
Layout View:
SETEC University
Report
Print
Report
(design) ។
-
Design View:
Report Layout View
Page 116 of 140
(formatting) ។
Chapter 5: Creating and Using Report
SETEC University
5.3. 1.
Management Information System
R port
Report Wizard
Ribbon,
tab: Create
Report,
icon: Report Wizard ។ Wizard
2. 3.
“T
l s
List “Av il
u ri s” ,
t
l Fi l s”,
fields “T
4.
l
query
fields
Report fields
:
l s
u ri s”
Report ។ table
query
table/query
។
Next
Chapter 5: Creating and Using Report
Page 117 of 140
Management Information System
5.
field
6.
SETEC University
grouping levels
fields
Next
(Sort)
Detail records
Next
Page 118 of 140
Chapter 5: Creating and Using Report
SETEC University
7.
Layout -
Stepped:
-
Block:
Management Information System
Orientation
Chapter 5: Creating and Using Report
Report
Page 119 of 140
Management Information System
-
8.
9.
SETEC University
Outline:
Next
Style
Page 120 of 140
Next
Chapter 5: Creating and Using Report
SETEC University
Management Information System
10.
Report
11.
“ r vi w th r port”
Finish
5.4.
Label
1.
Label Wizard
Navigation Pane, Double-click
table
query
Label
2.
Ribbon,
tab: Create
Reports,
icon: Label 3.
Label Wizard :
Chapter 5: Creating and Using Report
Page 121 of 140
Management Information System
4.
Label
5.
6.
Page 122 of 140
SETEC University
Text
fields
Label
Next
Next
mailing label
Next
Chapter 5: Creating and Using Report
SETEC University
Management Information System
7.
field
8.
Report
9.
(Sort)
“S
th l
Next
ls s th y will look print
”
Finish
Chapter 5: Creating and Using Report
Page 123 of 140
Management Information System
5.5.
R port
1.
SETEC University
Blank Report tool:
Ribbon,
tab: Create
Report,
icon: Blank Report
2.
Field List pane, fields
(+)
Table
table
3. Double-click
field
Report ។
fields
key: Ctrl +
fields ។
4.
tools
Control Logo,
tab: Format
,
Report ។ Page 124 of 140
Chapter 5: Creating and Using Report
SETEC University
5.6.
Management Information System
Report Sections:
Report
Report
: Header:
Report Logo,
។
Page Header
។ Report
Group
។
។
Header
Records
។
Records ។
Detail
Group
Row
Record Source ។
Footer:
Records
។
(Aggregate Functions) ។
Page Footer
។ ។
Report Footer:
Report ។ Report
Chapter 5: Creating and Using Report
។
Page 125 of 140
Management Information System
5.7.
Grouped Report
Record Source: 1.
SETEC University
Report Wizard:
Select Query
tables: Customers
Orders
fields:
2. Save query
qryTotal Freight by Shipped Date
Report Wizard:
1.
Ribbon,
tab: Create Wizard Report
Page 126 of 140
Report,
icon: Report Wizard :
Chapter 5: Creating and Using Report
SETEC University
2.
Management Information System
Tables/Queries, Source
3.
query
Record
Report
list “Av il
table
l Fi l s”,
fields
Report
Next
4.
Chapter 5: Creating and Using Report
Next
Page 127 of 140
Management Information System
5.
SETEC University
field
group
-
field (Data Type)
Date/Time
i on Grouping Options …
Grouping intervals
6.
Page 128 of 140
OK Next
Chapter 5: Creating and Using Report
SETEC University
Management Information System
7.
fields
(Sort)
Detail records -
i on Summ ry Options …
Aggregate function (Sum, Avg, Min 8.
Max)
OK Next
Chapter 5: Creating and Using Report
Page 129 of 140
Management Information System
9.
Layout
10.
Next
11.
Style
Page 130 of 140
SETEC University
Orientation
Report St pp
, lo k, Outlin
…
Next
Chapter 5: Creating and Using Report
SETEC University
Management Information System
12. 13.
Report
“ r vi w th r port”
Finish
Lab: េូរបដ្កើត report មួយ េ ូ រូបខា្ដរោម ៖
Chapter 5: Creating and Using Report
Page 131 of 140
Management Information System
SETEC University
Solution:
5.8. Homeworks 1. បដ្កើត tblCustomers ដ ើមបីផ្ទុកព័ត៌មានអ្ំពីអ្តិថិជន
2. បដ្កើត tblProducts ដ ើមបីផ្ទុកព័ត៌មានអ្ំពីផ្លិតផ្ល
3. បដ្កើត tblOrders ដ ើមបីផ្ទុកព័ត៌មានអ្ំពីោរបញ្ជាទិញទំនិញពីអ្តិថិជន
Page 132 of 140
Chapter 5: Creating and Using Report
SETEC University
4. បដ្កើត
tblOrderDetails
Management Information System
ដ ើមបីផ្ទុកព័ត៌មានបន្នែមអ្ំពីោរបញ្ជាទិញទំនិញពីអ្តិថិជន
ដោយរួម
បញ្ចូលទំ្ទំនិញន្ លអ្តិថិជនទិញ ។
5. បដ្កើត relationships រវា្ tables :
6. បដ្កើត frmOrderDetailProductSub ដេញពី tblOrderDetail និ្ tblProducts រួេគណនារក Sub Total និ្ Total ូេខា្ដរោម :
Chapter 5: Creating and Using Report
Page 133 of 140
Management Information System
SETEC University
7. បដ្កើត frmOrders ដេញពី tblOrders រួេទញ Subform: frmOrderDetailProductSub ន្ ល បានបដ្កើតរួេខា្ដលើោក់េូល ូេខា្ដរោម :
frmOrders
frmOrderDetailProducttSub
8. បដ្កើត frmCustomerOrder (Mainform) ដេញពី tblCustomers រពមទំ្គូស combobox មួយសំរាប់ ន្សវ្រកតាមរយៈ ដ្មោះអ្តិថិជន ។
cboSearch a. សរដសរ code បដ្កើត event: After_Update ដលើ cboSearch ូេខា្ដរោម : Private Sub cboSearch_AfterUpdate() Dim SQL As String SQL = "Select * from tblCustomers Where CustomerName='" _ & cboSearch & "'" Form_frmCustomerOrder.RecordSource = SQL End Sub b. ទញ frmOrder ន្ លបានបដ្កើតដ
ើ្ខា្ដលើ ោក់ជា subform កនុ្ frmCustomerOrder
រួេគូស buttons ពីរ ូេរូបខា្ដរោម :
Page 134 of 140
Chapter 5: Creating and Using Report
SETEC University
Management Information System
frmCustomerOrder
frmOrders
btnPreviewReport
btnPrintReport
c. សរដសរ code បដ្កើត event: On_Click ដលើ btnPreviewReport ូេខា្ដរោម : Private Sub btnPreviewReport_Click() DoCmd.OpenReport "rptCustomerOrder", acViewReport, , _ "CustomerID=" & Me.CustomerID End Sub d. សរដសរ code បដ្កើត event: On_Click ដលើ btnPrintReport ូេខា្ដរោម ៖ Private Sub btnPrintReport_Click() DoCmd.OpenReport "rptCustomerOrder", acViewNormal, , _ "CustomerID=" & Me.CustomerID End Sub
សំគាល់ៈ -
acViewReport: សំរាប់ បង្ហាញ report កនុ្ Print Preview
-
acViewNormal: សំរាប់ print report
-
េូរអ្នុវតតេំនុេ c និ្ d ដរោយដពលបដ្កើត report រួេ ។
9. បដ្កើត rptOrderDetailProductSub ដេញពី tblOrderDetail និ្ tblProducts រួេគណនារក Sub Total និ្ Total ូេខា្ដរោម :
Chapter 5: Creating and Using Report
Page 135 of 140
Management Information System
SETEC University
10. បដ្កើត rptOrders ដេញពី tblOrders រួេទញ Subreport: rptOrderDetailProductSub ន្ ល បានបដ្កត ើ រួេខា្ដលើោក់េូល ូេរូបខា្ដរោម :
rptOrders
rptOrderDetailProducttSub
11. បដ្កើត rptCustomerOrder (Mainreport) ដេញពី tblCustomers :
ទញ rptOrder ន្ លបានបដ្កើតដ
ើ្ខា្ដលើ ោក់ជា subreport កនុ្ rptCustomerOrder
ូេរូបខា្ដរោម :
Page 136 of 140
Chapter 5: Creating and Using Report
SETEC University
Management Information System
rptCustomerOrder
rptOrders
12. រដបៀបកំនត់ដលខដរៀ្ដោយបង្ហាញដោយសវ័យរបវតតន៍តាមរកុម (Over Group) :
a. ដបើក rptOrderDetailProductSub កនុ្ Design View រួេគូស textbox មួយដលើផ្ផ្ទ Detail ដោយោក់ដ្មោះ txtNo ូេខា្ដរោម : Chapter 5: Creating and Using Report
Page 137 of 140
Management Information System
SETEC University
b. កំនត់ property របស់ txtNo : -
Control Source: =1
-
Running Sum: Over Group
***End of Chapter 5***
Page 138 of 140
Chapter 5: Creating and Using Report
