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.
When you want to use VBA to manipulate a table, form, or other database object, you need to assign that object to a variable. Those variables are, not surprisingly, called object variables. For this movie we will use the Object Variables code module that I have here in the Navigation pane, so I will double-click it to display it. The Module contains just a skeleton of an ObjectVariables subroutine, so I will click between the Sub and End Sub statements to start working. You declare an object variable the same way you declare any other variable.
You just specify the type of object you want that variable to represent. So let's say that I want to create an object variable for a form. For that I will Dim, space, then the name of a variable. And I usually just type in myForm to refer to a form, then As, space, and then the word Form, and Return. So now Access understands that the variable myForm will represent a form within the database.
After you declare the object variable, you use the Set command to assign an object to that variable. So I'll type Set, a space, then myForm = then Forms, and Forms refers to the collection of all forms within the database. Then a left parenthesis, double quote, and then the name of the form, which is Products, and double quote, and a right parenthesis, and Enter.
So now what I have done is indicate that the myForm variable refers to the Products Form, and the Set statement allows me to do that. After you declare and set an Object Variable, you can use it as a shorthand reference to refer to that object. In this case, I'll create a message box that uses the myForm.Name property. So MsgBox, then myForm, a period, and then Name.
When I type the period, the Visual Basic Editor recognized that I could now enter a property related to a form. So when I did, it displayed a list of all the properties that were available to me. And the one I am going to use is the Forms Name, and I will press Enter to accept it. One last thing that I need to do before I can run this code is to go back to the main database window--that's by pressing Alt+F11--and open the Products Form, because the form's collection refers to all of the forms that are currently open.
Now, if I press Alt+F11 again, and press F5, Access displays the Message Box and it contains the name of the form, Products. Let's see what would happen if I go back to the database and close it. So I press Alt+F11, and if I press Ctrl+W to close the Products Form and then Alt+F11 to switch back to the Visual Basic Editor, now if I press F5, I get an error dialog box indicating that Access can't find the reference form Products, and that's because the form isn't open--it's closed.
I know what the problem is, so I can just click End, rather than debug the code. You can assign a new object to an object variable by using a second Set statement. So let's say, for example, that I wanted to assign the Clientsform to the myForm variable. To do that, I would just type Set myForm = Forms, left parenthesis, double quote Clients, double quote, and a right parenthesis. Now, again, Forms refers to all the open forms, so to avoid an error, you would need to have the Clients form open in the main database window.
When you're done working with an Object Variable, you should set that variable to Nothing. The reason you do that is to free up the memory that is being used by that object. To do that, you just type another Set statement, Set, then the name of the variable, which is myForm, then equal, and then the keyword Nothing, and Return. Object variables are a real time saver when it comes to manipulating Access. Every object type has its own idiosyncrasies, but you'll find plenty of information in the Autocomplete list and in the help files.
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.