Macros Session 32 34 Session 35 Session 36

Macros Session 32 -34 Session 35 Session 36 -41 • Creating Macro Group • Creating Macro Dialog Access 2003

en. A MS Access VII. Macros va. Ca. Pasab. Ba¢armant. Yna. TIbegáInel, Ón kargaren. Akñúg MS Access va. Gac. CYyey. IgkñúgkarbegáIt Button, Menu cg Object Application b. Ba©Úl. Kñas. Mrab; e. Rb. IR)as; pÞal; el. Ikic©karrbs; ey. Ignig Gacykkmµvi. FIep. Sgmkrt; en. Akñúg Ms Access b¤]bmafaey. Igman e. Rc. Iney. Ig. Gace. Rb. I Macro Object Table Prepared by Sann Sothearath Forms, Reports Access 2003 85

karbegþIt Macro Autoexec ey. Ige. Rb. I Macro Autoexec s. Mrab; d. Me. NIrkarc. Ule. TAeb. Ik Form edays½V en. Ae. Bl. Edley. Igeb. Ik Database. en. Akñúg Database Windows Macro Object New ]Tahr. N_faey. Igman Form Main Switchboard Save Macro edaydak; e. Qµa. Hfa Autoexec Prepared by Sann Sothearath Access 2003 86

karbegáIt Open Button kñúg Database Windows Macro New e. FVI Button e. Qµa. H Open ed. Im, Ieb. Ik Suppliers Macro Window e. FVId. Ucxage. Rkamen. H ³ Action Arguments Open Form Name : Suppliers Save Macro Prepared by Sann Sothearath edaydak; e. Qµa. Hfa Mcr. Suppliers Access 2003 87

karbegáIt Close Button kñúg Database Window Macro New e. FVI Button e. Qµa. H Close ed. Im, Ibi. T Form C kñúg Macro Window e. FVId. Ucxage. Rkamen. H Action Close Save Macro Prepared by Sann Sothearath Action Arguments Object Type : Form Object Name : Categories Save : Prompt Mcr. Close edaydak; e. Qµa. Hfa Access 2003 88

KYrdak; e. Gayc. M²e. RBa. He. Bley. Igman Form b¤ Table e. Rc. Innwgman kar. Rcb. Uk. Rcbl; e. Bl. EdlbegáIt Macro e. Gayva. rebob. P¢ab; Macro to Form eb. Ik Form Ca Design View (Form: Categories) cucel. I Window Menu r. Ycyk Tile Horizontal e. Bl. Edl Form Design and Database Window bgáðajxøÜn. TnÞwm. Kñael. I Screen cucel. I Macro Object kñúg Database Window r. Yccab; yk Macro e. Qµa. H Mcr. Suppliers (edaysgát; Mouse e. Gay. Cab; ) r. Yc. GUsyke. TAdak; kñúg Forms Rtg; k. Enøg. Nam. Yysm. KYr. bnÞab; mk cab; yk Macro e. Qµa. H Mcr. Close e. FVId. Ucxagel. I. e. Blena. Hey. Ignwg)an Button c. Mn. Yn. BIr m. Yys. Mrab; Open Table m. Yys. Mrab; Close Form Categories. Prepared by Sann Sothearath Access 2003 89

Creating Switchboard Prepared by Sann Sothearath Access 2003 90

Macro Group Prepared by Sann Sothearath Access 2003 91

Creating Dialog Box Prepared by Sann Sothearath Access 2003 92

rebobe. FVI³ 1 - begáIt Form Customer Labels 2 - cucel. I Option Group Tool mk. KUsel. IépÞ Form Prepared by Sann Sothearath Access 2003 93

sresrkñúg Label Name (d. Uc. KMr. Uxagel. I) r. Yccuc Next Ne 4 - e. RCIser. Is. KMr. UNam. Yy. Edl. Gñkcg; )an r. Yccucel. I Next but 5 - vay. Bakü Print Labels For: en. Akñúg. Rb. Gb; xagel. I r. Yccucel. I 6 - Select el. Is‘umén Option Group View Properties - All Rtg; Name sresr Print. Labels. For 3 - Prepared by Sann Sothearath Access 2003 94

