- [Instructor] Hi, and welcome
back to myexcelonline.com. Today, we are going to go
over how to write a module in Microsoft Access. Now, a module is simply
a collection of VBA code that is used to do certain actions inside of Microsoft Access. Now, VBA stands for Visual
Basic for Applications, and it is the coding language
behind Microsoft Access as well as other Microsoft
products, such as Word and Excel. If you want to learn more about
Microsoft Excel and Office, join our Academy Online Course, and access more than 1,000
video training tutorials so that you can advance your level and get the promotions,
pay raises, or new jobs. The link to join our Academy Online Course is in the description. So how do I go about running code inside of Microsoft Access? Well, usually code is run off of a form. So I'm going to go into our Main Menu using the Parts database that we've been using up until now, and I'm going to right-click
and go to Design View. Now, in here I already
have a button right here, and I can click on this
button and run some code, but if I were to go up to Form Design and select the text box right here, I can click and draw on my
form and create a text box. So let's say I would like to run some code after I enter some data
inside of my text box. If you look over here at the
Property Sheet for my text box, you will see that there's
a tab here called Event, and this is a very important tab. When I look through the
options on the Event tab you will see some actions that can be taken on this text box. I have On Click, Before
Update, After Update, On Change, et cetera. These actions are called events, and an event happens when a
user interacts with the form. So if I were to click on
the text box on the form I would be running the On Click event, but in this case I want to do something after I update the text box, but one thing I wanna do before that is go over to this All tab, and just rename the name of
this text box to txtTestCode, and then I'm going to go
back to the Event tab. So if I go down here to After Update, and I go over here to these three dots, I will have three options come up: a macro builder, an expression builder, and a code builder. In the case of a module,
I want to double click on the code builder, and you will see that I have been taken to the Visual Basic for
Applications window. And let's go over what this is right here. Do you see the words
Private Sub and End Sub? This is a subroutine, and
what is the subroutine for? Well it's for the text box, txtTestCode, and the information following
this underscore here is the event for this text box. So in this case, it's txtTestCode,
the After Update event. Also, if I go up here
in this little dropdown, you can see everything
that I have on this form, ad if I click on this dropdown you can see all the events that I have for my selected text box. So let's do something right here. Let's send a message box
that says, "Test Code," and then I'm going to close this window, and I'm going to right click on my form, and I'm going to go to Form View. Now I'm going to type in
this text box, "Test," and hit Enter, and you will see that a
message box did appear as soon as I hit enter,
because after I hit enter I am updating this text box right here. And so I'm just going to say okay. Now I'm going to go back to Design View, and I'm going to go
back to my code window. You can also, on your keyboard, hit Alt + F11 and get back
to the VBA window as well. If you are liking this video,
please give us a thumbs up and subscribe to our channel, and hit the bell button to get notified when we release our weekly videos. So let's look at a few things over here. You can see my forms that I have some code for
over here in this window which is called the Project Explorer. Down here, this is called
the Immediate window. I can keep track of variables in my code, but that is a little outside the scope of what we're doing today. And then this box right
here is the code window that we were just talking about. So if I wanted to run the
same code over and over again, what I could do is take
this message box code, and I'm going to hit Ctrl
+ X to cut that code, and then I'm going to
right click over here and go to Insert, Module, and then I'm going to
look at this window here. This window here that appears is the code
for this module itself. So let's see, if I put here
"public sub SendMessage," and then if I paste in the
code I have cut earlier, what I have done here
is I have put my code into a central location, into a subroutine called Send Message, so if I go back to the
code for my main menu form, and this time, instead of
putting the message box, I'm just going to write SendMessage. And let's close this and see what happens. So when I right-click
here and go to Form view, I'm going to put a 1 after
my test and hit Enter, and you can see I am getting
my message box again. But why would I want to put my code inside of a module instead of in my form? Well, the reason I would want to do that is if I want to reuse the
same code over and over again. So I have a PartsForm here, I'm going to right-click on
this and go to Design View. And on this Parts Form, I'm
going to go up to Form Design, click on this button right here, and just draw a button
on the top of my form, and I'll just cancel out
the wizard that comes up. And if I go to the On Click
event here for this button, and hit these three dots,
and select Code Builder, this time I'm going to put in
the SendMessage code again. And I do wanna point out
one thing right here, I forgot to rename my Command button, so I got the default name of Command14, and that isn't really self-explanatory, so that's the reason why
you want to name the objects that you put on a form before
you write code for them. But I wanted to show you what
happens if you didn't do that. So if I close out of here and I right-click on the
PartsForm, and go to Form View, and click the button, you can see I'm now running
that same exact code. So I'm going to go back to Design View, and click these three dots right here to get back to my code. Now, see my Module1 right here? If I double-click on that, that's where my SendMessage code is. So I'm going to go up to
the Save button right here, and I'm being asked if I wanna
save the changes to my form. Sure, I'll save those, but look right here, Module:
Module1 is also being saved, so if I say yes, I will now
be prompted to name my module, so I'm just gonna call this
TestModule and say okay. And now when I close this
window and I go back to Access, you can see that my code for the module is actually appearing here
underneath my objects. So if I double-click on TestModule I will be brought right back
to that SendMessage code. So if I change this code
to put three Z's after it, and I save this, and I
go back to my database, when I run my Parts form
by going to Form View, and I run this, you can
see I'm getting the zzz, and if I say okay, and
go back to my MainMenu, and I update this text box,
I'm also getting the zzz. So I only had to change
my code in one place in order to have that
change show up everywhere where I'm calling the
SendMessage subroutine. There is so much more
you can do with VBA code, the purpose of this video was just to give you an introduction of what VBA is and why you might start wanting to use it. We will have many, many
more VBA codes eventually in our Access series, so keep a lookout for
those sometime in the Fall. As always, if you have
any questions or comments, please leave them below
and we'll get back to you. Thanks for watching, and
see you again next time. If you want to learn more about
Microsoft Excel and Office, join our Academy Online Course, and access more than 1,000
video training tutorials, so that you can advance your level and get the promotions,
pay raises, or new jobs. The link to join our Academy Online Course is in the description. - If you liked this video,
subscribe to our YouTube channel and if you're really serious about advancing your
Microsoft Excel skills so you can stand off from the crowd, to get the jobs, promotion,
and pay rises that you deserve, then click up here and join our
Academy Online Course today. (bright music)
