VBA Beginner
If you want to learn VBA and how to use it in MS Access then this is a good place to start. These videos lead you through from creating a simple Command Button right through to manipulating an SQL Statement.
Once you have completed this set of videos, you can move on to the next set of videos here:- Building Search Criteria which follow on from the information provided here and demonstrate how to create a Search Form that builds Search Criteria.
Command Button
Command Button
Video 1 (5:47)
In this video, the first of a set of 12 I demonstrate how to create your first VBA code. First, create a Form, then a Command Button. Use the Command Button to open a Message Box and display “Hello World”
Command Button - Bullet Points 1
00:10 make sure the navigation pane is open
00:14 click on the create tab
00:18 select form design
00:21 now click on the design tab
00:24 select a command button and paste it on the Form
00:30 save it with the default name Form1
00:35 open the form from the navigation pane
00:37 notice that the button blinks but does not do anything
00:42 open the form in design view
00:48 open the forms code module
01:16 make sure you have “Option Compare Database” and “Option Explicit” at the top of the code window
Create a video explaining option explicit and link to it here!!!
01:30 close the forms code window and return to the form in design view
01:33 double clicking on the command button calls up the command buttons property sheet
01:37 you can also select the forms property sheet
01:46 I demonstrate how you can sometimes accidentally load up the wrong property sheet!
01:52 notice; I also accidentally open the property sheet for the forms detail section!
01:59 it’s a good idea to check you are actually in the right property sheet. This is a major cause of head scratching being in the wrong sheet at the wrong time!
02:13 it’s a good idea to name your objects correctly
02:20 notice the command button flashing but nothing happens
02:26 that flashing indicates that the command button onclick event has been called but because there’s nothing coded then nothing happens
02:30 when the form opened several events occurred, but again they have not been coded so nothing happened
Video 1 (5:47)
Command Button - Bullet Points 2
02:38 an event is a way in to the code module behind the form
02:45 when you create the events (write code for them) they will be here in the forms code module
02:55 using the command button onclick event is the simplest and easiest way to understand how all this works
02:57 I again demonstrate how easy it is to switch between the property sheets so again, be aware of which property sheet you are editing
03:12 the only event we are interested in at the moment is the command buttons on click event
03:17 find the onclick event under the events tab and click on the ellipsis this will pop up the “choose Builder”dialogue box which allows you to choose the method you want to use to create your code
03:31 because we are coding with VBA we want the “Code Builder” option
03:33 the Form code window opens and a code stub representing the onclick event of the command button “btnTest” is created and waiting for you to add code
04:10 let’s call a message box inside the command button code stub
04:12 notice typing in the command “Message Box” “MsgBox”
04:17 a pop-up lists the parameters that you can set for the message box function, the first one is prompt.
04:30 you can see the options available for each parameter by pressing a comma
04:54 something really special hey! It’s just the same ol’ “Hello World”
05:12 close and save the form and reopen it
05:15 press the button can you see it depress? And hey presto a message Pops up. I wonder what it will say?
05:30 you now written your first bit of VBA code and I am going to show a little recap
… …
Setup the Northwind dB
Setup the Northwind dB
Video 2 (1:19)
In this video I demonstrate how to find and load a Microsoft Access Demo Database, in particular the Northwind Sample. I show how to set up the Navigation Pane, how to set Compact on Close and how to set it up to use Tabbed Documents. I also explain how to Display a Particular Form when the database Loads up…
Setup the Northwind dB - Bullet Points 1
Video 2 (1:19)
Setup the Northwind dB - Bullet Points 2
00:34 select object type / All Access Objects
00:50 open the file tab again
00:54 select options
00:56 this opens the access options dialogue box select current database from the list on the left
00:59 set compact on close to true
01:05 I normally select tabbed documents, but occasionally use overlapping Windows you might want to try out both selections and see which one you prefer
01:12 you can specify a form which loads up when the database is opened
… …
Import From a dB
Import From a dB
Video 3 (1:20)
In this video I demonstrate how to import external data. In particular how to Import Objects from another MS Access Database.
Video 3 (1:20)
… …
Creating Macros
Creating Macros
Video 4 (4:54)
Demonstration of how to use the Command Button Wizard to generate Macros. Demonstrates how to Convert the Macros into VBA Code. A nice and gentle way of getting a foothold in the VBA world
Creating Macros - Bullet Points 1
00:20 always change the Controls default name into something meaningful
00:32 create another command button, using the Command Button Wizard, Open a Query
00:48 now run the query by clicking on the Command Button
00:58 if you open the Command Button Property Sheet you see that the On Click Event points to an Embedded Macro
01:02 the hello world command button points to an event procedure
01:20 looking at the code behind the form there is just the “Hello World” Command button click event
01:28 under the “Design Tab” there is a button named “Convert Form’s Macros to Visual Basic”
01:40 press convert
01:52 looking at the code behind the form you can see that the macro associated with the command button has been converted into VBA code
02:07 you can remove the automatically generated error code
02:10 the macro has been converted into the VBA statement DoCmd.OpenQuery
Video 4 (4:54)
Creating Macros - Bullet Points 2
02:24 What is “DoCmd.OpenQuery”?
02:38 here is the description of the “DoCmd.OpenQuery” method on the MS Access Website
03:16 these optional parameters created by the macro conversion can be removed
03:23 and command button now calls the VBA code and runs the query correctly
03:26 you read that VBA code “DoCmd.OpenQuery” as Do Command Open Query
03:40 let’s run the AutoExec macro
03:50 and we get the login dialogue box pop-up!
03:55 let’s convert this New macro to VBA
04:11 now we have created another Do Command Run Macro “DoCmd.RunMacro”
04:26 the only bit of code we’ve written so far is the message box “MsgBox “code, the rest has been automatically generated from macros
… …
Opening a Query
Opening a Query
Video 5 (9:53)
XXXXXX
Opening a Query - Bullet Points 1
00:22 open the command button property sheet and go to the onclick event and select code builder this puts a code stub in the forms code module
00:32 type in DoCmd, then press the “Dot” you get a list of available options
00:42 select “OpenQuery”
00:45 MS Access shows you a hint as to what is required
00:50 it’s expecting a query name
00:55 let’s make up a query name and see what happens. Type in “XXX”
01:07 close, and then reopen the form and run the query
01:14 because we put in a fictitious query name that isn’t a query! So access complains with error number “7874” Microsoft Access cannot find the object “XXX”
01:30 press the debug button, and a yellow arrow and yellow highlighting indicate where MS Access thinks the fault lies
01:45 you can press Run, Reset to stop the code execution
02:00 commence creating a query by opening the create tab and clicking the query design button
02:12 build your query based on the customers table
02:24 if you’ve downloaded your own copy of the northwind database then please note, I have made changes to some of the data in the company table for better testing of this search
02:40 by prefixing the query name with an underscore you bring it to the top of the list which gives you easy access to the latest stuff you are working on
02:53 go back to the form, open the form module and change its name to “_qry1”
03:15 open the query in design view, paste owner as criteria against the “job title” column
Video 5 (9:53)
Opening a Query - Bullet Points 2
03:45 open the form and see there is a first name and last name text box
04:45 we can replace the hard coded criteria
04:47 right click in the criteria box and load up the expression Builder
04:52 select all forms, Form1, select the text box “txtCustomerFName”
05:00 that process installs the criteria for you
05:05 the coding structure query to look in form one and find a textbox customer first name and extract the information from it
05:15 run the query again but we have a problem it’s expecting a parameter value
05:24 the forms closed, so the access asks you for a parameter in a pop-up dialogue box
05:42 enter Criteria you know exists “Anna”
05:53 now run the query again but with the form open
05:58 the query takes Anna from the textbox and use it as Criteria
06:10 it would be nice to be able to type in text and wildcards
06:26 look at the query in design view
06:30 insert “a*” and note MS Access automatically adds “Like”
07:10 let’s see if we can get MS Access to produce a wild card query, including the text box
07:30 add the wildcard star just after the text box by concatenating it with an ampersand
09:00 let’s see what happens when we try our updates from the form
09:30 now we got some quite useful functionality
… …
Opening Another Query
Opening Another Query
Video 6 (6:42)
Here, I show how to create an “AND” and an “OR” Query in the Query Designer grid. I show how to Create and setup an Option Group I present the values Returned by an Option Group in a Message Box I demonstrate how to Open two Queries with one Button:- Using an If Statement and using a Select Case Statement. I emphasize the importance of using an “Else” clause in your Case Statement
Opening Another Query - Bullet Points 1
00:14 this row is the “AND” Query
00:27 dropping the Criteria for “Last Name” by one row turns it into a “OR” Query
01:21 now create an event stub for the new button
02:04 description of an “AND” statement
02:15 description of an “OR” statement
02:28 changing the default value of the unbound text boxes
02:50 demonstration of how to create an Option Group with the Option Group Wizard
03:32 Option Group named and set out correctly
03:43 demonstration of the values Returned by the option group into a message box
Video 6 (6:42)
Opening Another Query - Bullet Points 2
03:55 the VBA code to pass the value of the option group into the message box
04:15 write an if statement to make a decision based on which option group button is selected
05:05 the same thing can be achieved with a Select Case Statement
05:25 explanation of the way the Case Statement works
05:46 it is always a good idea to add a “Case Else” to pick up unforeseen errors
06:15 changing the “AND” option group value from 1 to 10 to force a case else error
06:24 the case else was triggered and a message box pops up to notify you
… …
Searching For Answers
Searching For Answers
Video 7 (1:27)
Link for Searching Access World Forums:-
Video 7 (1:27)
… …
Events
Events
Video 8 (4:11)
Events - Bullet Points 1
00:25 a stand-alone Query isn’t really something you would want to present to your user
01:11 code to close the query – DoCmd.Close “query name”
01:23 it is best to close the queries (both of them) before opening them
01:28 you might expect that using the code to close a query that already closed would throw an error but it actually it doesn’t
02:18 now let’s have a look at “Events”
02:20 the Command Button On Click event
02:43 the property sheet of the Option Group
02:46 the “Boundary” around the Option Group is an important feature. It means that everything within that boundary is treated as belonging to the group
Video 8 (4:11)
Events - Bullet Points 2
03:10 the Afterupdate Event of the Option Group
03:28 use the ellipsis and create the afterupdate event notice a code stub is automatically created in the code module behind the form
03:30 place the code from the button subroutine into the option group after update routine
03:38 as soon as you make a selection within the option group the option group afterupdate event runs and loads the query
03:52 what we’ve done is access the afterupdate event of the option group, not the individual components in the option group – there’s a subtle difference
… …
Queries on a Form
Queries on a Form
Video 9 (6:25)
Queries on a Form - Bullet Points 1
00:05 you can place a query on a form
00:14 drag the queries onto the form
00:23 both queries are displayed on the form
00:35 we can change the option group after update event code to show and hide each query
00:40 start by making the queries displayed on the form invisible
00:58 open the property sheet of the subform/subreport control which contains the form which displays the query details
01:00 when the query was dragged onto Form1 two things happened behind the scenes. A new form was created, a form listing the query results. A subform/subreport control was created. This subform subreport control houses, and displays the new form on Form1
01:20 the action of dragging the query onto the form create two new forms
01:32 the term subform is a little misleading when applied to a Form, because the form is just a NORMAL FORM. The only difference is it is displayed on top of another form, and contained within a subform/subreport control
01:53 make the subform/subreport controls for both the subforms invisible
02:00 now open the form, the subforms have disappeared!
02:09 change the names of the subform/subreport controls to something meaningful and recognisable you’re going to need to look them up in code!
Video 9 (6:25)
Queries on a Form - Bullet Points 2
02:20 Access automatically gives the subform/subreport controls names but they don’t follow any naming convention, and quite honestly they are very misleading especially to a novice programmer!
03:01 think of it as a window for displaying your form, a subform window. There you have it, a nice descriptive memorable name subFrmWinXXXX where xxxx gives you an indication of what it’s for
03:14 look even me, a seasoned programmer manages to open the wrong property sheet! If you’re not aware of how easy it is to make this mistake, then you could be scratching your head for days!
03:50 with your memorable subform Windows names “subFrmWinXXXX” write code under each Select Case to make the subform window visible
04:23 it works it makes them visible but hold on we are stuck with them “visible”
04:33 add code at the beginning of the option group afterupdate event to make both subform Windows “invisible”
04:51 by giving things very similar names and distinguishing them with a numeric suffix it makes it very easy to build your code
05:02 now we get the desired effect, as one subform window becomes visible the other one is rendered invisible
05:22 still not getting the correct results because the data is not updating in each of the queries
05:48 we need to refresh both subforms so they show up-to-date information
… …
Queries on a Form - Recap
Queries on a Form - Recap
Video 10 (4:03)
Queries on a Form - Recap - Bullet Points 1
00:15 when you let access rename controls you can end up with some very unhelpful names.
00:21 MS Access does not follow a naming convention naming conventions are very useful and you should adopt one as soon as you realise how helpful it is
Post a link to a naming convention here
01:04 if you have a look at the forms record source you will see that it is the actual query, Query “_qry1” This was the query that was dragged onto the form “Form1”, this “Dragging” action caused the form to be created (and also a subform/subreport control)
01:22 access has also added an order by clause to the forms order by property
01:43 it is much better policy to create forms yourself because you know what’s in them!
Video 10 (4:03)
Queries on a Form - Recap - Bullet Points 2
02:00 you can easily create a form based on a query or table using this method. Select the query (_qry1) then select the create tab, click on the form create button
02:20 the form is displaying the records in the wrong format it is preferable to have the datasheet format
02:25 open the forms property sheet, select the format tab change the default view to datasheet
02:40 open Form1 in design view delete both subform/subreport controls, (automatically taking the forms away at the same time)
02:50 now drag the new form you’ve just created “sfrmQry1” on to Form1 there’s not a lot of obvious differences except you’ve built the form yourself
03:15 change the subform windows name to something more memorable
… …
VBA Queries - P1
VBA Queries - P1
Video 11 (7:13)
Avoid having lots of queries, lots of forms. Adopt an approach where you have one Form and modify it with VBA for different use.
VBA Queries - P1 - Bullet Points 1
00:15 Avoid having lots of queries, lots of forms. Adopt an approach where you have one Form and modify it with VBA for different use.
00:50 try and reuse each object as much as possible
01:12 even in this simple example we are starting to get a proliferation of queries and forms
01:25 demonstration of swapping forms in and out of a subform/subreport control
01:40 Explanation of a subform/subreport control.
The name of this control (subform/subreport control) is unwieldy and unclear as to its use. A much better name for it would have been subform window (In your Code write “subFrmWin”) and that’s what I call it! It tells you exactly what it does.
01:50 the subform/subreport control has properties, one of the properties is source object
02:00 we can change the source object displayed by the subform/subreport control by entering the name of another form
02:20 demonstrate how to swap forms in and out of a sub Form Window – “subFrmWin”
02:30 enter some VBA code under Case 1 that will change the subform/subreport (Subform Window) source object to another form
02:45 do the same under Case 2, but with a different form
02:55 on the first run the form displayed is the one hard coded into the source object Form “sfrmQry1”
Video 11 (7:13)
VBA Queries - P1 - Bullet Points 2
02:57 selecting “OR” in the option group causes the case statement to change the Subform Window’s Rowsource to one of the other Forms _qry1 subform
03:00 clicking the other option switches in the other subform _qry2 subform so here you see three different forms presented in that single subform/subreport Control all one after the other
03:08 Demonstrate how to change the source object property of a sub form window with VBA.
3:27 show how to transfer the text from a query into a form module so that the query can be manipulated by VBA code.
04:18 easily remove the reference to the table from a simple SQL statement.
04:55 create some string variables to hold the parts of the SQL Statement
05:30 how NOT TO create string variables
06:27 don’t forget the object is to create small manageable components and assemble them here we can divide that SQL Statement up into smaller bits
06:52 concatenate the string components into one SQL Statement using the ampersand (&)
… …
VBA Queries - P2
VBA Queries - P2
Video 12 (8:36)
MS Access VBA formats the text wrongly, turns an Asterix into a multiplication sign. The query is outside of the form, if the form is closed the query doesn’t work properly. The query needs a loaded form but the form was closed, so it didn’t work. SQL view of a query. Like * (“”Star””) in query criteria. You can’t have the same type of speech marks within a string (surrounded by speech marks). Find a Form in a Subform window and read or write to the Record Source, changing the subject of a subform window (Subform/Subreport Control). You now have the ability to manipulate queries with VBA
Download the sample database this video is based on here:-
VBA Queries - P2 - Bullet Points 1
00:03 you may have observed something strange happened in the last video access added some characters to this string
00:10 Access formats the text wrongly
00:24 access detected two strings and a multiplication sign and decided to format accordingly
00:28 Turns an Asterix into a multiplication sign
00:58 this isn’t a string it’s a bit like a variable it actually points to an object within the Form which you want to extract information from
01:22 the query is outside of the form
01:24 this SQL statement originated in a query but now we’ve placed it inside the form so before it had to look for the form from the outside( because it was in a separate object, a query) but now this code is inside the form it’s in the forms code module so this instruction is superfluous
01:53 now this code resides within the form in the forms code model. Because it’s now part of the form, you don’t need all of that direction to the form.
02:16 at this point in the video I came to the conclusion that I was going in the wrong direction. So I decided to go back to the query and make a much simpler SQL Statement
02:35 SQL view of a query
02:35 I realised this was a difficult example to kick off with so I decided to simplify a bit
Video 12 (8:36)
VBA Queries - P2 - Bullet Points 2
02:58 I’ve opened a query in design view and simplified the criteria
03:15 Like * (Like Star) in query criteria
03:32 now we have a much simpler SQL Statement it will be much easier to understand what’s going on
03:39 and that’s a good tip for you if things start to get difficult go back a few steps and see if you can come up with an easier way
04:02 the SQL Statement is simpler now because it contains hard coded criteria instead of a reference to the form
04:20 you can’t have the same type of speech marks within a string (surrounded by speech marks)
04:22 access doesn’t like the code and is displaying it in red by way of complaint! the problem is we’ve got speech marks within speech marks!
04:28 the simplest solution is to replace the double quotes (“) with single quotes (‘)
04:33 I’ve added a piece of code which finds the form in the subform window, changes its record source to “strSQL0”
05:10 changing the Source Object of a subform window (Subform/Subreport Control)
05:40 open the form, “_qry1 subfrm” and add a label with the caption “This One”
05:55 open Form1 again, press the option group to Run the “update code” we see that the form we added the label to, showing the caption “This One” appears.
06:11 open the form “_qry1 subfrm in design view open the property sheet, data tab and see that the forms record source is _qry1 empty the record source and close the form
06:16 you get the error #Name? Returned by the text boxes, letting you know that something is wrong
06:23 open Form1 and we see the same form again with a broken record source
06:26 go to form ones code module
08:15 you now have the ability to manipulate queries with VBA
…. ….