cucel. I Combo box Tool mk. KUskñúgs‘umén Option Group e. Rkam Speci 8 - cucel. I I want the combo box to look up the values in a table or query 7 - 9 - cucel. I Table: Customers r. Yccucel. I Next Prepared by Sann Sothearath Access 2003 95

e. RCIser. Isyk. Bakü Country r. Yccuc Next Finish 11 - Mouse sþa. Mel. I Combo box yk Properties - Rtg; Name dak; fa Select. Country - Rtg; Enable yk No 12 - bnÞab; mkey. IgbegáIt Macro m. Yyeday Save dak; e. Qµa. Hfa Customer Labels Dialog 10 - Prepared by Sann Sothearath Access 2003 96

r. Ycerobc. Md. Ucxage. Rkam³ Macro Name Condition Action Enable Select Country [Print. Labels. For]=1 Set. Value Item [Print. Labels. For]=2 Set. Value Item Preview . . . Go. To. Control [Print. Labels. For]=1 Open. Report [Print. Labels. For]=2 Msg. Box Argument : [Select. Country]. [Enabled] Expression: No : [Select. Country]. [Enabled] Expression: Yes Control Name : Select. Country Report Name : Customer Labels View : Print Preview Message : To preview or Control Name print labels, you must either pick a country from the list or click All Countries : Select. Country Is. Null([Select. Country]) . . . Prepared by Sann Sothearath Go. To. Control Stop. Macro Access 2003 97

Macro Name Condition [Print. Labels. For]=2 Action Argument Open. Report Name : Customer Labels View : Print Preview Where Condition : [Country]= [Forms] ![Customer Labels Dialog]! [Select. Country] Close Cancel Close Object Type Object Name Save : Form : Customer Labels Dialog : Prompt Object Type : Form Object Name : Customer Labels Dialog Save Prepared by Sann Sothearath : Prompt Access 2003 98

cuc Mouse sþa. Mel. Is‘umén Option Group yk Properties Event Rtg; k. Enøg After Update vay. Bakü Customer Labels Dialog. Enable Select. Country 14 - cucel. I Command button r. Yc. KUsel. IIépÞ Form Rtg; Name dak; Preview Rtg; Caption dak; Pre&view cuc Event 13 - Prepared by Sann Sothearath Access 2003 99

Rtg; k. Enøg On Click vay. Bakü Customer Labels Dialog. Preview 15 - cucel. I Command button r. Yc. KUsel. IépÞ Form Rtg; Name dak; Print Rtg; Caption dak; &Print cuc Event Rtg; k. Enøg On Click vay. Bakü Customer Labels Dialog. Cancel Prepared by Sann Sothearath Access 2003 100

Rtg; k. Enøg On Click vay. Bakü Customer Labels Dialog. Print 16 - cucel. I Command button r. Yc. KUsel. IépÞ Form Rtg; Name dak; Cancel Rtg; Caption dak; Cancel cuc Event Prepared by Sann Sothearath Access 2003 101

Dialog Forms ey. Ige. Rb. I Dialog Form s. Mrab; b. Ba©Últ. Mél. Calkçx½NÐed. Im, IRsg; Tinñn½y (eyagtam t. Mél. Edley. Ig)anb. Ba©Úlkñúg Dialog Form) en. Ae. Bleb. I Form b¤ Report nwg. Gac Print tamlkçx½NÐEdley. Igpþl; e. Gayva. Prepared by Sann Sothearath Access 2003 102

rebobe. FVI³ begáIt Forms “Dialog Form” cucel. I Text box Tool mk. KUs. Ca Text box c. Mn. Yn. BIr m. Yys. Mrab; Enter beginning date m. Yys. Mrab; Enter ending date erob Properties d. Ucxage. Rkam³ + Text box Enter beginning date Text Rtg; lb; Bakünwgecal sresr Enter beginning date - cuc Mouse xagsþa. Mel. I Unbound yk Properties All Name: sresr Enter beginning date Prepared by Sann Sothearath Access 2003 103

