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

how can i get started with vba in access why would i want to use vba and access and when can i use vba and access and you know how do i go about getting started using vba and access i think these are all great questions and for those of you that are just on the edge and ready to take that step off to start your programming this video is for you i'm your host sean mckenzie thank you for joining me once again on my channel on data engineering in this episode we're going to talk about how to use vba and access in different ways i'm going what i'm going to show you is the two most important ways to use vba and those are using it in you know your form and report sort of code behind in the in the class modules behind there and also in regular modules where uh you know your code is available to all parts of the program and so without further ado let's get to our vba in access need help for coaching on your project make sure to check out my patreon the link is in the description this is a blank access file as you can see i've created this new file called vba.accdb and i'm going to grab a table from another file that we've used many times but i wanted to create a blank document for this demonstration because we're going to start from scratch and this is kind of like you know like i said before what i wish someone had said to me when i started programming with vba so i've grabbed a table i'm going to close that and now we have this table if i double click you can see it's a simple table with some candies a candy type a name and a price and so what we're going to do in exploring vba is that we're going to we're going to first create a couple of forms because i want to show you guys the code behind and how it works with with forms and so the first thing i'm going to do is i'm going to show you code behind and then the second thing later in the video i'm going to show you how how modules work and uh and that's how we're going to progress here so i just used the wizard there and i created this simple form as you can see and the biggest thing you need to realize to get started using vba is that everything starts with events so i'm going to create another form here i just created a blank form in design view that has nothing on it and i'm going to save this you know i'll call it my empty form so that now we have a candy form which we can open and it has candies and then we have a empty form which if we wanted to we could open so the empty form it has nothing on it candy form is bound to to a record set this one the empty form is not bound to anything and as you can see if i click on that form and i go over to the right here in the properties there's all these events these are called events and so these are all different ways that can trigger that you can trigger some code or a macro to run so in my other videos i've covered on current on load and click you can check out that video but in general there's a whole bunch of form controls or form events there and events are are everything you need to know about kicking off your uh you know vba procedure is something you want to happen and so as you can see there's all these events you know after the form updates you know before it updates even keystrokes if you want to have some little bit of code happen on keystroke there's also you know on close activate and all of those events are available to the form but they're also available to each of the controls so you know each text box will have events and a combo drop down has an events a click button has events and so and so what you'll see is you know all of those events are the things that you have to choose from to make things happen like a pop-up message box or navigate to another form and you could use a macro like you know so this is the macro area um you know a macro is a very simple way to have some actions happen without writing any code and in fact macros are very powerful but generally what happens is you know people start to use macros you can build all kinds of crazy cool macros and then you can assign those macros into the events too but generally what happens is as you get more and more advanced using macros and things like that you just naturally gravitate toward building code and so um because the macros are actually just code in in behind that have been sort of automatically built for you but they lack a whole bunch of things and they're not as flexible as using you know using vba so when i click the ellipsis and i choose that code behind or the you know for the code builder you can see up at the top here um it says you know form candy form code and so this is what's known as the the code behind and it's really cool because this code is that you see in this particular document is just for that form so it handles the events that happen on that form and so you can have you know form uh code on there you can have your you know your little text box or you know your drop downs when they've been updated somebody selects something and you kick something off that all happens here and so in this case the form current event is when you move from record to record in the form it kicks off this event so what i'm going to do is i created a message box that says hey you moved to this record to show you now if i go from design i go to form view the first on current fires so it there's my white chocolate it fires when this form opens and so and and when i move to another record you can see the little play button down at the bottom you can see it saw the purple fizzers and if i go again it goes to cherry bomb and you can see on the left hand side of the form there's a little arrow and that shows you where you are on your form which record you're on and so each time you move it fires off that form current event and so all we did was we clicked the ellipsis and went into our code here and we said on form current you know have this message box come up and sort of like what we see if i go back to the code here you know that created the little subroutine that's where it says private sub that handles that event that's what it's called and so in the drop down here you'll see the form plus all of the controls on the form like text boxes and stuff and then when you choose something from that first list it updates the second list to give you all of the events that are on there so if you have a button for example there will be an on click event which is the most common one that's used so if you have an on click event then whatever you code in there happens when the button is pushed and so if i go back to design view here and i grab a button and then i can drop that onto this form here up in the header and i'll cancel the command button wizard here and what i'm going to do is i'm just going to you know rename this so that it has a name that makes sense like you know command hi so this is again this button is going to say hi we're going to do something really simple here and then under format under caption i could put you know say hi and then when i tab off of that you'll see the button in the background here changes so very very simple now there's a button on the form and how do we write vba for that so um and all the controls on that forum also have their own their own events but in this case we've got the button selected we click the ellipsis click the code builder and now you can see it's created this nice little sub here called cmd high underscore click and we'll do the simplest thing possible we'll say message box hi so it's just going to say hi when we click that and now if i go to my form view and i click on it then you can see that it gives a little message box that says hi and so that's the starting point for using vba is understanding that everything starts with events so there's all different kinds of events that can happen and each object type has its own set of events so text boxes are different from buttons and you know forms are different from you know text boxes and everything has its own kind of events that are built into it usually and so in access you know so if you create a text box and you know i'll i'll create this one and i'll say this is the current uh current product you know so whatever is in our list there whenever we move to a new record we're going to show you know we're going to show that the name of that candy in this text box and just as i did with the command button i'm going to give it a name that makes sense so txt product for text box and it is an unbound control so it's just a tech empty text box sitting on the forum and now uh now if i you know i've got a you know i've got a high button there but now i'm also going to change it so that the form current which we used already which is the event for when you scroll from one record to the next so as it scrolls to the next record on current happens when it's got there and it's loaded up that record already and you can tell that your your control is being recognized if you type a dot after it and you get the intellisense which is that little drop down then you know you got it uh that they're connected and so uh in this case we can just say me exclamation txt product is equal to me candy name which is a field and so it's going to grab the value from the field and put it into that text box and so now if i uh if i go ahead and open this form in in form view if i scroll from record to record you can see that that text box up above gets loaded and that's because the on current event of the form is fired so it's really cool there's all kinds of uh you know events that are attached to different kinds of objects and controls that you can put on there and it can really give you a lot of power over your programming once you understand how all the the events work now there's another way that you can use the the code behind and that's just to create your own subroutine and that's so we'll say sub and we give it a name so we want some stuff to happen you could have like a thousand lines here of code or one line and what we're going to do is we're going to make a message box that says me you know candy name and then and then we'll put a comma and then the price say and now by not having this attached to our our form uh like the form itself or any controls what this does is it allows us to create some code that can run and we can call this code from several different places within this like set of code this document of code here and so um so this save product and price we can take that and we can you know we can replace you know in our button say we'll just paste that in and what that does is it allows us to create one function so say you had a calculation that got all this different data from all over and and then gave you a value that you wanted to stick in a in a field or something well you could reuse you know that calculation over and over again in all different functions and code in your form by just having it in one place in one subroutine so here we go if i click say hi now you can see it it grabs the light toffee 23 because that as you can see the little record indicator there says that that we're on that record that's a light toffee in 23 and if i go scroll down to say 15 and and click it now you can see it changes and it gets the data from there and so we've used our little custom subroutine to to give us some data back and so the first thing to remember from this is that you can use the events to write code and the second thing to remember is that you can write your own subroutines or functions and you can use those in multiple places in your code behind however those subs that you create are they're sort of like bound to that little form in the code behind of that form and so they're not visible to other parts of the program so what if i go to our empty form here and i throw a button on there just like we did before and i'll give it a name i'll call it you know command say you know product and price just like we did on our other forum so i should be able to you know take that you know the custom subroutine that i just created that gives you know the product and the price i should be able to put that in here too right so let's go and take a look so basically if i you know i've got one button on this forum and now if i go and you can see this is the the the subroutine that we created with a you know custom sub there we've called it there and there's the sub down below and so um if i go on this form here and i you know create an event just like we did on the other one i'll click on the button so that i see the events for that button go to the ellipsis and the code builder and all i should do here is just paste that in right well let's just see so we'll paste in that say product and price that we created on our other form there and i'm going to go and i'm going to open this form and then i'm going to click the button and see what happens well we can't use it there so you can see it says it's not it's not defined and that's because the the subroutine that we created belongs to the other form and we can't see it here but you're going to have other places in your program that you want to see something like that so this is the the code for the empty form that we created there but the function that we wanted to call or the subroutine was in the candy form and so this is where you run into the visibility differences and this is where we're going to create a module so go to the create ribbon and click module and this is where modules are very handy is that modules have a you know a big set of code whole bunch of subs a whole bunch of functions in them that are available everywhere so those are you can see in the window there are two form codes plus the module and you can see this function here that we created we're going to cut that out of our our form code and we'll go to our our module code and we're going to paste it in there now we cannot use the me for our references here uh for our the for for our expression i should be clear so we're using a an expression and so we're going to put in forms candy form to make an expression so now this is a function in our module and it's going to look into that other form there in order to to get you know to to get the values and so i'll just save this i'll just call it our module so this is a this is a module that is available the function is available the default is a public function now you can make this into a private function which i'll show you um but if i run it oh i guess i have the form so the form is not open right now so here we go i'll go back i'm going to open that form i'll go to home and open the form now the form is open so when we run that function the nice thing about modules is too is that you can stick your cursor in you can just hit play on subroutines and they'll just play and they'll just execute so it says white chocolate 15 and that's exactly what we wanted to see there so now we've got a function or i should say a subroutine that is in a accessible module that's accessible to you know all the different parts of the program and now i can put that name back in in my form code and and now it should execute when i click that button so now if i go back to the empty form and i click that you can see now this form is getting data from the other form because we call we called a general function from the module and so that's basically how that part of it works and if i go choose a different one on the other form and then i come back to this form and open it you can see it grabs the right data that we wanted and so that's how you can see using that module instead of the code behind is really important so you can see the module there in the left and you can see our forms as well and you know that the code behind for the forms belong to the forms and the the code that's in our module which is a module is available throughout the program and so that makes the re reusability of code a lot better a lot more you know it's better to always reuse code when you can so there's our module in our list so these are all the windows that are open and you can see the two form codes are there with their events and then you can see if you right click on the the name of the of the subroutine it'll take you to that subroutine in the module so that's a neat little shortcut for you so now you can use the default is is public sub so that means that it's available throughout the program but if we changed it to private and we tried to run our subroutine now you can see that it can't find it it doesn't know what that is because we put private in front of the name of the funk function or subroutine in our module so that means that you can make functions in subroutines that are only accessible within that module and sometimes that is something that you want to do and in this case we want this little subroutine to be able to be available throughout the program and so we're going to leave it as public now the other nice thing about modules is that they can have some variables that are available to you throughout the program and so in this case we can have a public variable which can be set you know at different places in the program if you need to as forums close and open but you need to remember a particular value then you can set that value either from inside of this module code or you can set it from your your forms code behind as well so so that makes it very very flexible and you can set it from just about anywhere in this case we're going to set that variable that pstr message variable we're going to set it and when our subroutine runs it's going to set that variable so that the variable's set and then after the procedure is done the the string the global or pardon me the public string will still be available for other you know other you know procedures and things in the program to access and so now anytime i i run that little procedure and so if i go back to the code here just to clarify that see how in this uh little procedure here i'm setting this uh this variable to to be my standard message and so it will be available so now if i click that button it's going to set that global variable and it's also going to do the other action that we assigned to it and it now it is finished processing but that variable is still available for us to use and so if i go back to our uh you know the current product that we did on the other form if i say you know just do a message box to you know get this system message so i'm going to say you know message box um you know public string message and so now every time we navigate records on there in our our code behind for our form you can see now it's going to give that standard message so if i go okay and i navigate to the next one now it's giving that message but we didn't click that button again that loaded that public variable and so that's a nice handy feature of the module is that you can load some some values in there in order to be retrieved or set by other parts of your program and that's very very handy and this is procedural style programming so we can you know we've got our subroutine that we created in our module and uh the other thing you need to know to get started with vba is you need to understand functions and so we've been using subroutines which which execute a series of commands and they don't return any value or anything they just do that stuff and then they're done whereas a function will do a bunch of stuff but then it'll return a value to you and so in this case i'm going to say this is my function get the message and all it's going to do is it's going to say hey my you know the message is the value of the public string there and now i'm going to go to the immediate window which is sort of where you can you can call a subroutine to run just by putting the name in and hitting enter like i did there and that's going to load that public variable like we saw and it's also going to give the message but when you want the value from a function in the immediate window you're going to use a question mark and you'll say question mark get the message and then my function and then if there's any arguments you'll put those in after and so in the most sort of simple simple sense that you can call functions and subs from anywhere in your program using the uh using the immediate window or you know you can call them from your forums and things like that and that makes it very very handy now one of the other things that you can do in your modules is you can also you know in addition to this my standard message that was set from somewhere in the code you can also have constants which are set right when you declare them and they cannot be changed by program code so you might have i don't know like a serial number of your the application you're building or you might have you know like a system number or something like that and if you want that to be available throughout your program then you'll put public on it but if you want it to only be available inside of your module then you'll just say constant and then your value and in fact i'll go ahead and create a just a regular constant in addition to this one here so our model number is going to be available throughout the program and it's a constant it does not change and then i'll put the something that's available inside of our module code as you can see at the top there we're writing into our module code and it'll be one two three so now if i you know i can call like i said call that one from anywhere but i cannot call you know the system number uh from from everywhere and so if i go into the immediate window i can put my question mark just like i did for my function because i'm asking for a value i can say what is my model number um and it and it returns that just great but if i put in you know what's my system number well the immediate window is calling the uh calling it as if it's calling from somewhere else in the program and so it doesn't actually know what system number is because it is it's it's a private constant inside of the procedure itself and so if i go but i can make a function to return the system number that's saved in the module i could say get system number and i could say you know get system number is equal to system underscore number which is our constant and now if i call that from outside because the function is public and it's getting the value which is private it can return that value so we can say what is the system number and it says one two three and that's exactly sort of how we want that one to work and so there we go there's our system number which we retrieved using a public function even though the system number is private uh we created a function to return that to somewhere else that might be asking for it so that's how the public and and private sort of work and so this is our form code uh which is what you know we've been using there's our our empty form form code which you know is calling that that public function that's in the module and in fact we could go ahead and create a report because reports also have events so you know if i go to the report wizard and i grab our table data and i just you know choose some default options here do a tabular report and then just say you know this is the candy report and that's going to give me a nice little report here with all of our data on it and if i go to the design view of my report you can see that this report has a lot of similar events that we noticed from our forums including some ones that aren't on there like on no data so if that report has no data then you can give a message saying it no it doesn't have anything then you can cancel it from opening at all those kind of things and so just like we did with the form you'll choose your event in this case i chose report load which happens right as the the report is opening and i'll put a message box saying loading in that event and so as you can see now if i go back we've got our candy report down here and if i double click it to open it says loading and that's exactly what i want to see as you know as it's happening you can also you know you can append and transform data just before the report opens and then have the report open on the transform data which is a really nice uh technique and if you're interested in that make sure to check out my video on report open which is an event uh that allows you to do all kinds of stuff just before the report opens and so as you can see uh we've we've got our our form code behind we've got some report code behind that's all bound to the you know those objects but then we have our general code that's available throughout the program and that's how you can get started with vba in access your programmer looking for your next gig make sure to check out the links in the description hope you enjoyed today's discussion on how to get started with vba and microsoft access if you like what you saw today please give the video a thumbs up make sure to subscribe to the channel click the bell when you see the bell and if you have any questions or comments make sure to put those in the comment section below and i'll be happy to answer any questions you might have have a great day have a safe day and i'll catch you next time

Channel: Sean MacKenzie Data Engineering

Views: 161

Keywords: how to get started with microsoft access vba, vba code behind, vba custom function, vba function, vba sub, vba public, vba private, access form code, access report code, vba tutorial, microsoft access tutorial, microsoft access, visual basic for applications, ms access, code builder, microsoft access vba, sean mackenzie data engineering

Id: Mbr47ILlrPQ

Length: 32min 6sec (1926 seconds)

Published: Tue Dec 14 2021