Docs457998200542

Page 1

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]<Date(),“OVERDUE",IIf([Due Date]=Date(),“Due",“Not Yet Due"))

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,

á&#x;”

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 á&#x;”

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 á&#x;”

Page 73 of 140


Management Information System

-

Layout View:

SETEC University

form

(formatting) á&#x;”

-

Design View:

form

(formatting)

Layout View

4.3. 1.

Form Navigation Pane,

Page 74 of 140

á&#x;”

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 á&#x;”

fields

key: Ctrl +

fields á&#x;”

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 á&#x;Ą 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) á&#x;”

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) á&#x;”

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 á&#x;Ą:

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) á&#x;”

-

Design View:

Report Layout View

Page 116 of 140

(formatting) á&#x;”

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


Turn static files into dynamic content formats.

Create a flipbook
Issuu converts static files into: digital portfolios, online yearbooks, online catalogs, digital photo albums and more. Sign up and create your flipbook.