dak; e. Qµa. Hfa Sales by Year Dialog begáIt Button OK and Cancel el. I Dialog Form - eb. Ik Dialog Form Ca Form Design View - cucel. I Command Button (bi. T Control Wizard) r. Ycmk. KUr. Ca Button s. Mrab; OK m. Yys. Mrab; Cancel r. Ycerob Properties d. Ucxage. Rkam ³ + Button OK Rtg; - Name sresr cmdok - Caption sresr OK - Control Tiptext sresr Click here to Open (e. Gaybgðaj. Baküen. Hen. Ae. Blyk Mouse Pointer e. TAcg¥úle Save Form Prepared by Sann Sothearath Access 2003 104

cucel. I Event dak; Cursor Rtg; On Click r. Yccucel. I OK Save r. Ycerobd. Ucxage. Rkam³ - yk Macro Builder Macro Name Action Arguments OK Open. Report Name : Sale by Year View : Print Preview Filter Name : Where Condition : [Orders]![Order. Date] Between [Forms]![Sales by Year Dialog]! [Beginning. Date] And [Forms]![Sales by Year Dialog]![Ending. Date] Window Mode : Normal Prepared by Sann Sothearath Access 2003 105

Rtg; - Name sresr cmdcancel - Caption sresr Cancel - Control Tiptext sresr Click here to Close - cucel. I Event dak; Cursor Rtg; On Click r. Yccucel. I yk Macro Builder Ok Save r. Ycerobd. Ucxage. Rkam³ + Button Cancel Prepared by Sann Sothearath Access 2003 106

karbegáIt Shortcut Key kñúg Database Windows cucel. I Macro Object Macro Name ^O Save Action Open. Form Autokeys dak; e. Qµa. Hfa New Argument Form Name: Categories Keys List Keys Syntax ^A c. MNa. M³ ^3 s. BaØa (^) s. Mrab; {F 1} Ctrl s. BaØa (+) s. Mrab; ^{F 1} Shift +{F 1} Prepared by Sann Sothearath Key Combination Ctrl+A Ctrl+3 F 1 Ctrl+F 1 Shift+F 1 {INSERT} Ins ^{INSERT} Ctrl+Ins {DELETE} or {DEL} Del ^{DELETE} or ^{DEL} Ctrl+Del +{DELETE}or +{DEL} Shift+Del Access 2003 107

Creating Find Record ey. Ige. Rb. I Find Record ed. Im, IEs. Vgrk. Tinñn½y. RKan; Et Select el. Ielxk. Ud b¤e. Qµa. HGVIm. Yyen. Ae. Blen. H Find Record nwgbgðaj. Tinñn½y. Ta. Mg. Rsug. Prepared by Sann Sothearath Access 2003 108

rebobe. FVI 1 - ]bmafaey. Igman Table m. Yye. Qµa. H Customer 2 - begáIt Form edayyk Field Ta. Mg. Gs; BI Table Customer eb. Ik Form Ca Design View 4 - yk Mouse cucel. I Combo box (bi. T Control Wizard) ykmk KUrel. I Form en. Ak. Enøg. Na m. Yysm. KYr r. Ycerob Properties d. Ucxage. Rkam³ 3 - - Name : Cust_ID - Rowsource : Customer Prepared by Sann Sothearath Access 2003 109

5 - cucel. I Event Rtg; After Update: cucel. I yk Macro Builder 6 - Save Macro dak; e. Qµa. H Find Record r. Ycerobd. Ucxage. Rkam Macro Name Action Find. Cust_ID Goto. Control Find. Record Prepared by Sann Sothearath Argument Control. Name Findwhat : Customer. ID : =[Cust_ID] Access 2003 110

Creating Menu File New Open Save Close Exit Edit Undo Find Replace New Record Delete Record Prepared by Sann Sothearath Form Categories Customer Labels Dialog Customer Orders Customers Employees Report Catalog Customer Labels Invoice Access 2003 111

File Menu yk Mouse cucel. I Macro tab New View Macro Name erobc. Md. Ucxage. Rkam Macro Name Action Argument &New Run. Command: New. Database &Open Run. Command: Open. Datebase &Save Run. Command: Save &Close Run. Command: Close &Exit Run. Command: Exit Save dak; e. Qµa. H File Close Prepared by Sann Sothearath Access 2003 112

Edit Menu yk Mouse cucel. I Macro tab New View Macro Name erobc. Md. Ucxage. Rkam³ Macro Name &Undo &Find &Replace Action Run. Command Action Argument Command: Undo Command: Find Next Command: Replace &New Record &Delete Record Run. Command: Record. Goto. New Command: Delete. Record Save dak; e. Qµa. H Edit Close Prepared by Sann Sothearath Access 2003 113

