Getting Started with MS Access Visual Basic for Applications - VBA Code-Behind and Modules

This episode is for those of you who have spent some time making forms and reports, and have started looking over the fence at VBA and how it could help you in your project. This introduction to VBA shows the two main ways that VBA is used in Access, through "code-behind" on forms/reports, and in module code. I'll show you how to find events on your form, how to code in an Event Procedure, and how to make your own custom subs and functions. I'll also introduce you to function visibility, and where and when you can use your functions, in modules and in code-behind. Buckle up and let's go check out VBA.
Related Videos:
Getting Started with MS Access Visual Basic for Applications - VBA Code-Behind and Modules
You are watching this video now!
VBA MsgBox - How to use message boxes in MS Access
• VBA MsgBox - How to us...
How to Use a Do Until Loop in MS Access VBA
• How to Use a Do Until ...
How to Use Iif in Microsoft Access
• How to Use Iif in Micr...
How to Use the Replace Function in Microsoft Access
• How to Use the Replace...
How to Use Nz in Microsoft Access to Handle Null and Empty Values
• How to Use Nz in Micro...
Iif, If Then Else, and Select Case in MS Access
• Iif, If Then Else, and...
How to Use DLookup in Microsoft Access
• How to Use DLookup in ...
How to Create and Configure a Custom Pop-Up Form in MS Access
• How to Create and Conf...
For developers looking for a new role, check out and sign up:
www.toptal.com/qKaO2b/worlds-...
Needing to hire technical resources for your project? Get the best:
www.toptal.com/qKaO2b/worlds-...
Want to get access to premium content made just for you and have a chat with me? Find me on Patreon :
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.com/posts/how-to-...
Want to work with me 1:1? Contact me today and book your free 20 min consultation!
Contact form you can find at www.mackenziemackenzie.com/
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksynergy.com/fs-bin/...
Got a KZbin Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy.com/seanmackenz...
#msaccess #vba #dataengineering
• Getting Started with M...

[@khabathawrami]

Your videos about Ms Access and VBA are great .. Thank you

[@seanmackenziedataengineering]

Glad you like them! Thanks!

[@jamesmartinez9069]

Magnifica enseñanza, diez mil gracias señor Sean

[@seanmackenziedataengineering]

Me alegro de que haya ayudado. Gracias!

[@user-wt1lz4vt1b]

Thanks for the great effort. I need to paste a value into a selection rows in the table through VBA Code

[@seanmackenziedataengineering]

Thanks! You could try just doing an update query: kzbin.info/www/bejne/b3Slqad4rtGGrKc You can use the VBA in this video that shows how to update/add a value via VBA code: kzbin.info/www/bejne/p6eQqaVor6d2eqM In the line rst.Add, use rst.Edit instead. Make sure you put an if statement in there too (if you need it) ie. if MyType = 'ABC' Then rst.Edit rst!MyField = SomeValue rst.Update End if

[@davegoodo3603]

Hi Sean, I'm taking a walk through this playlist. I have VBA experience in Access but I want to do an intro to see if there are gaps in my knowledge. I hope to do the Intermediate and Advanced as well so that I'm really prepared for a project I'm involved in. I've checked out some of your videos and I feel confident that you will have the info I'm looking for. Thanks as always, on with the show! I watched this intro video Sean, you do set a fairly steep learning curve. The focus was mainly on visibility and getting the difference between Public & Private and how that relates to forms and modules. You didn't mention your prefix "p" for public variables, if I was brand new I'd wonder what that was. Anyway, I'm sure an intro to anything is going to be challenging because there's so many bases to cover. I don't want to sound too critical, your relaxed style always makes the content easy to follow even if at times it can be distracting. I'm going to continue with the next in the series. Thanks Sean.

[@seanmackenziedataengineering]

