Access 2003 VBA Programmer's Reference

982 Pages·2004·13.626 MB·English

by  Patricia Cardoza,Teresa Hennig,Graham Seach, et al.John Wiley & Sons, Inc.|Wiley||WroxAdult NonfictionComputer TechnologyLanguage(s): EnglishOn sale date: 15.06.2004Street date: 28.04.2004Preview

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,[phone],
fax[phone].RequeststothePublisherforpermissionshouldbeaddressedtotheLegal
Department,WileyPublishing,Inc.,10475CrosspointBlvd.,Indianapolis,IN46256,[phone],
fax[phone],E-mail:[email protected].
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