Form Menu yk cucel. I erobc. Md. Ucxage. Rkam³ Mouse Macro tab New View Macro Name Action Argument &Categories Open. Form Name: Categories &Customer Labels Dialog Open. Form Name: Customer Labels Dialog &Customer Orders Open. Form Name: Customer Orders &Customers Open. Form Name: Customers &Employees Open. Form Name: Employees Save dak; e. Qµa. H Prepared by Sann Sothearath Form Close Access 2003 114

Report Menu yk cucel. I erobc. Md. Ucxage. Rkam³ Mouse Macro tab New View Macro Name &Catalog Action Open. Report Argument Report Name: Catalog &Customer Labels Open. Report Name: Customer Labels &Invoice Report Name: Invoice Save Open. Report dak; e. Qµa. H Prepared by Sann Sothearath Report Close Access 2003 115

Group Menu yk cucel. I erobc. Md. Ucxage. Rkam³ Mouse Action Add Menu Save Macro tab New View Macro Name Argument Menu Name Menu Macro Name dak; e. Qµa. H Prepared by Sann Sothearath : &File : &Edit : &Form : &Report : Report Menu Close Access 2003 116

P¢ab; Macro e. TA Form eb. Ik Form Nam. Yy. Ca Form Design View 2 - cucel. I Edit Select Form View 1 - Properties 3 Other tab kñúg Rtg; Menu bar vayb. B©Úal. Bakü Menu 4 - eb. Ik Form Ca Form View Prepared by Sann Sothearath Access 2003 117

kar. Rsg; Tinñn½ytaml. Mdab; Gk. Sr Prepared by Sann Sothearath Access 2003 118

rebobe. FVI³ 1 - begáIt Table Customer 2 - begáIt Form Customer Phone List 3 - eb. Ik Form Ca Design View r. Ycerob Properties d. Ucxage. R +cucel. I Data tab Rtg; ³ - Allow Deletions : No - Allow Additions : No + Format tab cucel. I Rtg; ³ - Record Selectors : No - Min Max button : No Prepared by Sann Sothearath Access 2003 119

cucel. I Option Group (eda. H Control Wizard ecj) ykmk. KUsel. I Form Footer r. Yccucel. I View Properties Rtg; Name vayb. Ba©Úl. Bakü Company. Name. Fi 5 - cucel. I Toggle button mk. KUskñúgsu‘m Option Group c. Mn. Yn 27 6 - cucel. I Toggle button TI 1 Rtg; 4 - - Name - Caption - Option Value 7 - : A : &A : 1 e. FVId. Ucxagel. Irh. Utdl; button TI 27 Rtg; ³ - Name - Caption - Option Value : All : A&ll : 27 Prepared by Sann Sothearath Access 2003 120

cuc Mouse xagsþa. Mel. Is‘um Option Group r. Ycyk Properties Event Rtg; After Update cucel. I yk Macro Builder OK 9 - Save Macro edaydak; e. Qµa. Hfa Filter r. Ycerobd. Ucxage. Rkam³ 8 - Condition [Company. Name. Filters]=1 Action Apply. Filter Action Argument Where Condition: [Company. Name] Like "[AÀÁ ÃÄ]*" Condition [Company. Name. Filters]=2 Action Apply. Filter Action Argument Where Condition: [Company. Name] Like "B*" Condition [Company. Name. Filters]=3 Action Apply. Filter Action Argument Where Condition: [Company. Name] Like "[CÇ]*" Condition [Company. Name. Filters]=4 Action Apply. Filter Action Argument Where Condition: [Company. Name] Like "D*" Prepared by Sann Sothearath Access 2003 121

e. FVId. Ucen. Hrh. Utdl; Toggle button TI 27 Condition [Company. Name. Filters]=27 Action Show. All. Records e. Rkam button TI 27 erobc. Md. Ucxage. Rkam Condition [Current. Record]>0. . . [Current. Record]=0 Action Go. To. Control Stop. Macro Msg. Box . . . Show. All. Records . . . Set. Value Prepared by Sann Sothearath Action Argument Control Name: Company. Name Message: There are no records for that letter Beep : Yes Type : Information Titel : No Records Returned Item : [Company. Name. Filters] Expression : 27 Access 2003 122

Prepared by Sann Sothearath Access 2003 123

Convert Macro to Module Access 2003 124