Viewers: in countries Watching now:
Enhance productivity by automating routine tasks and providing custom functionality not built into Access with a few basics in VBA code. Author Curt Frye introduces object-oriented programming and provides database designers with a foundation in the Access object model and the Visual Basic for Applications (VBA) programming language. The course covers automating tasks with macros, working in the Visual Basic Editor, writing functions, adding logic, reading data, controlling forms, and more.
Normally, when you work with variables in a code module you want the variables values to reflect the current run of the code and then reset to 0 for when the modules run the next time. If you want a variable to retain the same value throughout the code run, you can define it as a concept. If you want the variable to remember its value until you close the database, you can define it as a Static variable. For this movie, we will use the Constant and Static code module. And that is over here in the Navigation pane, so I'll double-click it to display it.
I have two subroutines in this code module: the first one displays a delivery charge for regular delivery and then displays a message box indicating the number of regular deliveries, and then the second subroutine does the same thing only for special deliveries. The difference between the two is that a regular delivery costs $20 and a special delivery costs $37. To begin, let's work with the SpecialDelivery subroutine. And I'll remove the comment from the Constant assignment line.
And how that works is it uses the Constant or Const keyword, then the name of the variable, and then the value you're signing as a constant. So the current delivery charge is 37. If you press F5 to run the code, Access displays a message box indicating the charge is 37, and when you click OK, it says that the "Number of special deliveries is 1." And click OK again. Once you assign a variable as a constant, you can't change its value.
So let me type in "curdleCharge" and then = and make it, say, 25 and press Enter. Now when I press F5 to run the code, Access indicates that you cannot assign a value to a Constant. So I'll click the OK button, click the Reset button to get out of editing mode, and then delete the line I just typed in. Now, let's see what happens if I add the comment indicator back to that Constant declaration line.
So I'll just click here to the left of the Const keyword and type a single quote, which indicates its a comment. Now I'm going to go up to this Constant definition line, which is above the first sub declaration, and remove the comment from it, and what that does is it assigns a Constant value of 20 to the DelCharge variable. And because this is above the first subline, it is now a global variable, or in this case a global constant.
So if I were to click in the first subroutine and press F5 to run it, I would get a delivery charge of 20, deliveries is 1. And if I go to the second subroutine and press F5, I also get a deliver charge of 20, and special deliveries is 1. If you define a Constant as a global variable then you can always change that value within another subroutine. So let's say, for example, that I remove a comment marker from the Constant declaration in the SpecialDelivery subroutine, so now what happens is that the value in the SpecialDelivery subroutine is 37--in other words, that's the deliver charge--whereas the value for every other subroutine--there is only one in this case, but you get the point--is 20.
So with the cursor flashing within the SpecialDelivery subroutine, if I press F5, I get a deliver charge of 37. Click OK, and special deliveries is 1. So those are Constants. Now let's talk about Static variables. A Static variable retains its value, regardless of how many times you run the subroutines, until you close the database. So every time that I have run the RegularDelivery or SpecialDelivery subroutine, it's always said that the number of deliveries is 1, regardless of how many times I have done it.
If you make a variable Static, it retains its value. So let's work with the first subroutine, and I will remove the comment marker from the Static lngAllDeliveries line and then press the down arrow. So now that I have declared that the variable is Static, I should see the lngAllDeliveries variable increment because the line right below that indicates that every time the procedure runs 1 should be added to that value. So let's do it a few times. I will press F5 and I get a delivery charge is 20. Click OK.
Number of regular deliveries is 1. Click OK. Now, F5 again. Deliver charge is 20, but the number of regular deliveries is 2. Click OK and just one more time. Charge is 20. Deliveries is 3. That declaration of a Static variable only works within this RegularDelivery subroutine. So if I were to go down to SpecialDelivery and press F5 to run it, the charge is 37--click OK--but the number of special deliveries is 1. So this variable is different from the Static variable that's declared within RegularDelivery. And one thing you can't do is make a variable Static in the Declarations section, so there are no global Static Variables.
Constant and Static variables give you a great deal of flexibility in assigning and to tracking values within a code module. I'm sure you'll find a lot of uses for them.
There are currently no FAQs about Up and Running with VBA in Access.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.