IntroductionWelcome| 00:04 | Hi! I am Curt Frye.
| | 00:06 | Welcome to Up and Running with VBA in Access.
| | 00:10 | In this course, I will show you how to
automate Access databases using macros, to
| | 00:15 | run a macro by clicking a Command button,
and to manage macro security settings.
| | 00:19 | I will introduce Visual Basic for
Applications, also known as VBA, starting with
| | 00:25 | an overview of object-oriented
programming, the Access Object Model, and using
| | 00:32 | the Visual Basic Editor.
| | 00:34 | The course focuses on adding logic to
your VBA code, manipulating database
| | 00:38 | objects, and the controlling
forms and reports using the Me object.
| | 00:43 | Whether you use Access in a small
business or from home projects, you will
| | 00:47 | discover that VBA programming can
make your databases much more useful.
| | 00:51 | In short, I'm sure you'll find that
your time with Up and Running with VBA in
| | 00:55 | Access will be time well spent.
| | 00:56 | Let's get going.
| | Collapse this transcript |
| What you should know before watching this course| 00:00 | Thanks again for your interest in
Up and Running with VBA in Access.
| | 00:05 | This is an intermediate- to expert-level
course, so I assume that you know a fair
| | 00:09 | amount about how to use and
manipulate Access databases.
| | 00:13 | Specifically, I'm assuming that you can
create and format database objects, that
| | 00:18 | you know how to use other programs with
Access, and that you know how to manage
| | 00:22 | your database's options using
the Access Options dialog box.
| | 00:27 | If you need to review any skills, you
should look into the Access Essential
| | 00:30 | Training courses in the Online Teaching Library.
| | 00:33 | Those are available for Access
2003, Access 2007, and Access 2010.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you have either an Annual
subscription or a Premium Monthly subscription to
| | 00:05 | the lynda.com Online Training Library,
then you have access to the exercise
| | 00:09 | files for this course.
| | 00:11 | You can download this exercise files
from the course page, and when you do, you
| | 00:15 | can save them on your computer.
| | 00:17 | I've saved my files in a directory
called Exercise Files on my Desktop.
| | 00:23 | When I double-click it, I have files
for each chapter And then I can just
| | 00:28 | double-click that folder for the
chapter that I want, and inside will be any
| | 00:34 | exercise files that I need.
| | 00:36 | If you have a regular Monthly
subscription to the Online Training Library, then
| | 00:40 | you can create your own files or
just follow along on the screen.
| | Collapse this transcript |
|
|
1. Automating Processes Using Macros Introducing Access Automation| 00:00 | Microsoft Access is a powerful program.
| | 00:02 | One of its most useful aspects is the
built-in flexibility to customize the
| | 00:06 | program using macros, data macros,
and Visual Basic for Applications.
| | 00:12 | In this movie, I'll describe how you
can use each of those capabilities to
| | 00:16 | enhance your Access databases.
| | 00:18 | Macros are a series of steps that you
can have Access replay automatically.
| | 00:22 | For example, in this database I have
the Open Products Report macro, which is
| | 00:28 | here in the Navigation pane.
| | 00:30 | If I right-click the macro and click
Design view, you can see the steps, or in
| | 00:35 | this case the single step, in the macro.
| | 00:37 | What this macro, which is named Open
Products Report, does is to implement the
| | 00:43 | OpenReport method, and then it opens
the report named Products Report, and that
| | 00:50 | Product Report is here in the Navigation pane.
| | 00:53 | With the macro open, if I click Run,
Access runs the code and displays
| | 00:59 | the Products Report.
| | 01:00 | I will go ahead and close the Products
Report by pressing Ctrl+W and do the same
| | 01:05 | for the Open Products Report
macro, pressing Ctrl+W again.
| | 01:09 | Data macros, which are new in Access
2010, let you attach logic to a table,
| | 01:15 | which was more difficult to do in the past.
| | 01:17 | For example, let's suppose that you
run a company that sends shipments to
| | 01:21 | customers and you want to be notified by
email whenever a shipment is sent out late.
| | 01:27 | To do that, you can create a data macro
that is attached to the Shipments table.
| | 01:31 | So if I were to double-click the
Shipments table in the Navigation pane, it
| | 01:37 | would open, and then on the Table
contextual tab, I can click the Named Macro
| | 01:43 | button, point to Edit Named Macro,
and then click Late Shipment Notification.
| | 01:50 | And when I don you see a data macro
interface that looks a lot like the macro
| | 01:56 | interface that we saw earlier.
| | 01:58 | In this case, the macro asks if the
ShipmentActualDate is greater than--
| | 02:04 | that means after--the ShipmentTargetDate,
and if it is, then it sends an email.
| | 02:09 | And there's the email address,
the subject, and the body.
| | 02:13 | I'll go ahead and press Ctrl+W to close
that and also to close the Shipments table.
| | 02:19 | Finally in this overview,
I'd like to show you VBA code.
| | 02:22 | VBA code lets you customize your
database to automate processes and to control
| | 02:27 | what your users can do.
| | 02:28 | So let's say, for example, that you
wanted to put a button on a form that
| | 02:32 | allowed users to close the current database.
| | 02:36 | There is VBA code to do that, and I'll
press Alt+F11 to open the VBA Editor.
| | 02:42 | When I do, we see the code, and in
this case there are two code modules.
| | 02:46 | The first one indicates that when
the user clicks a Command button named
| | 02:51 | Command+0, then Access
performs the action CloseDatabase.
| | 02:56 | If they click the Command+1 button, then
the user will quit Access and save everything.
| | 03:01 | That is what acQuitSaveAll means.
| | 03:04 | Now, I am going to press Alt+F11 to go
back to the main Access database and show
| | 03:09 | you what happens when we run that code.
| | 03:12 | In this case, the buttons are on the
Close Database, or Quit Access, form, so I
| | 03:17 | will double-click that, and you
can see those two buttons there.
| | 03:21 | If you click the Close Database button,
doing so closes the database and takes
| | 03:27 | you back to the Backstage view.
| | 03:29 | I'll provide a brief overview of macros
and data macros in this chapter before
| | 03:33 | moving on to VBA, which is the most
powerful and versatile automation tool
| | 03:38 | available in Access.
| | 03:39 | I am sure that, among these three
options, you'll find the tools to create
| | 03:43 | the solution you want.
| | Collapse this transcript |
| Running macros| 00:00 | A macro is a series of steps
that Access runs automatically.
| | 00:04 | Access macros are different from
macros and other Office programs in that
| | 00:08 | you don't record them.
| | 00:09 | Instead, you select actions from a
menu and a save those steps as a macro.
| | 00:14 | One common use for a macro is
for controls on a navigation form.
| | 00:19 | For this movie, we're going to
create a macro to open the Products form.
| | 00:24 | To do that, on the Ribbon, click the
Create tab and then in the Macros &
| | 00:29 | Code group, click Macro.
| | 00:32 | Doing so opens a new macro and also
displays the Design tab under the Macro
| | 00:37 | Tools heading on the Ribbon.
| | 00:40 | To the right side of the macro, there
is the Action Catalog Task pane, and that
| | 00:45 | allows you to set the program flow, for
example, by using an if-then statement,
| | 00:50 | or to set your macro up for other
actions, such as data entry, data
| | 00:55 | import, export, and so on.
| | 00:57 | In this case, we're going to create
a macro that opens the Products form.
| | 01:02 | To do that, I'll click the down arrow
here in the Add New Action list box, then
| | 01:08 | scroll down and click OpenForm.
| | 01:15 | When I do, the interface changes and allows
you to set the parameters for this action.
| | 01:19 | In this case, we're only going to set
one, and that is the Form Name, so I'll
| | 01:23 | click the Form Name list box's
down arrow and click Products.
| | 01:28 | Then with that in place, I'll press
Ctrl+S to save my work and then type in a
| | 01:33 | new name for the macro. And in this
case I'll just call it Open Products Form,
| | 01:40 | and press Enter to accept that,
and then Ctrl+W to close it.
| | 01:45 | Now, if you want, you can run the Open
Products Form macro by double-clicking it
| | 01:49 | in the Navigation pane, as so,
and I'll press Ctrl+W to close the form.
| | 01:55 | If you want to rename or delete the
macro you just created, you can do so like
| | 01:59 | any other object in the database.
| | 02:01 | To rename or delete the macro,
right-click it in the Navigation pane and then
| | 02:06 | select the option you
want from the shortcut menu.
| | 02:09 | So, for example, if you want to
open it in Design view, you can do so.
| | 02:12 | If you want to rename it, you can
click Rename, or if you want to delete it,
| | 02:16 | you can click Delete.
| | 02:18 | Macros make it easy for users of all skill
levels to add automation to their databases.
| | 02:23 | If you need to have Access perform a
simple task, such as opening a form, you
| | 02:27 | could easily use a macro instead of VBA.
| | Collapse this transcript |
| Working with data macros| 00:00 | Data macros, which are new in Access
2010, add macro capabilities to tables.
| | 00:05 | One common use for data macros is to
send an email when the table data meets a
| | 00:10 | predefined condition.
| | 00:11 | For example, the data macro could send
an email anytime an order is over $150.
| | 00:18 | In this movie, we will create
a macro that does just that.
| | 00:21 | We're going to work with the Orders
table, and I will double-click that in the
| | 00:25 | Navigation pane to open it.
| | 00:27 | And then on the Table tools contextual
tab, I will click Table, and then we need
| | 00:32 | to select which event we
want to trigger this macro.
| | 00:36 | The event that I am going
to select is After Insert.
| | 00:41 | The reason that I want to use the
After Insert event is that as soon as an
| | 00:45 | order goes into the table, I want the email
to be sent, or at least attempt to be sent.
| | 00:50 | You do have the option of not
allowing the email to go through.
| | 00:53 | So now let's go ahead and create the macro.
| | 00:56 | Because we're checking for a condition,
in this case any order of more than
| | 01:00 | $150, we need to use an If
statement, and you can see the If statement here on
| | 01:06 | the right in the Action Catalog task pane.
| | 01:09 | If you don't see the Action Catalog
task pane, then you can go to the Design
| | 01:13 | Contextual tab, in the Show/Hide
group, click the Action Catalog button.
| | 01:18 | But in this case it is shown, so I'll
double-click If, and doing so creates an
| | 01:23 | If statement within the body of the data macro.
| | 01:26 | Now I need to define the condition
even further; in other words, I want to
| | 01:31 | tell it to look in the Orders field and
determine if the value is greater than $150 or not.
| | 01:37 | To do that, click the Build button at
the right side of the If statement's box,
| | 01:41 | and then you can use the Expression Builder.
| | 01:43 | We're checking for OrderTotal, so in
the Expression Categories Pane, I will
| | 01:48 | double-click OrderTotal,
and then I can type in the rest.
| | 01:52 | So we're looking for whether an order is
greater than $150 or not. And you don't
| | 01:59 | have to put in the dollar sign;
we're only checking the numerical value. Okay.
| | 02:01 | So I have OrderTotal greater than 150.
| | 02:05 | That looks correct, so I can click OK to
add it to the If statement. And you see
| | 02:10 | it here in the If box.
| | 02:12 | Now we need to tell Access which action to
take if an order of greater than $150 is entered.
| | 02:19 | To do that, you click the Add New
Action list box's down arrow and then select
| | 02:24 | the action you want.
| | 02:25 | In this case, we want to send an email,
so I'll click SendEmail from the list,
| | 02:30 | and then you get a list
of items that you can set.
| | 02:33 | For To, you type in the To email address,
and that will be this address here.
| | 02:42 | Then I will press the Tab key to move
down to the Subject line, and I'll type
| | 02:47 | "Offer Customer Discount."
| | 02:51 | Press Tab to move to Body, and I'll
just type in something simple, "Order more
| | 02:57 | than $150," then two hyphens, "offer
customer a discount on future orders."
| | 03:09 | That's there, and I can press Ctrl+S
to save my work, and then on the Design
| | 03:15 | Contextual tab, click the Close button.
| | 03:18 | Now within the table, which reappears,
click in the first empty CustomerID
| | 03:23 | field--the OrderID filed is an auto
number that will come up automatically--
| | 03:27 | and then we will say that we have a
CustomerID of 102. Press Tab, and the order is $175.
| | 03:36 | So 175 and press Tab.
| | 03:39 | When that happens, I see a dialog box
indicating that a program is trying to
| | 03:43 | send an email message on my behalf.
| | 03:45 | I can either allow it or deny it.
| | 03:48 | In this case I will go ahead and deny
it, just so the email doesn't get sent.
| | 03:52 | But if I clicked Allow, then the email
would have been sent from the parameters
| | 03:56 | that I've named within Microsoft Outlook.
| | 03:59 | Data macros extend the traditional
macro functionality, which lets you build
| | 04:04 | even more capabilities into your databases.
| | 04:06 | Data macros occupy the middle ground
between traditional macros and VBA.
| | 04:10 | They're not as complicated as VBA
code, but not as powerful either.
| | Collapse this transcript |
| Running a macro by clicking a command button| 00:00 | Access macros run when a trigger event occurs.
| | 00:03 | By far the most common trigger is
for a user to click a Command button;
| | 00:07 | however, you can also run a macro by
double-clicking it in the Navigation pane
| | 00:13 | or by clicking a Ribbon
control that you assign the macro to.
| | 00:16 | In this movie, I will show you how
to run a macro in each of those cases.
| | 00:20 | The simplest case is to run a macro by
double-clicking it in the Navigation pane.
| | 00:25 | So, for example, I have the
Open Products Report macro here.
| | 00:29 | If I double-click it, Access
runs the macro and opens the report.
| | 00:33 | I'll go ahead and press Ctrl+W to close it.
| | 00:37 | Now let's suppose that I want to
assign that macro to a Command button.
| | 00:41 | To do that, we will create a new form,
so click Create on the Ribbon, and
| | 00:46 | then click Form Design.
| | 00:49 | Now, on the Design Contextual tab of
the Ribbon, click the Command button, and
| | 00:55 | then in the body of the form
drag to create a Command button.
| | 00:58 | And I'm making mine fairly wide.
| | 01:00 | I am dragging from just inside the
left border to about three inches, so that
| | 01:05 | should give us plenty of room.
| | 01:07 | Now, in the Command Button Wizard,
click Miscellaneous in the Categories list
| | 01:11 | and then in the Actions list that
appears when you click it, click Run macro.
| | 01:17 | Now, click the Next button, and this is
where you select the macro you want to run.
| | 01:22 | In this case there is only
one, Open Products Report,
| | 01:25 | so with it highlighted, click Next,
and then you can select what you want to
| | 01:30 | appear on the face of the button.
| | 01:32 | In this case, click the Text radio
button, and then select the text inside of
| | 01:37 | the text box and type Display Products.
| | 01:44 | Then click Next, and now you
can assign a name to the button.
| | 01:49 | In this case, I will just give it the same
name of Display Products, and click Finish.
| | 02:00 | When we do, Access adds that text to the
face of the button and also resizes it
| | 02:05 | so it is just large enough to
accept the text that you just typed in.
| | 02:09 | Now we will save the form by pressing
Ctrl+S, and then in the Save As dialog
| | 02:14 | box click Display
Products Report and press Enter.
| | 02:23 | Now, right-click the Display Products
Report Objects tab and click From View.
| | 02:30 | Now, when you click the Display Products
command button, Access displays the Products table.
| | 02:35 | Now, we will close the Products table by
pressing Ctrl+W and do the same for the
| | 02:40 | Display Products Report Form.
So we press Ctrl+W again.
| | 02:44 | Now I will show you how to run a macro
by assigning it to a button or a control
| | 02:49 | on the Quick Access Toolbar or the Ribbon.
| | 02:52 | I will show you the method for
assigning it to a Quick Access Toolbar control,
| | 02:56 | but the method is exactly the
same for any other tab on the Ribbon.
| | 03:00 | So on the Quick Access toolbar, click
the Customize Quick Access toolbar button,
| | 03:05 | and then click More Commands.
| | 03:09 | Doing so displays the Access
Options dialog box, opened to the Quick
| | 03:14 | Access Toolbar page.
| | 03:15 | If you want to customize the Ribbon, then
you would click the Customize Ribbon page.
| | 03:20 | In this case, we're going to add a new
button to the Quick Access Toolbar and
| | 03:23 | that will run a macro.
| | 03:25 | So click the Choose commands from
list down arrow and then in the top
| | 03:30 | section, click Macros.
| | 03:32 | Doing so displays all the macros
available in your database, and in this case
| | 03:37 | that is Open Products Report.
| | 03:39 | Please note that this list doesn't
show any data macros or any VBA code. In
| | 03:45 | other Office programs this list would
display any routines that you had made
| | 03:50 | with VBA code, but in this case it
doesn't because macros are a separate entity.
| | 03:55 | With Open Products Report displayed,
click the Add button to add it, and then
| | 04:00 | just as you can do with any other
control, you can change its position.
| | 04:04 | So in this case I'm going to click Open
Products Report and then click the move
| | 04:09 | down arrow until the new button is at
the bottom of the Quick Access Toolbar
| | 04:14 | list, so it appears all the way to the right.
| | 04:17 | You might not have had to do that based
on how your computer is configured, but
| | 04:21 | in my case I just wanted the
button to be at the far-right side.
| | 04:24 | With that in place, click OK, and when
you do, you can go up to the Quick Access
| | 04:30 | Toolbar and see that the new macro
button, which opens the Products Report, is
| | 04:35 | on the Quick Access Toolbar.
| | 04:37 | When you click the button, Access
runs the macro and displays the report.
| | 04:42 | So that is how you run a macro using a
Command button or Ribbon control inside
| | 04:46 | of Access 2007 or 2010.
| | 04:49 | Now I am going to switch over to Access
2003 and show you how to work in that program.
| | 04:55 | In 2003, running a macro from a Command
button on a form or a report is exactly
| | 05:00 | the same as doing so within Access 2007 or 2010.
| | 05:05 | You add the Command button to the form
or report, right-click it, click Build
| | 05:08 | Event, and follow the same set of steps.
| | 05:11 | In Access 2003, which has the menus and
toolbars user interface, you can add a
| | 05:17 | Command button to any
toolbar and run a macro from there.
| | 05:21 | To do that, you need to customize the
toolbars, so for that, you click the Tools
| | 05:26 | menu and then click Customize.
| | 05:29 | Then on the Commands tab, scroll down in
the Categories list until you see All Macros.
| | 05:36 | Go ahead and click it.
| | 05:38 | Then drag the macro you
want to a toolbar location.
| | 05:41 | So if you want to add the Open
Products Report macro, click that and drag it,
| | 05:46 | and I will place it to the left of the
Help button on the standard toolbar. And
| | 05:50 | when you're done, click the Close button.
| | 05:52 | Then with that button on the toolbar, you can
click it to run the macro and open the report.
| | 05:58 | Running a macro can be, and often is, as
simple as clicking a button or Ribbon control.
| | 06:03 | You'll find there are lots more
events you can use as a trigger for your
| | 06:06 | code when you start working with VBA,
but sometimes the simplest approach is
| | 06:11 | the best.
| | Collapse this transcript |
| Managing macro security settings| 00:00 | Access 2007 and 2010 introduce macro
security settings that limit how and when
| | 00:06 | macros and VBA code will run.
| | 00:08 | If you're having trouble running
macros or VBA, you can use the techniques in
| | 00:12 | this movie to enable them.
| | 00:14 | To change Access's Macro security
settings, click the File tab and it's the File
| | 00:19 | tab in Access 2010 that would be the
Office button which is also in the upper
| | 00:23 | left-hand corner of the screen in Access 2007.
| | 00:27 | So just, on the File tab, click Options,
and it's Options in Access 2010 and Access >
| | 00:34 | Options in Access 2007.
| | 00:37 | In either case, go ahead and click Options.
| | 00:40 | Doing so displays the Access Options
dialog box. Then in the left-hand panel
| | 00:46 | click Trust Center and then in the
right pane on the Trust Center page click
| | 00:51 | Trust Center Settings.
| | 00:53 | This gives you a new dialog box with new
options. Click Macro Settings, and then
| | 01:01 | you'll see a list of available macro settings.
| | 01:04 | The first is Disable all macros without
notification. That does exactly what it says it does.
| | 01:10 | It turns off all macros and VBA
code without letting you know.
| | 01:14 | Next is Disable all macros with
notification. In that case, Access displays a
| | 01:19 | Message bar with a button indicating
that macros have been turned off. Then
| | 01:25 | there is Disable all macros except
digitally signed macros. And finally is,
| | 01:30 | Enable all macros and the
parenthetical remark that it's not recommended
| | 01:35 | because potentially
dangerous code can run is accurate.
| | 01:39 | Viruses are spread through VBA code,
and those are called macro viruses, so
| | 01:44 | you should only enable all macros if
you're certain of your computer and its security.
| | 01:50 | Now the only truly secure computer is
one that's disconnected from any network,
| | 01:54 | including the power grid. But if you
feel good about it and you want to be able
| | 01:58 | to run macros then you can go ahead
and enable them all, at least for testing.
| | 02:04 | If you're going to do that, I highly
recommend that you disconnect your computer
| | 02:08 | from the Internet and not download any software.
| | 02:11 | Then when you're done using macros
in Access or any other Office program
| | 02:16 | then you can select one of the other settings,
such as Disabling all macros with notification.
| | 02:22 | When you've made your setting change you
can click OK and OK again, and then you
| | 02:28 | would need to close and
restart Microsoft Access.
| | 02:31 | That's how you manage macro
security in Access 2007 and 2010.
| | 02:37 | Now I'm going to switch over to Access
2003 to show you how to set your macro
| | 02:41 | security options in that program.
| | 02:43 | In Access 2003, you can change your macro
security settings by going to the Tools
| | 02:49 | menu, pointing to Macro,
and then clicking Security.
| | 02:55 | When you do, you get the Security
dialog box, and you see the familiar three
| | 02:59 | options: High, Medium, and Low.
| | 03:02 | Again, if you want to run macros
without any sort of intervention then you can
| | 03:06 | set your security to Low, but that
does open you up to macro viruses and
| | 03:11 | other forms of malware.
| | 03:13 | So if you're absolutely certain that
your computer will be fine, go ahead and
| | 03:17 | set it to Low so that you can run your
macros while you're working through the course.
| | 03:21 | However, make sure that you
don't visit any web sites;
| | 03:24 | make sure that you don't download
anything, such as from Outlook; and if possible,
| | 03:29 | disconnect from the network entirely.
| | 03:31 | Then when you're done set your security level
back to either Medium or High and then click OK.
| | 03:37 | There are more security options
available to you, such as saving a file in a
| | 03:41 | trusted location or using a trusted publisher.
| | 03:44 | I encourage you to read the text in the
Trust Center and the related help files
| | 03:48 | for more information.
| | Collapse this transcript |
|
|
2. Introducing Visual Basic for Applications (VBA)Introducing object-oriented programming| 00:00 | Visual Basic for Applications, the
language you use to program Access, is an
| | 00:05 | object-oriented programming language.
| | 00:07 | In this movie, I will introduce object-
oriented programming as a concept, before
| | 00:11 | moving on to the specifics of
Access VBA in subsequent movies.
| | 00:16 | For our purposes, a programming language
is a system of statements that are used
| | 00:20 | to manipulate a computer.
| | 00:22 | At a practical minimum, a programming
language must give you the ability to
| | 00:27 | accept data input, store the data,
manipulate the data, and then send the
| | 00:32 | results of those manipulations to an output
device, such as your monitor, a file, or printer.
| | 00:38 | Input, storage, manipulation, and output:
every programming language must have
| | 00:43 | those basic features.
| | 00:45 | The question then becomes, how do you
design a programming language so humans can
| | 00:48 | create meaningful sets of instructions?
| | 00:50 | There are many ways to do it, but the
two organizational concepts I'll cover are
| | 00:55 | procedural programming languages and
object-oriented programming languages.
| | 00:59 | A procedural programming language focuses on
the data and the tasks required to produce a result.
| | 01:05 | You break everything down into tasks,
write your programming instructions in the
| | 01:09 | series of what are called
subroutines, and then use logic to decide which
| | 01:12 | instructions to follow.
| | 01:14 | Object-oriented programming, by contrast,
focuses on the objects involved in calculation.
| | 01:19 | As an example think of a car. You can
describe the car, specify actions you
| | 01:24 | can perform with the car,
and enumerate things that can happen to the car.
| | 01:29 | These three types of
characteristics form the backbone of
| | 01:32 | object-oriented programming.
| | 01:33 | Descriptive items are called properties.
| | 01:36 | For a car, you can have properties
that store the car's manufacturer, model,
| | 01:40 | year, color, and price.
| | 01:43 | Actions you can perform with a car are
called methods and are described using verbs.
| | 01:48 | For example, you can drive a car, park
a car, or wash a car. Things that can
| | 01:53 | happen to a car are called events.
| | 01:56 | Some events mirror the object's methods,
such as a car being started or being parked.
| | 02:01 | The difference is that when an event
such as a car being started occurs, the
| | 02:05 | program detects the change in the car's
state and runs any instructions you want
| | 02:09 | to be run when that event occurs.
| | 02:11 | For example, your car's onboard a
computer could be programmed to turn on the
| | 02:15 | car's headlights when you start it.
| | 02:17 | If you create an event called car_On_
Start, the instructions you include in that
| | 02:23 | events definition will run
whenever someone starts the car.
| | 02:26 | Object-oriented programming was the
hot new thing in the late 1980s and early
| | 02:30 | 1990s, and languages such as
Smalltalk and Objective-C provided some neat
| | 02:35 | capabilities that weren't easy to
build into procedural languages.
| | 02:39 | However, as with most new things, some
advocates claimed these techniques would
| | 02:43 | deliver benefits that just were not possible.
| | 02:46 | Object-oriented programming is a
powerful paradigm, especially when you use it
| | 02:50 | to work with programs such as
Microsoft Access, but at its base it's just
| | 02:55 | another way of organizing the same
properties, methods, and events you work with
| | 02:58 | in procedural languages.
| | 03:00 | In the next movie, I'll introduce the
Access Object Model which gives you
| | 03:04 | the hooks you need to manipulate
Access databases, database objects, and
| | 03:09 | the application itself.
| | Collapse this transcript |
| Examining the Access Object Model| 00:00 | Object-oriented programming languages,
such as Visual Basic for Applications, let
| | 00:05 | you create and manipulate objects using
their properties, methods, and events.
| | 00:10 | Access is a huge program with hundreds
of objects, but the program team defined
| | 00:15 | every element of Access from the
application itself to individual table rows as
| | 00:20 | objects you can
manipulate using VBA commands.
| | 00:23 | Taken as a whole, these definitions
comprise the Access Object Model.
| | 00:28 | In this movie I'll highlight the most
commonly used objects in Access VBA.
| | 00:32 | The first of those objects is the
application object, and there are a number of
| | 00:37 | properties that you will use.
And actually, some of the properties are objects
| | 00:42 | themselves that I'll get into in a
little bit more detail later in this movie.
| | 00:46 | So, for example, you have the
CurrentDb, which refers to the current
| | 00:50 | database, then CurrentProject--and a
project is a VBA code collection that
| | 00:56 | I'll touch on later.
| | 00:57 | DoCmd is a series of menu items.
| | 01:01 | Basically, the DoCmd object in VBA
includes all of the methods that you can use
| | 01:07 | if you create macros, like I
showed you in Chapter 1.
| | 01:10 | Then you have the collections for
forms and reports and ways to work with the
| | 01:14 | printer, the screen, and also SysCmd
which allows you to do things such as sound
| | 01:19 | beeps and to show progress bars.
| | 01:22 | Methods, or actions that you can
take use in the application object, are
| | 01:26 | calculating averages, counts, maximums,
and so on, within a table. Also, you can do
| | 01:32 | a variety of lookup tasks, such as
finding the first or last value that meets a
| | 01:36 | criteria, or you can use the DLookup to
find values in one table field based on
| | 01:42 | values in another field.
And finally, if you want, you can quit Access.
| | 01:47 | The Me object refers to either forms
or reports, and there are variety of
| | 01:52 | properties that you can use, such as
identifying the ActiveControl and also
| | 01:57 | indicating whether you want to allow users to
be able to manipulate the form or report data.
| | 02:03 | You can also control filters, the
caption, the picture that appears in the
| | 02:07 | background, and also identify the
RecordSource that provides values for that form
| | 02:11 | or report. And as with controlling the
caption and the picture, you can decide
| | 02:16 | which, if any, scrollbars to display,
and whether you make the form or report
| | 02:20 | visible or invisible.
| | 02:22 | The methods that you can use with the
Me object, which again refers to either
| | 02:26 | a form or a report, are to Refresh which
updates the data supplied to the form or report;
| | 02:32 | Repaint, which redraws the
form or report on the screen;
| | 02:35 | Requery which updates the data for the
form or report; and finally, Undo which
| | 02:41 | allows you to give the user the
option to essentially undo the last action.
| | 02:46 | It's equivalent to clicking the Undo
button on the Quick Access Toolbar pr
| | 02:50 | pressing Ctrl+Z. Next, there is the
CurrentDb object. That refers, as I said earlier,
| | 02:56 | to the current database. And you have
Properties--and this list is not exhaustive.
| | 03:00 | These are just the ones
that are most commonly used.
| | 03:03 | You have the name and then the
collection of all queries, called QueryDefs, and
| | 03:08 | the collection of all tables, called
TableDefs. And methods, or actions, you can
| | 03:13 | take on the CurrentDb Object are to
create a query, create a table, or to open
| | 03:18 | an existing query or table as a
recordset. And speaking of the Recordset Object,
| | 03:23 | you will use this object a lot
when you analyze your table data.
| | 03:27 | The Recordset's most commonly uses
properties are Beginning of File and End of
| | 03:31 | File, so you can identify where the
data starts and where it ends; the Fields,
| | 03:35 | which are a collection of the
fields or columns in your tables;
| | 03:38 | NoMatch, which identifies whether a
find function was able to locate data that
| | 03:44 | meets the criteria; and then finally,
RecordCount, which tells you the number of
| | 03:48 | rows in the table. And the Methods
include AddNew, which is adding a new record;
| | 03:54 | closing a recordset and then either
deleting a row or editing a row; and then
| | 03:59 | you also have methods to find values
within a recordset; to move within the
| | 04:03 | recordset; to requery, meaning to
update the data; and then also to update the
| | 04:09 | recordset based on input from the user.
| | 04:11 | You can find more information about the
Access Object Model through the Visual
| | 04:15 | Basic Editor's help
facility or the Object Browser.
| | 04:18 | The help facility works just like the
help system in the regular Access program.
| | 04:23 | You can find the Object Browser in
the Visual Basic Editor, which I cover in
| | 04:26 | detail in the next movie.
| | Collapse this transcript |
| Working in the Visual Basic Editor| 00:00 | When you write or edit VBA code in Access
you'll do so using the Visual Basic Editor.
| | 00:06 | The editor gives you all the tools you
need to manage your code effectively.
| | 00:10 | There are two ways to open the
Visual Basic Editor in Access.
| | 00:13 | The first is to go to the Database Tools
tab on the Ribbon and then click Visual Basic.
| | 00:20 | The tooltip that appears when you hover
the mouse pointer over the Visual Basic
| | 00:23 | button indicates that you can press
Alt+F11 to go to Visual Basic, and that's
| | 00:29 | the way that I usually do it.
| | 00:30 | So I'll go ahead and press the Alt+F11 key
sequence and open the Visual Basic Editor.
| | 00:37 | When you go to the Visual Basic
Editor it will appear in exactly the state
| | 00:41 | that you left it in.
| | 00:42 | If you've attached some code to an
object or created a code module and that
| | 00:47 | object is open when you switch to the
Visual Basic Editor, you'll see that code
| | 00:52 | window, unless you closed it earlier.
| | 00:54 | In the database with existing code,
like this one, you'll see the Project
| | 00:58 | Explorer panel on the left.
| | 01:00 | That panel contains references
for the code that's available.
| | 01:03 | If you don't see it, you can display it
by clicking the Project Explorer button
| | 01:07 | on the standard toolbar or by pressing
Ctrl+R. You can manage windows within the
| | 01:13 | Visual Basic Editor exactly the way that
you do in the Windows operating system.
| | 01:18 | You have the Minimize, Maximize,
and Close buttons on all the windows and you
| | 01:23 | also have the same thing for the Visual Basic
Editor at the top-right corner of the screen.
| | 01:27 | Also within the Visual Basic Editor,
you can view the Object Browser either by
| | 01:32 | pressing F2 or by clicking the Object
Browser button on the standard toolbar. So
| | 01:37 | this is the Object Browser button and
when you open it, you get a list of all the
| | 01:42 | objects that are available
within the Access Object Model.
| | 01:46 | In Access objects are broken down to
classes and then members of those classes.
| | 01:51 | So, for example, if you wanted to
scroll down in the list and display the DoCmd
| | 01:58 | object, clicking it displays a
list of all of the members of DoCmd.
| | 02:04 | So, for example, you can apply a
filter, you can make the computer sound a
| | 02:08 | beep, and so on. And then to close the Object
Browser, you can just click the Close button.
| | 02:14 | If you want to save your work, you can
do so either by clicking the Save button
| | 02:18 | on the standard toolbar or by pressing
Ctrl+S. Also, if you want to print a code
| | 02:23 | module, you can make sure that the code
module is active by clicking inside of
| | 02:27 | it, so that the cursor is flashing,
and then press Ctrl+P, or click the File menu
| | 02:32 | header and then click Print.
| | 02:36 | If you want to return to Access, you can
do so either by pressing Alt+F11 or by
| | 02:41 | clicking the view Microsoft Access button.
| | 02:44 | Like any editing environment, it
will take you some time to become fully
| | 02:48 | comfortable using the Visual Basic Editor.
| | 02:50 | Once you're familiar with its
controls though, you'll find that it is a
| | 02:54 | powerful, flexible tool that helps
you manage your VBA code effectively.
| | Collapse this transcript |
| Creating, exporting, and deleting code modules| 00:00 | When you're ready to write VBA code
that can be used anywhere in your database,
| | 00:04 | you can create a code module.
| | 00:06 | You create a code module by going into
the Visual Basic Editor, which I will do
| | 00:11 | by pressing Alt+F11. And then I have
some existing code for a form that I want
| | 00:18 | to get out of the way, so I'll just
click the close button to close it.
| | 00:21 | If it doesn't appear in your
database, don't worry about it.
| | 00:23 | Now to create a code module click the
Insert menu header and then click Module.
| | 00:30 | Doing so creates a code module, and in
this case it's named Module1, and it comes
| | 00:35 | with the Option Compare Database statement.
| | 00:38 | And what that does is it allows Access
to compare its database to its previous
| | 00:43 | state so that it can detect changes.
| | 00:45 | So it's very important that
you leave that statement there.
| | 00:48 | And you'll also notice that in the
Project window, on the left side of the screen,
| | 00:54 | you'll see that there is now a Modules
section under the Project, and that the module,
| | 00:59 | Module1, appears there.
| | 01:01 | When you create a code module, you can
now create a procedure, and that procedure
| | 01:05 | can either be a subroutine or a function.
| | 01:08 | In this case, I'll just create a very
simple subroutine, and it will be the
| | 01:12 | traditional "hello world" message.
| | 01:14 | So I'll type "Sub," which is a first for
sub or subroutine a space and then the
| | 01:21 | name of the subroutine, and that will be
"HelloMessage," all one word, and then
| | 01:29 | a left parenthesis and a right
parenthesis with nothing between them.
| | 01:34 | Now when I press Enter, Access creates
the End Sub statement for me and then
| | 01:39 | also draws a line above the subroutine
so that it is in a distinct section as
| | 01:45 | compared to the Option
Compare Database statement.
| | 01:47 | Now I can just type my code. And don't
worry about the specifics of this too much.
| | 01:51 | I just want to indicate how it works.
| | 01:54 | We'll get into the specific
command that I use later in the course.
| | 01:58 | So I'll type message box MsgBox,
a space, then a, double quote, and then
| | 02:05 | Hello, World, then a period, double
quote to end the text string, and a right parenthesis.
| | 02:15 | Now if I press F5, Access runs the
code, and it displays a message box with
| | 02:21 | "Hello, World" in it.
| | 02:23 | You can have multiple subroutines in a
code module as long as each one has its
| | 02:27 | own Sub statement at the top and
End Sub statement at the bottom.
| | 02:32 | To illustrate the point, I'm going to
click below the End Sub statement, and
| | 02:37 | I'll press Enter just to give myself
a little more room. And now I'll type
| | 02:41 | Sub, space, and then Goodbye, and then a left and
right parenthesis. Press Enter and Access again draws a
| | 02:52 | line to separate this subroutine from
the previous subroutine and also gives me
| | 02:57 | my End Sub statement.
| | 02:59 | And I'll go ahead and just
type "MsgBox ("Goodbye.")."
| | 03:13 | Now because the cursor is flashing
inside of the Goodbye subroutine, if I press
| | 03:19 | F5, Access runs that code, whereas if I
were to click within the HelloMessage
| | 03:25 | subroutine and then press F5,
Access would run that code.
| | 03:30 | Another element of the Visual Basic
Editor worth pointing out is that when you
| | 03:34 | have multiple subroutines in a code
module, you can click the sections list box
| | 03:38 | at the top right of the Editor and then
click the code section to which you want
| | 03:42 | to move--and that is here in the
top-right corner, just below the title bar.
| | 03:47 | So you can see currently there is the
HelloMessage, and that is the name of the
| | 03:51 | subroutine where the
cursor is currently flushing.
| | 03:54 | If I click the list box's down arrow,
we'll see that we have Declarations, which
| | 03:59 | is where the Option Compare
Database statement is, and also Goodbye.
| | 04:04 | So if I were to click Goodbye in this
list then the cursor would start flashing
| | 04:09 | inside of the Goodbye subroutine.
| | 04:11 | This capability doesn't mean much when
you have one or two subroutines in the
| | 04:15 | code module, but it means a lot when
you have a lot of them, so keep it in mind.
| | 04:20 | The natural next question is whether
you can have multiple code modules in the
| | 04:24 | single database, and the
answer is yes, absolutely.
| | 04:28 | To create a new code module, just do
what you did to create the first one.
| | 04:31 | Click tools and then Module.
| | 04:34 | Just as you keep different types of
data in separate databases, you should keep
| | 04:38 | different types of
subroutines in different code modules.
| | 04:41 | For example, if you have code that
displays forms and another set that
| | 04:45 | displays tables, you should put all of
the forms macros in one module and the
| | 04:51 | tables macros in another.
| | 04:53 | The default names for code modules are
Module1, Module2, and so on, which isn't
| | 04:58 | much help when you're trying to
remember which module contains which macros.
| | 05:02 | You can rename a code module by
clicking the Module in the Project panel, and
| | 05:07 | then in the Properties pane, which is
just below the Project pane, you can click
| | 05:11 | in the Name Properties box and
type a new name for the module.
| | 05:17 | And in this case I'll just
type HelloGoodbye and press Enter.
| | 05:23 | When I do, you can see that the code
module's name changed here in the Project
| | 05:27 | panel and also here on
the title bar of the module.
| | 05:32 | Now let's say that you've created
some very useful code that you want to
| | 05:36 | share with a colleague.
| | 05:37 | You can always copy and paste the
code into a Word document or email
| | 05:41 | message, but doing so runs the risk of
having a spellchecker or auto-correct
| | 05:45 | operation change your code.
| | 05:47 | The cleaner way to save your code
into text file is to right-click the code
| | 05:51 | module's name and then click Export File.
| | 05:54 | Doing so opens the Export File dialog box,
and you can save the text as a .bas file.
| | 06:02 | If you want to save the file as a plain
text file, you can click the Save as type
| | 06:07 | down arrow and click All Files.
| | 06:12 | Just as you can export a code
file, you can import one too.
| | 06:15 | To import a code file, click the File
menu and then click Import File. You can
| | 06:21 | then find the file that you
want to import and then click Open.
| | 06:26 | You can get rid of a code module by
right-clicking the module name and
| | 06:29 | then clicking Remove.
| | 06:33 | When you do, Access displays a dialog
box asking if you want to export the
| | 06:37 | code before removing it. You can either click
Yes, No, Cancel, or get help on the operation.
| | 06:43 | In this case, I'll click
Cancel to cancel the operation.
| | 06:46 | Working with code modules in the
Visual Basic Editor is not complicated, but
| | 06:51 | there are a lot of options
you can take advantage of.
| | 06:53 | You can use the techniques from this
moving to create and manage your code
| | 06:57 | modules effectively.
| | Collapse this transcript |
| Creating a subroutine| 00:00 | When you read about VBA programming
you might see the writers refer to
| | 00:03 | subroutines and functions.
| | 00:06 | Even though some authors use the
two terms synonymously, they actually
| | 00:10 | mean different things.
| | 00:11 | The generic name for both
subroutines and functions is a procedure.
| | 00:15 | To demonstrate how to create the
subroutine, I'll press Alt+F11 to open up the
| | 00:20 | Visual Basic Editor and
then click Insert > Module.
| | 00:26 | You create a subroutine by opening a
code module and then typing Sub, then
| | 00:30 | a space, and then the subroutine's name.
| | 00:35 | The subroutine's name can have no internal
spaces, and it has to start with a letter.
| | 00:41 | So in this case I will make it Welcome,
then type left and right parenthesis--in
| | 00:49 | this case with nothing
between them--and then press Enter.
| | 00:51 | When you press, Enter Access creates the
subroutine, adds an End Sub statement, and
| | 00:58 | gives you a blank line where
you can start typing your code.
| | 01:01 | In this case, I'll have a display or
message box that says, "Welcome to the
| | 01:04 | Shipment Tracking database."
| | 01:06 | So MsgBox and don't worry; we'll cover
message boxes later. Then the space, left
| | 01:11 | parenthesis, double quotes, and then
I'll just type in "Welcome to the Shipment
| | 01:18 | Tracking database," then a period,
and double quotes, and a right parenthesis.
| | 01:26 | Now when I press F5 Access runs the
macro and displays my message box.
| | 01:32 | Subroutines are the
backbone of Access programming.
| | 01:35 | You use them to manipulate
database objects and add functionality for
| | 01:39 | your users.
| | Collapse this transcript |
| Creating a function| 00:00 | In the previous movie, I showed
you how to create a subroutine.
| | 00:03 | Functions are similar to subroutines,
but they're different in that you need to
| | 00:07 | give the function a
value, or values, to work with.
| | 00:10 | To do that, you tell Access what to
call the value, or values, it's receiving and
| | 00:15 | what type of data it is.
| | 00:17 | In this case, I'll create a
function that calculates markup for a
| | 00:21 | particular product.
| | 00:23 | First I'll go into the Visual Basic
Editor by pressing Alt+F11 and then
| | 00:28 | inserting a code module, so I'll click
Insert on the menu bar and then click Module.
| | 00:34 | I will start by typing "Function," and
that is the keyword indicating that this is
| | 00:42 | a function that returns a value, as
opposed to a subroutine, which can manipulate
| | 00:48 | the database, but does not
actually calculate anything.
| | 00:52 | So we have Function and I'll call it Markup,
then a space, and then a left parenthesis.
| | 01:00 | Now when we created a subroutine in
the previous movie I didn't pass any what
| | 01:05 | are called arguments or values to the
routine, but because this is a function, we
| | 01:10 | need to give it some values and
tell Access what type of data it is.
| | 01:15 | I'll get into data types a lot more
later on in the course, but for now I'll
| | 01:18 | just say that we have a variable named
cur and then Price, a space, and
| | 01:25 | then As, a space, and then we need
to tell the data type, and this will be a
| | 01:30 | currency value. Currency. And then type a
right parenthesis and type "as" and then
| | 01:39 | indicate what the Markup will be,
and for that, it's also a currency.
| | 01:44 | Now I'll press Enter and just as
Access added the End Sub statement to my
| | 01:50 | subroutine, it will also gives
me an End Function statement.
| | 01:54 | Now we can create the
calculation for the function Markup.
| | 01:58 | So for that it will be Markup, then
equal, a space, and then it will be
| | 02:04 | current price, or curPrice,
times .1, and then press Enter.
| | 02:14 | Now what I can do is use the Markup
calculation inside of another procedure, and
| | 02:19 | it can be either a
subroutine or another function.
| | 02:22 | In this case, I'll make it a subroutine.
| | 02:24 | To create the subroutine, I'll type Sub,
a space, and then call it PricePlus--
| | 02:35 | again, no spaces--and then an empty pair of
left and right parenthesis, and press Enter.
| | 02:41 | Now I can type "MsgBox," then a left
parenthesis and then type in a double quote
| | 02:49 | mark and then "For a price of 12.50" or
$12.50, "markup is," then a space and then
| | 03:03 | a double quote and then an ampersand--
and we'll get into all of this later on in
| | 03:07 | the course, so I will just put it in for now.
| | 03:09 | For a price of 12.50 Markup is...then
type "Markup," and a left parenthesis. And now
| | 03:16 | you can type in a value for the
currency price variable, and that is 12.5, then
| | 03:22 | a right parenthesis, then an ampersand,
double quote, and a period, and then
| | 03:28 | another double quote, and a right
parenthesis to end the MsgBox function's argument.
| | 03:35 | So what I'm saying is that for a price
of $12.50 markup is. Then I use the Markup
| | 03:40 | function to do the
calculation and finish with a period.
| | 03:44 | So now when I press F5 Access indicates
that for a price of 12.50 Markup is 1.25,
| | 03:51 | and when I click OK the message box closes.
| | 03:55 | Access functions are a little bit
more limited than functions are in Excel.
| | 03:59 | For example, in Excel you can use a
function in a worksheet calculation. You
| | 04:05 | can't do anything similar in Access;
instead, you can only use functions inside
| | 04:09 | of other VBA procedures.
| | Collapse this transcript |
| Adding comments to your code| 00:00 | It takes even the most skilled VBA
programmer some time to examine an unfamiliar
| | 00:04 | database's code to learn how it operates.
| | 00:07 | Whether you're viewing a new
database or one you created some time ago,
| | 00:11 | you'll find that adding comments
to the code helps you understand its
| | 00:14 | operation much more quickly.
| | 00:16 | To demonstrate how to add comments, I'll
switch over to the Visual Basic Editor.
| | 00:20 | To do that from the main
database window, I'll press Alt+F11.
| | 00:24 | If you don't see the code module
attached to the Form_Close database, then go
| | 00:29 | over to the Project window and
double-click it to open that code.
| | 00:34 | To add a comment, you type a single
quotation mark at the start of a line and
| | 00:38 | then type the comment.
| | 00:39 | So in this case, let's say that
I want to add a comment to the
| | 00:43 | Command0_Click event code.
| | 00:45 | I'll click above DoCmd.
| | 00:48 | I'll press Enter to give myself some
room to work with and then type a single
| | 00:52 | quote and then just a comment, such as
"Code to close the database" and a period,
| | 01:00 | and I'll press Enter.
| | 01:01 | When I press Enter, or otherwise move
off of the line, Access displays the code
| | 01:06 | in green, which indicates that is a
comment, and it's not code that will be run.
| | 01:10 | You can also add a comment
to an existing line of code.
| | 01:13 | So let's say, for example, that I go down to
the second subroutine, and that is Command1_Click.
| | 01:20 | Then I click to the right of the line
of code, which is a sequence SaveAll.
| | 01:24 | Press Space, and now I'll type a single
quote and then "Saves all objects before
| | 01:32 | quit" and then press the down arrow key.
| | 01:36 | When I do, Access displays the Saves
all objects before quit, which is a comment
| | 01:42 | in green, but it retains the regular
formatting for the DoCmd.Quit line, and it
| | 01:48 | will run as expected.
| | 01:50 | Another good use for comments is to
render a line of code, or several lines
| | 01:55 | of code, inoperative.
| | 01:57 | So let's say, for example, that this
is a larger procedure, and I want to
| | 02:01 | temporarily disable the CloseDatabase command.
| | 02:04 | To do that, I can just click to the
left of the first character, type a single
| | 02:08 | quote, and then move off of the line.
| | 02:10 | When I do, Access treats that
code as a comment and won't run it.
| | 02:15 | Then when I'm ready for it to be
active again, I can just delete the single
| | 02:20 | quote, and the line is no longer comment.
| | 02:23 | Those of the mechanics of adding
comments to your VBA code, but the real secret
| | 02:27 | to creating effective comments is
to know when and where to add them.
| | 02:31 | The first comment I add to any
nontrivial subroutine or function includes my
| | 02:35 | name and when I created the procedure.
| | 02:38 | Then if I think there is any chance
the procedure's purpose is unclear, which is
| | 02:43 | more often than I'd like, I add a
comment describing what the code does and the
| | 02:47 | name of the database.
| | 02:49 | I add the database name so that in
case someone copies the code to another
| | 02:52 | database and would like to see
how the original was structured,
| | 02:55 | they don't have to try to
visualize the database based on the code.
| | Collapse this transcript |
| Running a VBA procedure| 00:00 | After you create a VBA procedure, you
can run that procedure from within the
| | 00:05 | Visual Basic Editor.
| | 00:07 | To illustrate that point,
I'll create a new code module.
| | 00:11 | I'll do that by pressing Alt+F11 to go
to the Visual Basic editor and then click
| | 00:17 | the Insert menu and click Module.
| | 00:19 | Now I'll just create a very quick,
subroutine. So I'll call it Sub ShowMsg,
| | 00:26 | then open and close parentheses, and in the
middle, I'll just type "MsgBox" and then "hello"
| | 00:36 | and press the down
arrow to get myself some room.
| | 00:38 | When the insertion point--that is, the
blinking cursor--is within the body of a
| | 00:42 | subroutine, then pressing F5 will run it.
| | 00:46 | So if I were to press F5 right now, see
the message box with the message hello.
| | 00:52 | If you click outside of the
subroutine or any procedure and then press F5,
| | 00:57 | Access displays the macros dialog box,
and you can select the macro that you want to run.
| | 01:03 | And in this case, you do have ShowMsg
and if you click Run, it runs as normal.
| | 01:08 | And you'll find this behavior is
exactly the same in 2003, 2007, and 2010.
| | 01:15 | There are other ways to run a VBA procedure.
| | 01:19 | I'll show you how to do that in
the chapter on handling errors.
| | Collapse this transcript |
|
|
3. Debugging Your VBA CodeManaging errors in VBA code using On Error statements| 00:00 | One of the more challenging aspects of
Access VBA programming is handling errors.
| | 00:05 | Errors can result from programming
logic mistakes or user mistakes, but in
| | 00:09 | either case, you should include code
that lets your procedures fail gracefully
| | 00:14 | when something goes wrong.
| | 00:15 | For this exercise, we'll use
the ErrorHandling code module,
| | 00:18 | so I'll just go over to the
Navigation pane and double-click it.
| | 00:22 | What this code does, just as a brief
overview, is it accepts input in an input box
| | 00:28 | that ask for a number, and then it
adds three to the number and then displays
| | 00:33 | the result in a message box.
| | 00:34 | So I'll go ahead and with the cursor blinking
in the body of the code, press F5 to run it.
| | 00:40 | I am asked to enter a number.
| | 00:42 | I'll type in 3 and click OK.
| | 00:45 | When I do, Access displays a message
box with the total, three plus three, which
| | 00:51 | is six, and I can click OK to go
back to the Visual Basic Editor.
| | 00:55 | But now let's see what happens
if I type in an incorrect value.
| | 00:59 | So I'll press F5 again and instead of
entering a number, I'll type in a letter--
| | 01:04 | in this case the letter A--and then click OK.
| | 01:07 | When I do, Access indicates
that there is a run-time error,
| | 01:10 | in this case a type mismatch,
| | 01:12 | and what a type mismatch means is that
Access was expecting one type of data--in
| | 01:17 | this case a number--and it got
another one--in this case a letter.
| | 01:21 | So that's a fairly informative message,
but what happened was that the routine
| | 01:25 | just came to a sudden stop. And if
that happens, it takes the user out of
| | 01:29 | experience, and what's worse is it's
possible that they can get into VBA and
| | 01:34 | have the ability to start messing with your
code, and that is something you want to avoid.
| | 01:39 | So you need to find a way for
your code to fail gracefully.
| | 01:42 | I'll go ahead and click End to
go back into Visual Basic Editor.
| | 01:47 | So how do you make your
programs fail gracefully?
| | 01:49 | You use error handling.
| | 01:51 | There are three different modes that you can
use for error handling inside of Access VBA.
| | 01:55 | The first is the default, and that is
the option that is currently highlighted
| | 02:00 | here, On Error GoTo 0.
| | 02:02 | That's the default handling mechanism.
| | 02:04 | When an error occurs, Access stops
the procedure, and it displays a message
| | 02:09 | indicating the type of error.
| | 02:11 | The second type of error handling you
can do is using the On Error Resume Next
| | 02:16 | statement, which is right below
the On Error GoTo 0 statement.
| | 02:20 | I have it currently in as a comment.
| | 02:23 | On Error Resume Next says okay,
there was an error. Don't worry about it;
| | 02:28 | just keep going as best you can.
| | 02:30 | So in other words, ignore all errors
and run the code until you get to the end.
| | 02:34 | Now that's something you
might want to do occasionally.
| | 02:37 | I personally have never used it in my
programming, because I always want to know
| | 02:41 | when an error is occurring.
But you might find a use for it.
| | 02:45 | Now the third option is much more
useful, and that is on Error GoTo and then the
| | 02:50 | name of a line label.
| | 02:52 | So I'll go ahead and type a single quote
to the left of the On Error GoTo 0 line
| | 02:58 | of code and then go down to On Error
GoTo Handler, followed by a colon, and delete
| | 03:04 | the single quote in front of
it so it's now active code.
| | 03:08 | On Error GoTo Handler says, if there is
an error, go to the line labeled Handler,
| | 03:14 | followed by a colon, and then start
executing code from there, and that code is at
| | 03:19 | the bottom of the subroutine.
| | 03:21 | So we have handler with a quote
and then MsgBox (Not a number).
| | 03:25 | So let's see how that works.
| | 03:27 | I have ErrorHandling turned on with GoTo
Handler in case something is wrong and a message box.
| | 03:32 | So I'll press F5 to run the code.
| | 03:35 | It asked me to enter a number.
| | 03:36 | I'll type in "a" and click OK.
| | 03:40 | When I do, I get the message box
saying that it's not a number, and click OK.
| | 03:45 | So what happened was that the program
failed much more gracefully than it did before.
| | 03:48 | But now with that error handling
turned on, what would happen if I were to
| | 03:53 | enter a number correctly?
| | 03:55 | Well, let's take a look at the code.
| | 03:57 | First in the input box, I am
asked to enter a number, so I do.
| | 04:00 | Then Access calculates the result,
adding 3 to whatever number I typed in, and
| | 04:05 | then it displays a message box.
| | 04:07 | And then it hits the Exit Sub line of code.
| | 04:11 | And what that does is it exits the
subroutine. And I don't have to run the code
| | 04:17 | again, because we've already seen that.
| | 04:18 | When I typed in the number the first
time, it displayed the message box with the
| | 04:22 | result and then exited the subroutine.
| | 04:25 | Now it's very important that you put
an Exit Sub line of code right above
| | 04:30 | any error handling that you create so that
Access knows that it can stop running the error.
| | 04:35 | If I were to comment out the Exit Sub
line by typing a single quote in front of
| | 04:40 | it and then press F5, I'm not going to
create an error; instead, I'm going to
| | 04:45 | type in a number, the number 3, and then click OK.
| | 04:48 | And when I do, Access displays the
result in a message box, but then when I
| | 04:53 | click OK, it says, "Not a number."
| | 04:56 | That's because it kept running;
| | 04:58 | there is no Exit Sub line of code.
| | 05:00 | So, clicking OK to close the message box.
Always be sure that you have an Exit
| | 05:04 | Sub line of code above any
error handling that you create.
| | 05:09 | And if you have multiple line labels
with multiple ways of handling errors then
| | 05:13 | make sure that you have an Exit Sub
statement in each of those routines as well.
| | Collapse this transcript |
| Stepping through a subroutine or function| 00:00 | Running a VBA procedure shows you
the code's end result, but it doesn't
| | 00:04 | necessarily show you how the
code arrived at that result.
| | 00:07 | If you want to dig into a section of
code to find out how it works, or to
| | 00:10 | identify the cause of an error, you
can move through the code step by step.
| | 00:15 | In this movie, we'll use
the WatchValue code module,
| | 00:19 | so I'll just go over to the
Navigation pane and double-click it.
| | 00:22 | This code finds a total of all
orders within the Orders database.
| | 00:27 | If I press F5 then I'll run all the way
through the code, and I'll simply see the result.
| | 00:32 | Now if I want to go through
step by step, I can press F8.
| | 00:36 | So I'll click anywhere in the body of
the code that I want to test, and then
| | 00:40 | instead of pressing F5, I'll press F8.
| | 00:43 | When I do, Access highlights the first
line. Then it skips over the variable
| | 00:49 | declarations and it goes to the
assignment of setting the curTotal variable to 0.
| | 00:54 | Press F8 again. It sets the myR
variable to the Orders table, and then I
| | 01:02 | go into a Do Until loop.
| | 01:04 | Basically, what this code does is to
get each order total value from within the
| | 01:09 | Orders table and add it to current total.
| | 01:12 | So if I press F8, it moves through
each of the rows, doing the Do Until loop.
| | 01:19 | So that's the second time through,
and now that's the third time through. And when
| | 01:23 | I press F8 again, it goes back up, asks
if it's at the end of the file--it is.
| | 01:29 | So when I press F8, it goes down to the
end of the subroutine. And if I press F8
| | 01:34 | again, it steps out of the testing mode.
| | 01:37 | So that's the basics of
using F8 to step through code.
| | 01:40 | Now I will show you one or two other things.
| | 01:42 | So I'll press F8 again to start stepping
through, and I'll just press it a number
| | 01:46 | of times until I assign a new
value to the current total variable.
| | 01:51 | So I currently have Do Until
myR.EOF is highlighted. Press F8 again.
| | 01:57 | Now I'm going to assign a new value to curTotal.
| | 02:00 | Basically, I'm adding the current
records OrderTotal. And press F8 again.
| | 02:06 | Now if I were to hover my mouse pointer
over the curTotal variable, here we see
| | 02:11 | that the curTotal is 44.95.
| | 02:14 | So that's a great way of checking what a
value is within a subroutine as you go along;
| | 02:19 | just hover your mouse pointer over it.
| | 02:21 | Now there are two other ways that you
can move through your code, and both of
| | 02:26 | those deal with having any nested subroutines.
| | 02:30 | So let's say, for example, that
you have one subroutine and you call
| | 02:33 | another subroutine.
| | 02:35 | Two things that you can do are step over
that subroutine or step out of it once
| | 02:39 | you're inside of it.
| | 02:40 | Stepping over means that Access would
run all of the code in the subroutine and
| | 02:45 | then start debugging again
here in this code module.
| | 02:49 | Step out would indicate that you are
already in a subroutine that is called by
| | 02:54 | another subroutine code--in other words,
something that is nested--and you want to
| | 02:58 | run the rest of the code and when you
get back here, start going step by step.
| | 03:03 | To do that, you can click the Debug menu
header and then either Step Over, which
| | 03:08 | is also Shift+F8; or Step
Out, which Ctrl+Shift+F8.
| | 03:13 | In this case, I don't have any nested
subroutines, so I won't demonstrate, but I
| | 03:18 | just want you to know
that those options are there.
| | 03:20 | Now finally, if you are stepping
through a routine and you want to end it
| | 03:25 | then you can either press F5 to run
it all the way to the end, or you can
| | 03:29 | click the Reset button, which ends the
debugging mode and allows you to edit
| | 03:35 | your code normally.
| | 03:37 | Moving through your code one step at a
time can be a laborious process, but it
| | 03:41 | is often the easiest way to find an
error, or to determine how the original
| | 03:45 | programmer constructed the code's logic.
| | 03:47 | You'll find that pressing the F8
key is usually time well spent.
| | Collapse this transcript |
| Setting breakpoints in your code| 00:00 | When you test your VBA code, you will
often want to stop your procedure partway
| | 00:04 | through so that you can verify
everything is working correctly up to that point.
| | 00:09 | You can do that by setting a breakpoint.
| | 00:11 | For this movie, we're going to
use the WatchValue code module,
| | 00:15 | so I'll double-click that over
in the Navigation pane to open it.
| | 00:19 | This code looks in the Orders table and it
finds a running total for all of the order values.
| | 00:26 | In the previous movie, I showed you how
to step through a procedure by pressing
| | 00:30 | F8, and that goes through step by step.
| | 00:32 | Let's say that rather than going step by
step what you want to do is to run the
| | 00:37 | procedure to a certain point and then stop.
| | 00:40 | So let's say that I want to
break at the end of each loop.
| | 00:43 | I can set a breakpoint by moving the
mouse pointer onto this horizontal bar,
| | 00:48 | which is to the left side of the code, and then
clicking next to the line where I want to stop.
| | 00:54 | When I do, Access displays a brick
red dot and also highlights the line at
| | 00:59 | which I will stop in red.
| | 01:01 | Now when I press F5, instead of running all
the way through, Access will at the breakpoint.
| | 01:06 | So I'll do that, and Access has gone
through the Loop in the first time.
| | 01:10 | Now if I hover my mouse pointer over
the curTotal variable, Access displays the
| | 01:17 | value that is currently
assigned to it, and that is 44.95.
| | 01:21 | Now, if I press F5 again, Access runs
through the Loop again, and now when I
| | 01:26 | hover the mouse pointer over curTotal,
it displays the value of 127.4. So it
| | 01:32 | added the second row,
OrderTotal, to the previous value.
| | 01:36 | Now if I press F5 again, Access
does the same thing for the third row.
| | 01:40 | Now when I press F5, Access completes
the program, because there were no more
| | 01:44 | rows in the Orders table.
| | 01:47 | There are different ways that
you can work with breakpoints.
| | 01:49 | I'm going to press F5 again to start
running the code and the Loop statement is
| | 01:54 | highlighted, and let's say
that I want to step out;
| | 01:57 | in other words, I want to stop running the code.
| | 01:59 | To do that, go up to the standard
toolbar and click the Reset button.
| | 02:04 | Doing so it steps you out of debugging mode.
| | 02:08 | You can turn a breakpoint off, that
is, remove it, by clicking the dot, which
| | 02:12 | indicates that there is a breakpoint there.
| | 02:14 | So clicking it removes it,
and clicking it brings it back.
| | 02:18 | You can set multiple breakpoints in your code.
| | 02:20 | So let's say, for example, that you
wanted to have a breakpoint here at the top
| | 02:24 | of the Do Until loop. You could;
| | 02:26 | it wouldn't make a lot of sense in this
case, but that just illustrates the point
| | 02:30 | you can have multiple
breakpoints. And I'll clear that one away.
| | 02:33 | And finally, I should point out that
clicking to the left of an empty line of
| | 02:37 | code doesn't do anything.
| | 02:39 | So if I try to establish a breakpoint here
or here on a blank line, it doesn't happen.
| | 02:44 | Don't forget that you can use comments to
have Access ignore certain lines of code.
| | 02:49 | Combining comments with breakpoints
helps you limit which code will run, greatly
| | 02:53 | simplifying the debugging process.
| | Collapse this transcript |
| Verifying output using temporary message boxes| 00:00 | When you write VBA code, you will
often want to display the result of a
| | 00:03 | calculation or provide
users with other information.
| | 00:07 | One useful way to provide that
information is to create a message box.
| | 00:11 | For this movie, we will use
the WatchValue code module,
| | 00:15 | so I'll double-click that over the
Navigation pane to open it, and display my code.
| | 00:20 | And what this code does is it steps
through every record in the Orders table and
| | 00:26 | adds the values of the OrderTotal
field to the curTotal variable.
| | 00:32 | What it doesn't do is
display that information for you.
| | 00:35 | So in this movie, we will add a message box.
| | 00:38 | I will click to the right of the
curTotal assignment line and press Enter twice
| | 00:44 | to give myself some room to work.
| | 00:46 | Then I will just type in
a very simple message box.
| | 00:49 | So it'll be MsgBox, a space, and then type in
the prompt, and the prompt is a string, so
| | 00:56 | it needs to go within double quotes.
| | 00:59 | So I will type double quote mark and
then "The running total is," then a space
| | 01:06 | and then a dollar sign and then a double quote
mark, press space, and then type an ampersand.
| | 01:14 | You use an ampersand to
concatenate multiple strings together.
| | 01:18 | So, do that, press the spacebar,
and then type in the name of the variable,
| | 01:23 | curTotal, then a
space, and then ampersand, double quote, and
| | 01:30 | then a period, and then double quote again.
| | 01:32 | Now press the down arrow to remove the tooltip.
| | 01:36 | So what I have now is a message box that
will indicate that the "running total is"
| | 01:42 | and then show the value that is in the
curTotal variable at this point. And also
| | 01:49 | note this statement's
location within the routine.
| | 01:53 | I have a Do Until loop.
| | 01:55 | So what it will do is it will go
through every record in the table--in this
| | 02:00 | case there are only three--and after
it updates the curTotal variable, it
| | 02:05 | displays a message box.
| | 02:06 | So what I should see is a series of three
message boxes, one for each line in the table.
| | 02:13 | So if I press F5 to run the code, I get
a running total 44.95. Click OK, a running
| | 02:20 | total of 127.4, click OK, and then
225.35, click OK, and the routine ends.
| | 02:29 | This is a very basic message box.
| | 02:31 | There are some other
parameters that you can change.
| | 02:34 | To indicate what those are, I will click
to the right of the current message box
| | 02:38 | line of code and then type a comma.
| | 02:41 | When I do, Access displays the
other arguments that I can set,
| | 02:45 | and the first is the style of the message box.
| | 02:49 | A message box can have buttons inside of it.
| | 02:52 | So for example, if I were to scroll
down in the list--and I will scroll all the
| | 02:59 | way to the bottom--you can see that
you can have the options for Yes and No
| | 03:03 | buttons, YesNoCancel buttons. And what
happens is that you can use those inputs,
| | 03:10 | or those button clicks, as
input for the subroutine.
| | 03:13 | So let's say that if someone were to
click yes or no, you could say "if vb yes,
| | 03:20 | then," and create some code based off of that.
| | 03:22 | I will show you how to do that in
another movie later on in this course.
| | 03:27 | For now, I'll just go ahead and click
vbOKOnly and then press Tab, and that is the default.
| | 03:34 | What it does is it displays the message box,
and all you can do is click OK to dismiss it.
| | 03:39 | Now if I type a comma, I'll show you
the last argument that I will discuss in
| | 03:43 | this movie, and that is the title.
| | 03:45 | And the title, as the name implies, is the
text that appears on the message box's title bar.
| | 03:51 | It's a string, so it needs to go in quotes.
| | 03:53 | So I will type a double quote and
then just type in "Running Total," and then
| | 04:00 | a double quote.
| | 04:02 | I'm not going to worry about
any of the other arguments,
| | 04:04 | so I press the down arrow to clear away
the tooltip. And now, when I press F5,
| | 04:10 | Access displays a message box and it has
the Running Total text on the title bar
| | 04:16 | like I wanted it to.
| | 04:18 | Click OK, click OK again, and for a final
time, click OK, and I'm done running my code.
| | 04:25 | Message boxes let you display information
without changing any values in your database.
| | 04:30 | When you or your user is done looking
at the information, just click the OK
| | 04:34 | button to dismiss the message
box and keep right on working.
| | Collapse this transcript |
| Watching a value in a routine| 00:00 | When you create a subroutine that
contains a For Next or Do loop, you might
| | 00:05 | encounter situations where your code
returns unexpected values but doesn't
| | 00:09 | contain any obvious errors.
| | 00:11 | Rather than create a series of
message boxes or print values to the
| | 00:14 | immediate window, you can create a
watch that displays the value of a
| | 00:18 | variable continuously.
| | 00:20 | You can use the watch to monitor the
variable's value and by adding breakpoints
| | 00:24 | in your procedure, perhaps
find where the error occurs.
| | 00:27 | For this movie, we will use
the WatchValue code module,
| | 00:31 | so I will double-click it to open it.
And if you're continuing on from the
| | 00:37 | previous movie where I added a message
box line here, go ahead and comment that
| | 00:41 | out, because we are not going to use it.
| | 00:43 | I will reset my code between exercises.
| | 00:47 | To set a watch, click the Debug
menu and then click Add Watch.
| | 00:53 | When you do, the Add Watch dialog
box appears and you can identify the
| | 00:58 | expression that you want to watch.
| | 01:00 | And that could be an equation, or it
can be a single variable. And in this
| | 01:04 | case, I will just make it the current total
variable, and that is curTotal and then click OK.
| | 01:13 | When I click OK, the Watches pane
appears at the bottom of the Visual Basic
| | 01:18 | Editor, with the expression curTotal,
and its current value, its type, and its context.
| | 01:24 | What I'm interested in doing is to
watch the variable as it goes along,
| | 01:29 | so what I'm going to do is I am going to set
a breakpoint to the left of the Loop line.
| | 01:34 | So for that, I am going to click the
vertical bar next to the code window.
| | 01:39 | That brick red circle indicates that that
is where my code will break as I go along.
| | 01:44 | Now, I'll press F5 to start running
my code, and it runs to the break. And you
| | 01:48 | can see in the Watch window that the Value
has been updated to 44.95. Press F5 again;
| | 01:55 | it runs through the Loop one more time.
The value is 127.4, and the Type is a
| | 02:00 | Currency value, and the Context is
within the RunningTotal subroutine.
| | 02:06 | Press F5 one more time and we get the
final value of 225.35. F5 again and we've
| | 02:14 | run out of rows inside the table so the
procedure ends, and the Watch resets to
| | 02:19 | out of context, because there is no
value assigned to curTotal at the moment,
| | 02:23 | and the Type is empty.
| | 02:25 | If you want to edit a watch, you can
right-click it and then click Edit Watch
| | 02:30 | and that opens the Edit Watch dialog
box, which is basically the same as the Add
| | 02:35 | Watch dialog box, just with a slightly
different name and the expression already filled in.
| | 02:41 | You can either then delete the
watch or just click OK to leave it.
| | 02:45 | In this case, I'll go ahead and
click Delete to get rid of it.
| | 02:48 | And then to close the Watch's window, you can
click the Close button at this top-right corner.
| | 02:53 | Watching a variable's value helps
you verify that your code produces the
| | 02:57 | correct results and, more importantly,
follows the correct path to generate
| | 03:01 | those results.
| | Collapse this transcript |
|
|
4. Defining Variables, Constants, and CalculationsIntroducing Access data types| 00:00 | When you work with variables in your
VBA code, you should define them, so they
| | 00:04 | can store the data you want to work with.
| | 00:06 | Access VBA has several data types,
which I'm going to summarize in the
| | 00:10 | following three slides.
| | 00:12 | The first four data types on this slide
all deal with numbers that do not have
| | 00:15 | a decimal component.
| | 00:17 | Byte stores numbers from 0 to 255; and
Boolean, which is used in Boolean logic,
| | 00:23 | can be either true or false, and that's
often represented as either 0 or 1; and
| | 00:28 | then Integer and Long can store
numbers in the ranges that you see there.
| | 00:33 | If you want to work with numbers that have
decimal components, you have several options.
| | 00:37 | The first is called Single and you can
see the range there; it's quite large.
| | 00:41 | If you need work with absolutely
incredibly large numbers, you can use Double.
| | 00:45 | It's unlikely that you would need to
use the Double data type in your typical
| | 00:50 | Access programming application, but you might,
so it's there if you want to use it.
| | 00:55 | Next, you have the Currency data type,
and that has four digits to the right of
| | 01:01 | the decimal point. And the reason it has
four digits to the right is that if you
| | 01:05 | multiply two currency values, which
typically have either one or two places to
| | 01:10 | the right of the decimal, then you can
have up to four places to the right, so
| | 01:14 | that's a special data type used for currency.
| | 01:17 | Then you have the Decimal type, which
can handle the values that you see listed
| | 01:21 | on the slide, and again
it's quite a large range.
| | 01:25 | Next, you have data types
that handle other types of data.
| | 01:28 | So, for example, you can have a date from
January 1 of the year 100 to December 31, 9999.
| | 01:35 | Then you can have an object.
| | 01:37 | And an object refers to
something within the Access database.
| | 01:42 | So it can be a table; it can be a form;
| | 01:44 | it can be a report--something like that.
| | 01:46 | We will use it a lot during this course.
| | 01:48 | Then you have four more data types.
| | 01:50 | The first two of those are Strings,
and you can either have a string of
| | 01:55 | variable length or a string of fixed length.
| | 01:58 | I almost always use a string of
variable length, so bear in mind that you can
| | 02:03 | define a variable as a fixed-length
strength, but you probably will not need to.
| | 02:08 | The last two data types are called
variants, and Access to finds of variable of
| | 02:13 | the variant type when you don't
tell it what type of variable it is.
| | 02:17 | In other words, if you type in a
number and it doesn't know whether it's a
| | 02:21 | double, a single, an integer, or what, it
assigns it the variant type. And because
| | 02:27 | it's a number, it has the same
value range as the double data type--
| | 02:30 | in other words, the largest possible.
| | 02:33 | It does something similar for characters.
| | 02:35 | If you have a string that you type into
a variable but you haven't defined that
| | 02:39 | variable's type, then it defines it as a
character variant and gives it the same
| | 02:44 | length as the variable-length string type.
| | 02:47 | If you're not sure what type of data
you're going to get, or be using, for
| | 02:51 | a particular variable, then you can define
your variables as a variant type yourself.
| | 02:57 | The data types available on Access VBA
let you optimize your code by declaring
| | 03:01 | exactly what type of data
each variable will contain.
| | 03:05 | The speed of most modern computers means
your program's performance won't suffer
| | 03:09 | too greatly if you make
every variable a variant;
| | 03:12 | however, if you go on to learn other
programming languages, you will find that
| | 03:16 | many of them do require you to
assign your variables a type,
| | 03:20 | so it's a good habit to pick up now.
| | Collapse this transcript |
| Declaring variables and requiring declaration before use| 00:00 | When you write Access VBA code, you
will often use variables to represent
| | 00:04 | numbers, character strings, and objects.
| | 00:07 | In this movie, I will show you how to
define those variables and to reduce
| | 00:11 | the likelihood of errors by requiring that
you declare variables within a code module.
| | 00:16 | For this movie we will use the
Declaring Variables code module,
| | 00:20 | so I'll just go to the
Navigation pane and double-click it.
| | 00:24 | This code module contains the skeleton
of a subroutine called DeclareVariables.
| | 00:30 | So I'll just press the down arrow twice
to move into the middle and start typing.
| | 00:34 | Now the first thing that you do to define a
variable is to type a dimension, or dim statement.
| | 00:41 | So if you just type "Dim" and then the
space, you can start declaring a variable.
| | 00:46 | Now the question is, what
should you name your variable?
| | 00:49 | When it comes to creating
variable names, I use three criteria.
| | 00:53 | The first is that the name of the variable
should relate to what I use the variable for;
| | 00:58 | in other words, if its sales total then it
should be something like sales or total or so on.
| | 01:03 | Secondly, it should be as short as
possible so it's easy to type. And third, you
| | 01:08 | should put some sort of an indicator
at the start of the variable name to
| | 01:12 | indicate what type it is.
| | 01:13 | So let's say, for example, that I wanted
to have a currency variable that is a
| | 01:18 | variable of the currency
type that tracked a sales total.
| | 01:22 | So for that I might type "cur," which is
short for currency and then "Total."
| | 01:30 | And note that I use the first three
letters as lowercase and the T in total as
| | 01:35 | uppercase, and that just indicates the
prefix as distinguished from the suffix,
| | 01:40 | or what you might think of as the
proper variable name. But again, none of this
| | 01:45 | is written in stone;
| | 01:46 | you can use any method that you want.
| | 01:48 | Next, you type "as" and then a space,
and then you enter the data type for your variable.
| | 01:56 | In this case, I want to
make it a currency variable,
| | 01:58 | so I will type in "Currency" and press Enter.
| | 02:04 | So now I have a variable named curTotal,
and it is defined as holding currency values.
| | 02:11 | If you want, you can dimension multiple
variables on the same line, but there is
| | 02:14 | a right way to do it and a wrong way to do it.
| | 02:17 | Here is the right way.
| | 02:19 | Let's say that I want to make
two more currency variables.
| | 02:21 | I will type in "dim cur," which is the
prefix I use for currency variables, and
| | 02:28 | then have commission, which I will
shorten to "Com As Currency," then a comma, a
| | 02:36 | space, and then another currency
variable, "cur," and this time I will make it Tax,
| | 02:43 | space, then "As Currency" as before.
| | 02:46 | Now here's the wrong way to do it.
| | 02:48 | What you might see in some books,
and not as much anymore, but you'll see folks
| | 02:53 | telling you to dimension
your variables this way.
| | 02:57 | So you dimension a variable called
curCom and then a second variable curTax As
| | 03:05 | Currency, but what you've done is
declared the first variable, curCom, to be a
| | 03:12 | variant, because you have not assigned it a type.
| | 03:15 | Anytime that you name a variable and
you want to assign it a type, you must type
| | 03:19 | "as" and then the type name;
otherwise it's a variant.
| | 03:22 | It's not a big deal, but it's
something you might run into, and in languages
| | 03:27 | that are more strongly typed, you might run
into trouble, so it's a good habit to get into.
| | 03:31 | Now one of the main concerns that you
can have when you are declaring variables
| | 03:36 | is a spelling mistake--and I
will correct before I move on.
| | 03:40 | So As Currency then Tab, and I
will press the down arrow key.
| | 03:46 | Now let's say that I want to
create a message box that displays the
| | 03:49 | commission for a sale, and I will
assign the variable curCm--and that is an
| | 03:55 | intentional misspelling.
| | 03:57 | It's not the currency
commission variable that I had above.
| | 04:01 | So curCm = 1000, so 1 followed
by three 0s and then press Enter.
| | 04:10 | Now if I want to create a message box
with just that value, I would type "MsgBox," all
| | 04:15 | one word, no space, then a space, and then
the name of the variable that I wanted to
| | 04:21 | use in the first place, so
that would be "cur" then "Com."
| | 04:25 | So the idea is that I have a mismatch
between the variable to which I assigned
| | 04:30 | the value and the variable I
am using in the message box.
| | 04:33 | So when I press F5 to run the code, the
commission has displayed a 0, and when I
| | 04:38 | click OK, I can see going back that the
problem is that I used the wrong variable
| | 04:44 | when I assigned it the value of 1000.
| | 04:47 | That's very easy to see in a
subroutine as small as this one, but when you
| | 04:50 | start getting pages and pages of code,
you might run into a problem that you
| | 04:54 | just can't fix very easily.
| | 04:56 | One way that you can make sure that
you have all of your variables spelled
| | 05:00 | correctly, or at least that you have
named the variable you are using, is to use
| | 05:04 | the Explicit option.
| | 05:06 | To do that, you go up to the
Declarations section, which is above the line where
| | 05:11 | Option Compare Database is, press Enter
and then type Option, then a
| | 05:17 | space, and then Explicit, and then press down arrow.
| | 05:23 | Option Explicit requires that you
name any variable before you use it.
| | 05:28 | So if I click inside the body of the
DeclareVariable subroutine and then press
| | 05:33 | F5, Access displays an error indicating
that the variable was not defined, and it
| | 05:39 | highlights the line where the error occurred.
| | 05:42 | Now, if I click OK and then click the
Reset button to go into editing mode, I
| | 05:47 | can click where the first error found
is. Type an o so that the variable name
| | 05:52 | is now correct, in other words, it's
the same as the variable I defined above.
| | 05:57 | Now when I press F5, I get a
commission of 1000, which is correct.
| | 06:03 | Declaring your variables before use
them in a code module helps you remember
| | 06:07 | which variables you are using and
prevents mistyped variable names from
| | 06:10 | making your programs fail in mysterious ways.
| | 06:13 | There is very little more frustrating
than discovering that transposing two
| | 06:17 | letters cost you four hours of
detective work to find the problem.
| | Collapse this transcript |
| Managing variable scope| 00:00 | Code modules often contain subroutines and
functions that are related to each other.
| | 00:04 | If you want to use a variable in more
than one procedure within a code module,
| | 00:08 | you can declare that
variable as a global variable.
| | 00:12 | For this movie, we will use the
Variable Scope code module, and that is here at
| | 00:17 | the bottom of the Navigation pane.
So I will double-click it and open it to
| | 00:21 | display the code that it contains.
| | 00:24 | I have two subroutines in this code module.
| | 00:26 | The first is called CalculateTax01
and in it I define three variables: one
| | 00:31 | for sales, one for the tax rate, and then
one for the tax--and that is a currency value.
| | 00:37 | Then in the second subroutine, which
calculates tax also, I only declare the
| | 00:42 | sales and a tax currency
variables. I leave the Rate commented out.
| | 00:47 | That means I haven't actually defined
that rate. And you will also see that the
| | 00:52 | line where I assign a value to the
Rate variable is commented out as well.
| | 00:58 | So what's going to happen is that I'm
going to see whether the second subroutine
| | 01:02 | will be able to use the value
from the previous subroutine.
| | 01:06 | Now with the cursor flashing in the top
CalculatTax routine, I will press F5 to
| | 01:12 | run it, and it indicates that 5% tax on
a $1,000 sale is $50, and I will click OK.
| | 01:19 | Now when I go down to the second routine,
you will see that once again this code
| | 01:25 | is trying to calculate the tax by
multiplying sales by the tax rate, and then it
| | 01:30 | uses a message box to display that result.
| | 01:33 | So I have the sales of 1,000, but the tax
rate of 0.04 is commented out, so it's not active.
| | 01:40 | Now if I press F5, we get a
tax of 0, and that makes sense;
| | 01:44 | I didn't define a value for the tax
rate within this second subroutine.
| | 01:50 | Now if I remove the comment marker
from these two lines, the one that defines
| | 01:55 | the rate as the Double and the second that
assigns a value of 0.04 to the Rate, and now press F5,
| | 02:02 | when I run the code in the second
module, Access calculates the tax correctly
| | 02:07 | at $40. And click OK.
| | 02:10 | If you want to use a variable in more
than one subroutine, you can declare it as
| | 02:14 | a global variable and to do that, you
define the variable, that is, write the
| | 02:19 | dimension statement, above the first
sub statement within the code module.
| | 02:25 | So, for example, here we have Sub
CalculateTax01 and if I copy the dim dblRate
| | 02:32 | As Double line and then press Ctrl+X to
delete it and then click above the first
| | 02:38 | Sub CalculateTax sub-line and press
Ctrl+V, I now have the Rate variable defined
| | 02:45 | outside of the subroutines, so
it makes it a global variable.
| | 02:48 | Now if I go down to the second
subroutine and comment out the dblRate dimension
| | 02:54 | statement and then also the dblRate
variable value assignment and make sure that
| | 03:00 | you press the down arrow or an up arrow
or just move off that line so that the
| | 03:04 | comment takes hold and that
the text appears in green.
| | 03:07 | Now let's see what happens inside
CalculateTax02 if I press F5 to run the code.
| | 03:12 | So we have current sales of
1000m, and if I press F5, I get the answer
| | 03:18 | of 0. And when I click OK,
again we can see what happened.
| | 03:22 | Ever since I made the dblRate
variable a global variable, it has not had a
| | 03:27 | value assigned to it.
| | 03:29 | So now click in the CalculateTax01
subroutine and press F5 to run it.
| | 03:35 | We get the value of 50, which is correct.
| | 03:37 | Now if we go down to the second subroutine
and press F5, we get a value of 50 as well.
| | 03:42 | That's because Access has assigned a
value to the global variable, which is
| | 03:47 | dblRate. And click OK.
| | 03:50 | Now let's remove the comments from the
dblRate As Double statement and the assignment.
| | 03:58 | So what we have now is two versions of
the same veritable: dblRate, which is a
| | 04:03 | global variable and then a local
variable that only operates within the
| | 04:07 | CalculateTax subroutine--and
that is with a value of 0.04.
| | 04:12 | So now if we are in the second
subroutine and press F5, we get the result we
| | 04:16 | intended, which is 40.
| | 04:19 | One thing you can't do is assign a
value to a global variable in the
| | 04:23 | Declarations section.
| | 04:24 | So for example, if I wanted to go up
above the first subline and then type
| | 04:30 | "dblRate = 0.03" and then press the
down arrow and then try to run either of the
| | 04:37 | code modules by pressing F5, I would
get a compile error saying that it's an
| | 04:42 | invalid action outside of a procedure.
And click OK. And then I'll just go
| | 04:47 | ahead and delete the line.
| | 04:49 | It's okay to use variables with the
same names inside of separate subroutines,
| | 04:53 | but you need to be careful with
your variable naming and usage when you
| | 04:57 | introduce global variables.
| | 04:58 | I personally use global variables
sparingly, but they are valuable technique to
| | 05:02 | have in your programming toolkit.
| | Collapse this transcript |
| Defining constants and static variables| 00:00 | Normally, when you work with variables
in a code module you want the variables
| | 00:04 | values to reflect the current run of
the code and then reset to 0 for when the
| | 00:08 | modules run the next time.
| | 00:10 | If you want a variable to retain the
same value throughout the code run, you can
| | 00:13 | define it as a concept.
| | 00:15 | If you want the variable to remember
its value until you close the database, you
| | 00:19 | can define it as a Static variable.
| | 00:21 | For this movie, we will use the Constant
and Static code module. And that is over
| | 00:27 | here in the Navigation pane, so
I'll double-click it to display it.
| | 00:32 | I have two subroutines in this code module:
| | 00:34 | the first one displays a delivery
charge for regular delivery and then
| | 00:38 | displays a message box indicating the
number of regular deliveries, and then the
| | 00:44 | second subroutine does the same
thing only for special deliveries.
| | 00:48 | The difference between the two is
that a regular delivery costs $20 and a
| | 00:53 | special delivery costs $37.
| | 00:56 | To begin, let's work with the
SpecialDelivery subroutine. And I'll remove the
| | 01:01 | comment from the Constant assignment line.
| | 01:05 | And how that works is it uses the
Constant or Const keyword, then the name of the
| | 01:12 | variable, and then the value
you're signing as a constant.
| | 01:16 | So the current delivery charge is 37.
| | 01:19 | If you press F5 to run the code, Access
displays a message box indicating the
| | 01:24 | charge is 37, and when you click OK, it
says that the "Number of special deliveries
| | 01:29 | is 1." And click OK again.
| | 01:32 | Once you assign a variable as a
constant, you can't change its value.
| | 01:36 | So let me type in "curdleCharge" and
then = and make it, say, 25 and press Enter.
| | 01:46 | Now when I press F5 to run the code,
Access indicates that you cannot assign a
| | 01:51 | value to a Constant.
| | 01:52 | So I'll click the OK button, click the
Reset button to get out of editing mode,
| | 01:57 | and then delete the line I just typed in.
| | 02:01 | Now, let's see what happens if I add
the comment indicator back to that
| | 02:05 | Constant declaration line.
| | 02:07 | So I'll just click here to the left of
the Const keyword and type a single quote,
| | 02:13 | which indicates its a comment.
| | 02:14 | Now I'm going to go up to this
Constant definition line, which is above the
| | 02:20 | first sub declaration, and remove the
comment from it, and what that does is
| | 02:26 | it assigns a Constant value of 20 to
the DelCharge variable. And because this
| | 02:32 | is above the first subline, it is now a
global variable, or in this case a global constant.
| | 02:38 | So if I were to click in the first
subroutine and press F5 to run it, I would get
| | 02:44 | a delivery charge of 20, deliveries is
1. And if I go to the second subroutine
| | 02:49 | and press F5, I also get a deliver
charge of 20, and special deliveries is 1.
| | 02:55 | If you define a Constant as a global
variable then you can always change that
| | 03:00 | value within another subroutine.
| | 03:03 | So let's say, for example, that I
remove a comment marker from the Constant
| | 03:07 | declaration in the SpecialDelivery
subroutine, so now what happens is that the
| | 03:12 | value in the SpecialDelivery subroutine
is 37--in other words, that's the deliver
| | 03:17 | charge--whereas the value for every
other subroutine--there is only one in this
| | 03:21 | case, but you get the point--is 20.
| | 03:24 | So with the cursor flashing within the
SpecialDelivery subroutine, if I press F5,
| | 03:29 | I get a deliver charge of 37.
| | 03:31 | Click OK, and special deliveries is 1.
| | 03:35 | So those are Constants.
| | 03:36 | Now let's talk about Static variables.
| | 03:38 | A Static variable retains its value,
regardless of how many times you run the
| | 03:43 | subroutines, until you close the database.
| | 03:46 | So every time that I have run the
RegularDelivery or SpecialDelivery
| | 03:51 | subroutine, it's always said that the
number of deliveries is 1, regardless of
| | 03:56 | how many times I have done it.
| | 03:57 | If you make a variable
Static, it retains its value.
| | 04:01 | So let's work with the first
subroutine, and I will remove the comment marker
| | 04:05 | from the Static lngAllDeliveries line
and then press the down arrow. So now that
| | 04:10 | I have declared that the variable is
Static, I should see the lngAllDeliveries
| | 04:16 | variable increment because the line
right below that indicates that every time
| | 04:20 | the procedure runs 1
should be added to that value.
| | 04:24 | So let's do it a few times.
| | 04:25 | I will press F5 and I get a
delivery charge is 20. Click OK.
| | 04:30 | Number of regular deliveries
is 1. Click OK. Now, F5 again.
| | 04:35 | Deliver charge is 20, but the
number of regular deliveries is 2.
| | 04:39 | Click OK and just one more time.
| | 04:42 | Charge is 20. Deliveries is 3.
| | 04:45 | That declaration of a Static
variable only works within this
| | 04:49 | RegularDelivery subroutine.
| | 04:51 | So if I were to go down to
SpecialDelivery and press F5 to run it, the charge is
| | 04:55 | 37--click OK--but the number
of special deliveries is 1.
| | 04:59 | So this variable is different from the
Static variable that's declared within
| | 05:05 | RegularDelivery. And one thing you
can't do is make a variable Static in the
| | 05:09 | Declarations section, so there
are no global Static Variables.
| | 05:13 | Constant and Static variables give you
a great deal of flexibility in assigning
| | 05:17 | and to tracking values within a code module.
| | 05:20 | I'm sure you'll find a lot of uses for them.
| | Collapse this transcript |
| Creating a calculation using mathematical operators| 00:00 | When you analyze data in Access you
will often use mathematical operators to
| | 00:04 | create the expressions you need.
| | 00:06 | In this movie, I will describe the operators
available to you and show you how to use them.
| | 00:11 | For this movie, we will use the Demo
Operators code module, so I'll just go into
| | 00:15 | the Navigation pane and double-click it.
| | 00:18 | This code module, called
DemonstrateOperators, has the varResult variable, which
| | 00:24 | is defined as a variant, and then a
message box at the end, which displays the
| | 00:29 | value of the varResult variable.
And I defined that as a variant because my
| | 00:35 | calculations will produce a wide variety
of results, and no other single variable
| | 00:39 | type could cover all the possibilities.
| | 00:42 | Now the first operator here at the
top is called the equal sign, and the
| | 00:46 | equal sign can be used in two ways: as
a test for equivalence or to assign a
| | 00:50 | value to a variable.
| | 00:52 | So let's work with variable assignment first.
| | 00:55 | Let's say that I have varResult,
space, then an equal sign, and then 14.
| | 01:00 | Now when I press F5 Access will
display a message box with the value of the
| | 01:05 | varResult variable and pressing F5,
| | 01:08 | we see the value of 14, and that
makes sense because I just assigned that
| | 01:12 | value to the variable.
| | 01:14 | But now let's say that I want to test
equivalence, and the easiest way to do that
| | 01:19 | is just type two different numbers.
| | 01:21 | So I will backspace over the 14, type a
left parenthesis. So now we are checking
| | 01:26 | to see where the expression 14 = 9
and then a right parentheses is true.
| | 01:33 | So in other words we are checking for
equivalence on the right side, and we are
| | 01:37 | using the equal sign to
assign the result of that comparison.
| | 01:41 | It will be either true or false,
and that's normally the Boolean variable type,
| | 01:46 | but a variant can handle any
type of data, so we will use it.
| | 01:50 | Now, if I press F5 to run the code, we
see that the result is false, and that is
| | 01:55 | as expected; 14 does not equal 9.
| | 01:57 | I will go ahead and comment out that
line of code and move down to the plus,
| | 02:04 | minus, multiply, and divide symbols,
and these are almost certainly familiar to you.
| | 02:08 | So let's say that I have varResult = 14 + 9.
| | 02:17 | Now, when I press F5 I should get the
result of 23, and I do. And just to run
| | 02:22 | through, 14-9, F5, is 5.
| | 02:27 | 14*9, which is the asterisk, and F5, is 126 and
finally 14/9 is, pressing F5, 1.5 repeating.
| | 02:42 | Now notice that the division, the
regular division operator, is the forward slash,
| | 02:49 | and that is the slash that starts to
the left at the bottom and goes up and
| | 02:53 | the right, and that will be important when we
get to another operator later in this movie.
| | 02:58 | I will go ahead and comment out the
line and click below the exponentiation.
| | 03:04 | The caret, which you type by pressing
Shift+6 on most standard keyboards, is used
| | 03:10 | to apply an exponent to a number.
| | 03:12 | So let's say that varResult = 2^3, which
is 2 to the 3rd power, and press F5, and I
| | 03:21 | got the result of 8. And comment that out.
| | 03:27 | Now the next two operators aren't as
commonly used, but they are actually very handy.
| | 03:31 | The first one is the backslash,
and that is used for integer division.
| | 03:37 | Integer division tells you how many
times a number goes into another number, and
| | 03:42 | discards the remainder.
| | 03:44 | So let's say that you have an olive
oil company and you sell bottles of olive
| | 03:48 | oil in 12-bottle cases.
| | 03:50 | If you have 30 bottles--and let's go
ahead and type that in--varResult = 30 and
| | 03:58 | then a backslash 12, you can find out how many
12 bottle cases you can make from 30 bottles.
| | 04:06 | So if I press F5 to run the
code, we see that we get 2.
| | 04:10 | The final operation is called
modular division, and that is the complement
| | 04:14 | of integer division.
| | 04:15 | What it does is ask, what is the
remainder? In other words, how much is left over
| | 04:20 | as a result of this division operation?
| | 04:23 | So I will go ahead and comment out this
previous code and click below the Mod
| | 04:30 | comment there. And I will
assign varResult = 30 Mod 12.
| | 04:41 | So the result should be, how many
bottles do I have leftover if I'm making 12
| | 04:44 | bottle cases out of 30 bottles? And when
I press F5 I get 6, and that makes sense.
| | 04:51 | 2 12-bottle cases is 24 bottles,
out of 30, so I have 6 left over.
| | 04:57 | The mathematical operators available in
Access VBA let you calculate any values you need.
| | 05:03 | You might not use integer division and
modular division as often as you use the
| | 05:07 | other operations, but they are
handy tools to have at your disposal.
| | Collapse this transcript |
| Displaying a calculated result in a message box| 00:00 | One useful way to communicate the
results of a subroutine or function is to
| | 00:04 | create a message box.
| | 00:06 | In this movie, I will show
you how to do exactly that.
| | 00:09 | The code that we are going to use is
in the Display Messages code module,
| | 00:14 | so I'll double-click
that in the Navigation pane.
| | 00:18 | My code module called Display
Messages has two variables defined:
| | 00:22 | Those are the strResponse
variable and the curPrice variable.
| | 00:27 | Now let's say that I want to create
a message box and just assign a text
| | 00:32 | string as its prompt.
| | 00:34 | To do that, I can click in the white
space above the end of Sub statement and
| | 00:39 | then type "MsgBox," all one term, then space,
then double quote, and the prompt. And I
| | 00:46 | will just say, "The price
is $14.95," and a down arrow key.
| | 00:55 | Now when I press F5 to run the code, I
get a message box indicating exactly that: The price is $14.95.
| | 01:01 | Now let's suppose that I want to use a
string variable as the prompt for a message box,
| | 01:08 | in other words, the text that is
displayed within the message box.
| | 01:12 | To do that, I can give myself a
little space to work and then type
| | 01:18 | "strResponse =," and then I can retype
the text I have below and assign that to
| | 01:26 | the string variable.
| | 01:27 | So "The Price is $14.95."
| | 01:35 | Now down in the MsgBox line, instead of
the text, I can type strResponse and then
| | 01:44 | when I press F5 to run the code, it
still says, "The price is $14.95," but clicking
| | 01:50 | OK to go back, it happened indirectly.
| | 01:53 | In other words, if I change this
variable to say the price is $12.95 and then
| | 01:58 | press F5 to run the code,
that's what it displays.
| | 02:03 | You're not limited to working with a
single variable when you create a string
| | 02:07 | that will be used as the
prompt for a message box.
| | 02:10 | You can do what's called
concatenating multiple values together.
| | 02:13 | So let's say that I want to have the
price in the curPrice variable and then
| | 02:18 | concatenate that with the strResponse variable.
| | 02:21 | First, I will click to the left of the
strResponse variable and then press Enter
| | 02:27 | twice to give myself some room to work.
| | 02:29 | Now I'm going to assign the value
of $14.95 to the price variable, so curPrice = $14.95.
| | 02:41 | Now I am going to edit the definition
of the strResponse variable so that it
| | 02:47 | says, "The price is $."
| | 02:50 | Now I am going to use the right arrow
key to move outside of the double quote
| | 02:57 | marks and then type an ampersand.
| | 02:59 | The ampersand means "And"
and that's what we are doing:
| | 03:02 | we are combining one string or
one value with another value.
| | 03:07 | I have the value $14.95 assigned to the
price variable, so I'll type curPrice &,
| | 03:17 | then double quotes so that I could
start typing another string, a period, and
| | 03:23 | then another double quote.
| | 03:25 | So what I have is the price is a
dollar sign, followed by the value of the
| | 03:30 | current price variable, followed by a period.
| | 03:33 | So now when I press F5 I get the
string that says "The price is $14.95."
| | 03:39 | If I were to edit the price so that it
read $13.95 and then press F5, the message
| | 03:45 | box's prompt reflects that change.
| | 03:49 | Message boxes are fairly straightforward,
but there are some things about them
| | 03:52 | that you can change.
| | 03:54 | So for example, let's say that I
wanted to add a title to the title bar of the
| | 03:58 | message box, or I wanted to add buttons.
| | 04:01 | For a message box, to add a title, the
first thing you need to do is to click to
| | 04:06 | the right of whatever you have as your
prompt, and it doesn't matter if it's in
| | 04:10 | double quote or if it is in parentheses,
which I will show you how to do later.
| | 04:14 | Then type a comma, and then you can tell
Access the type of message box you want
| | 04:20 | to create by adding buttons to it.
| | 04:22 | The default is simply to display a button that
says OK, but you have quite a few other options.
| | 04:28 | The one that I like to use a lot is
called YesNo, and that is the next-to-
| | 04:33 | last one in the list.
| | 04:35 | So if I click vbYesNo and then press
Tab, that will be the message box style
| | 04:40 | for this message box.
| | 04:42 | Then type a comma, and then the last
argument that I will talk about is the title.
| | 04:47 | So if I want to add a title other than
Microsoft Access to the title bar, I can
| | 04:52 | type a double quote and
type in, say, "Extended Price."
| | 04:57 | So with that, I can press the down arrow
and then when I press F5 to run the code,
| | 05:04 | we get a message box with the title
Extended Price, the prompt as before, $13.95,
| | 05:10 | and then either Yes button or No button.
| | 05:13 | I don't have any logic hooked up to the
buttons, so I can click either Yes or No
| | 05:17 | and the box will simply disappear.
| | 05:19 | But let's say that I do
want to do something with it.
| | 05:22 | Let's say that I want to create an
If-Then statement that indicates that if the
| | 05:26 | user clicks Yes, it will say, "The price is final."
| | 05:30 | To do that, I need to use the message box's
button click as input for an If-Then statement.
| | 05:36 | The way that you do that is to click
to the left of the MsgBox statement then
| | 05:41 | type "If." Then go over to the right of
MsgBox and before the prompt, type a left
| | 05:49 | parenthesis. And then after your final
argument for the MsgBox--in this case
| | 05:54 | that's the title of Extended
Price--type a right parenthesis.
| | 05:59 | Then you need to type in the condition,
so you can either have a button click
| | 06:03 | being vbYes or vbNo.
| | 06:05 | It's simply vb, which stands for Visual
Basic, followed by the name of the button.
| | 06:10 | So type an equal sign, then vbYes,
and you see an auto-complete list of buttons
| | 06:18 | that are available to you.
| | 06:19 | Press Tab. Then type Then and press Enter.
| | 06:24 | Now you can tell Access what to
do if the Yes button is clicked.
| | 06:28 | So I will press Enter again and press
Tab to offset my code to the right to
| | 06:32 | indicate that it's
happening within another command.
| | 06:36 | And I will display a message box with
the prompt "OK, the price is final."
| | 06:49 | Press Enter twice, Backspace to
move back, and then type "End If."
| | 06:52 | So now when I press F5 Access indicates
the price is $13.95, and if I click Yes,
| | 06:59 | I get another message box
indicating that OK, the price is final.
| | 07:05 | Now if I run the code again and click No,
I'll see The price is $13.95. Click No.
| | 07:10 | There is no logic that reacts
when the No button is clicked, so the
| | 07:14 | routine simply ends.
| | 07:15 | Message boxes are simple but
surprisingly versatile tools.
| | 07:19 | You'll find lots of uses for them
when you write and debug your VBA code.
| | Collapse this transcript |
| Defining an array| 00:00 | Most of the variables you deal with
when you write Access VBA code will be
| | 00:03 | single variables, such as item
prices, shipping rates, and so on;
| | 00:08 | however, if you must deal with a set of
values of the same type, such as a set of
| | 00:12 | shipping rates, you can
do so by creating an array.
| | 00:16 | In this movie, we'll use the code
in the Define Arrays code module,
| | 00:20 | so I'll double-click that over
in the Navigation pane to open it.
| | 00:24 | And I have a very simple subroutine.
Basically, I have the DefineArrays
| | 00:29 | subroutine named, and then I create a
variable called ChargesCount, and that is an integer.
| | 00:35 | An array is a set of values of the
same type. And you define an array like you
| | 00:40 | would any other variable, but you add a
number, or numbers, in parentheses after
| | 00:44 | the variable's name to indicate
the number of elements in the array.
| | 00:48 | So let's say, for example, that you
ship products using one of six different
| | 00:52 | shipping rates, and that rate
is based on the package's weight.
| | 00:56 | To create an array to hold those six
rates, you would use this command. And
| | 01:01 | typing below the ChargesCount As
Integer line, it would be "Dim cur."
| | 01:08 | That's the indicator I
use for a currency variable.
| | 01:11 | And then type ShippingCharges.
| | 01:17 | Then a left parenthesis and the number 5,
then a right parenthesis, a space, "as
| | 01:23 | Currency," and Return.
| | 01:25 | Now notice that the number in the
parentheses is one less than the number of elements.
| | 01:30 | That's because the first element
has the number 0 as its identifier.
| | 01:34 | VBA, like most programming
languages, starts counting at 0.
| | 01:39 | That means an array with six items will
have those items numbered 0 through 5.
| | 01:44 | If you want to number array
item starting at 1, you can.
| | 01:47 | To do that, you click in the declaration
section and type "Option Base 1" and then Return.
| | 01:55 | If you do that, make sure that you put
comments elsewhere in your code so that
| | 01:59 | your colleagues will know
that's what you've done.
| | 02:01 | In most cases, you should stay with 0
unless there's a good reason not to,
| | 02:05 | so I'll go ahead and erase Option
Base 1 by selecting it and then pressing
| | 02:10 | Delete a couple of times.
| | 02:12 | Now the next question is,
how do you populate the array?
| | 02:15 | There are a number of ways you can do that.
| | 02:17 | You can use a DoWhileLoop to read
values in from a table, for example, or you
| | 02:23 | can assign the values directly.
| | 02:25 | So I'll click below the two declaration
statements, press Enter, give myself a
| | 02:30 | little bit of room to work.
| | 02:31 | Now let's say that I want to
create a variable called ShippingTypes.
| | 02:35 | So for that it would be Dim
strShipTypes, and again, that's all one word.
| | 02:46 | And I will declare it as a variant.
| | 02:50 | Now note that even though I have named
ShippingTypes as a string, or at least
| | 02:55 | used my indicator to indicate
that it will contain string values,
| | 02:58 | I have to define the variable as a variant.
| | 03:02 | The reason is that anytime you
create an array, you must use the
| | 03:05 | variant variable type.
| | 03:08 | That's okay, because a variant can
handle any type of data within it.
| | 03:11 | With the ShipTypes variable defined as a
variant, now I can define the array values.
| | 03:17 | So for that it would be strShipTypes,
then equal, and then a space, and the
| | 03:24 | keyword Array, then a left parenthesis,
and a series of series of values that
| | 03:31 | you'd want to put into the array.
| | 03:33 | All of the strings must be
within the sets of double quotes.
| | 03:35 | So ("Overnight","TwoDay","ThreeDay",
"Ground"), because that's the last item I
| | 03:55 | want to put in, and press Enter.
| | 03:58 | Now let's say that I want to display
one of those values in a message box.
| | 04:02 | To do that, type in "MsgBox," a space,
and then variable name, which is
| | 04:09 | strShipTypes, then a left
parenthesis and then the number of the item.
| | 04:16 | Now remember that unless you
change it, the first item is number 0.
| | 04:19 | So I'll type in a 0 and then a right
parenthesis and a down arrow so that we can
| | 04:25 | see the line without interference.
| | 04:27 | So I have a message box that displays
the string ShipTypes array element 0, and
| | 04:34 | that should be Overnight.
| | 04:35 | So I'm going to press F5 to run the code.
| | 04:37 | I see Overnight in the message box.
| | 04:41 | The procedure I showed you assumes that you
know how many items will end up in your array,
| | 04:45 | but let's suppose you're creating an
array from user input and you don't know
| | 04:48 | how many items they'll enter.
| | 04:50 | In that case, you can create a dynamic
array and change the array's size using a
| | 04:56 | Redim statement later in the program.
| | 04:58 | To define a dynamic array, you
use a statement such as this one.
| | 05:02 | You would say Dim curShippingCharges,
but then instead of having a number inside
| | 05:09 | of the parentheses, you
would just leave it blank.
| | 05:13 | So that means that you are defining it
as an array, but you don't know how many
| | 05:16 | elements will be in it.
| | 05:18 | Now you can use what's called a
Redim statement later on in your program
| | 05:23 | to indicate the size.
| | 05:24 | So let's say that I want to
work with the ShippingCharges.
| | 05:30 | So I'll type Redim, a space, then
curShippingCharges, and we'll make it 5.
| | 05:37 | End the parenthesis and press Enter.
| | 05:41 | So that redefines it.
| | 05:43 | The problem is that if you
re-dimension an array, Access deletes the existing
| | 05:47 | values in the array unless you use
the Preserve keyword after Redim.
| | 05:52 | So, for example, if I wanted to keep my
existing values in the ShippingCharges
| | 05:57 | array, I would need to type Preserve
after Redim, Preserve, and then press the down arrow.
| | 06:05 | Doing so would keep existing
values inside of the array.
| | 06:09 | Arrays help you maintain and look up
data quickly without having to look up
| | 06:13 | values in your tables.
| | 06:14 | Maintaining these values in memory
speeds up program execution and it simplifies
| | 06:18 | your programming job significantly.
| | Collapse this transcript |
| Defining and using an object variable| 00:00 | When you want to use VBA to manipulate
a table, form, or other database object,
| | 00:05 | you need to assign that object to a variable.
| | 00:07 | Those variables are, not
surprisingly, called object variables.
| | 00:11 | For this movie we will use the Object
Variables code module that I have here in
| | 00:16 | the Navigation pane,
| | 00:17 | so I will double-click it to display it.
| | 00:20 | The Module contains just a skeleton of
an ObjectVariables subroutine, so I will
| | 00:25 | click between the Sub and End
Sub statements to start working.
| | 00:29 | You declare an object variable the
same way you declare any other variable.
| | 00:32 | You just specify the type of object
you want that variable to represent.
| | 00:37 | So let's say that I want to
create an object variable for a form.
| | 00:40 | For that I will Dim, space, then the
name of a variable. And I usually just
| | 00:46 | type in myForm to refer to a form, then As,
space, and then the word Form, and Return.
| | 00:58 | So now Access understands that the
variable myForm will represent a form
| | 01:03 | within the database.
| | 01:04 | After you declare the object variable,
you use the Set command to assign an
| | 01:09 | object to that variable.
| | 01:11 | So I'll type Set, a space, then
myForm = then Forms, and Forms refers to the
| | 01:23 | collection of all forms within the
database. Then a left parenthesis, double
| | 01:27 | quote, and then the name of the form,
which is Products, and double quote, and a
| | 01:36 | right parenthesis, and Enter.
| | 01:39 | So now what I have done is indicate
that the myForm variable refers to the
| | 01:45 | Products Form, and the Set
statement allows me to do that.
| | 01:49 | After you declare and set an Object
Variable, you can use it as a shorthand
| | 01:53 | reference to refer to that object.
| | 01:55 | In this case, I'll create a message
box that uses the myForm.Name property.
| | 02:01 | So MsgBox, then myForm, a period, and then Name.
| | 02:11 | When I type the period, the Visual
Basic Editor recognized that I could now
| | 02:16 | enter a property related to a form.
| | 02:19 | So when I did, it displayed a list of
all the properties that were available
| | 02:22 | to me. And the one I am going to use is the
Forms Name, and I will press Enter to accept it.
| | 02:28 | One last thing that I need to do
before I can run this code is to go back to
| | 02:33 | the main database window--that's by
pressing Alt+F11--and open the Products
| | 02:38 | Form, because the form's collection refers
to all of the forms that are currently open.
| | 02:44 | Now, if I press Alt+F11 again, and press
F5, Access displays the Message Box and
| | 02:50 | it contains the name of the form, Products.
| | 02:54 | Let's see what would happen if I go
back to the database and close it.
| | 02:58 | So I press Alt+F11, and if I press
Ctrl+W to close the Products Form and then
| | 03:03 | Alt+F11 to switch back to the Visual
Basic Editor, now if I press F5, I get an
| | 03:09 | error dialog box indicating that Access
can't find the reference form Products,
| | 03:13 | and that's because the
form isn't open--it's closed.
| | 03:16 | I know what the problem is, so I can
just click End, rather than debug the code.
| | 03:21 | You can assign a new object to an object
variable by using a second Set statement.
| | 03:26 | So let's say, for example, that I
wanted to assign the Clientsform to
| | 03:31 | the myForm variable.
| | 03:32 | To do that, I would just type Set
myForm = Forms, left parenthesis, double quote
| | 03:39 | Clients, double quote, and a right parenthesis.
| | 03:45 | Now, again, Forms refers to all the
open forms, so to avoid an error, you would
| | 03:49 | need to have the Clients form
open in the main database window.
| | 03:52 | When you're done working with an
Object Variable, you should set that
| | 03:55 | variable to Nothing.
| | 03:57 | The reason you do that is to free up the
memory that is being used by that object.
| | 04:02 | To do that, you just type another
Set statement, Set, then the name of the
| | 04:06 | variable, which is myForm, then equal,
and then the keyword Nothing, and Return.
| | 04:14 | Object variables are a real time saver
when it comes to manipulating Access.
| | 04:18 | Every object type has its own
idiosyncrasies, but you'll find plenty of
| | 04:22 | information in the
Autocomplete list and in the help files.
| | Collapse this transcript |
|
|
5. Adding Logic to Your VBA CodeRepeating a task using a For...Next loop| 00:00 | Many of the code segments you write,
such as assigning a value to a variable or
| | 00:04 | displaying a message box,
will just need to run one time;
| | 00:07 | however, if you work with arrays, or
tables, or collections of objects, you
| | 00:11 | might need to repeat a segment several times.
| | 00:14 | The easiest way to repeat code in
Access VBA is to create a ForNext loop.
| | 00:20 | For this movie, we will use the
ForNext code module. And that is over here in
| | 00:24 | the Navigation pane, so I'll double-click it.
| | 00:28 | The code, which is in the subroutine
named DemonstrateForNext, has a counter
| | 00:33 | variable, which is an integer,
and then an array of shipping codes:
| | 00:38 | those are Overnight,
TwoDay, ThreeDay, and Ground.
| | 00:42 | The structure of a ForNext loop looks like this.
| | 00:45 | And I will click above the End Sub statement.
| | 00:48 | First you type the word For, then a
space, then the variable that is being
| | 00:53 | used as the counter--
| | 00:55 | and in this case it is the integer
named counter, intCounter--a space then an =
| | 01:02 | sign, and then one number to another number.
| | 01:06 | So let's say that I want to make it 0 To 3.
| | 01:11 | And the reason I make it a 0 is
because an array has members that are
| | 01:16 | numbered from 0 and so on.
| | 01:19 | So in the array that I have for my
shipping codes, Overnight is 0, TwoDay is 1,
| | 01:25 | ThreeDay is 2, and Ground is 3.
| | 01:29 | So I have For intCounter = 0 To 3.
| | 01:32 | Now I will press a Tab to indent my code,
indicating it's within a ForNext loop,
| | 01:37 | and I will type MsgBox, space,
and then strShipCode,
| | 01:48 | all one term, and then a
left parenthesis, then intCounter, and a
| | 01:55 | right parenthesis, then return twice,
backspace, and then type Next intCounter.
| | 02:03 | And it is important that you have the
variable named in the Next statement.
| | 02:09 | The reason is that you might have
nested ForNext loops, so Access needs to know
| | 02:14 | which one it's going back to.
| | 02:16 | So I have the loop in place, and when I
press F5 to run the code, it displays 0,
| | 02:23 | which is Overnight, and then when I
click OK, it will go back through the loop
| | 02:27 | and then display TwoDay,
so we're at 1. Click OK.
| | 02:31 | Now we're on ThreeDay, which
is element number 2. Okay.
| | 02:35 | The fourth element, which is
numbered number 3, and that is Ground.
| | 02:38 | Then click OK, and we're done with the loop.
| | 02:41 | One way that you can control the
numbers that are counted--in other words, what
| | 02:46 | happens to this intCounter variable in
this case--is to use the Step keyword.
| | 02:51 | So let's say that I have
intCounter from 0 To 3, and I have Step 2.
| | 02:58 | That means that it will start at 0, then
go to 2, and then end, because the next
| | 03:03 | step would be 4, but that is
beyond the top range, which is 3.
| | 03:08 | So I'll go ahead and press F5.
So first we'll see element 0, which is
| | 03:11 | Overnight; then we'll see element 2,
which is ThreeDay; then when I click OK,
| | 03:18 | we step out of the loop, because there are no
more values from 0 To 3 stepping two at a time.
| | 03:25 | You can also go in reverse.
| | 03:26 | So I'll just delete the text that
says 0 To 3 and then say 3 To 0, but
| | 03:34 | that's not normally how you'd do it,
so you need to make your Step -1, and
| | 03:40 | press the down arrow.
| | 03:41 | Now we will go in the reverse order.
| | 03:43 | So let's start with Ground, pressing
F5 to run the code, start with Ground,
| | 03:47 | click OK, go to ThreeDay,
TwoDay, Overnight, and we're done.
| | 03:53 | ForNext loops let you repeat your code
a specific number of times, with a number
| | 03:58 | of repetitions controlled either by
specifying the number of steps in the
| | 04:01 | initial For statement or through a variable.
| | 04:04 | You can also skip values using the
Step keyword, which lets you examine a
| | 04:08 | subset of the values stored in the
range, or to change the order by stepping
| | 04:12 | with a negative number.
| | Collapse this transcript |
| Stepping through all items of a collection using a For...Each loop| 00:00 | The Access Object Model groups
objects of the same type into collections.
| | 00:05 | For example, the forms collection
contains all of the forms that are currently
| | 00:08 | open within your workbook.
| | 00:10 | If you want your code to affect every
member of a collection, or an array, you
| | 00:13 | don't have to count the number of items in
the collection and work through them one by one.
| | 00:18 | Instead, you can use a ForEach loop to
interact with every element in the array,
| | 00:23 | or the collection, in turn.
| | 00:24 | For this movie we'll use the
ForEach code module. And that is here in
| | 00:29 | the Navigation pane, so I'll
double-click it to open it.
| | 00:32 | There is a single subroutine called
DemoForEach, and I have defined three variables.
| | 00:38 | The first one is varShipCodes as a
variant, and that is going to be an array that
| | 00:43 | is defined within the subroutine.
| | 00:46 | Next is a varCode, which will be an
individual code within that array. And then
| | 00:51 | I have myF, which I'm defining as a form,
and that will be used later on in this movie.
| | 00:57 | Below that I have my array of
shipping codes that are Overnight, TwoDay,
| | 01:01 | ThreeDay, and Ground.
| | 01:03 | Now if I want to step through each
element of the array, I can use For Each. Now
| | 01:11 | I want to use a Variant that
would be a subset of the array.
| | 01:15 | So rather than use varShipCodes, which
is the array, I use another variant, and
| | 01:19 | that I have as "varCode in," space,
and then the name of the array.
| | 01:29 | So that's varShipCodes. Then press
Enter a couple times and go up. Then press
| | 01:37 | Tab to move over so that this code
stands out as being within a loop.
| | 01:42 | Now in this case all I want to do
is display a message box that
| | 01:46 | contains a shipping code,
| | 01:47 | so I will type MsgBox, a
space, and then the varCode.
| | 01:55 | So in other words, whatever the
current member of the ShipCodes array is
| | 02:00 | contained in the varCode variable, that
will be displayed in the message box.
| | 02:03 | Now I'll press Enter twice and then
Backspace and then type "Next varCode" and
| | 02:11 | press the down arrow key.
| | 02:13 | So now when I run my code I should see
each member of the array in the message
| | 02:17 | box and it will happen four times.
| | 02:19 | So I press F5 to run.
| | 02:21 | I have got Overnight, OK, TwoDay,
OK, ThreeDay, OK, and Ground--
| | 02:27 | that's the last one--and we're done.
| | 02:30 | So that's how you use an array.
| | 02:31 | Now let's say that you want to work
through a collection, such as all of the
| | 02:35 | forms that are currently
open within your database.
| | 02:37 | Well, to make that work,
I'll have to open a couple of forms.
| | 02:40 | So I'll press Alt+F11 to go back to
the main Database window. And then in the
| | 02:46 | Navigation pane, I'll double-click
Clients, and then I'll double-click Products.
| | 02:50 | So my Clients and Products
forms are currently open.
| | 02:54 | Now, I'll press Alt+F11 to go back.
| | 02:56 | Now I'm going to edit the code within
the subroutine so that I work through all
| | 03:00 | of the forms, as opposed to
the members of this array.
| | 03:04 | So the first thing I'll do is
comment out all of this code.
| | 03:10 | I'm commenting out the code, rather
than deleting it, because there is a
| | 03:13 | possibility that I would want to
use this code later, either here or in
| | 03:17 | other module. You should never erase any
working code, because you might be able to reuse it.
| | 03:22 | So now I can type my For Each routine
to work through all of the open forms, and
| | 03:27 | that would be For Each, then myF.
And again the third dimension statement in the
| | 03:36 | subroutine defines myF as a form.
| | 03:39 | So I have For Each myF in and then the
Forms collection, returned twice and then
| | 03:46 | a Tab. Now message box, MsgBox, then
myF--and again that represents a form--
| | 03:53 | period, and then the property of the form
I want to use. And I'll just display the
| | 03:58 | Name, Enter twice,
Backspace, and then type Next myF.
| | 04:08 | So what this code will do is it will
step through each member of the forms
| | 04:11 | collection, which is the open forms within
the database, and display each form's name.
| | 04:17 | So when I press F5 I get
the Clients form. Click OK.
| | 04:21 | I get Products form.
| | 04:22 | Those are only two forms that are open,
so when I click OK, I go back to the
| | 04:26 | Visual Basic Editor.
| | 04:28 | The ForEach loop greatly simplifies
your programming code when you want
| | 04:31 | to interact with each member for a
collection, such as every open form in the database.
| | 04:36 | You can always add other logic to work
with the items, or you can simply display
| | 04:40 | properties that are of interest to you.
| | Collapse this transcript |
| Repeating a task using a Do...While loop| 00:00 | When you create an Access VBA
subroutine, you expect it to take data from the
| | 00:04 | source, transform it in some way,
and then either change the database or
| | 00:09 | display the result.
| | 00:10 | Sometimes, however, you will want your
VBA code to run while a condition is met.
| | 00:14 | When you want a segment of your code
to repeat while a specific condition is
| | 00:18 | true, you can use the DoWhileLoop construction.
| | 00:21 | For this movie we'll use the
DoWhileLoop code module, which is over here in the
| | 00:26 | Navigation pane, so I will
double-click it to open it.
| | 00:29 | This module contains a single
subroutine called RunningTotal.
| | 00:32 | It has two variables.
| | 00:35 | The first one is an RT variable.
| | 00:38 | That's a currency that
refers to the RunningTotal.
| | 00:41 | And the second is MyR, which is a recordset.
| | 00:44 | And then I have Set MyR to the
OpenRecordset called Orders, and that is
| | 00:51 | essentially assigning it to
the contents of the Orders table.
| | 00:54 | Now, what I want to do is to display a
RunningTotal as long as that RunningTotal
| | 01:01 | is less than 250 when the loop starts.
| | 01:04 | To do that, I will type Do While, then
the variable that I am using to track the
| | 01:12 | RunningTotal, which is curRT.
| | 01:14 | So curRT, and the condition
that I want is less than 250.
| | 01:24 | Because I am working with a table,
it's possible that I might run out of
| | 01:27 | records before I get to that number.
I won't in this case, but to account for
| | 01:31 | the possibility that I could, I need to add
another condition, and that is And Not MyR.EOF.
| | 01:42 | So what that means is that as long as
the RunningTotal is less than 250 when the
| | 01:47 | loop starts, and we have not reached
the end of the file, continue performing
| | 01:52 | the steps inside the loop.
| | 01:53 | Then I will press Enter and press Tab to
move over to indicate that this code is
| | 01:59 | operating within the loop.
| | 02:00 | The next thing I want to do is add the
next value in the Order Table field to
| | 02:05 | the current RunningTotal.
| | 02:07 | So I will type curRT =, and then I am
adding it to its existing value, so curRT+,
| | 02:18 | and then I need to refer to
the table field. And that's MyR!
| | 02:32 | left square bracket, OrderTotal,
and then a right square bracket.
| | 02:32 | Now I want it to display the
current RunningTotal in a message box.
| | 02:36 | So that's MsgBox, a space. And
I won't do any fancy verbiage;
| | 02:41 | I will just display the value.
And that is curRT, then press Enter, and now I
| | 02:47 | want to move to the next
record within the table.
| | 02:50 | To do that, we use the MoveNext method,
| | 02:53 | so that's MyR, which represents the table,
period, and then MoveNext,
| | 03:00 | all one term. And press Tab.
| | 03:02 | So as long as the RunningTotal is less than
250 and we haven't reached the end of the file,
| | 03:06 | we will see message boxes updating the value.
| | 03:09 | Now I need to close out the loop, so
I will press Enter again and then a
| | 03:12 | Backspace and then type "Loop" and Return.
| | 03:17 | So what will happen when we run the
code is that as long as the RunningTotal is
| | 03:21 | less than 250 and we haven't reached
the end of file, we will see a message box
| | 03:26 | indicating what the running total is.
| | 03:28 | So I will press F5 to run the code.
| | 03:31 | So the first order is 104.95.
| | 03:33 | Then I will click OK.
| | 03:35 | The code continues to run, and the
RunningTotal is 132.9, 147.85, 167.85, 192.8,
| | 03:44 | 274.8, so we're above 250.
| | 03:47 | So when I click OK, as expected,
the loop terminates because the While
| | 03:52 | condition is no longer true.
| | 03:55 | The DoWhileLoop and its variations
let your code repeat a series of actions
| | 03:59 | until a condition is met.
| | 04:00 | You should be very careful, though.
| | 04:02 | If you define a condition that can't
be met or that will always be met, your
| | 04:06 | code might run indefinitely.
| | 04:08 | If that happens, you can press
Ctrl+C or the Escape key to stop the code
| | 04:13 | from running forever.
| | Collapse this transcript |
| Repeating a task using a Do...Until loop| 00:00 | In the previous movie, I showed you how
to create a DoWhileLoop, which runs while
| | 00:04 | a condition is true.
| | 00:06 | In this movie I'll show you the
DoUntilLoop, which runs until a condition is true.
| | 00:11 | So again, a DoWhileLoop runs while s
condition is true, and the DoUntilLoop
| | 00:16 | runs until the condition is met.
| | 00:18 | As an example, let's suppose you want
to display every product name and cost in
| | 00:22 | a table, stopping only when
you get to the end of the file.
| | 00:26 | That's a perfect use for a DoUntilLoop.
| | 00:29 | So for this movie, we'll use the
DoUntilLoop code module, which is here in
| | 00:33 | the Navigation pane. I'll double-click it.
| | 00:35 | The code module contains a single
subroutine called DoUntilLoop, and I define MyR
| | 00:42 | as a recordset variable, and then I
set that variable to the Products table.
| | 00:47 | Now I can create a DoUntilLoop that will
loop through each of the records in the
| | 00:51 | table until we reached the end of the file.
| | 00:54 | So I'll just click in the whitespace
above End Sub and start typing. And the
| | 00:58 | code is Do Until, now a space, now MyR,
which again is the Products table,
| | 01:07 | Period, and then EOF.
| | 01:10 | The EOF property indicates the end of
file, so we'll keep going until we hit the
| | 01:15 | end of file marker. Press Enter twice
and then Tab once, so that I shift this
| | 01:20 | line of code over to the right
to indicate it is within a loop.
| | 01:24 | Now what I want to do is to display a
message box displaying the product name
| | 01:29 | and the product cost.
| | 01:30 | So for that, it's MsgBox MyR--again, the
Products table--exclamation point, left
| | 01:36 | square bracket and then the name of the
first field, the one that contains the
| | 01:43 | product name, and that is ProductName,
no spaces. So it's ProductName with no
| | 01:51 | space between the words,
then a right square bracket.
| | 01:55 | Now I want to concatenate other values
together, so I'll type an ampersand, a
| | 02:00 | space, then double quotes, and a comma,
and a space, then double quotes, a space,
| | 02:07 | another ampersand, and then the
value in the Product Cost field.
| | 02:12 | So that will be MyR--again, that's the
Products table--an exclamation point
| | 02:17 | indicating I'm about to type a field.
And that field name goes inside square
| | 02:21 | brackets, so left square bracket.
And then ProductCost--again all one term--
| | 02:26 | ProductCost and a right
square bracket, and press Enter.
| | 02:33 | Once the message box has been displayed
and cleared, I want to move to the next
| | 02:37 | record in the recordset.
| | 02:39 | For that, we use the MoveNext method,
so MyR.MoveNext and press Enter twice and
| | 02:47 | Backspace once and we can close out
the Loop, and a down arrow.
| | 02:53 | So again, this code will point out the
Products table and display every product
| | 02:57 | name and the associated product cost
from the same row, and it will do so until
| | 03:03 | it reaches to the end of the file.
| | 03:04 | So now I can run the code. Press F5.
| | 03:07 | We get Garlic, 12.5. Click OK.
| | 03:10 | Basil, Jalapeno, Lemon,
Mandarin, Rosemary, and we're done.
| | 03:18 | You can also put the condition on the
Loop line at the bottom of the DoUntilLoop.
| | 03:23 | If you do, then the code runs once and
then checks the controlling condition at
| | 03:28 | the end of the loop.
| | 03:29 | You should be very careful, though.
| | 03:31 | If you define a condition that can't
be met, your code might run indefinitely.
| | 03:35 | If that happens, you can press either
Ctrl+C or the Escape key to stop your code
| | 03:40 | from running forever.
| | Collapse this transcript |
| Performing a task when conditions are met using an If...Then statement| 00:00 | So far, most of this course's example
files have contained subroutines or
| | 00:04 | functions that run all of their
code every time you call them.
| | 00:07 | There will be times, though, when you want
parts of your subroutines to run when a
| | 00:11 | condition is true or false.
| | 00:13 | In business, the canonical example
is calculating sales commissions.
| | 00:17 | Many companies have separate
commission rates for different sales totals,
| | 00:20 | so I'll use that example as a context
for how you can use If-Then statements to
| | 00:25 | execute code conditionally.
| | 00:27 | For this movie we will use the
IfThenCode code module. And that's here at the
| | 00:32 | bottom of the Navigation pane,
so I'll double-click it to open it.
| | 00:36 | In this code module I have a single
subroutine, and in it I've defined two variables:
| | 00:41 | Total and Commission. And then I get
the total value from an input box asking
| | 00:46 | for the amount of the sale.
| | 00:47 | At the end of the subroutine there
is a message box that will display the
| | 00:51 | value of the commission.
| | 00:52 | The simplest form of an If-Then
statement is a one line If-Then.
| | 00:57 | So for that I'll click above the
MsgBox statement and create an If-Then
| | 01:03 | statement that checks to see if the
value is greater than or equal to 1000.
| | 01:07 | So I will type If curTotal, which
again is the amount of the sale, >=1000.
| | 01:17 | Then curCom, the commission,
= curTotal * .03, and press the down arrow.
| | 01:30 | So in other words, if the total is
greater than or equal to 1000, you get a 3%
| | 01:35 | commission, and that will appear in the message box.
| | 01:37 | So I'll press F5 and run the code once
to show what happens when the condition
| | 01:42 | is true, and again to show what
happens when the condition is false.
| | 01:45 | So I'll press F5, and the amount of the
sale, let's make it 2000, which is above
| | 01:51 | 1000, click OK, and the commission is $60.
| | 01:56 | Now, if I press F5 to run the code
again and enter the amount of the sale, and
| | 02:00 | we'll make it 100, which is well below
1000, click OK, and Access displays a box
| | 02:07 | that shows the commission is 0.
| | 02:09 | So that is the simplest way
to create an If-Then statement.
| | 02:13 | If you want to create an If-Then
statement that makes multiple tests, then you
| | 02:17 | can use the Else keyword.
| | 02:19 | So for that, let's say that the total
is greater than or equal to 1000. Then,
| | 02:25 | and I'll press Enter, and then
press Tab to move the code over.
| | 02:31 | I pressed Enter twice to
give myself a blank line.
| | 02:33 | If the total is greater than or equal to
1000, then we'll make the commission 6%.
| | 02:40 | Press Enter.
| | 02:41 | Then type "Else," then Return,
and Tab again to move in.
| | 02:47 | If it's below 1,000,
we'll make the commission 5%.
| | 02:51 | So that would be curCom = curTotal
* 0.05. And then press Enter twice,
| | 03:01 | Backspace twice to move back to the
left margin, and then to close out the If
| | 03:05 | statement by typing End If.
| | 03:11 | This code checks to see if
the sale was a least $1000.
| | 03:14 | If it is, you get a 6% commission.
| | 03:17 | If not, you get 5%.
| | 03:18 | So I'll press F5 to run the code,
and I'm asked to enter the amount of the sale.
| | 03:23 | I will make it 1000.
| | 03:25 | So I should see a commission of 60. I do.
| | 03:27 | Then I'll click OK.
| | 03:29 | Now if I press F5 to run it again and
type in 100, I should see a 5% commission,
| | 03:36 | or $5. And when I click OK,
that is indeed what appears.
| | 03:41 | You should be sure to put your Else
statement on its own line and also to make
| | 03:46 | sure that the first Then
statement is on its own line as well.
| | 03:51 | If you don't, Access treats those
keywords as line labels, and it will generate
| | 03:55 | all kinds of errors in your code.
| | 03:57 | So just make sure that after you type
Then you press Enter to put the line on a
| | 04:02 | different code, and then when we have Else,
make sure that's on its own line as well.
| | 04:06 | If you want to make even more
complex If-Then statements, you can use of
| | 04:10 | the Else If command.
| | 04:12 | So let's say, for example, that you wanted to
have three tiers of commissions: 8%, 6%, and 5%.
| | 04:18 | For the first level, let's say that the
sales total is greater than 10,000, and
| | 04:23 | if that's the case,
you want to give an 8% commission.
| | 04:27 | So we'll edit those values so that
now if the total sale is greater than or
| | 04:32 | equal to 10,000, you get an 8% commission.
| | 04:35 | Now we will edit the Else
| | 04:36 | so it says Else If. And that is one
term else, ElseIf, then a space. And now we
| | 04:42 | can define the conditions, and that is
if the total is greater than or equal to
| | 04:49 | 1000, then the commission
equals the total, curTotal * 0.06.
| | 05:03 | So in other words, if the total sales
is greater than 10,000, 8% commission;
| | 05:07 | if it's greater than or equal
to 1000, then a 6% commission.
| | 05:12 | Now press Enter twice.
| | 05:14 | Now, you can type the final Else statement,
| | 05:17 | so that's Else. And again, that needs to be on
its own line, or Access treats it as a keyword.
| | 05:23 | Then we can leave the curCom = curTotal * .05.
So greater than 10,000, 8% commission;
| | 05:31 | greater than or equal to
1000, 6%; anything else, 5%.
| | 05:36 | So let's check all three of those conditions.
| | 05:39 | So I'll press F5 to run it.
| | 05:41 | Now let's enter the amount of
the sale, and I'll make it 20,000.
| | 05:46 | So when I click OK, I should see
a commission of 1600, and I do.
| | 05:51 | Now, I'll press F5 again, and let's make
it 1000, which meets the criteria for the
| | 05:57 | next level, just barely.
| | 05:59 | Say I have a $1000 sale.
| | 06:01 | That should be a 6%
commission, so click OK and I get 60.
| | 06:06 | Then the final condition has a commission of 5%.
| | 06:08 | So if I press F5 and enter 100,
I should see $5, and I do.
| | 06:15 | If-Then statements let you execute code
conditionally based on the result of the
| | 06:21 | expression you set in the opening If statement.
| | 06:23 | I found that a three-part test, such as
the If-Then, Else If, Else, is about all
| | 06:29 | I can handle before I start to get confused.
| | 06:31 | If I want to execute code based on more
than three possible conditions, then I
| | 06:35 | usually create a case statement,
which I cover in the next movie.
| | Collapse this transcript |
| Selecting actions using a Case statement| 00:00 | The If-Then code construct, which I
described in the last movie, lets you
| | 00:04 | determine which code segments to
run based on the rules you define,
| | 00:08 | but its syntax is a bit convoluted.
| | 00:10 | If you need to run a brief code segment,
such as assigning a value to a variable
| | 00:15 | based on another variable's value,
you can use a Case statement.
| | 00:19 | A Case statement is more compact than
the If-Then statements, and I usually find
| | 00:23 | that I am less likely to make
mistakes when I create the conditions.
| | 00:27 | In this movie, I will use the Case
Statement code module. And that is here in
| | 00:31 | the Modules section of the Navigation Pane,
so I will double-click it and open it.
| | 00:36 | This code module contains the Sub and
End Sub lines and then also an InputBox
| | 00:42 | that gets the total of an order.
| | 00:44 | And our goal is to use multiple criteria to
calculate the commission due a salesperson.
| | 00:49 | So let's go ahead and start that.
| | 00:52 | First, I need to begin the Case
statement by saying Select Case, then a space,
| | 01:03 | and then the variable that I'll be testing.
| | 01:07 | That is curTotal, so cur and
then Total, and press Return twice.
| | 01:13 | Now I can start defining the cases.
| | 01:15 | The first one is Case Is, and that's how
you begin defining all but the last case.
| | 01:24 | So Case Is >= 10000. Press Enter.
| | 01:31 | I'll do it twice because I like to
have whitespace. Press the Tab key, and
| | 01:35 | now the commission, curCom equals the total,
curTotal * 0.08, then Enter twice, and Backspace.
| | 01:47 | Now I can define another condition, Case
Is, then >= 1000, Enter twice, curCom =
| | 02:01 | the total, curTotal * 0.06.
| | 02:06 | So in other words, I've
created two cases so far.
| | 02:09 | If the sale is $10,000 or more, the
salesperson gets a commission of 8%;
| | 02:15 | if the sale is $1000 or more but
less than $10,000, then they get 6%.
| | 02:20 | Now, I will press Enter, Enter
twice, and then press Backspace.
| | 02:25 | Now, another Case, and this will be
if it's greater than or equal to 500.
| | 02:30 | That is Case Is >= 500, Enter twice, Tab
over, and that would be a 5% commission.
| | 02:39 | curCom equals the total,
curTotal *0.05. Enter twice.
| | 02:50 | Now, finally, we have the catch-all case.
| | 02:53 | If none of the other cases are True,
then you type Case Else, Return twice, and
| | 03:02 | Tab over, and then in this
case the commission will be 4%.
| | 03:06 | So basically, anything below 500, you
get a 4% commission, and that is curCom
| | 03:12 | equals the total, curTotal * 0.04,
then Enter twice, and Backspace to move to
| | 03:20 | the left line. And now you
need to close the Case statement.
| | 03:23 | To do that, you type End Select.
| | 03:27 | Doing so parallels the Select Case
statement at the top. So our four cases:
| | 03:33 | 8%, 6%, 5%, and 4%.
| | 03:36 | Now that I've created the Case
statements, I can create a message box that will
| | 03:41 | display the commission. And I'll just
make that very simple, MsgBox, space, and
| | 03:48 | then curCom and Return.
| | 03:51 | So pick the commission, make the
calculation, and then display the
| | 03:55 | commission in a message box.
| | 03:57 | Now we can test the code, so I'll press F5.
| | 04:01 | Access asks what the total is.
| | 04:03 | The first case was 10,000
or more, so I'll type 1.
| | 04:06 | That's 10, then 1000 is 1, 2, 3 more 0s,
and I should see an $800 commission
| | 04:12 | when I click OK, and I do.
| | 04:14 | Second case is 1,000 or more, so I'll
press F5 and I'll type in 1,000, and add 6%.
| | 04:22 | I should see a $60 commission.
| | 04:24 | Click OK, and I do.
| | 04:26 | Now, the next is a new
case for 500, and that is 5%.
| | 04:30 | So if I press F5 and then type in 500
and click OK, I should see $25, and I do.
| | 04:38 | And then finally, for any other
value I should get a 4% commission.
| | 04:42 | So I will press F5, type in
100, and at 4% I should get $4.
| | 04:50 | Select Case statements let you evaluate a
variable and take action based on that value.
| | 04:55 | The easy-to-understand structure of
the Case statement lets you and your
| | 04:58 | colleagues maintain your code
effectively long after you create it.
| | Collapse this transcript |
|
|
6. Manipulating Database Objects Using DoCmdOpening a form| 00:00 | When you develop an Access
application for your users, one of the most
| | 00:03 | common tasks you'll have them
perform is to click a Command button that
| | 00:08 | displays a database object.
| | 00:10 | In this movie, I'll show you how to
create a VBA macro to open a form.
| | 00:14 | For this movie, we will use the form
that is named Open a Form, but we'll want
| | 00:20 | to open it in Design view.
| | 00:22 | So I will right-click the form and then from
the pop-up menu that appears, click Design View.
| | 00:29 | When we do, we get the form in Design
view, and it contains the Display Products
| | 00:34 | Form Command button.
| | 00:36 | Now, I'm going to right-click the
Command button and from the shortcut menu that
| | 00:40 | appears, click Build Event.
| | 00:42 | Then in the Choose Builder dialog box,
I'll click Code Builder, because we want
| | 00:46 | to use VBA code that runs when we
click the button, and then click OK.
| | 00:52 | Clicking OK causes Access to create a
new code module that is attached to the
| | 00:56 | button, and it will operate
when the button is clicked.
| | 01:00 | So that is why it is named Private
Sub Command0, which is the name of the
| | 01:05 | button, and then _Click, which
is the action that will drive this code.
| | 01:10 | So now I'll press Enter twice and the
up arrow once to give myself some room
| | 01:15 | within the code module.
| | 01:16 | And now I can type in the
code that will open the form.
| | 01:20 | The simplest way to open a form is
to use the DoCmd.OpenForm method.
| | 01:25 | So for that, I'll type in Do, capital
Cmd, period. And then the name of the
| | 01:32 | method under the DoCmd object
is OpenForm, and then a space.
| | 01:41 | After you type the space, Access displays
the arguments for this particular method.
| | 01:46 | The only one that's required is the FormName.
| | 01:49 | So I'll type double quote and then
the name of the form is Products, and
| | 01:56 | then close the quotes.
| | 01:58 | Now that I have that code, I can press
Alt+F11 to go back to the form. Change
| | 02:04 | the form to Form view.
| | 02:06 | For that, on the Design tab of the
Ribbon, I'll just click the View button and
| | 02:11 | that clicks it over to Form view.
| | 02:14 | And now, when I click the Display
Products Form button, the form appears.
| | 02:19 | What happened was that I clicked the
Command button and then Access ran the code
| | 02:24 | that was attached to it and
identified when it was clicked.
| | 02:27 | Now, that's not all you can do with the
OpenForm method, so I'm going to close
| | 02:32 | the Products Form by
right-clicking its tab, and then clicking Close.
| | 02:36 | And now I'm going to press Alt+F11
to go back to the VBA Code Module.
| | 02:42 | So what are some other things that
you can do with the OpenForm method?
| | 02:46 | Well, if you type a comma, then
Access displays the other attributes, or the
| | 02:51 | other arguments that you can
use, for the OpenForm method.
| | 02:55 | The first thing you can specify is what
sort of view you want to open the form in.
| | 03:00 | In this case--and I'll press Escape
to get rid of the super tooltip there--
| | 03:05 | we have Design, FormDS, PivotChart,
PivotTable, Layout, Normal, and Preview.
| | 03:10 | So we have all these different
views that you can choose from.
| | 03:14 | If you don't specify which view
you want, then you get Normal.
| | 03:17 | So for the purposes of this exercise,
I am going to use the down arrow key to
| | 03:22 | highlight Preview and then
press the Tab key to accept it.
| | 03:26 | And now when I type a comma,
I get the rest of the arguments.
| | 03:30 | The next argument is called FilterName.
| | 03:33 | A FilterName, or a filter, is
a query within the database.
| | 03:37 | So let's say, for example, that I had a
query, which I do, called By Name, which
| | 03:43 | sorts the contents of the Products
field that are displayed in the Products
| | 03:47 | table alphabetically by name,
as opposed to by Product ID.
| | 03:51 | So if I wanted to use that query as a
filter, type double quotes, and then the
| | 03:55 | name of the query, which is By Name,
and then double quotes and a comma.
| | 04:05 | Next is the WhereCondition argument,
and WhereCondition uses a SQL, which is
| | 04:11 | Structured Query Language, query,
which is put in without a Where statement.
| | 04:16 | In this case, SQL queries are beyond
the scope of this course, so I won't
| | 04:20 | be covering it here.
| | 04:21 | So I'll just type a comma.
| | 04:23 | And next, we have the Datamode.
| | 04:26 | This argument tells the users whether
they can open the form in Add mode, Edit
| | 04:30 | mode, PropertySettings mode, or ReadOnly mode.
| | 04:34 | ReadOnly mode is extremely useful
because it allows you to give users access to
| | 04:38 | the data, but without
allowing them to change it.
| | 04:41 | So I'm going to press the down
arrow key four times to highlight the
| | 04:45 | FormReadOnly property, or argument
setting in this case, and press Tab.
| | 04:50 | And now I'll type a comma and select
what type of window mode I want for this.
| | 04:56 | We can open it as a dialog, as a
hidden box--which means that it's invisible,
| | 05:00 | but it is open and can be acted upon--
as an icon, or as a normal window.
| | 05:05 | I'm going to hit the down arrow key
four times for normal window and press Tab.
| | 05:10 | And then finally, if I were to type
a comma, I could add OpenArgs, which
| | 05:14 | are specific controls.
| | 05:16 | But again, those are beyond the scope
of this course so I won't be using them.
| | 05:20 | Now, if I press Enter, I see the
command in the code window. And press Alt+F11
| | 05:25 | to go back to the form.
| | 05:26 | So now when I click the Display
Products Form button, Access opens the form in
| | 05:32 | Preview mode, which I specified,
and uses the other parameters.
| | 05:36 | And also, even though I can display the
data, you'll notice that I can't select it.
| | 05:41 | So even if I were to close Print
Preview mode and go back to Form mode, even
| | 05:46 | though I could select the data inside
of a form field, I couldn't edit it.
| | 05:51 | So for example, right now I'm tapping
the spacebar, and it's not changing it.
| | 05:54 | Same thing here in the Bottle
value in the ProductPackaging field.
| | 05:57 | If I type, nothing happens.
| | 06:00 | So that's the benefit of
opening the form in ReadOnly mode.
| | 06:04 | If all you want to do is open a form,
you can use the simple DoCmd.OpenForm
| | 06:09 | code, followed by the form's name in quotes.
| | 06:12 | If you want more control over how it
opens, set the method's arguments so you
| | 06:16 | get the effect you want.
| | Collapse this transcript |
| Opening a report| 00:00 | When you develop an Access
application for your users one of the most
| | 00:04 | common tasks you'll have them
perform is to click a Command button to
| | 00:08 | display a database object.
| | 00:10 | In this movie, I'll show you how to
create a VBA macro to open a report.
| | 00:15 | In the previous movie,
I showed you how to open a form.
| | 00:18 | If you didn't change the form's mode,
Access display that in normal mode.
| | 00:22 | If you open a report in default mode,
Access sends it directly to the printer.
| | 00:27 | So let's see what we need to do.
| | 00:29 | First thing I'll do is right-click the Open a
Report form and then open it in Design view.
| | 00:37 | So now I've this form open in Design
view, and I can right-click the Display
| | 00:42 | Products Report command button and
then from the shortcut menu that appears,
| | 00:47 | click Build Event. Then in the Choose
Builder dialog box, click the Code Builder
| | 00:53 | option and then click OK.
| | 00:56 | Doing so creates a new code module and
it's called Command0_Click, and that means
| | 01:02 | that anytime we click Command button
Zero, which is the one that we right-clicked
| | 01:08 | and then launch the Code Builder from,
| | 01:10 | when that button is clicked, this code will run.
| | 01:12 | So I'll press Enter twice and then the
up arrow to give myself a little room, and
| | 01:17 | now I can type in the code
that will open the report.
| | 01:21 | So in this case it's DoCmd.
| | 01:28 | and then the method we're
using is OpenReport, all one word.
| | 01:32 | So it's OpenReport and then a space.
| | 01:38 | Now we type the report name and
that name needs to be in double quotes.
| | 01:44 | So I'll type "Products Report."
| | 01:55 | Now if we were to do run the macro in
exactly this state, as I said earlier, Access
| | 02:00 | would send the report to the printer.
| | 02:02 | If you have got a large table or a
large report, that means you could be
| | 02:05 | printing out a lot of pages
or maybe a large PDF file.
| | 02:09 | So instead, what you probably want to
do, if you're going to display it on the
| | 02:12 | screen, is to change it to Preview mode.
| | 02:16 | To do that, type a comma, and then
you can select which mode you want to
| | 02:20 | display the report in,
| | 02:22 | so Design, Layout, and so on. And in
this case we want Preview, which is the
| | 02:27 | next-to-last item in the list, and when
it's highlighted I'll press tab to accept it.
| | 02:32 | And now we can apply some
other arguments if we want to.
| | 02:35 | So if I type comma, I can apply a filter
name. And in Access a filter is a query.
| | 02:41 | So in other words, if I had a query,
which I do, that sorts the contents of the
| | 02:46 | Products table by name, as opposed to the
usual sort, which is by Product ID, then
| | 02:52 | I could specify that here and have
the resulting report displayed in
| | 02:57 | alphabetical order by product
name, as opposed to by number.
| | 03:00 | So let's go ahead and do that. And we
need to put the query name in double quotes,
| | 03:06 | so "By Name," and then type a comma.
| | 03:13 | The next argument is a WhereCondition,
and that it is a Structured Query Language,
| | 03:18 | or a SQL, Where statement that you
can type in, just leaving out the where.
| | 03:23 | The SQL Where statements are beyond
the scope of this course, so we won't be
| | 03:27 | using it. And I'll just type a comma.
| | 03:29 | Next we can choose how to open this
particular report, in other words, what type of window.
| | 03:36 | We can open as a dialog box, as a
hidden window--which means it's there and can
| | 03:40 | be acted upon but the user can't see
it--as an icon or as a normal window.
| | 03:45 | In this case, I'll just go ahead and go
with the default, which is Normal Window,
| | 03:49 | so I'll use the down arrow to
select it and then press Tab.
| | 03:53 | Now if I were to press a comma,
I could apply some open arguments which are
| | 03:57 | basically arguments that you set
to control how the report is opened.
| | 04:01 | In this case, we won't be using them,
and they're beyond the scope of this
| | 04:04 | discussion, so I'll just press Enter.
| | 04:07 | Now with that code in place, I can press
Alt+F11 to go back to the form that has
| | 04:13 | the Display Product Report button.
And then on the Design tab, I can click the
| | 04:17 | View button which is
currently set for Form view.
| | 04:20 | If you need to, you can click the View
button's down arrow and then click Form
| | 04:23 | view. And then with the Form in place,
you can click the Display Products Report
| | 04:28 | button, and Access displays the report.
| | 04:32 | You have lots of control over your
report when you open it using the VBA code,
| | 04:36 | but remember that using the DoCmd
object's open report method prints the report
| | 04:41 | immediately, unless you change
the statement's view argument.
| | Collapse this transcript |
| Opening a table| 00:00 | When you create a database
application using VBA code, you will often allow
| | 00:04 | users to open tables or view queries results
by clicking a button that you place on a form.
| | 00:09 | In this movie, I'll show you how to write
the code that opens a table. To do that,
| | 00:13 | I'm going to open the form that
has the button that I want to click.
| | 00:17 | The form is called Open a Table.
| | 00:20 | So I will right-click it
and then click Design View.
| | 00:24 | Opening the form in Design view allows me
to interact with the objects on the form.
| | 00:29 | I want to add code to be run when the
Show Products Table button is clicked,
| | 00:34 | so I will right-click that
button and then click Build Event.
| | 00:38 | Then in the Choose Builder dialog box,
I will click Code Builder and click OK.
| | 00:44 | Doing so causes Access to create a new
VBA code module, and this code module will
| | 00:49 | be run when we click the Command+0
Command button, which was back on the form.
| | 00:54 | So now within the code module I'll
press Enter twice and then the up arrow key,
| | 00:59 | just to give myself a little bit more
room. And now I'll type in the code that
| | 01:03 | allows me to open a table, and that is
DoCmd, no spaces, then a period. And then the
| | 01:11 | method is OpenTable, space, and then
the name of the table in double quotes.
| | 01:21 | So double quote, and in this case the
name of the table is Products with an S, so
| | 01:27 | capital Products, then double quotes.
And now we can add other arguments.
| | 01:34 | So, for example, we can select
which view we want to open the table in.
| | 01:39 | So we can open it in Design view, in
Layout view, Normal view, and so on.
| | 01:43 | In this case, I want to open it in Normal view,
| | 01:46 | so I'll press the down arrow key until
viewNormal is selected and then press Tab,
| | 01:52 | and then type a comma.
| | 01:55 | And then the final argument we can set is
the mode that we want to open the table in.
| | 02:00 | So there are three: Add, Edit, and ReadOnly.
| | 02:03 | Anytime that a user looks directly
at a table, you should strongly consider
| | 02:07 | putting them in ReadOnly mode, and the
reason is that you don't want them to
| | 02:11 | edit existing values.
| | 02:13 | Now if it's a customer-service
application where they're taking address
| | 02:17 | changes, or could potentially put in new
orders, then editing or adding is fine.
| | 02:22 | But in this case, if you want them just to look
at the data, then you should make it ReadOnly.
| | 02:27 | So that is the argument that I will select.
| | 02:30 | So I pressed the down arrow key to
highlight ReadOnly, and I will press Tab, and
| | 02:35 | I have completed the statement. And then by
pressing Enter, I complete the code entry.
| | 02:41 | Now if I press Alt+F11 and go back to
the form and then on the Design Contextual
| | 02:47 | tab click the view button
to switch back to Form view,
| | 02:51 | if I click the Show Products Table
Command button, then the table appears.
| | 02:56 | And because I have it in ReadOnly
mode, I can't make any changes.
| | 03:02 | I just highlighted the data in row 7
and I am pressing the Delete key, but
| | 03:06 | nothing is happening.
| | 03:07 | And if I press Enter, I move,
but it doesn't add any more rows.
| | 03:12 | If I press Tab, the same thing happens.
| | 03:14 | So remember that display a table could
let your users change the data it contains.
| | 03:19 | If you don't want that to happen,
either display the table in ReadOnly mode.
| | 03:23 | Or if you want them to be able to add new
records but not edit existing records,
| | 03:27 | then select Add mode.
| | Collapse this transcript |
| Opening a query| 00:00 | When you create a database application
using VBA code you'll often want to
| | 00:04 | allow your users to open query results by
clicking a button that you place on a form.
| | 00:10 | In this movie, I'll show you how to
write the code that will display a query.
| | 00:13 | In the database, go ahead and in the
Navigation pane, right-click Open a Query--
| | 00:18 | that's the form we are going to
use--and then click Design view.
| | 00:24 | Now with the form opened in Design
view, right-click the Show Bottles Query
| | 00:29 | Command button and then click Build Event.
| | 00:33 | Now in the Choose Builder dialog box,
click Code Builder, and then click OK.
| | 00:40 | When we do, Access creates a code module that
will run when that command button is clicked.
| | 00:45 | Now to give myself a little room, I will
just press the Enter key twice and then
| | 00:50 | the up arrow key once.
| | 00:52 | Now we can type in the code that will
open the query, and that is the Do Command
| | 00:57 | Objects Open Query method.
| | 00:58 | So I will type in "DoCmd," no
spaces, and a period, and then we'll use
| | 01:06 | the OpenQuery method.
| | 01:08 | So that's OpenQuery and then a space.
| | 01:14 | And now we type in the name of the
query and enclose it in double quotes.
| | 01:19 | So double quotes and then Products,
space, Query, then double quotes. And if
| | 01:28 | we left this command exactly as it is, then
the query would open to display the results;
| | 01:34 | however, if we type a comma then Access
gives us the option of determining the
| | 01:39 | view we want to open it in.
| | 01:41 | So you can open it in Layout view, Normal view,
| | 01:43 | and I will press the Escape key so that
you can see the top one, which is Design view.
| | 01:48 | I would never recommend opening a
query in Design view because that would
| | 01:52 | allow your users to change it.
Most likely you will use either Normal view
| | 01:56 | or Preview or Report.
| | 01:59 | In this case, I will use Preview,
| | 02:00 | so I will just press the down arrow key
until Preview is highlighted, then press
| | 02:05 | Tab. And now if I type a comma, you can
select which of three modes you want to
| | 02:10 | open the query in. And I will press
the Escape key to get rid of the super
| | 02:14 | tooltip that was obscuring the top option.
| | 02:17 | So there are three modes:
Add, Edit, and ReadOnly.
| | 02:21 | Those are the same modes as are
available for a table, because a query is simply
| | 02:26 | a dynamically created table.
| | 02:28 | Of the three modes I recommend
that you always use ReadOnly.
| | 02:32 | That means that your users
can't get in and change anything.
| | 02:36 | So I will press the down arrow key
three times to highlight it, then press the
| | 02:40 | Tab key to select it.
| | 02:41 | There are no more arguments to set, so I'll
press the Enter key, and now press Alt+F11
| | 02:48 | to go back to the main database object.
And then on the Design Contextual tab,
| | 02:52 | click the view button, which
will take me back to Form view.
| | 02:56 | And now on the form, I will click the
Show Bottles Query button and when I do,
| | 03:01 | Access displays the Products query.
And if I click it and scroll up using my
| | 03:06 | mouse's scroll wheel, I can see the results.
| | 03:09 | And it came in Preview mode because I
selected Preview mode in the view argument.
| | 03:15 | After a user views a query's results,
they can close the object by clicking
| | 03:19 | the close box, which is located at the
top-right corner of the Object window,
| | 03:23 | as here.
| | Collapse this transcript |
| Closing an object| 00:00 | When a user finishes working with
a database object, they can close it
| | 00:03 | through the user interface;
| | 00:05 | however, if you would like to close an
object programmatically, you can do so
| | 00:09 | using the DoCmd.Close method.
| | 00:12 | So let's say that we are in the sample
database and we have two objects opened:
| | 00:17 | the first will be the Products Report
and the second will be the Products form.
| | 00:24 | So at present, the Products form has the focus.
| | 00:27 | That will be important in a moment.
| | 00:29 | Now I'll press Alt+F11 to go to the
Visual Basic Editor and then on the Insert
| | 00:35 | menu, click Module to insert a code module.
| | 00:39 | And I'll create a subroutine, sub, space.
And I'll just call it closing, open
| | 00:47 | and close parentheses, and then press Return
several times to give myself some room to work.
| | 00:53 | Now I have a space to type
in the DoCmd.Close method.
| | 00:58 | So, to do that, I'll type "DoCmd," no
spaces there, then a period, and then
| | 01:04 | Close, and then press Tab.
| | 01:08 | Now, I can add more arguments, and I
will later, but let's see what happens when
| | 01:12 | I just run DoCmd.Close.
| | 01:14 | And remember that the
Products form was in front.
| | 01:17 | It had the focus in the main database window.
| | 01:20 | When I press F5 to run this code,
nothing seems to happen here, but if I press
| | 01:26 | Alt+F11 to go back to the main
database window, we see that the Products form
| | 01:31 | has closed, and the only thing
that's left is the Products Report.
| | 01:35 | So what happened was that running
DoCmd.Close closed the object that had the
| | 01:40 | focus, and that was the Products form.
| | 01:42 | Now we're going to work
with the Products Report.
| | 01:45 | I'll press Alt+F11 again to go back to
the VBA code module, and now if I type
| | 01:51 | space, I can select from a list of
objects that I would like to close.
| | 01:56 | I'll press the Escape key
to get rid of the Tool tab.
| | 01:59 | So we can open or close the
DatabaseProperties window, the diagram, a form,
| | 02:05 | a function, whatever.
| | 02:06 | In this case, we want to close a report,
so I'll just scroll down and then click
| | 02:12 | Report and press Tab.
| | 02:15 | Now I can set some options for how
I want to close the report, and for
| | 02:18 | that, I'll type comma.
| | 02:20 | Now, we can name the object, and we do
need to if we use the acReport argument.
| | 02:25 | The report's name is Products
Report, and it needs to be in quotes.
| | 02:30 | So double quote "Products Report,"
so there is the name.
| | 02:41 | And now type a comma, and we
can select the Save option.
| | 02:46 | And the Save option is what appears when
you try to close a report, or any object,
| | 02:51 | and you've made changes.
| | 02:52 | So if you've made changes, you're often
asked if you want to save the changes or
| | 02:55 | not, and in this case you have three options.
| | 02:58 | The first one is SaveNo, which doesn't
save any changes regardless of what happened;
| | 03:04 | the second is SavePrompt, which gives
you the familiar dialog box asking if you
| | 03:09 | want to save your changes or not;
| | 03:11 | and then third, you have SaveYes, which
saves the changes again, regardless of
| | 03:16 | what action the user wants to take.
| | 03:18 | In this case, I don't want to save any
changes, so I'll use the down arrow key
| | 03:22 | to highlight SaveNo and press Tab to
accept that option, and then press the down
| | 03:28 | arrow key to complete entering the line of code.
| | 03:31 | So now we're going to close report
named Products Report, and we're not going
| | 03:35 | to save any changes.
| | 03:37 | So if I press F5 to run the code and
then Alt+F11, go back to the database
| | 03:43 | window, we see that the report
named Products Report has been closed.
| | 03:48 | Closing a database object seems simple,
and it can be, but you need to make sure
| | 03:53 | your object closes exactly how you want it to.
| | 03:56 | That's what the commands arguments are for.
| | Collapse this transcript |
| Closing a database or quitting Access| 00:00 | When your user has finished their
work in Access, they should close the
| | 00:03 | program to free up system resources
and prevent passersby from viewing data
| | 00:07 | they might not need to see.
| | 00:09 | In this movie, I'll show you the VBA code
you use to close a database, or to quit Access.
| | 00:15 | For this exercise, I'm going to open the Close
Database or Quit Access form in Design view.
| | 00:22 | So in the Navigation pane, I'll right-click
that form and then click Design View to open it.
| | 00:28 | And the form has two buttons on it: the
first one is called Close Database, and
| | 00:32 | the second is called Close Access.
| | 00:35 | To start out, let's work with Close Database.
| | 00:38 | So I'll right-click the button and then
from the shortcut menu, click Build Event.
| | 00:44 | Then in the Choose Builder dialog box,
click Code Builder, because we're working
| | 00:48 | with VBA code, and then click OK.
| | 00:51 | When we do, Access creates a code
module that will run when the button that we
| | 00:56 | selected is clicked.
| | 00:58 | And I'll just press Enter a few times
and move up using the up arrow to give
| | 01:02 | myself a little room to work.
| | 01:04 | The command to close a database is
probably about what you'd expect.
| | 01:08 | It is DoCmd no spaces, then a period,
and CloseDatabase, and then I'll press Tab to accept it.
| | 01:20 | This action does exactly
what it says it will do:
| | 01:23 | it will close the database.
| | 01:25 | What it doesn't do is quit Access.
| | 01:28 | So now if I were to press F5 to close
the database, which I won't do because I
| | 01:33 | want to continue working in it,
then I would see the database close.
| | 01:36 | So now I'm going to press Alt+F11 to go
back to the main database window, and now
| | 01:42 | we can work with the Close Access button.
| | 01:45 | So right-click that, click Build Event,
click Code Builder, and then click OK.
| | 01:53 | And the idea is to create another
subroutine that will run when the second command
| | 01:57 | button is clicked, and that is called Command1.
| | 02:00 | Press Enter a couple of times
to give myself a little room.
| | 02:02 | The CloseDatabase method is very
intuitively named, and so is the option to quit Access.
| | 02:08 | It's called quit.
| | 02:09 | So to type that in, I would type DoCmd.Quit.
| | 02:17 | Then when I press the spacebar, I can
select my options, and I'll press the Escape
| | 02:22 | key to remove the super tooltip
that was obscuring the top option.
| | 02:27 | The options I can set control how
Access will handle the data within the
| | 02:31 | database as it closes.
| | 02:33 | The top option, QuitPrompt, asks you if
you want to save any changes in any open
| | 02:38 | object; the second is QuitSaveAll,
which saves everything regardless of your
| | 02:42 | choice; and the third is QuitSaveNone.
| | 02:45 | In this case, I will use the down
arrow to highlight QuitSaveAll to save any
| | 02:49 | changes and then press the Tab key.
| | 02:52 | Now if I press the down arrow, I need
to save this code module, so I'll press
| | 02:56 | Ctrl+S, and I will save changes both
to the form and to the code module.
| | 03:02 | So I'll click Yes and the Module Name,
| | 03:04 | I'll leave as Module1.
| | 03:06 | Click OK, and now I'll press
Alt+F11 to go back to the main database.
| | 03:12 | Now on the form I need to switch back
to Form view, so that I can click the
| | 03:15 | button and have it run its code.
| | 03:18 | So on the Design Contextual Tab, I'll
click the View button to go back to Form
| | 03:23 | view and then click Close Access.
| | 03:26 | When I do, Access closes, and because I
said Save no on the Quit option, it closed
| | 03:33 | without asking me if I
wanted to save any changes.
| | 03:36 | Closing a database removes its
information from Access's user interface, while
| | 03:41 | quitting Access entirely closes the program.
| | 03:43 | When you write automated procedures to
import or export data, your code should
| | 03:47 | always contain code to quit Access so
you don't leave it running on your system.
| | Collapse this transcript |
| Sounding beeps and displaying the hourglass cursor| 00:00 | Very few things frustrate computer users
more than an unchanging computer screen.
| | 00:05 | When Access updates a query, for example,
you might see the mouse pointer change
| | 00:09 | from an arrow to an hourglass.
| | 00:11 | The system also uses beeps to indicate
when something has happened, such as
| | 00:15 | when you start your computer.
| | 00:16 | In this movie, I'll show you how to
give users feedback using beeps and
| | 00:20 | the hourglass cursor.
| | 00:22 | For this exercise, we are going to
use the Beep and Hourglass form.
| | 00:27 | So I go over to the Navigation pane,
right-click that form, and click Design View.
| | 00:32 | I have three command buttons on this form:
| | 00:34 | Sound a Beep, Show the
Hourglass, and Hide the Hourglass.
| | 00:38 | We will work with Sound a Beep.
| | 00:41 | To add code to that Command button,
I will right-click it, then click Build Event.
| | 00:47 | This opens the Choose Builder dialog box.
| | 00:50 | I will click Code Builder and then click OK.
| | 00:54 | When I do, Access creates code that will
run when this command button is clicked.
| | 00:59 | And the code to sound a beep is very simple.
| | 01:01 | I will just press Enter twice and
then the up arrow key to give myself some
| | 01:06 | room. And it is DoCmd.Beep. And I'll
press the Tab key to accept that entry.
| | 01:17 | Now, when I press the button on the form--
and to do that, I'll switch back using
| | 01:22 | Alt+F11, and take the form over to
Form view by clicking the view button--and
| | 01:28 | then when I click the Sound a Beep
button, the system plays a beep sound.
| | 01:34 | You can use beeps to indicate that a
form attached to a query has appeared.
| | 01:38 | For example, if the query has to run
against a large table or has to get at its
| | 01:42 | source data over a network,
there could be a delay.
| | 01:45 | Sounding a beep calls your attention back to
the computer when the process is completed.
| | 01:49 | Now, you can also use an hourglass
to indicate that a process is running.
| | 01:53 | The code to turn on the hourglass and
turn off the hourglass are very similar to
| | 01:59 | the Beep method of the DoCmd object.
| | 02:01 | So I am going to switch the Form back
over to Design view, clicking the View
| | 02:05 | button's down arrow,
and then clicking Design view.
| | 02:09 | Then I'll right-click the Show the
Hourglass button, click Build Event, and then
| | 02:15 | in the Choose Builder
dialog box, Code Builder, and OK.
| | 02:19 | Now I am back in my same code module,
and I'll press Enter twice. And now I'll
| | 02:24 | type in the code to display the
hourglass cursor, and that is DoCmd,
| | 02:31 | then Hourglass, space, and then we can set
the hourglass to either True of False.
| | 02:39 | In this case, we are turning it on, so
we'll make it True, and press Enter.
| | 02:43 | Now, I am going to press Alt+F11 to
switch back to the form, and I am going to
| | 02:47 | add the code to the Hide the Hourglass button.
| | 02:50 | That way we only have to go back once.
| | 02:52 | So I'll right-click the Hide the
Hourglass Command button, click Build Event,
| | 02:59 | click Code Builder, and click OK.
And here we are with our third Command
| | 03:03 | button, Click Event Code.
| | 03:06 | And as you might expect, the
code to turn off the hourglass is
| | 03:09 | DoCmd.Hourglass, space, and then False.
And with that change in place, I will
| | 03:18 | press Ctrl+S to save my work,
and then Alt+F11 to switch back to the form,
| | 03:25 | which is still in Design view.
| | 03:27 | On the Design Contextual tab, I will
click the View button, which will take me
| | 03:31 | back to Form view. And now if I click
the Show the Hourglass command button, the
| | 03:36 | cursor changes to an hourglass, or in
this case, in this Windows metaphor, or
| | 03:41 | theme, it's the spinning circle. And if
I click Hide the Hourglass, the cursor
| | 03:46 | changes back to its normal arrow.
| | 03:48 | One thing to note is that if a
subroutine fails while the hourglass is on, it
| | 03:52 | might not change back.
| | 03:54 | So if you use error handling code,
and you should, make sure that in the On
| | 03:58 | Error Statement you have a
DoCmd.Hourglass False statement, to change it back
| | 04:04 | to the regular arrow.
| | Collapse this transcript |
| Running a menu command| 00:00 | The typical Access application limits
what users can do--usually by hiding
| | 00:04 | significant chunks of the user interface.
| | 00:07 | If you want to allow your users to
take actions they would usually initiate
| | 00:10 | through the user interface, you can
attach a run command code segment to a
| | 00:14 | Command button or to a custom Ribbon item.
| | 00:17 | In this movie, we will use a Command button.
| | 00:20 | For this exercise, we will
use the Run Menu Command form.
| | 00:23 | I will go to the Navigation pane,
right-click the form, and then from the
| | 00:27 | Shortcut menu, click Design view.
| | 00:30 | I have my Command button here,
Run the Design view Command,
| | 00:33 | so I will right-click it, and then
from the Shortcut menu, click Build Event,
| | 00:38 | and then click Code Builder from the
Choose Builder dialog box, and click OK.
| | 00:44 | Doing so opens a code window
inside of the Visual Basic Editor.
| | 00:48 | I will just move this window up a bit,
make it a little easier to see, and press
| | 00:52 | Enter twice, and the up arrow once
to give myself some room to work.
| | 00:56 | So we want to run a menu command,
and the code for that and Access VBA is
| | 01:01 | DoCmd, period, and then
RunCommand, all one word,
| | 01:10 | then a space. And then you can select which
command you want to run, and there are hundreds.
| | 01:17 | So to keep things simple, I'm going to
use acCmd, and they all start with those
| | 01:24 | five characters, and then Designview, and press Enter.
| | 01:33 | So now the code in this module will
display the form or active object in Design view.
| | 01:39 | One reason I selected Design view is
because every object type has a Design view.
| | 01:44 | We have to get in and be able to design it.
| | 01:47 | So now, I am going to switch back to the form.
| | 01:49 | I will press Alt+F11.
| | 01:51 | The form is currently in Design view,
| | 01:53 | so on the Design Contextual Tab I will
click the View button to switch it to Form view.
| | 01:58 | Now, when I click the button, the code will run.
| | 02:01 | Remember, the target is to get
the form open in Design view.
| | 02:05 | So I'm going to click the button,
and when I do, the code runs, and the form
| | 02:10 | switches over to Design view.
| | 02:12 | The commands available to you for the Run
command method change from version to version.
| | 02:16 | Some commands are added to reflect new
capabilities, and some are taken away to
| | 02:20 | reflect commands that are no longer available.
| | 02:22 | That means you should take the time to
scroll through the command list to see
| | 02:25 | what's available in the version you're using.
| | Collapse this transcript |
| Printing the active database object| 00:00 | You can view the objects in an Access
database quickly, but you can't distribute
| | 00:04 | that data, or its summaries, all that easily.
| | 00:07 | If you do want to share summaries of
your data, you can do so by printing a form
| | 00:11 | or report on paper, or as a PDF file.
| | 00:14 | If you want to enable your users to do
that through VBA code, you can use the
| | 00:18 | DoCmd.PrintOut method.
| | 00:20 | For this movie, we'll use the
Products Report report, which is down here in
| | 00:25 | the Navigation pane.
| | 00:27 | I'll right-click its title and then
click Design view. And in the report, I
| | 00:32 | already have a button in the
header called Print Report,
| | 00:35 | so I'll right-click it and click Build Event.
| | 00:39 | Then in the Choose Builder dialog box,
I'll click Code Builder, because we want to
| | 00:43 | use VBA code, and then click OK.
| | 00:47 | So now I have the Command
button's Click event code.
| | 00:50 | I'll press Enter twice and the up
arrow once to give myself some room to work,
| | 00:55 | and I'll type in the code for printing out,
which is DoCmd.PrintOut, and then a space.
| | 01:09 | When you press the spacebar, Access
gives you a super tooltip that describes the
| | 01:13 | arguments you can set for this method.
| | 01:15 | So for example, you can
print all, or you can print some.
| | 01:19 | If I type ac then that you see that
you can print either pages, the entire
| | 01:24 | report, or just the selection.
| | 01:26 | I'll go ahead and use the down arrow key to
highlight PrintAll, then press Tab, then a comma.
| | 01:32 | Next, you have page from PageFrom.
| | 01:34 | So, for example, if you wanted to
print pages, then you could have page 1 to
| | 01:39 | 2, 2 to 4, whatever.
| | 01:41 | In this case, I am not
going to use that argument,
| | 01:43 | so I will just type a comma.
And the next argument is PageTo,
| | 01:48 | so this would be the final page it would print.
| | 01:50 | So if you wanted to print pages 1 to 4,
PageFrom would be 1, PageTo would be 4.
| | 01:56 | I'll type of comma because I am
not going to a value for that one.
| | 01:59 | Now we have PrintQuality. And we
select Draft, High, Low, or Medium.
| | 02:03 | I prefer High Quality, so I'll press the
down arrow twice, highlight acHigh, then
| | 02:09 | press the Tab key to select it. Then a comma.
| | 02:12 | Now we have the number of copies.
| | 02:13 | I just want one copy--that is
the default--then a comma, and we
| | 02:18 | have CollateCopies.
| | 02:19 | This can be either True or False.
| | 02:22 | So I will type in True and press Enter.
| | 02:25 | Now, if I go back to the report by
pressing AltF11 to switch from the Visual
| | 02:31 | Basic Editor back to the main Access
database and a change from the Design view
| | 02:37 | to Report view--to do that, I will
click the View button on the Design tab--
| | 02:42 | now when I click the Print Report button,
Access prints out a copy of the report.
| | 02:49 | Printing an Access Database object
lets you distributes its contents to your
| | 02:52 | colleagues without sharing the underlying data.
| | 02:55 | Remember, though, that anything
you print can be shared beyond your
| | 02:58 | original target audience.
| | Collapse this transcript |
| Displaying or hiding warnings| 00:00 | Writing VBA code lets you
automate actions within your database.
| | 00:04 | One benefit of that automation is speed.
| | 00:06 | You don't need users to
initiate or confirm any actions.
| | 00:09 | They just happen because of your code.
| | 00:11 | Some actions, such as deleting a table row,
usually requires users to verify the action.
| | 00:17 | You can choose to hide those
warning boxes so your code runs
| | 00:19 | without interruption.
| | 00:21 | For this exercise, we're
going to use two database objects:
| | 00:24 | first is the Products Table, and the
second is the Manage Warnings form.
| | 00:29 | First I'm going to open the Products
Table just by double-clicking it in the
| | 00:33 | Navigation pane, and then click
the first open ProductName field.
| | 00:38 | And the idea is that I'm going to add two new
products that we'll just delete in a minute.
| | 00:44 | So let's say that we have Lime-infused
olive oil. And don't worry about the ProductID.
| | 00:50 | That's an auto-number field,
and because I've worked through this exercise
| | 00:53 | before, the numbers are
higher than they would normally be.
| | 00:57 | So we have Lime--we'll press
the Tab key--and ProductPackaging.
| | 01:00 | It is a Bottle.
Press Tab, and the cost is also $12.50.
| | 01:06 | Now I'll press Tab and then Tab again
so that I can enter a new product name.
| | 01:11 | And in this case, we'll do Lilac.
| | 01:15 | It is a Bottle, so I press Tab to
move to the ProductPackage. And then the
| | 01:20 | ProductCost $12.50, and press Tab again.
| | 01:24 | With that change in place, we
can open the Manage Warnings form.
| | 01:28 | So I'll right-click the Manage Warnings form
in the Navigation pane and click Design view.
| | 01:35 | This form has two buttons:
| | 01:37 | the first is Turn Warnings Off,
and the second is Turn Warnings On.
| | 01:41 | So we're going to add code to the buttons.
| | 01:44 | To do that, I will right-click
the Turn Warnings Off button--
| | 01:47 | that's the one on top--then click Build
Event, then in the Choose Builder dialog
| | 01:53 | box, click Code Builder, and then click OK.
| | 01:57 | When I do, Access creates a code window
so that I can put in code that will run
| | 02:01 | when the button is clicked.
| | 02:03 | And I'll press the Enter key twice and the up
arrow once to give myself some room to work.
| | 02:07 | Now we're turning warnings off,
so I will use the code DoCmd,
| | 02:12 | DoCmd.SetWarnings, space, and then
WarningsOn can be either True or False.
| | 02:24 | I want them to be off, so I'll
type False, and then press Enter.
| | 02:28 | Now I'm going to press Alt+F11 to go
back to the form and right-click the Turn
| | 02:34 | Warnings On button. And we'll
just repeat the steps we did before.
| | 02:38 | Click Build Event, in the Choose Builder
dialog box, click Code Builder, and then click OK.
| | 02:44 | So now I can add code for this Command
button, and it's exactly the same as what
| | 02:49 | we had above, except instead of False,
| | 02:52 | it's going to be True.
| | 02:54 | So I will copy that code, selecting it
with my mouse. Press Ctrl+C to copy, and
| | 03:00 | then paste it in the subroutine below,
and Backspace over False and type in
| | 03:06 | True and press Enter.
| | 03:08 | So with that code in place, I can
press Alt+F11 to go back to the form and
| | 03:13 | then on the Design tab of the Ribbon, click
the View button to switch back to Form view.
| | 03:19 | Now before I go back to the
Products Table, I want to click the Turn
| | 03:22 | Warnings Off button.
| | 03:24 | You didn't see anything from the code
running, but it did, and it turned off warnings.
| | 03:28 | So now if I click the Products Table's
tab at the top of the Object window and
| | 03:34 | then right-click the row header from
Lilac and click Delete Record, Access
| | 03:40 | deletes the record without
verifying that I wanted to do so.
| | 03:44 | As an advanced user of Access, I'm
sure that you've seen that when you delete
| | 03:48 | a record Access, by default, asks if you're
sure you want to do that because the data is gone;
| | 03:54 | it cannot be recovered.
| | 03:55 | So now let's turn warnings back on.
| | 03:57 | To do that, click the Manage Warnings
form's tab on the Tab bar and then click
| | 04:03 | the Turn Warnings On button.
| | 04:06 | Then switch back to the Products Table,
right-click the row header for the Lime
| | 04:11 | row, and from the shortcut
menu, click Delete Record.
| | 04:15 | And when we do, we get the You are about
to delete 1 record confirmation dialog box.
| | 04:20 | In this case, we want it to
stay gone, so we'll click Yes.
| | 04:24 | Turning Warnings Off helps your code
run without interruption, but it also
| | 04:28 | denies users the ability to think twice
about deleting a table row or a database object.
| | 04:33 | It's a powerful technique, but use it wisely.
| | Collapse this transcript |
|
|
7. Reading and Manipulating Table DataDisplaying every row in a table (Recordset)| 00:00 | If you, or one of your users, wants to
review the values in a table field without
| | 00:04 | using a form, you can do so by moving
through each table row and displaying the
| | 00:09 | target field's value in a message box.
| | 00:11 | For example, you can display each
product's name to ensure that you have no
| | 00:15 | duplicates in the table.
| | 00:17 | To do that in VBA, you can use the
code that I'll show you right now, and it's
| | 00:21 | contained within the ShowEach module.
| | 00:24 | So over in the Navigation pane,
double-click ShowEach and you'll see the
| | 00:28 | skeleton of the code that you need to use.
| | 00:31 | I've already put in the Sub and End
Sub statements and also the dimension
| | 00:36 | statements for the myR variable. And I
already Set myR to an OpenRecordset, and
| | 00:43 | that is going to be the Products
table, which is named here as the
| | 00:47 | OpenRecordset method's argument.
| | 00:50 | Okay, with that code in place, we
can complete what we need to do.
| | 00:54 | The first thing is to do a loop, because
we're going through every record in the
| | 00:58 | table, and this will be a Do until loop.
| | 01:02 | So it's Do until, space, and then it's going to
be every record in the Recordset that I
| | 01:11 | currently have defined as myR.
| | 01:13 | So it's myR, then a period, and we're
going to use the End of File, or EOF, property.
| | 01:21 | So EOF, and what the EOF, or End of File,
property indicates is whether we are at
| | 01:28 | the last row in the table.
| | 01:30 | If we are, the Do until loop stops;
| | 01:32 | if not, it continues on to the next record.
| | 01:35 | So I can press Enter, and then we have
MsgBox myR, then an exclamation point
| | 01:47 | so that we can name it the field we
want to display. And that is done by
| | 01:51 | typing a left square bracket and
then the name of the field, which is
| | 01:55 | ProductName, right square bracket, then an ampersand,
double quote, a comma, and then a space, and
| | 02:08 | then a double quote, then an ampersand
again, space, and then we're going to
| | 02:11 | do the product cost field value. So it's myR!
| | 02:23 | left square bracket, ProductCost, and a
right square bracket. Then press Enter.
| | 02:25 | So what that code does is it
displays a message box with the product name
| | 02:29 | field's value, then a comma followed
by a space, and then the ProductCost.
| | 02:35 | Then finally, we want to move down to
the next record as long as we're not at
| | 02:38 | the end of file, and that
code is myR.MoveNext, all one word, no space.
| | 02:52 | Then press Return twice and type "Loop."
| | 02:57 | So this concludes the Do until loop
here, and everything else will happen as
| | 03:02 | long as we're not at the end of the file. Okay.
| | 03:05 | With that information in place,
we can press F5 to run the code.
| | 03:09 | So the first record in the Products
table is Garlic, and its cost is 12.5, or 12
| | 03:14 | dollars and 50 cents.
| | 03:15 | I didn't worry about formatting in this case;
I just wanted to illustrate the point.
| | 03:19 | And when I click OK, we go to
the next record, Basil, also 12.5.
| | 03:23 | They will all be 12 and a half dollars.
Jalapeno, Lemon, Mandarin, Rosemary. and we're done.
| | 03:32 | This technique for moving through each
record in the table lets you view each
| | 03:35 | row's contents, or you can use it
internally to move through each record and
| | 03:40 | make a change to certain values.
| | Collapse this transcript |
| Adding a new record to a table| 00:00 | Every time your business hires a new
employee, accepts a new order, or adds a
| | 00:04 | customer, you need to enter
their information into your database.
| | 00:08 | In this movie, I'll show you how to
add a new record to a table using VBA.
| | 00:13 | We are going to be using the SalesPeople table,
| | 00:15 | so I will just open that real quick to
indicate what information we have in there.
| | 00:19 | So over in the Navigation pane, double-
click SalesPeople. And you don't need to
| | 00:23 | follow along if you don't want to,
but just to show you what's here, we
| | 00:27 | currently have three individuals.
And then I'm going to close the table by
| | 00:32 | pressing Ctrl+W. Now, we're
going to use the AddNew code module.
| | 00:37 | So here in the Modules section of the
Navigation pane, I'll double-click AddNew,
| | 00:42 | and there we have the code.
| | 00:43 | I've already put in the
skeleton of the subroutine,
| | 00:46 | so we have AddNew and then the End
Sub statement, and then I've created a
| | 00:51 | variable myR as a recordset, and then
I've used the Set command to assign that
| | 00:58 | variable, myR, to an
OpenRecordset action on SalesPeople.
| | 01:03 | So in other words, myR is going to operate
on the contents of the SalesPeople table.
| | 01:09 | With that information in place, we can
put in the commands that will add a new
| | 01:13 | record to that table.
| | 01:15 | That code starts out with myR.
And we are going to use the AddNew method.
| | 01:23 | It's the second one in the list.
| | 01:24 | so I'll press the down arrow twice to
highlight AddNew and then press Tab,
| | 01:29 | and then press Enter.
| | 01:32 | That method adds a new row to the recordset.
| | 01:35 | Now, we can assign
valuables to the different fields.
| | 01:38 | To do that, type myR!
And this allows us to put in a field name.
| | 01:45 | That field name is enclosed in square
brackets, so we type a left square bracket
| | 01:50 | and then the SPFirstName.
| | 01:54 | So SP, short for SalesPeople, then
FirstName, and there are no spaces anywhere
| | 02:00 | in that field name.
| | 02:01 | Then type a right square bracket,
space and an equal sign, and then the
| | 02:06 | value in quotes, and in this case that value
will be curtis, so "Curtis," and then press Enter.
| | 02:15 | Now, we'll do the same thing to add the
last name or Frye to the SPLastName field.
| | 02:21 | So it's myR! left square bracket, SPLastName,
right square bracket, space, then an equals sign,
| | 02:34 | and again, the value in
quotes. And we'll do "Frye," or you can
| | 02:40 | use any name you like, and then press Enter.
| | 02:44 | So there is that information.
| | 02:45 | Now, we need to tell Access to
update the table, and that is myR.Update,
| | 02:55 | and press Enter, or Tab--both will work.
| | 02:58 | So with that information there, this
code will add a record with the name Curtis
| | 03:03 | Frye to the SalesPeople table.
| | 03:05 | I'll press F5 and then go back to the
main database window by pressing Alt+F11,
| | 03:13 | and then in the Navigation pane,
double-clicking SalesPeople.
| | 03:16 | When I do, we see that Access has
added a new record with the SalesPerson
| | 03:21 | with the FirstName Curtis and the LastName Frye.
| | 03:24 | In this example, I assigned the New
Records field values in the code, but you
| | 03:29 | could just as easily get the value
from another source, such as a text file or a form.
| | Collapse this transcript |
| Editing values in an existing table row| 00:00 | When you want to allow your users to
edit values within a table, you can do so
| | 00:04 | by creating a subroutine that
opens the table and applying the edits.
| | 00:09 | In this movie, I'll show
you how to create that code.
| | 00:12 | We're going to be working
with the Clients tables' contents,
| | 00:15 | so I'll just go ahead and open that
up. And you don't need to follow along;
| | 00:19 | I just want you to see that when we
double-click the Clients table, that we
| | 00:23 | have three clients:
| | 00:25 | TerraBone, Buena Suerta, and CartierLite.
| | 00:28 | So those are the three companies
that are in there right now, and we'll
| | 00:31 | change them around.
| | 00:32 | I'll press Ctrl+W to close that table,
and then open the code module that we're
| | 00:38 | going to use, and that is EditExisting.
| | 00:41 | So I'll just go down to the Modules
section of the Navigation pane and
| | 00:45 | double-click EditExisting.
| | 00:46 | I've already put in the
skeleton of the EditExisting code.
| | 00:51 | I've created a variable called myR,
which I've defined as a recordset, and then
| | 00:58 | I've assigned the Clients Table through
the OpenRecordset method of a CurrentDb
| | 01:03 | object to that variable.
| | 01:05 | Now we can type in the rest of the
code that will make the macro work.
| | 01:09 | The first code is probably what
you'd expect--that is, myR.Edit.
| | 01:16 | This opens the current table row for editing.
| | 01:20 | Press Enter a couple of times,
and then we have myR. And then we're going to
| | 01:26 | define which fields we're working with,
| | 01:28 | so I'll type an exclamation point, a left
square bracket, and then ClientCompany, all
| | 01:34 | one word, and then a right square bracket,
an equal sign, and now the value that
| | 01:44 | we're going to assign to the
ClientCompany field. Double quote and then
| | 01:48 | BuenaVista, all one word in this case,
although it doesn't really matter if you
| | 01:57 | have a space in there--I just prefer to
type it without it. Then press Enter.
| | 02:01 | And now we are going to put in a
value for the ClientSalesPerson field.
| | 02:06 | So that's myR!, left square bracket,
ClientSalesPerson, right square bracket, = "3". Now I'll press Enter.
| | 02:28 | And we need to tell Access to update
the field, and that command is myR--again,
| | 02:35 | we're working with a
recordset--.Update, and Enter.
| | 02:40 | So with this code in place, if I press
F5, Access runs the code. And now I'll
| | 02:47 | switch back to the main database by
pressing Alt+F11, and then double-clicking
| | 02:52 | the Clients table to open it, and we
see that the first client's name has been
| | 02:58 | changed to BuenaVista.
| | 03:00 | The code we just created operates
on the first record of a recordset.
| | 03:06 | What if you want to affect a
record elsewhere in the recordset?
| | 03:09 | Well, what you need to do is to find
that record using the Find method and
| | 03:15 | then work on it there.
| | 03:17 | I'll show you how to do that.
| | 03:18 | So I'll press Ctrl+W to close this
table and then Alt+F11 to switch back to
| | 03:26 | the EditExisting code module. And we're going
to change the code in a couple of different ways.
| | 03:31 | For this example, I'm supposing that we
want to edit the sample name and sample
| | 03:37 | number values in the field with Client ID3.
| | 03:41 | To make that change, we need to
open the Clients table differently.
| | 03:46 | In other words, we're not just opening it;
| | 03:47 | we need to open it as what's called a dynaset--
| | 03:50 | in other words, a recordset
that is dynamic and can be changed.
| | 03:54 | So in the set myR = CurrentDB.
OpenRecordset statement, after Clients, but still
| | 04:01 | within the parenthesis, type a comma
and we're going to set the type, and that
| | 04:07 | is dbOpenDynaset, and then press the down arrow.
| | 04:17 | Now we need to add this code right
after the Set statement but before the Edit
| | 04:21 | statement, and that code is myR.
| | 04:25 | FindFirst, space, double quote,
and then a left square bracket, ClientID,
| | 04:39 | right square bracket, = 3, double quote, and press Enter.
| | 04:46 | So now, what does that statement do?
| | 04:47 | Well, what it does is it looks at
the myR recordset object and uses
| | 04:53 | the FindFirst method.
| | 04:56 | The FindFirst method requires an
argument that tells it what to look for.
| | 05:00 | It expects a field, and in this case
we're telling it that it is the ClientID
| | 05:04 | field that is set off by the square
brackets, and then the equal sign and the
| | 05:08 | value we're expecting, and that value is 3.
| | 05:11 | Now, let's change the ClientCompany
to something other than BuenaVista.
| | 05:16 | So I'll backspace over most of
BuenaVista, then type in BonSoir, and we'll make
| | 05:23 | the ClientSalesPerson, instead of 3, 4.
| | 05:27 | That way we make sure that the
information is completely different from what we
| | 05:30 | have in the table before.
| | 05:31 | What this code will do is to look
inside the Clients table, find the client ID
| | 05:38 | row that has the value 3--the first
one of those--and then it will change the
| | 05:42 | ClientCompany name to BonSoir and
the ClientSalesPerson to number 4.
| | 05:48 | I'll press F5 to run the code and
then press Alt+F11 to go back to the
| | 05:54 | main database window.
| | 05:55 | Then double-click the Clients table,
and we see that indeed client ID3 is now
| | 06:00 | named BonSoir and the
ClientSalesPerson is number 4.
| | 06:05 | The original code in this movie
changed the first row in the table we opened.
| | 06:09 | If we wanted to work with another row,
such as one that contains a specific
| | 06:13 | value in this index field, we
would need to use a Find method.
| | 06:17 | I showed you the most basic case for
using a Find statement, but I cover it in
| | 06:20 | much more depth elsewhere in this course.
| | Collapse this transcript |
| Preserving data integrity using transactions| 00:00 | It's easy to make mistakes
when you edit table data,
| | 00:03 | so most databases have a transaction
system that lets you verify that you want
| | 00:07 | to make your change.
| | 00:08 | Transactions work by accepting your
changes as temporary input and letting you
| | 00:13 | ask the user if they're sure
they want to make the change.
| | 00:16 | If so, Access completes the transaction.
| | 00:18 | If not, it returns the table to its
previous state, and it discards the changes.
| | 00:24 | The table we will work
with is the Clients table,
| | 00:27 | so I'll just double-click that over
in the Navigation pane to open it.
| | 00:31 | The table is in the state that it was
in after I completed the exercise in
| | 00:35 | the previous movie.
| | 00:37 | But what's important is that the
ClientSalesPerson field contains a value of 2.
| | 00:43 | So if there's at least one record there
that contains a 2 in that field, then fine;
| | 00:48 | if not, then go ahead and change any
of the values in the ClientSalesPerson
| | 00:53 | field to 2 so that the code will run properly.
| | 00:56 | In this case, it's the ClientID
number 2, Buena Suerta, that has a
| | 01:00 | ClientSalesPerson value of 2,
| | 01:02 | so that's the one that will be changed.
| | 01:03 | I'll press Ctrl+W to close the table,
and we're going to work with the
| | 01:08 | UseTransactions code module. And it's
all the way at the bottom of the Modules
| | 01:13 | section of the Navigation pane.
| | 01:15 | I'll double-click it, and it
appears inside the Visual Basic Editor.
| | 01:19 | Now, there's a lot of code here,
| | 01:20 | so instead of having you type anything
in, I'm going to save some time and just
| | 01:25 | go through what the code does.
| | 01:27 | I'll start out with the declaration for
the subroutine named AddingTransactions,
| | 01:32 | and then I define two variables:
| | 01:34 | the first is myR, which is a recordset,
and then the second is myWS, which is a workspace.
| | 01:43 | Then I assign the Clients table to the
myR recordset and the existing workspace--
| | 01:50 | in other words, the user environment
inside of Access--to the variable myWS.
| | 01:56 | We use workspaces so that we
have access to transactions.
| | 01:59 | And in fact, the very next
statement is myWS BeginTrans.
| | 02:04 | In other words, we're about to affect a
table, but Access is going to make sure
| | 02:09 | that it gets a commit statement before
it actually implements, or applies, any of
| | 02:14 | those changes to the table data.
| | 02:16 | Now the code is going to go through
each row in the recordset until it
| | 02:20 | reaches the end of file.
| | 02:21 | That is what the Do Until
myR.EOF and then loop code does.
| | 02:27 | Within that loop, it's going to look
at the ClientSalesPerson field for each
| | 02:32 | row, and if that field contains the
value of 2, then it's going to open that
| | 02:38 | row for editing, and it's going to
change the ClientSalesPerson value to 1, and
| | 02:43 | then it will update.
| | 02:44 | But again, that update isn't final
because we're in Transaction mode and we
| | 02:48 | haven't seen a commit transaction statement yet.
| | 02:51 | Then we close the If and move to the next row,
as long as we haven't reached the end of file.
| | 02:57 | When we do reach the end of file, we drop out
of the loop, and Access displays a message box.
| | 03:02 | That message box contains the text "Changed
salesperson 2 to 1," and asks, "Save the changes?"
| | 03:09 | Now, this message box has a question
format and also Yes and No buttons.
| | 03:16 | If the user clicks the Yes button, so
that is why = vbYes is the condition for
| | 03:22 | the If statement, then it
commits the transaction.
| | 03:26 | So it goes within the workspace object
and implements the CommiTrans method.
| | 03:31 | If not, which is signaled by the
Else statement, it does a rollback.
| | 03:36 | In other words, it discards the
changes and leaves the table in its
| | 03:39 | previous state. And then the rest of the code
is used basically to undo what we did before.
| | 03:44 | We close the If, then we close the
recordset, close the workspace, and set those
| | 03:49 | two variables to Nothing.
| | 03:51 | So now we can run the code.
| | 03:53 | I'll press F5, and then we flip back
to the Access database window, and we
| | 03:58 | see the message box indicating that
the change occurred and asking if we
| | 04:02 | want to save the changes.
| | 04:03 | I'll click Yes, and then we go back to
the Visual Basic Editor, because that's
| | 04:08 | where we were when the code ran.
| | 04:09 | Now, I'll press Alt+F11 to go back to
the main database window and double-click
| | 04:14 | the Clients table in the
Navigation pane to open it,
| | 04:17 | and we see that the second
record has indeed changed;
| | 04:21 | the ClientSalesPerson
value has changed from 2 to 1.
| | 04:24 | Transactions are one way to maintain
data integrity, but you should always back
| | 04:29 | up important business data every day to
make sure that if a mistake does occur,
| | 04:34 | you can rebuild your tables with recent data.
| | Collapse this transcript |
| Displaying a table property using the TableDef object| 00:00 | When a user adds data to a table,
there is the possibility they will need to
| | 00:03 | know information about
the settings for each field.
| | 00:07 | For example, they might need to know
the maximum number of characters that they
| | 00:10 | can type into a text field.
| | 00:12 | If you want to give them the
opportunity to display that information, you can
| | 00:15 | use the TableDef object's properties to do so.
| | 00:19 | For this movie, we're going to use the
DisplayProperty code module. And that's
| | 00:23 | here in the Navigation pane, in the
Module section. And I'll double-click
| | 00:27 | DisplayProperty to open it
in the Visual Basic Editor.
| | 00:31 | I've already created the
skeleton for this code module.
| | 00:34 | I have the Sub and End Sub statements,
and then I've created three variables:
| | 00:40 | They are Db, which refers to a Database;
myT, which refers to a TableDef;
| | 00:45 | and then myF, which is a field.
| | 00:48 | And I've already set the Db variable to
the current database, which is indicated
| | 00:52 | by the keyword CurrentDb. And now I can
start typing in the code that will allow
| | 00:57 | us to pull out and manipulate
information about one of the properties for the
| | 01:02 | table we want to work with.
| | 01:03 | I'll type in "Set myT" and again, this
refers to a table definition, or TableDef, space
| | 01:14 | equal sign. And then we need to identify the
table we want to use, so that will be Db,
| | 01:18 | which again refers to the
current database, .TableDefs.
| | 01:25 | So this refers to the collection of
all TableDefs within this database. Left
| | 01:28 | parenthesis. And now we're going to
identify the name of the table, so we need
| | 01:34 | to put it in quotes, because it's a text string.
| | 01:36 | So "Clients") and press Return.
| | 01:45 | Now we need to identify the field that
we're going to use, and that will be the
| | 01:49 | ClientCompany field.
| | 01:51 | That's another Set statement.
| | 01:52 | Set myF = and myT. And again,
that refers to the table or TableDef of
| | 02:03 | clients, .Fields, then a left
parenthesis, and we need to identify the field
| | 02:10 | again by name, double-quote mark and
then ClientCompany, all one word, double
| | 02:21 | quote, and a right parenthesis, and press Enter.
| | 02:24 | I pressed Enter twice so I had a
little bit more space to work with.
| | 02:27 | Now, the last statement we're going to
type into this code module takes the Size
| | 02:31 | Property and includes that in a
message box that indicates the maximum company
| | 02:35 | size that you can type
into the ClientCompany field.
| | 02:39 | So that command is MsgBox
"Maximum company name size is " &.
| | 02:57 | And again, the ampersand, which is what
you type by pressing Shift+7, means that
| | 03:02 | you're concatenating two values
together into a single text string.
| | 03:06 | So after the ampersand,
we're going to type in myF.Size.
| | 03:14 | When you type myF and then a period,
you get a list of all the properties for
| | 03:18 | that field. So you can use any one you want;
| | 03:21 | in this case we'll use Size, then a
space, another ampersand, because we're
| | 03:25 | going to concatenate yet another
value, or another text string in this case.
| | 03:29 | Type space, " characters."
and then a down arrow.
| | 03:39 | So what we've done is defined a message
box that says, "Maximum company name size
| | 03:43 | is." Then it includes the value from the
field Size Property and then ends with
| | 03:48 | characters and a period.
| | 03:50 | So when we press F5 to run the code,
Access displays a dialog box indicating
| | 03:56 | that the Maximum company
name size is 255 characters.
| | 04:00 | And when we click the OK button,
we go back to the VBA Editor.
| | 04:04 | You should take the time to look through
the list of properties available to you
| | 04:07 | after you type "myF." You
might find one or more other properties you
| | 04:12 | can use to your advantage.
| | Collapse this transcript |
| Closing a Recordset| 00:00 | When you're done working with a
recordset in your VBA code, you should close it
| | 00:04 | to release the system resources
used to keep it in working memory.
| | 00:07 | In this movie, I'll show you the code to do that.
| | 00:10 | One thing that I'd like to point out
is that a recordset usually refers to a
| | 00:14 | table, but it doesn't have to;
| | 00:15 | it can also refer to a query.
| | 00:17 | In this movie we're going to use the
close method from the Recordset object, but
| | 00:22 | you shouldn't confuse this close which
happens in working memory to closing a
| | 00:27 | table inside of the database window.
| | 00:30 | So for example, we're going to be
working with the Products table, and if I
| | 00:33 | double-click Products, then the Products
table opens, and the user can work with it.
| | 00:39 | The method we're going to
use does not close this table.
| | 00:43 | In other words, if I were to click the
Close button now and remove the table
| | 00:46 | from the database window, that would be
a different action from what we're going
| | 00:50 | to do inside of VBA.
| | 00:52 | With that in mind, I'll double-click
the CloseTable code module over in the
| | 00:57 | Navigation pane, and that displays
the Visual Basic Editor and opens the
| | 01:02 | CloseTable code module.
| | 01:04 | I've already created the
skeleton for this module.
| | 01:06 | We have the Sub and End Sub statements.
| | 01:09 | I've defined the myR variable as a
recordset, and then after the code that we
| | 01:15 | want to run runs, we Set myR to Nothing.
| | 01:19 | Now we can start typing in the
code that we're going to use.
| | 01:22 | The first thing we need to do is to set
the myR variable to the Products table.
| | 01:27 | So we'll set the myR variable by typing
Set myR = CurrentDb, all one word,
| | 01:41 | no spaces--and that refers to the
current database, the one that we're working
| | 01:45 | in right now--period, and then
OpenRecordset is the object we'll work with.
| | 01:50 | So that's OpenRecordset.
It's highlighted in the auto complete list,
| | 01:55 | so I'll press Tab to accept it.
| | 01:57 | Now we need to identify the table
that we want to use, and that will be
| | 02:00 | the Products table.
| | 02:02 | So I'll type a left parenthesis, double
quote, because the name of the table is
| | 02:06 | a text string, and then "Products"
and then Return. And type in some code so
| | 02:17 | that we see something happening between the
time that we open the recordset and then close it.
| | 02:22 | For this, I'll just do a message box
with the name of the recordset, and that's
| | 02:25 | MsgBox, short for message box, a space,
and then myR, which is the object variable
| | 02:33 | referring to the Products
table, a period, and then name.
| | 02:37 | So type an N and then press the Tab key
to use the name property. Press Enter,
| | 02:43 | and then finally, we'll do myR.Close.
| | 02:51 | One thing to note: when you type in Clo,
the first value that appears in the auto
| | 02:56 | complete list is Clone.
| | 02:58 | That's not what we want to use. We want Close,
| | 03:01 | so I usually just type it all the way out.
| | 03:03 | Now press Tab, and we have the code that we want.
| | 03:07 | So when we press F5 to run the code,
Access displays the name of the recordset
| | 03:13 | inside of a message box, and then when we click
OK, Access closes the message box, and then it
| | 03:19 | closes the recordset and sets it to nothing.
| | 03:22 | Modern computers have lots of built-in
memory, but closing an unneeded table
| | 03:26 | frees up some memory and reduces screen clutter.
| | 03:28 | That's especially true in Access,
where database tables can be extremely large
| | 03:32 | and take up a lot of memory.
| | 03:34 | So it's a good habit to get into
closing a recordset you're no longer using.
| | Collapse this transcript |
| Deleting the current record| 00:00 | When you work in Microsoft Access, you should
delete any table record you no longer need.
| | 00:05 | For example, if a customer places an
order and then calls back immediately to
| | 00:08 | cancel it, you should delete the order.
| | 00:11 | Here's how you delete a record in VBA.
| | 00:13 | In this movie, we're going to
work with two database objects:
| | 00:16 | the first is the DeleteCurrent code
module, which we'll go to in a moment, and the
| | 00:20 | second is the Clients table.
| | 00:23 | So I'll double-click the Clients table and
open it, and we see that we have three clients.
| | 00:29 | I'm going to add a new
client with the name of Test.
| | 00:34 | That's what we'll use in our code later.
| | 00:36 | On the New record row in the
ClientCompany field, type Test, then press the Tab
| | 00:43 | key, and then type any number.
I'll just type the number 1 and then press Tab.
| | 00:48 | With that record in place, I'll
go over to the Navigation pane and
| | 00:52 | double-click DeleteCurrent.
| | 00:55 | The DeleteCurrent code module already
has the skeleton of the code in place.
| | 01:00 | So we have the Sub and End Sub
statements, and then we're defining the myR
| | 01:04 | variable as a recordset. And then when
we're done working with it, we're going
| | 01:08 | to close it and release it from
memory using the Set to Nothing key-phrase.
| | 01:13 | The first thing we need to do is to use
a set statement to identify the recordset
| | 01:18 | that we want to use and how we're going
to open it. So that is Set myR, and again
| | 01:25 | that variable is defined as a recordset.
Then equal, a space, and then CurrentDb.
| | 01:34 | So we were working within the current
database. Period. Then OpenRecordset, and
| | 01:40 | you should see OpenRecordset
highlighted in the auto-complete list.
| | 01:44 | If you do, press Tab;
| | 01:45 | if not, you can just type
it the rest of the way out.
| | 01:48 | Now we need to identify the
table that we're going to use,
| | 01:50 | so type a left parenthesis, and then
double quote, and Clients, double quote, and
| | 01:57 | a comma. And now we need to tell
Access how we want to open it.
| | 02:02 | In this case we need to open it as
a dynaset, or a dynamic record set.
| | 02:07 | The key word for that is DbOpenDynaset,
And again, that's all one word.
| | 02:16 | Type a right parenthesis and then press Enter.
And I'll press Enter twice to give
| | 02:22 | myself a little bit more room.
| | 02:23 | The next line of code we're going to
type in finds the first instance of Test
| | 02:29 | inside the ClientCompany field.
| | 02:31 | So we have myR period FindFirst, and then
FindFirst appears in the auto-complete
| | 02:38 | list, so press Tab, then press a space,
and now we need to identify the name of
| | 02:44 | the field and also the value we're looking for.
| | 02:47 | So type a double quote and then a left
square bracket and ClientCompany, all
| | 02:53 | one word ClientCompany.
| | 02:58 | That's the name of the field, and then a
right square bracket. Now type a space, an
| | 03:03 | equal sign, a space, and the
value Test inside of single quotes.
| | 03:08 | We're identifying it as a string
within a string so instead of using double
| | 03:11 | quotes, we're going to use single quotes.
| | 03:16 | So a 'Test'". So looking over that line and the
syntax is a little convoluted, I realize.
| | 03:24 | We have myR,FindFirst, double quote, then in square
brackets the name of the field, ClientCompany, = 'Test'". Press Enter.
| | 03:39 | Now I'm going to type in the code
to delete the record we just found,
| | 03:43 | so that is myR.Delete, and press Tab.
| | 03:49 | With all that in place, press F5 to run
the code and then press Alt+F11, and when
| | 03:55 | you do, Access indicates that
the record has been deleted.
| | 03:58 | So now if you close the Clients
table by right-clicking its tab and then
| | 04:03 | clicking Close and then double-click it in
the Navigation pane in the Table section,
| | 04:09 | you'll see that the record is gone.
| | 04:10 | I would like to mention two
things before I end this movie.
| | 04:13 | The first is that you can use
transactions, which I describe in another movie in
| | 04:17 | this chapter, to verify the delete action.
| | 04:20 | Verifying that a user wants to delete
the selected information helps prevent
| | 04:24 | them from doing so accidentally.
| | 04:26 | Second, you should never delete any
information you could conceivably use again.
| | 04:30 | For example, if an employee moves to
another company, don't delete their record.
| | 04:36 | You might need to look up who made a
sale to a client and if you delete that
| | 04:39 | person's record, you'll have no idea who did it.
| | 04:42 | Always err on the side of keeping more
data than you need, especially now that
| | 04:46 | the disk space is so cheap.
| | Collapse this transcript |
| Finding records within a table| 00:00 | Whenever you call a company or place an
order over a web site, you usually log
| | 00:04 | into the system using some sort of user ID.
| | 00:07 | The customer service rep or computer
program then uses that ID to look up
| | 00:11 | your customer records.
| | 00:13 | You can provide that same functionality in
Access by using the Find family of methods.
| | 00:18 | For this movie, we are going to
use two objects within the database.
| | 00:22 | The first is the Clients Table, which I
will open, and the current state of this
| | 00:27 | table has all of the ClientCompany
names starting with the letter B--that will
| | 00:31 | become important throughout the lesson.
| | 00:33 | Now I will press Ctrl+W to close the
table, and I will then double-click the
| | 00:39 | FindRecords code module, also in
the Navigation pane, to open it.
| | 00:43 | I have already created
the skeleton of this code.
| | 00:48 | So we have the Sub and End Sub statements.
| | 00:51 | Then I have created a myR
variable and defined it as a recordset.
| | 00:56 | And then we're going to close the recordset
when we're done with it and set it to nothing.
| | 01:01 | And the four comment lines I have
there are the four methods that you can use
| | 01:05 | in a Find statement:
| | 01:06 | FindFirst, FindLast, FindNext, and FindPrevious.
| | 01:10 | We're going to use two of those in this movie.
| | 01:15 | The first thing we need to do in our
code is to identify the recordset that
| | 01:19 | we're going to work with,
and we'll assign that to the myR variable.
| | 01:24 | So that is Set myR, then an
equal sign, then CurrentDb, period.
| | 01:37 | And then we're going to
use the OpenRecordset method.
| | 01:40 | So Open, and then Tab if you see
OpenRecordset in the autocomplete list;
| | 01:45 | if not, just go ahead and type it out.
| | 01:48 | Then a left parenthesis, and then the name of
the table we're going to work with in quotes.
| | 01:54 | So double quote, then Clients, double
quote, then a comma, and now we need to
| | 02:02 | tell Access how to open the table.
| | 02:05 | In this case, we want it to be a
dynamic recordset, or a dynaset.
| | 02:09 | So it's dbOpenDynaset and then a
right parenthesis, and press Enter.
| | 02:20 | Now we need to use the Find method of our
choice, and in this case it will be FindFirst.
| | 02:25 | So we are going to look
in the myR Recordset, myR.
| | 02:32 | and now the FindFirst method.
| | 02:37 | And now here is where the syntax
gets a little convoluted, so just follow
| | 02:41 | along as best you can.
| | 02:42 | I'll take a moment to review when we're done.
| | 02:44 | So after you've typed FindFirst, press
the spacebar, now a double quote, and the
| | 02:51 | name of the field that you want
to look in, in square brackets.
| | 02:55 | So, left square bracket, and ClientCompany--
all one word, no spaces--
| | 03:04 | right square bracket, then a space,
and an equal sign, space,
| | 03:09 | and now single quotes, because we're
about to type in a string inside of a
| | 03:12 | larger string. And the name
of the company is Buena Suerta.
| | 03:21 | Then a single quote to end the search
string and a double quote to end the
| | 03:26 | criteria for the FindFirst method,
and then go ahead and press Enter.
| | 03:31 | The next line displays a message box with
the SalesPerson assigned to that company,
| | 03:36 | so that's MsgBox, space myR, then an
exclamation point, then a left square
| | 03:45 | bracket, and the name of the field
that contains the value, and that is
| | 03:48 | ClientSalesPerson--again, all one word--and a
right square bracket, and then press Return.
| | 04:01 | So we are setting the myR
variable to the Clients table, opening it as a
| | 04:04 | dynamic table or dynaset.
| | 04:07 | We're looking for the first
occurrence of Buena Suerta within the
| | 04:09 | ClientCompany field, and then
we're going to display that company's
| | 04:13 | ClientSalesPerson number in the message box.
| | 04:16 | When I press F5, we see the
number 2. I can click OK.
| | 04:21 | That is the correct value.
| | 04:22 | Now, one thing that you can use with
the FindFirst statement is a wildcard.
| | 04:27 | So for example, if you wanted to use
the asterisk, you can backspace over all
| | 04:34 | but the first letter of Buena Suerta,
so we just have a B, and then type an
| | 04:38 | asterisk. And the wildcard of the
asterisk refers to any collection of
| | 04:42 | characters, including no characters.
| | 04:44 | So basically, we're looking for the
first occurrence of anything that starts
| | 04:48 | with the letter B.
So remember, last time we did this
| | 04:51 | we found the company Buena
Suerta with a Sales Person ID of 2.
| | 04:55 | Now when we press F5, we get a value of
3, because the first row in the table,
| | 05:00 | if we remember--and I do--has a
Sales Person value of 3. Now click OK.
| | 05:05 | Now, let's suppose that we want to
look for the last occurrence of a
| | 05:10 | ClientCompany name that starts with B.
For that I'd backspace over First in the
| | 05:16 | FindFirst method name, and then type
in Last, and then press the down arrow.
| | 05:22 | So now we're looking for the last
occurrence of a company that starts with B and
| | 05:27 | then display the ClientSalesPerson's number.
| | 05:30 | Now when I press F5, we get the number 4 and,
again, that refers to the last row in the
| | 05:35 | table, and I click OK.
| | 05:37 | So far all of the cases I've shown you
are what you do if you do find a match,
| | 05:42 | but what you do if you don't find a match?
| | 05:45 | You can handle those situations by
using an If-Then statement and the NoMatch
| | 05:50 | property of the Recordset object.
| | 05:53 | So I'm going to click after the
MsgBox statement and then press Enter twice
| | 05:58 | to move down to a new blank line of code.
And I am going to type in an If-Then statement.
| | 06:03 | I will start by typing in If, space,
myR.NoMatch, then space, and equal sign,
| | 06:12 | and then True, then a space, and the word Then.
| | 06:19 | So in other words, what I am saying is
if you don't find a match within that
| | 06:23 | recordset, then do this.
| | 06:25 | And I'll press Enter twice, press the
Tab key to offset my code, and what we
| | 06:31 | want to do if we don't find a match is
type MsgBox, space, double quote, No match
| | 06:40 | was found, or any message of
your choice, and then a double quote.
| | 06:45 | Then I'll press Enter twice,
and now comes the Else statement.
| | 06:49 | So Else, down arrow, and here we're going
to use the MsgBox statement from earlier.
| | 06:56 | So I will select the statement MsgBox
myR!ClientSalesPerson and then press
| | 07:03 | Ctrl+X to cut it, move it down below
the Else statement, give myself some room,
| | 07:09 | and press Ctrl+V. And I'll just tab over
a couple of times, moving the cursor to
| | 07:14 | the left, and press Tab to move it in.
| | 07:16 | And now that I am done, press Enter
twice, Backspace a couple of times to get
| | 07:22 | over to the left margin, and End If.
| | 07:25 | So what this code does is
it asks, is there a match?
| | 07:29 | In other words, does the criteria in the
FindFirst or FindLast method return a value?
| | 07:34 | If it does, then display the message box;
| | 07:36 | if it doesn't, then say No match was found.
| | 07:39 | That's the way it should work,
but the fact that I am using a wildcard will
| | 07:42 | actually throw this off.
| | 07:43 | So, for example, we know based on
experience that there are values that start
| | 07:47 | with the letter B inside of this table,
but if I press F5 to run the code,
| | 07:53 | Access displays a message
saying, "No match was found."
| | 07:56 | And the reason that happens is
because we are using a wildcard.
| | 08:00 | If I change the value from the B* to
Buena Suerta, then a down arrow key, and
| | 08:10 | then press F5, Access
displays the correct value of 2.
| | 08:15 | Then when I click OK, the Message Box goes away.
| | 08:18 | So the lesson you should take away from
this is that if you're going to do error
| | 08:21 | checking--in other words, checking to
see if there is a match or not--then you
| | 08:26 | should not use wildcards;
| | 08:27 | you should only search for
literal strings, such as Buena Suerta.
| | Collapse this transcript |
| Moving within a Recordset| 00:00 | Tables usually contain a lot of data.
| | 00:02 | If you want to move within an Access
table while you have it opened in VBA, you
| | 00:06 | can do so using the code that
implements the Move family of functions.
| | 00:11 | In this movie, we're going to use
two objects within the database.
| | 00:15 | The first is the Products table, and I
will double-click that in the Navigation
| | 00:20 | pane to take a look at it.
| | 00:22 | We have six records:
| | 00:23 | Garlic, Basil, Jalapeno,
Lemon, Mandarin, and Rosemary.
| | 00:27 | There is no need to memorize them,
but if you could remember that Garlic is the
| | 00:30 | first and Rosemary is the last, then
you'll have a better idea for how we're
| | 00:35 | moving within the table.
| | 00:36 | I'll press Ctrl+W to close it,
and then I will double-click the MoveWithin
| | 00:42 | code module over in the Navigation
pane to open it. And I've already created
| | 00:48 | the skeleton for this code module, have
the Sub statement, End Sub, and then I
| | 00:53 | also create a recordset variable myR.
And then when we're done working with
| | 00:58 | it, Access will close it and then set that
variable to nothing to free up system memory.
| | 01:04 | Now, I am going to click two lines
below the dimension statement and
| | 01:08 | begin entering the code.
| | 01:09 | The first thing we need to do is to
open the Products table and open it so that
| | 01:15 | we can work with it.
| | 01:16 | In other words, it needs to be dynamic.
| | 01:18 | For this we'll use a Set statement,
Set myR, then space, equal, and a space,
| | 01:26 | then type CurrentDb.OpenRecordset.
| | 01:35 | As soon as you type an O,
OpenRecordset appears in the auto-complete list,
| | 01:40 | so go ahead and press Tab to accept it.
| | 01:43 | Then type a left parenthesis, double
quote, and the name of the table, and that
| | 01:48 | is "Products" and then a
double quote and a comma.
| | 01:55 | Now we need to tell Access how to open
the table. In other words, do we open it
| | 02:00 | as a recordset, which we can't
manipulate, or do we open it as a dynaset or
| | 02:05 | dynamic recordset, which we can?
And of course we want the latter.
| | 02:09 | We indicate that using the keyword
dbOpenDynaset and then a right parenthesis.
| | 02:19 | Now I am going to press Enter three
times to give myself some room to work,
| | 02:24 | because I am going to add a
line of code after I am done.
| | 02:27 | The last thing I want to do is to
display a message box with the name of the
| | 02:30 | product, in other words, the value from
the Product name field. And the code for
| | 02:35 | that is message box, MsgBox myR!
| | 02:43 | then a left square bracket and
ProductName, and again, no spaces.
| | 02:52 | That's all one word and then a
right square bracket and press Enter.
| | 02:56 | Now what is the code that I want to use to
move within the database table or the recordset?
| | 03:03 | That depends. There are four different Move
statements that you can use and I will type them
| | 03:07 | in here as comments.
| | 03:09 | So single quote. The first one is
MoveFirst; the second is move last, so
| | 03:17 | 'MoveLast; third is move next, so
'MoveNext; and finally, move previous, so
| | 03:27 | 'MovePrevious and down arrow.
And again, these are comments, so they won't
| | 03:33 | become part of your code and if you
haven't already typed them, don't worry about it.
| | 03:36 | You don't need to.
| | 03:37 | I am just putting them in so that
you'll have them to look at as I am
| | 03:40 | discussing what I'll be
using here in this routine.
| | 03:44 | Now, I am going to click above the MsgBox line.
| | 03:48 | But before I type in any additional
code, I am going to press F5 to run this
| | 03:53 | code module so you can see what happens.
| | 03:54 | When I press F5, we get Garlic, which is the
first record's product name value. So I'll click OK.
| | 04:02 | So what happens is that when Access
opens the Products table as a dynaset, the
| | 04:08 | pointer is set to the first record.
| | 04:10 | Now, let's see what happens when I
type in myR.MoveNext and press Enter.
| | 04:20 | So in other words, what's happening is
that we're opening the Products table as
| | 04:24 | a dynaset, but now, instead of just
displaying the current record, which would
| | 04:28 | be the first record, we're
moving to the next record.
| | 04:31 | So when I press F5 this time,
instead of seeing Garlic, we see Basil.
| | 04:34 | I'll click OK to clear it.
| | 04:37 | Now, let's say that we move last.
| | 04:40 | So I will edit the MoveNext method to
MoveLast and press the down arrow key.
| | 04:47 | Now, when I press F5, we see
Rosemary, which is the value in the last row.
| | 04:53 | The Move family of functions
implements the functionality of the record
| | 04:57 | navigation buttons at the bottom-left corner
of an Access table, query, report, or a form.
| | 05:02 | If you want that functionality without
displaying the table, such as by using
| | 05:06 | message boxes, as in this movie, you can
use these functions to make that happen.
| | Collapse this transcript |
| Counting the records in a Recordset| 00:00 | One of the most basic questions you can
ask about a table, or other recordset, is
| | 00:04 | how many records there are within that object.
| | 00:06 | In Access, you can find that
count in the OpenRecordset object's RecordCount property.
| | 00:12 | We use two different objects from the database
in this movie. The first is the Clients table.
| | 00:18 | I'll double-click to show you that we
have three rows, and I'll press Ctrl+W to
| | 00:22 | close it. And now I'll double-click the
CountRecords code module to open that
| | 00:28 | code module within the VBA Editor.
| | 00:30 | I've already created the skeleton
of the code. I have the Sub and End Sub
| | 00:35 | statements, and then I have defined
the variable myR As a recordset.
| | 00:41 | And then at the end, after we're done
working with that recordset, we close it and
| | 00:45 | set the variable to Nothing to free
up the memory and system resources.
| | 00:50 | To work with the Clients table, we
need to assign it as a recordset to the
| | 00:54 | recordset variable myR.
| | 00:56 | To do that, we create a set
statement, so Set myR = CurrentDb.
| | 01:09 | and then OpenRecordset. So type an O
and you'll see the OpenRecordset method
| | 01:15 | highlighted in the auto-
complete list. Press Tab.
| | 01:18 | Now we need to identify the table, so
type a left parenthesis, double quote, and
| | 01:24 | then the name of the table, which is
Clients, double quote, and then a right
| | 01:30 | parenthesis, and press Enter.
| | 01:33 | Now we're going to display the value,
| | 01:35 | so just type message box, MsgBox myR.
RecordCount. And RecordCount should
| | 01:47 | then appear in the auto-complete list, so
press Tab to accept it and then press Enter.
| | 01:52 | With those values in place, when we run
the code, by pressing F5, we get a value
| | 01:57 | of 3, which as we remember, are the number
of clients in that table. I'll click OK.
| | 02:03 | There is one mistake that's easy to
make when you use RecordCount, and that is
| | 02:07 | instead of opening the table as a
regular recordset, to open it as dynaset.
| | 02:12 | So let me show you what
happens when you do that.
| | 02:15 | So go up to the Set statement with the
OpenRecordset and then after Clients,
| | 02:20 | between the right parenthesis and the
double quote, type a comma, and then type
| | 02:25 | dbOpenDynaset, and press the down arrow key.
| | 02:33 | What you've done is change that line
of code so that instead of opening the
| | 02:37 | table as a regular recordset, Access is
now opening it as a dynaset, or dynamic
| | 02:43 | recordset, which can be edited.
| | 02:45 | Now remember, there are
three records in the table.
| | 02:48 | If I press F5 to run the
code, the answer comes back 1.
| | 02:52 | That's because the dynaset is dynamic,
and instead of looking at the entire
| | 02:57 | table, it only looks at the current selection,
and that current selection has one record in it.
| | 03:02 | I'll click OK to get rid of the box.
| | 03:05 | Now, if I were to delete dbOpenDynaset, I
just backspacing over it, and the comma,
| | 03:11 | pressing the down arrow key and then
pressing F5, everything is okay, and it goes
| | 03:16 | back to 3, which is the correct answer.
| | 03:19 | The RecordCount method summarizes
your data by determining how many records
| | 03:23 | appear in a table or other recordset.
| | 03:25 | That's useful information, particularly
if you want to know how many orders you
| | 03:29 | had in a day or how many new
clients you added in a month.
| | 03:32 | If you receive an answer you don't
expect, particularly if that answer is one,
| | 03:36 | go into your code and make sure that
you opened your recordset as a regular
| | 03:41 | recordset and not a dynaset.
| | Collapse this transcript |
|
|
8. Manipulating a Database Using the Application ObjectSummarizing values in a table field| 00:00 | You can use VBA code to view,
edit, or delete individual rows,
| | 00:04 | but you can also use a VBA to summarize all
of the values in a field in one calculation.
| | 00:09 | For example, you could find
the average or sum of the values.
| | 00:13 | In this movie, I will show you how
to create the code to do just that.
| | 00:16 | We will use two objects
inside the sample database.
| | 00:20 | The first is the Orders table, and I
will double-click that in the Navigation
| | 00:24 | pane to open it. And we can just take
a look at the data that's within it.
| | 00:28 | It's a series of ten orders, listed
by CustomerID and the OrderTotal.
| | 00:33 | So that is the data that we'll be working with.
| | 00:36 | I'll press Ctrl+W to close the table.
And now in the Modules section of the
| | 00:40 | Navigation pane, double-click Summarize
to display the Summarize code module.
| | 00:46 | I've already created the
skeleton of this routine.
| | 00:49 | I have the Sub and End Sub statements,
and then I also have a series of commands
| | 00:54 | that you can use the summarize data.
| | 00:55 | I have DCount, which finds the
count, the number of rows in the table.
| | 01:00 | Next is the DMax. That finds the
largest value in a given field.
| | 01:05 | Next is that DMin, which finds the
minimum value in a field; Sum, which finds the
| | 01:11 | sum of all the values in a field;
| | 01:13 | then DVar which is short for variance,
so it finds the variance based on a
| | 01:18 | sample of the data in a field; and then
finally DVarP refines the variance for
| | 01:25 | the entire population, or the entire dataset.
| | 01:28 | There's one other man that I haven't put
in that list, and that is DAverage, DAvg,
| | 01:34 | and that's what we'll use in the code now.
| | 01:36 | My goal is to create a message box
that displays the average of all of the
| | 01:40 | orders found in the Order table.
| | 01:43 | That command is MsgBox DAvg
("OrderTotal", "Orders"), and press the down arrow
| | 02:17 | to accept the input.
| | 02:18 | So I'll just look over the code.
Everything looks good. And when I press F5,
| | 02:24 | Access displays a dialog box, or a message box,
indicating that the average order value
| | 02:29 | is a little bit over $66.67.
| | 02:33 | Click OK to dismiss the box.
| | 02:36 | Let's just go ahead and
substitute in one of the other commands.
| | 02:39 | So let's say that I want to find the
maximum value, the maximum, or largest,
| | 02:44 | order from that list.
| | 02:46 | To do that, I'll go back up to the
MsgBox code line, backspace over the
| | 02:51 | characters Avg in the DAvg command,
and then type in Max. Then press the down arrow.
| | 02:59 | When I press F5 to run the code, Access
displays a message box with the largest
| | 03:04 | order total, which was $219.95.
| | 03:09 | Just click OK to dismiss the message box.
| | 03:11 | The D family of functions lets
you summarize your data quickly.
| | 03:15 | If you want your users to summarize
the table's values without viewing
| | 03:18 | individual rows, these
functions are the ones you want to use.
| | Collapse this transcript |
| Finding the first or last value in a table field| 00:00 | When you enter data into a table
sequentially, you can use the DFirst and DLast
| | 00:05 | functions to find the oldest
value or the newest value in a field.
| | 00:10 | To demonstrate those techniques, I am going
to use two objects from the sample database.
| | 00:14 | The first is the Orders table, and I'll just
double-click that to show how it's structured.
| | 00:20 | The Orders table has three fields:
| | 00:22 | OrderID, CustomerID, and OrderTotal.
| | 00:25 | Now I'll press Ctrl+W to close it.
And now in the Modules section of the
| | 00:30 | Navigation pane, I'll double-click First or Last.
| | 00:34 | That opens the First or Last code module.
And I've already created the basics for
| | 00:39 | this module, have the Sub and End Sub
statements, and then also as comments, I
| | 00:44 | have the commands that we'll use:
| | 00:46 | the first one is DFirst and the second is DLast.
| | 00:50 | So the idea, once again, is that I want
to display either the first or last value
| | 00:54 | from a field in the Orders table.
| | 00:57 | To do that, I'll create a message box,
MsgBox space, and then we'll look for the
| | 01:04 | first value in the CustomerID field.
| | 01:07 | So I'll type DFirst, left
parenthesis, then a double quote. Then we
| | 01:14 | are going to type in the name of the field.
| | 01:17 | So the field name, if we want to
see the first customer, is CustomerID,
| | 01:24 | double quote to close then string,
then a comma, and now the
| | 01:29 | table in which that field occurs.
| | 01:32 | So double quote and then Orders,
double quote, right parenthesis
| | 01:37 | DFirst, and then press the Down
arrow to accept that line of code.
| | 01:41 | So I have a message box that will
display the first value from the CustomerID
| | 01:46 | field of the Orders table.
| | 01:48 | When I press F5 to run the code, Access
displays a message box that contains the
| | 01:53 | CustomerID from the first order in the table.
| | 01:57 | Then click OK to clear the message box
and return to the Visual Basic Editor.
| | 02:01 | Now I am going to change
the command a little bit.
| | 02:03 | I am going to change it so that
instead of finding the first record,
| | 02:07 | we'll go to the last, and that is DLast.
| | 02:11 | So I am deleting F-i-r-s-t from the
command and making it look like D-L-a-s-t.
| | 02:18 | And then instead of the CustomerID
field, I'll backspace over the CustomerID
| | 02:23 | text, and then type OrderTotal,
O-r-d-e-r-T-o-t-a-l, and again, no spaces.
| | 02:30 | Then press the down arrow to accept
the input and when I press F5 to run the
| | 02:34 | code, Access displays the number 42,
which was the total for the last order.
| | 02:40 | And clicking OK gets rid of the message box.
| | 02:44 | The DFirst and DLast functions give you
a straightforward way to find the first
| | 02:48 | and last values in a field.
| | 02:49 | Rather than defining an object variable
and opening the table as a recordset,
| | 02:54 | you can use a single line of
code to find the values you want.
| | Collapse this transcript |
| Looking up a value in a table field| 00:00 | One database design best practice
is to have a field that contains a
| | 00:04 | unique value in each row.
| | 00:06 | For example, every customer would
have a unique customer ID number.
| | 00:10 | Having a unique value in each row of a
table lets you look up other values
| | 00:14 | based on that number.
| | 00:15 | For example, you could use a customer
ID number to look up their last order.
| | 00:20 | In this movie, I'll show you how to
use the DLookup method to find a value in
| | 00:24 | one field based on the value in another field.
| | 00:27 | We're going to use two objects
from the database in this movie.
| | 00:31 | The first is the Orders table.
| | 00:33 | So I'll just double-click it in the
Navigation pane, and we'll see that we have
| | 00:37 | the OrderID, CustomerID, and OrderTotal.
| | 00:41 | The DLookup command that I'm going to
create later will concentrate on CustomerID 103.
| | 00:47 | So if I create the code correctly, we
should see that the first order in this
| | 00:52 | table, from customer 103, which
is the only order, was worth $82.
| | 00:57 | I'll press Ctrl+W to close the table,
and now in the Modules section of the
| | 01:02 | Navigation pane, I'll double-click
Look Up Value to open that code module.
| | 01:09 | And I've already created the skeleton
of this code, and those are simply the Sub
| | 01:14 | and End Sub statements.
| | 01:16 | And we can perform this function
using a single line of code. And I want to
| | 01:21 | display the value in a message box,
| | 01:23 | so I'll type MsgBox, a space. And then
the function we're going to use is DLookup.
| | 01:30 | And that is D-L-o-o-k-u-p, space,
and then a left parenthesis.
| | 01:35 | Now we need to provide three different items.
| | 01:38 | The first is the field, and this is the
field that returns the value, not the
| | 01:43 | field we're looking in.
| | 01:44 | The second is the name of the table,
and the third is the criteria. And I'll give
| | 01:49 | you more information about
the criteria when we get there.
| | 01:51 | So first is the field, and this is the
field that we want to display the value from.
| | 01:56 | So I'll type a double quote,
and the field is OrderTotal;
| | 01:59 | O-r-d-e-r-T-o-t-a-l, then a double quote.
| | 02:06 | Now a comma, and we identify the domain.
| | 02:09 | In this case, that is the table
named Orders, so it's a double quote, then
| | 02:14 | O-r-d-e-r-s, double quote,
and a comma, and now the criteria.
| | 02:20 | The criteria has two parts:
| | 02:23 | the field we're looking in and then the
target value defined within that field.
| | 02:27 | So first, I'll type double quote and then
the name of the field we're looking in,
| | 02:32 | and that field is CustomerID.
| | 02:34 | So C-u-s-t-o-m-e-r-I-D, then an
equals sign, and then the target value.
| | 02:42 | And in this case, the
Customer ID we're looking for is 103.
| | 02:44 | 103, double quote, right parenthesis to
close everything out, and then I'm going
| | 02:52 | to press the down arrow
key to move off of that line.
| | 02:56 | So the DLookup will look in the Orders table,
find the first row that contains the
| | 03:02 | CustomerID of 103,
and then return the order total.
| | 03:06 | When I press F5 to run the code, we see
that the value is 82, and that was what
| | 03:11 | we saw in the table at
the beginning of the movie.
| | 03:14 | The DLookup method finds a row that
contains a target value and then returns a
| | 03:18 | value from the same table row.
| | 03:20 | If your table contains multiple
instances of a value, such as a customer ID and a
| | 03:24 | list of orders, then it returns a
value from the row that contains the first
| | 03:28 | occurrence of that value.
| | Collapse this transcript |
| Create a progress bar using the SysCmd object| 00:00 | Earlier in this course I showed you
how to indicate a process was underway
| | 00:04 | by displaying the cursor as an
hourglass and to indicate a process was done
| | 00:09 | by sounding a beep.
| | 00:10 | One other common way to show something is
happening is by displaying a progress bar.
| | 00:15 | In Access VBA, this item is called a
meter and you can use a series of VBA
| | 00:20 | commands to display, update, and remove a meter.
| | 00:24 | For this exercise, we will create
a form based of the Orders table.
| | 00:29 | So in the Navigation pane, I'll click the
Orders table, click Create, and then click Form.
| | 00:36 | With the form in place, I'll press
Ctrl+S to save it, and I'll just leave the
| | 00:41 | default name of Orders and click OK.
| | 00:45 | Now with the orders form in place,
I'll right-click the tab in the database
| | 00:50 | window of the form object
and then click Design View.
| | 00:54 | Now with the form in Design view, I'll
right-click anywhere in the body of the
| | 00:58 | form and then from the shortcut
menu that appears, click Build Event.
| | 01:02 | Then in the Choose Builder dialog box,
click Code Builder and then click OK.
| | 01:07 | The Visual Basic Editor appears,
and there is an event procedure for clicking in
| | 01:14 | the Detail area of the form.
| | 01:16 | That's actually not the
event that I want to work with.
| | 01:19 | So what I'll do is click the object down
arrow and then from the list of objects
| | 01:25 | that are available for me
to work with, I'll click Form.
| | 01:29 | Access creates a new event that will
happen when the form loads, and it leaves
| | 01:34 | the Detail_Click event code up top,
but we don't have to worry about that.
| | 01:39 | There is nothing here between the Sub and
End Sub statements, so nothing will happen.
| | 01:44 | We don't need to worry about that.
| | 01:46 | So let's go back to the Form_Load event.
| | 01:49 | Loading a form is when the form gets
its data from its underlying data source,
| | 01:54 | either a table or a query.
| | 01:55 | So what we want to do in this case is
to initiate our meter for the Progress
| | 02:00 | bar, then update it.
| | 02:02 | We will update it again,
and then we'll get rid of it.
| | 02:05 | So here's the code to create the meter.
And I'll just press Enter twice and then
| | 02:10 | the up arrow once to give
us a little bit more room.
| | 02:13 | The first command is Sys
command, and that is spelled SysCmd.
| | 02:18 | So Sys short for system, Cmd short for Command.
| | 02:22 | Then a space, and now we need to
select our action, and that is acSysCmd.
| | 02:30 | No surprises so far.
And now we type in InitMeter.
| | 02:37 | We do need to give a
little bit more information.
| | 02:39 | I'll type a comma, and now we can display
a message as the Progress bar is running.
| | 02:44 | We need to put that in quotes
because it's a string, and I will just type
| | 02:47 | in "Opening the Form".
| | 02:58 | Now we type a comma, and we can put in the
number that the progress bar will count to.
| | 03:04 | That's usually 100. When you think of
something downloading, it will say 100%
| | 03:08 | done, or 50% done, that sort of thing.
| | 03:11 | So in this case, we will just type in
the number 100 and then press Enter.
| | 03:16 | So we've just initiated the meter.
| | 03:18 | Next, we need to update the meter,
and for that, we type SysCmd, same as the
| | 03:23 | line before, acSysCmd. And now we need
to use the action to update the meter,
| | 03:31 | and that is UpdateMeter.
| | 03:37 | Now we can type a comma and tell
Access how much to update the meter.
| | 03:42 | In this case, I want to update
it to 50 and then press Enter.
| | 03:47 | Now if I were to type in another
UpdateMeter command, then the updates would go
| | 03:52 | by too quickly because on modern
computers these sorts of commands are
| | 03:55 | implemented almost immediately.
| | 03:57 | So I am going to put in a message box,
just to pause the action for a moment so
| | 04:01 | we can see what's happening.
So I will just type in MsgBox "50% done."
| | 04:12 | In this case, it's 50% of nothing,
but it will give us a chance to look at the
| | 04:16 | progress bar as it's going along.
| | 04:18 | Now press Enter, and let's select and
copy the second Sys Command line, so the
| | 04:25 | one that sets the meter to 50.
| | 04:27 | So select it using the mouse, then press
Ctrl+C, click below the MsgBox, and then
| | 04:33 | press Ctrl+V, and then edit the line
so that the meter is updated to 100.
| | 04:39 | So it should be SysCmd
acSysCmdUpdateMeter, 100. Then press Enter, and let's
| | 04:47 | create another MsgBox for 100% done.
| | 04:50 | So MsgBox "100% done." and press Enter.
| | 05:00 | Now finally, we need to get rid of the
meter, and to do that we use the Remove
| | 05:06 | Meter System command.
| | 05:07 | That code is SysCmd
acSysCmdRemoveMeter, and again, no spaces.
| | 05:19 | Go ahead and press tab to accept that and
the down arrow to move to the next line.
| | 05:24 | Now whenever we open the form, we will
see a message saying that the form is
| | 05:28 | opening, get a progress bar of 50%, a
message box. Then when we clear the message box
| | 05:33 | it will go to 100%, we will get another
message box, and then Access will remove the meter.
| | 05:38 | So let's go ahead and press Ctrl+S to
save our work and press Alt+F11 to go back
| | 05:46 | to the main database window.
| | 05:48 | Now with the Orders form
displayed, press Ctrl+W to close it.
| | 05:53 | Now in the Navigation pane, double-click
the Orders form and when we do, we see a
| | 05:59 | message box saying 50% done. And in the
bottom-right corner of the program window
| | 06:04 | we see the text "Opening the form," which
was the caption for the message bar, and
| | 06:09 | also the green progress bar at 50%.
| | 06:12 | When I click OK, we see the message box
saying it's 100% done and again, in the
| | 06:17 | bottom-right corner, we see the text
"Opening the form" and a progress bar at 100%.
| | 06:23 | And then when we click OK, Access
clears the message box and also the progress
| | 06:29 | bar and opens the form.
| | 06:31 | Meters give users a sense of progress,
which removes the uncertainty of watching
| | 06:35 | a blank screen and wondering how
much longer a process has to run.
| | Collapse this transcript |
|
|
9. Controlling Forms and Reports Using the Me ObjectAllowing or disallowing additions, deletions, and edits| 00:00 | When you create VBA code for a form
or report, you can use the Me object as a
| | 00:05 | shortcut to refer to that object.
| | 00:07 | If you're working with a form, for
example, you can use the Me keyword
| | 00:11 | properties to allow or prohibit additions,
edits, and deletions using that form.
| | 00:16 | In this movie, we are going to add code
to the Orders form, so in the Navigation
| | 00:21 | pane I'll right-click the Orders
Form and then click Design View.
| | 00:25 | Now in Design view I can right-click
anywhere on the form and then in the
| | 00:31 | Shortcut menu that appears, click Build Event.
| | 00:34 | Then in the Choose Builder dialog box,
click Code Builder, and then click OK to
| | 00:39 | open the Visual Basic Editor.
| | 00:41 | When the Visual Basic Editor appears,
you will get an event code, and that would
| | 00:46 | be for, in this case, clicking in
the detail section of the form.
| | 00:49 | That's actually not the
event that we want to use,
| | 00:53 | so I am going to go up just below the
Title bar of the code module, click the
| | 00:57 | Object down arrow, and then click Form.
| | 01:01 | So in other words, I want to effect the
form as a whole. And when I do, Access
| | 01:06 | inserts another event code, or
subroutine, and this is for when the form is
| | 01:11 | loaded. And that is what I want to use.
The form loads its data, and then it runs
| | 01:17 | the code I am about to give it.
| | 01:18 | Now as I mentioned, in this movie I will
show you how to allow or disallow three
| | 01:22 | different types of actions, that is,
allowing additions, allowing deletions, and
| | 01:27 | allowing edits. And the code for
that is fairly straightforward.
| | 01:31 | I will press Enter twice and the up
arrow key once to give myself a little room
| | 01:36 | to work. And then because I am in code
related directly to a form and that is
| | 01:40 | attached to the form,
| | 01:41 | I can type Me. And then I can type in
the property or method I want to use.
| | 01:48 | And in this case, I want to set a value
for the AllowAdditions property,
| | 02:00 | then a space, then an equal sign, and I
can set it to either True or False.
| | 02:05 | For the purposes of this
illustration, I will set it to False.
| | 02:08 | So type an F and press Tab
to accept the False value.
| | 02:14 | With that in place, I can press Enter
and now I can decide whether to allow
| | 02:18 | users to delete records or not.
| | 02:21 | In almost every case, you're going
to want that to be set to False,
| | 02:24 | so that's what I will do here. I will type Me.
| | 02:26 | AllowDeletions, then space, an equal
sign, another space, and I can set it
| | 02:38 | to either True or False.
| | 02:39 | In this case, once again, I will set it to False,
| | 02:41 | so type in F and then Tab, and press Enter.
| | 02:45 | Now finally, you can choose
whether to allow edits or not.
| | 02:49 | The default value is to allow edits,
and the default value, if you don't change
| | 02:53 | them, for each of these properties is
true; in other words if you don't do
| | 02:57 | anything, then users can add,
delete, and edit records.
| | 03:01 | In this case, I will just go through
and show you what it looks like if you
| | 03:05 | want to set it to True.
| | 03:06 | So that would be Me.AllowEdits = True.
| | 03:16 | So I will type a T and then press Tab to
accept the True value and hit the down arrow key.
| | 03:21 | I am showing you how to set AllowEdits
to True because you might want to set
| | 03:26 | it to False in almost every case.
But then if some condition is met, a
| | 03:30 | condition you can measure using an If-
Then statement, you could allow edits by
| | 03:34 | setting it to True--
| | 03:35 | for example, if a particular user
logged in. Now with these options in place, I
| | 03:40 | will press Ctrl+S to save the work
and then Alt+F11 to go back to the main
| | 03:46 | Access Database window and then
press Ctrl+W to close out of the form.
| | 03:52 | Now, when I open the form--and I'll
double-click that in the Navigation pane, the
| | 03:56 | Orders form--the form appears.
| | 03:58 | Now remember, I can't add anything
because I set AllowAdditions to False.
| | 04:04 | So down here on the Record Navigation bar,
the New (blank) record button is grayed out.
| | 04:10 | So even if I click it, or click
the spot where it is, nothing happens.
| | 04:13 | So I can still move by pressing the
Next Record or the Previous Record buttons,
| | 04:18 | but I can't actually create a new record.
| | 04:21 | Users also can't delete any records entirely.
| | 04:24 | They can delete individual, because
that counts as an edit, but they can't
| | 04:28 | delete entire records.
| | 04:30 | Now, if I want to edit a value, that is allowed.
| | 04:33 | So for example, if I change the
OrderTotal to 105.95 and then press the Tab key
| | 04:40 | to move to the next record and then
click the Previous Record button, I see that
| | 04:45 | the value is 105.95, and it has been accepted.
| | 04:49 | Then if I press Ctrl+W to
close the form, it goes away.
| | 04:52 | Using the Me keywords properties lets you
control how users interact with your data.
| | 04:57 | In general, you should allow
additions most of the time, edits occasionally,
| | 05:01 | and deletions only when you're
absolutely sure you will never need any record
| | 05:05 | in the table again--
| | 05:06 | in short, almost never, if at all.
| | Collapse this transcript |
| Manipulating form filters| 00:00 | Access forms and reports
summarize large datasets,
| | 00:03 | so you should set filters to limit the
data to just what you're interested in.
| | 00:07 | In this movie, I'll show you how to
allow, disallow, and set filters using the
| | 00:12 | Me keywords properties.
| | 00:14 | For this movie, we will use the Clients
form to attach our code to, but I want to
| | 00:19 | show you the contents of the Clients
table, because that's we're going to be
| | 00:23 | basing our filters on.
| | 00:24 | So in the Navigation pane, I'll
double-click the Clients table.
| | 00:29 | This table contains 12 records,
and they're ClientIDs 101 to 112, in
| | 00:35 | sequential order, and each of the
companies are just made-up companies, and I
| | 00:39 | gave each of them a color name.
| | 00:41 | So with that information in mind,
and again there are 12 records with numbers
| | 00:45 | 101 through 112, in order, I'll press
Ctrl+W to close the table. And now I'll
| | 00:51 | right-click the Clients Form object in
the Navigation pane and click Design View.
| | 00:57 | Now in Design view I'll click anywhere
on the form, click Build Event, and then
| | 01:03 | in the Choose Builder dialog box,
click Code Builder and then click OK.
| | 01:09 | When I do, Access creates a code module
for the event of clicking in the detail
| | 01:14 | section of the form.
| | 01:15 | That's actually not the event I want to use;
| | 01:17 | I want to run this code when the form is loaded.
| | 01:21 | So just under the title bar, I'll click
the Object list down arrow and click Form
| | 01:27 | and when I do, I get a Form_Load event.
| | 01:30 | That is what I want to use, so I'll
press Enter twice, and the up arrow key once
| | 01:34 | to give myself a little room to work.
And now I'll type code that prevents users
| | 01:40 | from using filters on
the form. So I'll type Me.
| | 01:44 | Then the property is AllowFilters, all
one word, AllowFilters =. And we'll set to
| | 01:55 | False, turning filters off.
| | 01:57 | So F and then Tab and then a down arrow.
| | 02:01 | So this code turns the filters off.
| | 02:03 | If you don't do anything, filters are
allowed, so the value defaults to True.
| | 02:08 | Now I'll press Alt+F11 and go back to the form,
| | 02:11 | press Ctrl+W to close it, and then in
the dialog box that appears click Yes
| | 02:17 | to save your changes.
| | 02:19 | You've just save the code
that is attached to the form.
| | 02:22 | Now back in the Navigation pane, double-
click the Clients Form to open it, and
| | 02:26 | when you do, even though you didn't see it,
Access ran the code that prevents filters.
| | 02:31 | Now if I click on the Home tab of the
Ribbon, you see that all of the filter
| | 02:36 | options in the Sort &
Filter group are grayed out.
| | 02:39 | So I can't create a filter;
| | 02:40 | I'm clicking on it now, but nothing's happening.
| | 02:43 | Then also Selection Filter, Advance Filter,
and Toggle Filter on and off are not allowed.
| | 02:49 | Now, I'll press Alt+F11 and go back,
and I'll set the AllowFilters property to
| | 02:55 | True and press Enter.
| | 02:59 | Now I'm going to show you how to use the
Filter property, and the Filter property
| | 03:04 | is used to display or to set a filter.
| | 03:08 | To do that, I'll go back to the form by
pressing Alt+F11 and then right-clicking
| | 03:14 | the Forms tab in the Database
window and clicking Design view.
| | 03:19 | I'll add a Command button that
will run the code that I want.
| | 03:23 | So I'll click in the Controls group on
the Design Contextual tab, a button, and
| | 03:29 | then draw the button in the forms header.
| | 03:31 | I'll click Cancel, because I don't want
to run any of the macros available there.
| | 03:36 | Then right-click the Command button,
click Build Event. Then in the Choose
| | 03:42 | Builder dialog box, click
Code Builder and click OK.
| | 03:45 | So now I have a click event for
Command button number 8, which was the button
| | 03:50 | I was working with.
| | 03:51 | I'll just give myself a
little space to work with.
| | 03:55 | Now when the button is clicked, I want to show
the filter, and we'll do that in a message box.
| | 03:59 | So I'll type in MsgBox Me.Filter and
then press Tab and a down arrow to clear
| | 04:11 | the super tooltip that appears.
| | 04:13 | So what this will do is when the
Command button is clicked it will open a
| | 04:17 | message box displaying
text describing the filter.
| | 04:21 | So I'll press Ctrl+S and
then Alt+F11 to go back.
| | 04:26 | Just to make things a little bit more clear,
I'll go ahead and edit the Command button.
| | 04:31 | To do that, I select the text inside of
the Command button, and I'll call it Show
| | 04:35 | Filter and press Enter.
| | 04:41 | Now, I'll press Ctrl+S to save the
form and then Ctrl+W to close it.
| | 04:46 | Now, I'll double-click the Clients Form
to open it and we see the Show Filter
| | 04:51 | button here in the header.
| | 04:52 | Now I'm going to apply a filter,
| | 04:54 | so I'll click the Home tab and then
click the Filter button, and I'll do a Number
| | 04:59 | Filter and make it Less Than.
| | 05:03 | I'll create a filter that will show any
ClientID that is less than or equal to 105.
| | 05:09 | Press Enter to accept this filter.
| | 05:12 | So the filter has been applied.
| | 05:14 | So I have ClientID 101 and we
see one of five down there,
| | 05:18 | so there is 101, 102, 103, 104, and 105.
| | 05:23 | Now if I click the Show Filter Command
button, Access displays a message box
| | 05:28 | indicating that within the Clients
table, the ClientID field must contain a
| | 05:32 | value of less than or equal to 105.
| | 05:35 | That's a great way to find out
precisely what filter is applied.
| | 05:38 | I'll go ahead and click OK, and then
I'll click Toggle Filter to get rid of the
| | 05:45 | filter and then click Filter again,
and click Clear filter from ClientID.
| | 05:50 | Now when I click the Show Filter button, it
comes up blank, because no filter is applied.
| | 05:55 | Whenever you set a filter on a form
be sure to remove the filter before
| | 05:58 | you close the form.
| | 06:00 | You won't lose any data by leaving the
filter turned on, but your users might
| | 06:04 | not realize it's there and believe
you're missing data you actually have.
| | 06:07 | If you give them a way to show the
filter, then they can make sure that nothing
| | 06:11 | is applied, or if there is,
| | 06:13 | they can know exactly what it is.
| | Collapse this transcript |
| Setting the caption and background picture| 00:00 | The basic forms and reports
access creates are just that--basic.
| | 00:04 | If you want to customize them you can use
two of the Me Objects properties to do so.
| | 00:09 | The Caption property controls the
text that appears on the form or report's
| | 00:13 | title bar, while the Picture property
contains a reference to the object's
| | 00:17 | background picture.
| | 00:18 | In this movie, I'll show you how to
use those two properties to control your
| | 00:21 | form or report's appearance.
| | 00:24 | For this exercise, we'll use the Orders report,
| | 00:26 | so I'll right-click it in the
Navigation pane and then click Design View.
| | 00:32 | Doing so opens the report in Design view.
| | 00:35 | Now I'm going to right-click anywhere
in the body of the report, then click
| | 00:39 | Build Event. Then in the Choose Builder
dialog box click, Code Builder, and then click OK.
| | 00:46 | When I do, Access creates a new code
module for the Detail_Format event, in other
| | 00:51 | words, if we were to change the format
of the detail section of the report.
| | 00:55 | That's not actually the event
that I want to trigger this code.
| | 00:59 | Instead, I want this code to
run when the report is opened.
| | 01:03 | So I'm going to go up to the Object
list, which is just below the title bar,
| | 01:08 | click the down arrow, and then click Report.
| | 01:12 | Report is the object from which I want
to choose my event. And the event that
| | 01:16 | appears by default is the Report_Open
event, and that is the one I want to use.
| | 01:21 | Now I'll press Enter twice and the up
arrow key once to give myself a little
| | 01:25 | bit of room to work.
| | 01:27 | Now I'm going to type in the code that will
set a value for the caption, and that is Me.
| | 01:32 | and then the Caption property,
Caption =, then double quotes, and whatever you
| | 01:43 | want to appear on the
caption or appear on the title bar.
| | 01:46 | In this case, I want that to
be orders in the last year,
| | 01:50 | so it would be Orders in the Last
Year and then double quotes again.
| | 02:01 | I'll press Ctrl+S to save my work and
then press Alt+F11 to go back to the
| | 02:06 | main database window.
| | 02:08 | Now with the Order Report still there,
I'll press Ctrl+W to close it. And now in
| | 02:12 | the Navigation pane, I'm going to
double-click Orders Report to open it.
| | 02:17 | When I do, the code that I just assigned
to the Report_Open event should run, and
| | 02:23 | we should see the new value on the title bar.
| | 02:25 | So I'm going to double-click Orders Report,
and there we see it, Orders in the Last Year.
| | 02:31 | The other change that I'd like show you
is how to add a background image, and the
| | 02:35 | background image uses the Picture property.
| | 02:38 | I'm going to right-click the tab for
the Report and then click Design View.
| | 02:45 | Doing so opens the report in Design view.
| | 02:47 | Now I'll press Alt+F11 to go back to the
Report_Open event module and then press Enter.
| | 02:55 | The code to set a background image for
a form or a report is Me.Picture =, then
| | 03:03 | double quotes, and then you're going
to need the full path, or directory file
| | 03:10 | structure, that leads to the image
you want to appear in the background.
| | 03:14 | In this case, I need to go
to my Exercise Files folder,
| | 03:18 | so I'm going to press Alt+Tab
until my desktop is displayed, and then
| | 03:22 | double-click Exercise Files, and then
double-click Chapter09. And the file that I
| | 03:27 | want to use is Confidential.png.
| | 03:31 | But I can't just type it in,
and obviously I don't see the entire path
| | 03:35 | anywhere on the screen--
| | 03:36 | it's not easy to get at.
| | 03:38 | But what you can do is right-click the
image that you want to set the link to
| | 03:43 | and then in the shortcut menu, click Properties.
| | 03:46 | In the Properties dialog box, you will see a
Location value, and it has the path to your file.
| | 03:52 | Notice that the mouse pointer is a cursor.
| | 03:54 | It looks like an I bar at this point.
| | 03:56 | That means that you can select that text,
| | 04:01 | so Users\Curt\Desktop\Exercise Files\
Chapter09. Then press Ctrl+C to copy it,
| | 04:07 | and then Alt+Tab to go back to the VBA Module.
| | 04:12 | Then press Ctrl+V to put in the path, at
least to the directory that contains the
| | 04:17 | image, and now you need to type the
name of the image with the file extension.
| | 04:21 | So that would be a backslash.
| | 04:23 | That's the slash that leans with the
bottom on the right and the top to the left.
| | 04:28 | Then this image is called Confidential.png
and then a double quote, because all
| | 04:36 | of that is a string.
| | 04:38 | Now I'll press Ctrl+S to save my work
and then press Alt+F11 to go back to
| | 04:44 | the main database window. And now I'll right-
click the Report title bar and click Close.
| | 04:50 | Now when I double-click the Orders
Report to open it, we see that we have Orders
| | 04:55 | in the Last Year and also Confidential.
| | 04:58 | Now, Confidential is somewhat obscured
by the data and the row structure within
| | 05:03 | the report, but the image is there,
and you can play around with the formatting
| | 05:06 | to make that image either stand out
more or drop more to the background.
| | 05:10 | Setting the caption or background image
for a form or report can communicate
| | 05:14 | information about the
object and make it easier to use.
| | 05:17 | Never underestimate the power of
appearances, but be sure your background image
| | 05:22 | doesn't distract the user from your data.
| | Collapse this transcript |
| Requerying and repainting forms| 00:00 | When you manage an Access database,
there is a lot going on behind the scenes.
| | 00:04 | Occasionally, you'll find that a table
feeding values to a form has changed and
| | 00:09 | your form doesn't contain the most recent data.
| | 00:11 | You can also find that Access
consistently fails to redraw a form properly.
| | 00:17 | If that's the case, you can use the Requery
and Repaint methods to correct the problem.
| | 00:22 | And during my introduction, you might
have noticed that I said these techniques
| | 00:26 | refer to forms, and that's the case.
| | 00:29 | Normally the Me object can refer to
either forms or reports, but in this case
| | 00:33 | what I'm going to show you in
this movie refers to forms only.
| | 00:37 | The first method I refer to is the
Requery method, and for this movie, we'll
| | 00:41 | use the Clients form.
| | 00:43 | So I'll go over to the Navigation pane,
right-click it, and then click Design View.
| | 00:48 | Then I'm going to right-click in the
body of the form, click Build Event, click
| | 00:54 | Code Builder, and then click OK.
| | 00:56 | And when I do, Access creates an
Event code module. And I'll just drag the
| | 01:02 | window up a bit by dragging the title
bar so that we have more room to work.
| | 01:06 | Now, in this case, this event is when
the Detail section is clicked, and that's
| | 01:10 | not the event that I want.
| | 01:11 | What I want is an event that
runs when the form activates--
| | 01:15 | in other words, when it becomes the main
object of focus inside the database window.
| | 01:20 | So I'm going to go up to the Object
list, which is just below the title bar,
| | 01:24 | click the Object down
arrow, and then click Form.
| | 01:29 | The event that appears is Form_Load.
And that's not actually the one that I want,
| | 01:32 | so I'll go over to the Event list,
and click the Procedure down arrow, and then
| | 01:38 | all the way to the top of the list is Activate.
| | 01:42 | And that is the event that I want.
| | 01:44 | So we don't have to worry
about Detail_Click or Form_Load.
| | 01:47 | There is no code in either of those events.
| | 01:49 | Instead, we're going to add our
code here in the Form_Activate event.
| | 01:54 | And that code is very straightforward.
| | 01:56 | It's just Me.Requery, and press Tab.
| | 02:02 | What the Requery method does is go
back and check the state of the table or
| | 02:08 | query that is providing values for the form.
| | 02:12 | If that's changed, then it updates the
form so that it contains the new records,
| | 02:16 | or reflects the edits and deletions.
| | 02:18 | So now with that in place, I'll press
Alt+F11 to go back to the main database
| | 02:22 | window, then right-click Clients
Form tab, and then click Form View.
| | 02:29 | And notice, in the bottom left-hand
corner, on the Record navigation bar, that
| | 02:33 | there are currently twelve records,
and that's going to change in a moment.
| | 02:36 | And it's going to change because we
are going to open the Clients Table by
| | 02:42 | double-clicking it in the Navigation pane.
| | 02:44 | Now, you notice that, as the form
indicated, there are 12 records, and the
| | 02:48 | ClientIDs go from 101-112.
| | 02:51 | Click in the first open ClientID field
and then type the value 113. Press Tab,
| | 02:58 | and then in the ClientName field,
type Orange Unlimited, and press Tab.
| | 03:09 | So now we have a new
record in the Clients Table.
| | 03:12 | So because I added code that will run
whenever the Clients form is activated--
| | 03:17 | that is, it gains the focus
within the database window--
| | 03:19 | it should update by requerying the
Clients Table to reflect the new data.
| | 03:24 | And when I click that tab, you see a
little bit of hesitation in the bottom
| | 03:29 | left-hand corner, and then on the
Record navigation bar, we see that instead of
| | 03:33 | being on record 1 of 12, we
are now on record 1 of 13.
| | 03:37 | And if I click the Last Record button,
we see that we have ClientID 113 and the
| | 03:43 | ClientName of Orange
Unlimited, which we entered earlier.
| | 03:46 | The other method I'm going to show you
I can't actually demonstrate, because I
| | 03:50 | couldn't generate an error
that would cause it to be useful.
| | 03:54 | So I'm going to right-click the Clients
Form tab in the database object window
| | 03:58 | and click Design View.
| | 04:00 | And then with the form in Design view, I'll
press Alt+F11 to move over to the code module.
| | 04:06 | Now, in the same Form_Activate event,
I'm going to press Enter, and I'm going to type
| | 04:11 | in the code Me.Repaint, and then press Tab.
| | 04:18 | What the Repaint method does is to
redraw a form on screen in case it gets
| | 04:24 | interrupted and hasn't been redrawn completely.
| | 04:26 | This often happens when you have a
long-running process, such as a query, or you
| | 04:30 | need to get data over a network.
| | 04:33 | If you find that Access doesn't draw
your form consistently, then you can use
| | 04:36 | the Repaint method to ensure that it does.
| | Collapse this transcript |
| Discovering a record source| 00:00 | Forms and reports display the contents of a
record source, that is, a table or a query.
| | 00:05 | If you display a form or report and its
contents aren't what you expect to see,
| | 00:10 | you should check which record
source it's drawing its values from.
| | 00:13 | In this movie, I'll show you how to
display the name of the database object from
| | 00:17 | which a form or report derives its values.
| | 00:20 | For this movie, we're going to use the
Territories report, and that is here in
| | 00:24 | the Navigation pane, under the Reports section,
| | 00:27 | so I'll right-click it
and then click Design View.
| | 00:30 | I want this code to run
whenever the report opens,
| | 00:34 | so I'll right-click a section of
the report, click Build Event.
| | 00:39 | Then in the Choose Builder dialog box,
click Code Builder, and then click OK.
| | 00:45 | Doing so creates a new code module
that will run when the ReportHeader is
| | 00:50 | formatted, and that might change based on
where you right-clicked inside of the report.
| | 00:55 | Now this isn't the event that I want
to use, so I'll go up to the Object List,
| | 00:59 | which is on the left side
just below the title bar,
| | 01:02 | click the Object List
down arrow, and click Report.
| | 01:06 | So I'll get a list of events that are
related to the report as a whole, and the
| | 01:11 | event that I get is Report_
Open. That is what I want to use,
| | 01:14 | so I'll press Enter twice and the up
arrow once to give myself some room.
| | 01:18 | And the property that I want to
use is the RecordSource property.
| | 01:21 | And the way I'm going to use it is to
create a message box that displays a
| | 01:25 | message, including the RecordSource.
| | 01:27 | So let's go ahead and do that.
| | 01:29 | The code is MsgBox for message box,
then double quotes, and then the text This
| | 01:35 | report gets its records from;
| | 01:38 | This, space, report,
space, gets, space, its,
| | 01:46 | space, records, space,
from, then a space, then a double
| | 01:53 | quote, then an ampersand.
| | 01:55 | The ampersand is simply used
to concatenate strings together.
| | 01:59 | So press spacebar, and then this
is where we use the Me object's
| | 02:05 | RecordSource property.
| | 02:06 | That's Me.RecordSource, then a space and then
| | 02:13 | ampersand, a space, double quotes, a period,
and double quotes again, and I'll press Enter.
| | 02:19 | So the message box will display "This
report gets its records from," then a space,
| | 02:25 | followed, by the RecordSource, and then
a period. And I'll press Ctrl+S to save
| | 02:30 | my work and Alt+F11 to go back
into the database window, and with the
| | 02:34 | Territories Report displayed,
press Ctrl+W to close it.
| | 02:38 | Now in the Navigation pane, double-
click the Territories report, and when you
| | 02:43 | do, you see that this report gets
its records from the Territories.
| | 02:47 | Click OK and the message box goes away.
| | 02:50 | Revealing a form or report's record
source is a useful way to check your
| | 02:54 | database's functionality and to
discover information about someone else's
| | 02:58 | database you're called in to maintain.
| | 03:00 | It's a handy technique to
have in your repertoire.
| | Collapse this transcript |
| Setting the ScrollBars property| 00:00 | Like other Access objects, forms and
reports have scrollbars at the bottom and
| | 00:04 | right edges of the object.
| | 00:06 | If your form or report can display
all of its contents without scrolling,
| | 00:09 | you should consider hiding one or both
scrollbars to give your object a cleaner look.
| | 00:14 | In this movie, I will show you how to
use Access VBA to control the scrollbars
| | 00:17 | on your forms and reports.
| | 00:20 | To demonstrate, I am going to work with
the Territories Form, which is here in
| | 00:24 | the Navigation pane.
| | 00:25 | I will right-click it
and then click Design View.
| | 00:29 | Then in the body of the form, I will
right-click and from the Shortcut menu that
| | 00:33 | appears, click Build Event. Then in
the Choose Builder dialog box, click Code
| | 00:38 | Builder, and then click OK.
| | 00:40 | When I do, Access creates an Event
code module that will be run whenever the
| | 00:45 | detail section of the form is clicked.
| | 00:47 | I want to use a different event,
| | 00:49 | so I am going to click the Object
Lists down arrow and click Form.
| | 00:56 | When I do, Access automatically fills
in the Form_Load method that is when the
| | 01:01 | form is loaded into memory.
| | 01:03 | That is the method I want to use,
| | 01:04 | so I will go ahead and leave that as
it is and press Enter twice and the
| | 01:09 | up arrow once to give myself some room to work.
| | 01:12 | The code to control the appearance of
scrollbars for forms or reports is this:
| | 01:17 | We use the Me object which again can
refer to a form or a report, period, and
| | 01:23 | then Scrollbars, all one word,
a space, then an equal sign,
| | 01:30 | and another space, and then you
can have the values 0, 1, 2, or 3.
| | 01:34 | 0, as you might expect, is no scrollbars;
1 is Horizontal only; 2 is Vertical
| | 01:41 | only; and 3 is both.
| | 01:43 | So I will go ahead and type in 0, so that
we will have no scrollbars on this form.
| | 01:48 | Then I will press Enter twice, and I am
going to type in all of the values so
| | 01:52 | that they are here as comments.
| | 01:55 | You could follow along as you like,
but what I wanted to do was to list them all
| | 01:59 | again so you have visual memory, in
addition to hearing me say the names.
| | 02:02 | So I'll make the comments. So a single
quote, then 0, space, No scrollbars, then a
| | 02:11 | single quote, and a 1, a space,
and that is Horizontal only, a Return. Single
| | 02:19 | quote 2 is Vertical only. And then
single quote 3 is Both, and then Enter.
| | 02:28 | So now I'll press Ctrl+S to save my work,
and Alt+F11 to go back to the form and
| | 02:35 | then press Ctrl+W to close the form,
and save my work. And now when I
| | 02:39 | double-click the form to open it,
it appears with no scrollbars.
| | 02:43 | Scrollbars come in handy when a form or
a report's data won't fit on the screen
| | 02:46 | without scrolling, but you should
consider removing them when you don't need
| | 02:50 | them, to avoid visual clutter.
| | Collapse this transcript |
| Rendering a form or reporting visible or invisible| 00:00 | When a user enters data into a form, you
might open another form that could use that data.
| | 00:04 | For example, you could enter a
customer's ID and want to write it into another
| | 00:09 | form's Customer ID field.
| | 00:11 | Closing the previous form would make it
difficult to find the data, so you need
| | 00:14 | to make the previous form
invisible instead of closing it.
| | 00:17 | In this movie, I'll show you how to use
the Me object's Visible property to make
| | 00:22 | forms visible or invisible.
| | 00:25 | I should note that making a form
invisible doesn't close it; the data is
| | 00:29 | still available for use.
| | 00:30 | For this movie, we will use the Products Form,
| | 00:33 | so I will right-click that
and then open it in Design View.
| | 00:37 | To create a code module, let's right-
click anywhere in the form, and then click
| | 00:41 | Build Event. Then in the Choose Builder
dialog box, click Code Builder, and then click OK.
| | 00:48 | Doing so creates an event code module for
when the detail section of the form is clicked.
| | 00:53 | I want to use a different event,
| | 00:55 | so I'll go up to the Objects list, which
is just below the Title bar. Click the
| | 00:58 | Object down arrow and click Form,
and the Form_Load event is created
| | 01:04 | automatically by default.
| | 01:06 | That is the one I want to use,
| | 01:07 | so I'll press Enter twice and the up arrow
once to give myself a little bit of room to work.
| | 01:11 | Now, my goal here isn't to do anything
incredibly complicated; all I want to
| | 01:16 | do is prove the concept by turning
the form invisible, setting visible to
| | 01:21 | false, showing a message box
indicating that it has happened, and then making
| | 01:25 | the form visible again.
| | 01:27 | So the code for that is Me.Visible,
space, then an equal sign, and False.
| | 01:37 | So in other words, Visibility is False;
| | 01:39 | we are making it invisible. Then press Enter.
| | 01:42 | Now, a message box saying that just
happened, MsgBox, space, then a quote, and
| | 01:48 | The form is still open, just invisible,
period, and then double-quotes. Press
| | 01:57 | Enter and then we'll turn the
visible property back to True.
| | 02:01 | So Me.Visible, space, equal,
and then True, and press Tab.
| | 02:08 | I will press Ctrl+S to save my work,
and Alt+F11 to go back to the main database
| | 02:13 | window, and then I'll press
Ctrl+W to close the Products Form.
| | 02:18 | Now, I am going to double-click the
Products Form, and it opened and then was
| | 02:23 | turned invisible more quickly
than we could see on screen.
| | 02:26 | But the Message Box appears:
| | 02:27 | "The form is still open, just invisible."
| | 02:29 | Now, if I click OK, the form opens as normal.
| | 02:33 | Making a form invisible so you can
remove it from the screen but still use its
| | 02:36 | data is a valuable technique.
| | 02:38 | I'm sure that you will find many
uses for it as you work in Access.
| | Collapse this transcript |
|
|
ConclusionAdditional resources and final thoughts| 00:00 | Thanks again for your interest in
Up and Running with VBA in Access.
| | 00:04 | I've covered the basics of
programming Access in this course.
| | 00:08 | If you want to get more information,
there are quite a few resources you can use.
| | 00:12 | The ones that I prefer are Microsoft Access
2010 VBA Programming Inside Out by Andrew Couch.
| | 00:19 | That is a book that was published
by Microsoft Press in July of 2011.
| | 00:22 | I also recommend Microsoft Access 2010
Programmer's Reference by Teresa Henning,
| | 00:28 | Rob Cooper, Geoffrey
Griffith, and Jerry Dennison.
| | 00:32 | That book has a bright red cover,
and it was published by Wrox Press.
| | 00:36 | Finally, if you want to get
information about Access or any Office program,
| | 00:40 | either using the program or
programming it using VBA, you can always go to
| | 00:45 | office.microsoft.com.
| | 00:48 | Thanks again, and I hope you have
a lot of fun using Access with VBA.
| | Collapse this transcript |
|
|