Hey Dave, many thanks and I'm always interested in constructive feedback. I concede that my methods can be a bit jarring. Generally I try to avoid providing an encyclopedic, classroom style of learning that you can find everywhere and instead focus on breakthroughs. If I am an analyst working on my prototype, what can I do right now? What are the levers I can apply? In broad strokes, how does it all come together? Will this technique even get me where I need to go? How can I go from A to B without going into the minutiae of why (right now)? My style comes from decades of office and shop-floor mentoring of professionals who are already mid-stride in any task. I provide the breakthroughs in situations where I know smart, tech-savvy learners will actively research documentation for detail anyway. Again, I always appreciate the feedback! Since I made this video I am trying to make the lessons more approachable while still focusing on breakthroughs.

[@davegoodo3603]

@@seanmackenziedataengineering Hi Sean, Understood. I now know more about your presentation style, thank you. Thanks for getting back to me, I really appreciate that.

[@garcimat]

Your videos are the best

[@seanmackenziedataengineering]

Thanks! Glad you enjoy them, cheers

[@matthewb5235]

Hi Sean. I appreciate your videos, so thanks first of for making them available. That's getting started, but where would you suggest going to to find a course in VBA? Any thoughts? Udemy? CodeAcademy? A college online course? Are you aware of any you like? There are so many. And I should perhaps add, a course that you provide but I think you might not be focused of the basics so to speak, but coding techniques for more experienced Access users.

[@seanmackenziedataengineering]

Great question - there are lots of good resources out there. I have found that people generally do better if they pick some kind of project to do, then learn concepts, researching as they go here on YT, stackoverflow, and other programming sites. VBA is great, but you should also learn database concepts early in your study. Soooo many programmers learn how to program in (insert language here) and work for years, then are blindsided on the first time they have to actually design a database or data repository for a project. Pay attention to your data concepts as you go! Any course from a reputable source will be good to get you started with VBA; just complement it with a data project of your own.

[@finvictho8484]

Can I set Access talk off (no any prompts from access )while using Querry update function.

[@seanmackenziedataengineering]

DoCmd.SetWarnings False DoCmd.RunSQL "Update MyTable Set Field1 = Forms!MyForm!txtMyField Where ID = 123;" DoCmd.SetWarnings True

[@charleskent6795]

This is a excellenf intro to VBA. One question: at 18.49, what did you do to "run the function" and open the OUR_MODULE window?

[@seanmackenziedataengineering]

Thanks! At 16:55 I go to the Create ribbon and then Module. This creates a general module that by default has Sub and Function code blocks that you can use throughout your app. Unlike the button click event code we created earlier, you can call them from code in your form modules' click events etc. This allows you to use one function many times ("code re-use") instead of copy-pasting code over and over. This makes your code more efficient and easier to maintain.

[@charleskent6795]

Thank you for your speedy respose! . I understood that. My question is more about navigation. At just before 18.49, running your video at 1 quarter speed I saw your mouse movements, hoping to see what you pressed to make the module code window pop up, but it suddenly popped up without me seeing what you did. I am completely new to access, and learning it to wean myself off Visual FoxPro.

[@seanmackenziedataengineering]

@@charleskent6795 Ah ok! I had the code window minimized and just brought it back to focus. However, you can use Ctrl+g to bring the Immediate Window to view which in turn brings your code editor up as well. I ran it by putting the cursor within the code lines of the Sub and then pressing play on the toolbar. This only works for Subs, which you can also run by simply typing their name in the immediate window and pressing enter. If your sub has arguments then you should just use the immediate window. To run a function, put ? just before your function name and with () or arguments to execute that function. You can also use the Immediate Window to get a value from your open form or to run an expression for basically anything. Ex. ?Forms!MyForm!txtMyTextForm will return that value. Good for finding excess spaces and funky entries.

[@charleskent6795]

Brilliant. Thank you so much. Things like that take ages to find out and are really useful.

[@krishnakuraku6853]

Can you please do a video on how to write and execute stored procedure or function in python..?

[@krishnakuraku6853]

This is related to snowflake

[@seanmackenziedataengineering]

That is a great video suggestion. Stay tuned!

[@seanmackenziedataengineering]

kzbin.info/www/bejne/mHy0mIekn8eYl68 Thanks for the request! Cheers