Table Of Content

P1:IML/FFX P2:IML/FFX QC:IML/FFX T1:IML WY007-FM WY007-Sample WY007-Sample-v4.cls February28,2004 13:37 Access 2003 VBA Programmer’s Reference i P1:IML/FFX P2:IML/FFX QC:IML/FFX T1:IML WY007-FM WY007-Sample WY007-Sample-v4.cls February28,2004 13:37 ii P1:IML/FFX P2:IML/FFX QC:IML/FFX T1:IML WY007-FM WY007-Sample WY007-Sample-v4.cls February28,2004 13:37 Access 2003 VBA Programmer’s Reference PatriciaCardoza TeresaHennig GrahamSeach ArmenStein WileyPublishing,Inc. iii P1:IML/FFX P2:IML/FFX QC:IML/FFX T1:IML WY007-FM WY007-Sample WY007-Sample-v4.cls February28,2004 13:37 Access 2003 VBA Programmer’s Reference Publishedby WileyPublishing,Inc. 10475CrosspointBoulevard Indianapolis,IN46256 www.wiley.com Copyright(cid:1)c 2004byPatriciaCardoza,TeresaHennig,ArmenStein,GrahamSeach.Allrightsreserved. PublishedbyWileyPublishing,Inc.,Indianapolis,Indiana PublishedsimultaneouslyinCanada eISBN:0-7645-7166-4 ManufacturedintheUnitedStatesofAmerica 10987654321 Nopartofthispublicationmaybereproduced,storedinaretrievalsystem,ortransmittedinanyform orbyanymeans,electronic,mechanical,photocopying,recording,scanning,orotherwise,exceptas permittedunderSection107or108ofthe1976UnitedStatesCopyrightAct,withouteithertheprior writtenpermissionofthePublisher,orauthorizationthroughpaymentoftheappropriateper-copyfee totheCopyrightClearanceCenter,Inc.,222RosewoodDrive,Danvers,MA01923,(978)750-8400, fax(978)646-8700.RequeststothePublisherforpermissionshouldbeaddressedtotheLegal Department,WileyPublishing,Inc.,10475CrosspointBlvd.,Indianapolis,IN46256,(317)572-3447, fax(317)572-4447,E-mail:permcoordinator@wiley.com. LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESSOFTHECONTENTSOFTHISWORKANDSPECIFICALLYDISCLAIMALLWAR- RANTIES,INCLUDINGWITHOUTLIMITATION,WARRANTIESOFFITNESSFORAPARTICULAR PURPOSE.NOWARRANTYMAYBECREATEDOREXTENDEDBYSALESORPROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOREVERYSITUATION.THISWORKISSOLDWITHTHEUNDERSTANDINGTHATTHEPUB- LISHERISNOTENGAGEDINRENDERINGLEGAL,ACCOUNTING,OROTHERPROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONALPERSONSHOULDBESOUGHT.NEITHERTHEPUBLISHERNORTHEAUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZA- TIONORWEBSITEISREFERREDTOINTHISWORKASACITATIONAND/ORAPOTENTIAL SOURCEOFFURTHERINFORMATIONDOESNOTMEANTHATTHEAUTHORORTHEPUB- LISHERENDORSESTHEINFORMATIONTHEORGANIZATIONORWEBSITEMAYPROVIDEOR RECOMMENDATIONSITMAYMAKE.FURTHER,READERSSHOULDBEAWARETHATINTER- NET WEBSITES LISTED IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHENTHISWORKWASWRITTENANDWHENITISREAD. Forgeneralinformationonourotherproductsandserviceortoobtaintechnicalsupport,please contactourCustomerCareDepartmentwithintheU.S.at(800)762-2974,outsidetheU.S.at(317) 572-3993orfax(317)572-4002. Wileyalsopublishesitsbooksinavarietyofelectronicformats.Somecontentthatappearsinprint maynotbeavailableinelectronicbooks. Trademarks:Wiley,theWileylogo,Wrox,theWroxlogo,ProgrammertoProgrammerandrelated tradedressaretrademarksorregisteredtrademarksofJohnWiley&Sons,Inc.and/oritsaffiliatesin theUnitedStatesandothercountriesandmaynotbeusedwithoutwrittenpermission.Allother trademarksarethepropertyoftheirrespectiveowners.WileyPublishing,Inc.,isnotassociatedwith anyproductorvendormentionedinthisbook. iv P1:IML/FFX P2:IML/FFX QC:IML/FFX T1:IML WY007-FM WY007-Sample WY007-Sample-v4.cls February28,2004 13:37 TomyhusbandRob,togetherwecanclimbmountains.Tomyparents,who’vegiven methefoundationtosucceedinlife;Iloveyou. —Patricia Tomyfamily,fortheirunwaveringloveandencouragement.TothePNWADG, fortheprivilegeofleadingsuchagreatgroupofdevelopers. —Teresa Idedicatethisbook(oratleastmychapters)tomybeautifulwifeJulie-Anne,for carryingtheworldonhershoulderssoIcouldworkuninterrupted,andforher unfailingsupport,encouragement,andsacrifice,despiteillnessandpersonal hardship.Andtomychildren,MadeleineandEli,whotriedtounderstandwhy Daddycouldn’tspendsomuchtimewiththem.Iamindeedaluckyman. —Graham v P1:IML/FFX P2:IML/FFX QC:IML/FFX T1:IML WY007-FM WY007-Sample WY007-Sample-v4.cls February28,2004 13:37 Credits Authors EditorialManager PatriciaCardoza KathrynMalm TeresaHennig ArmenStein VicePresident&ExecutiveGroupPublisher GrahamSeach RichardSwadley ExecutiveAcquisitionsEditor VicePresidentandExecutivePublisher RobertElliott RobertIpsen DevelopmentEditor VicePresidentandPublisher EileenBienCalabro JosephB.Wikert ProductionEditor ExecutiveEditorialDirector AngelaSmith MaryBednarek CopyEditor ProofreadingandIndexing TechBooks TECHBOOKSProductionServices SeniorProductionManager FredBernardi vi P1:IML/FFX P2:IML/FFX QC:IML/FFX T1:IML WY007-FM WY007-Sample WY007-Sample-v4.cls February28,2004 13:37 About the Authors Authors PatriciaCardozaisanExchangeandOutlookApplicationsdeveloper,writer,andMicrosoftOutlookMVP. PatriciacurrentlyservesasExchangeAdministratorandleaddeveloperforPacificSouthwestContainer, alargemanufacturingcompany.Shehasauthoredtwootherbooks,SpecialEditionUsingMicrosoftOffice Outlook2003andAbsoluteBeginner’sGuidetoMicrosoftOneNote2003.Shehasalsowrittennumerous articlesaboutOutlook,Exchange,andwirelessdevicesforavarietyofjournalsincluding.NETMagazine andTechRepublic.Inhersparetime,shelovestocook,read,watchmovieswithherhusband,andspoil herthreecats. TeresaHennigistheownerofEade.com,whichspecializesindevelopingcost-effectiveAccess applications.Herenergyandenthusiasmarecomplementedbyherabilitytoquicklygraspasituation andprovideasolution.Herbiggestrewardsarebeingapartnerwithherclientsandsharingtheirjoyin theirsuccesses.Hersite,www.DatabaseAnswerSite.com,isrecognizedasaresourceforlearningabout relationaldatabasesbyseveraluniversitiesandtechnicalsupportorganizations. AnavidAccessaficionado,TeresaisthepresidentofthetwoleadingAccessusergroupsintheUS northwest:thePacificNorthwestAccessDevelopersGroup(PNWADG)andtheAccessSpecialInterest Group.SheisalsodedicatedtohelpingtheSpinalCordSocietyraisemoneytofindacureforspinalcord injuries.And,shewishesthatshehadmoretimetoenjoydancing. GrahamSeachisChiefDevelopmentOfficerforaSydney-baseddatabasedevelopmentcompany,Pacific Database(www.pacificdb.com.au).HehasbeendevelopingapplicationsinAccesssinceversion1.0,has participatedintheMicrosoftOffice2003betaprogram,andhaspresentedatseveralMicrosoftevents, notablytheMicrosoftOffice2003SystemlaunchinSydneyandMelbournein2003. GrahamholdsanMCPinAccessDevelopment,master-levelBrainbenchcertificationinAccess,andis recognizedbyMicrosoftasanMVP(mostvaluableprofessional).Hehasreceivedthisawardthreetimes. HeregularlyprovidesworldwideAccessdevelopersupportforMicrosoftviatheMicrosoftnewsgroups, andhasbeenpublishedinMSDNMagazine.Graham’stechnicalfocusisnowonAccessandtheSQL Serverintegration,havingprovidedmanybusinesssolutionstoawiderangeofgovernment,military, andprivateorganizations. ArmenSteinistheownerandfounderofJStreetTechnology,Inc.,ateamofdatabaseapplication developersinRedmond,Washington.HeisPresidentEmeritusofthePacificNorthwestAccess DevelopersGroup,andhascontributedtoAccess/Office/VBAdvisormagazineandSmartAccess.Hehas taughtdatabaseclassesatBellevueCommunityCollegeandSeattleCentralCommunityCollegeandalso developedandtaughthisown1-daytrainingclasscalledAccessDevelopmenttheJStreetWay.Armen earnedabusinessadministration/computersciencedegreefromWesternWashingtonUniversity,and hasbeendevelopingcomputerapplicationssince1984.Hisotherinterestsincludeactivitieswithhis family,backgammon,cheeringontheMariners,anddrivinghis1969FordBroncointhesun. vii P1:IML/FFX P2:IML/FFX QC:IML/FFX T1:IML WY007-FM WY007-Sample WY007-Sample-v4.cls February28,2004 13:37 Contributors SteveClarkisaMicrosoftAccessMVP,anMCP,andaProjectManagerandDeveloperfortheFMS ProfessionalSolutionsGroup(www.fmsinc.com/consulting).Asadeveloper,hespecializesindatabase developmentandhasproducedAccess,VB(.NET),andASP(.NET)applicationsforclientsfromall elementsofbusiness,bothnationallyandinternationally.Heearnedabachelor’sdegreeincomputer sciencefromtheUniversityofCincinnatiin1994andhasbeenwithFMSsince1998andanAccessMVP since2001.Whenhe’snotmanagingordeveloping,heenjoysparticipatinginallformsofmotorcycle racing. BrianM.SockeyisfounderandpresidentofFarsightSolutions,Inc.(DBATeleVantageNorthWest),a MicrosoftBusinessSolutionspartner,andvalue-addedresellerofArtisoftTeleVantagesoftware-based businesstelephonesystems.BeforestartingTeleVantageNorthWest,BrianworkedintheDeveloper SupportgroupatMicrosoft,wherehespecializedinclient/serverdevelopmentissuesinvolving MicrosoftAccessandSQLServer.Brianenjoysthegreatoutdoors,theater,andgoodfood(almost everythingexceptbluecheese). RandallJ.Weers,thefounderandpresidentofProcia,Inc.,specializesinprocessanddatamanagement applicationsutilizingtheMicrosoftOfficesuite.Hisprimaryfocusistohelppeoplemanagebusiness processesandbusinessrulesthroughtheappropriateapplicationofprocessimprovementand automation.Randall’sexperienceincludeseverythingfrombusinessprocessanalysistotechnicaland trainingmanualwriting.RandallmakeshishomeinSeattle,Washington,whereheisthefacilitatorforIT WorkGroups,aPugetSound-basedallianceofsenior-levelindependentinformationtechnology professionals. SamRadakovitzjoinedMicrosoftin1998andwasamemberoftheAccessteamforAccess2000,2002,and 2003.Alotofhiseffortswerefocusedonwizardsandsecurityfeatures.ForAccess2003,Samwas thoroughlyengagedinthenewmacrosecurity.Thismadeitaperfectfitforhimtoreviewthisbook’s securitychapters,especiallytheoneonmacrosecurity.WhencreatingAccessapplications,Samenjoys goingoutsidetheboxandaddingabitofcreativity.So,beinginvitedtoprovideapplicationsthatcould demonstratesomeofAccess’snewfeaturesseemedlikeanawesomeopportunitytosharehisideaswith otherdevelopers. viii P1:IML/FFX P2:IML/FFX QC:IML/FFX T1:IML WY007-FM WY007-Sample WY007-Sample-v4.cls February28,2004 13:37 Acknowledgments We’dliketothankallthefolksatWileywhomadethisbookpossible.BobElliottworkedwith PatriciaandTeresatobuildtheamazingteamofauthorspresentonthisbookaswellastogetall themoundsofpaperworkstraight.EileenBienCalabroshowedincrediblepatienceformatting, organizing,andeditingourchapters.Shereassuredusthatwecouldgetallthisdoneanddone well,inaremarkablyshorttime,aslongaswefocused.Ourtechnicaleditor,MichaelStowe, tirelesslycheckedallofourfactsandgentlysteeredusbackoncoursewhenneeded.Techbooks InternationalPvt.Ltd.workedincrediblyhardtoformatallthepagesandworkwithallofthe figuresinthisbook.Thankseveryone! —TheGroup ThankstoallwhohadtoputupwithmewhenIhadtomeetdeadlines.Myhusband,Rob, enduredmanynightsoftakeoutandseveralweekendswhenIwasgluedtomycomputer.My parents,JoeandSally,havesupportedmeineverythingIdoandIthankGodforthemeveryday. IwouldnotbewhereIamtodayinmycareerifitwerenotfortheMicrosoftMostValuable Professional(MVP)program.Iamhonoredtobeincludedinthatesteemedgroupof professionals. Lastly,I’dliketothankTeresaHennig.Shehandledthemajorityoftheschedulingofchapters andhelpedmekeepalloftheinformationforthisbookorganized.Coordinatingfourauthors andseveralcontributorstakespatienceanddedication.ThanksTeresaforallyourhardwork. —Patricia First,I’dliketothankPaulEadeforbeingsuchanincredibleresourceandwealthofknowledge. Youareanamazingfriend,andIcherishtheopportunitiestolaughandjustbesilly. Ican’tsayenoughabouttheMicrosoftAccessTeam,includingRitaNikas.Theirpassionand dedicationhasmadeAccessanincrediblypowerfulprogram.Aspecialnoteofappreciationto BillRamos,TimGetsch,andSanjayJacobforinvestingsomuchofthemselvesintofindingout whatdeveloperswant,formakingtheADEsoawesome,andforbeingsoresponsivetoallofour requests!IalsowanttothankMichaelKaplanforsharinghisopinionsandencouragementand fordoingsomuchfortheAccessgroups. ToeveryoneI’vemetthroughthisbook...thankyouforanamazingopportunitytoenjoyanew challengeandtohelpAccessdevelopers!VeryspecialthanksgotoPatriciaCardoza,whoseems likeWonderWoman—balancingacareerandfamilywhilewritingmultiplebooks.Shenotonly wroteseveralelements,butalsoreviewedeverychapterandhelpedusallincountlessways. AndthankstoRandyWeers,whoofferedhelpwhenIneededit.Yes,workshouldbefun!And lastthanksgotoalltheMikesandMichaels.Ican’timagineaworldwithoutMichaels! —Teresa P1:IML/FFX P2:IML/FFX QC:IML/FFX T1:IML WY007-FM WY007-Sample WY007-Sample-v4.cls February28,2004 13:37 Acknowledgments Needlesstosay,Iwanttofirstthankmywifeandchildrenfortheirsupport,encouragement,and understandingthroughoutaverydifficultanddemandingtime. IwanttothankRitaNikas,myMicrosoftMVPLead,whofirstmademeawareofthebook,and whoseresolutedeterminationtosolveproblemsandtosourcemuchneededinformationwill alwaysbeappreciated. IalsowanttothankMikeGunderloy,who,despitethefactthatwehadnevermet,kindlysent pagesfromhisownbook,tohelpmewithmine;agestureIwillneverforget. Finally,IwouldliketoexpressmythankstoProfessorRogerBoxofCharlesSturtUniversity, whogavemea2-weekextensiononmyfinalassignment,soIcouldmakeabookdeadline. —Graham Iwouldliketoacknowledgetheencouragementandsupportfrommywife,Lori,andkids, LaurenandJonathan. —Armen I’mgladthatIcouldhelpRandyandTeresa.Itiscooltofeaturesomeofmyworkonthebook’s Website.IwanttothankmygirlfriendDeniceandmyparentsforputtingupwithmylong hoursofworkandextrawork!Withouteveryone’ssupportandunderstandingmydailylife wouldbemuchmoredifficult! —Steve MythankstoTeresaHennigforgivingmetheopportunitytocontributetothisbookandfor crackingthewhipwhenIwasstrugglingtokeepmovingforward.MydeepappreciationtoSam RadakovitzfortheinvaluableinsightintothenewsecurityfeaturesofAccess.Thanksalsotothe membersinITWorkgroupswhohelpedmedeliverabetterpicturefortheAccessSecurity model.Andmostofall,inlovingmemoryofmywifeLisa,Idedicatemyeffortsonthisbook. —Randall x