Using a Select Case block
Each Case statement can have any number of statements beneath it. When the code executes, only those statements after the Case statement that matches the value at the top of the block execute. Figure 4-9 shows the general concept. Case possibleVahie2 statements statements Case possibleVahie3 statements statements These statements executed only if no
Using the WithEnd With statements
If you want your code to change several properties of a control, you can use a With End With block of code to make your code easier to read. For example, if you want your code to change several properties of a control named myControl on a form named myForm and the code isn't in a class module , you could include that lengthy identifier on every line of code, as shown here Forms myForm.myControl.BackStyle 1 Forms myForm.myControl.BackColor vbWhite Forms myForm.myControl.ForeColor vbRed Forms...
Naming conventions for Variables
Some programmers use naming conventions to identify the data type of a variable as part of the variable's or constant's name. The naming conventions are entirely optional you don't have to use them. A lot of VBA programmers follow them, though, so you're likely to see them in any code you happen to come across. The idea behind a naming convention is simple When you define a new variable, make the first three letters of the name the tag stand for the type of variable or object. For example, the...
Using lfEnd If statements
You have a couple of ways to write VBA code that's capable of making a decision. The simplest and by far the most common is the lf End If block of code, which uses this syntax If condition Then statements Else where condition is an expression that results in True or False, and statements refers to any number of valid VBA statements. If the condition proves True, the statements between Then and Else execute, and all other statements are ignored. If the condition proves False, only the statements...
Opening and Closing Forms
VBA doesn't limit you to working with individual controls on forms. You can work with entire forms as objects, too. For example, VBA can open a closed form and display it on-screen. The OpenForm method of the DoCmd do command object gives you great flexibility in exactly how VBA opens a form. The syntax for using the OpenForm method of the DoCmd object is DoCmd.OpenForm formName, View , FilterName , WhereCondition , DataMode , WindowMode OpenArgs Only the first argument, formName, is required....
Understanding Your VBA Building Blocks
Commenting your code Storing data in variables and constants Repeating chunks of code with loops Making decisions with lf End If statements any programmers begin their careers not so much by writing code WWW from scratch as by acquiring bits of code from books, Web sites, and other resources because that's easier than trying to figure it out from scratch. Plenty of sample code is made available through books and Web sites. Don't worry about stealing the code If folks didn't want you copying...
More DoCmd methods for forms
The DoCmd object used in the example in the preceding section to open and close forms provides many methods for working with data on forms. Table 6-3 summarizes some of the more commonly used DoCmd methods for working with forms and data in forms. Send object Send object You don't need to study and memorize all these methods now because you can easily get detailed information as needed. Just type the beginning of the statement into your code, like this Just double-click the method name such as...
Enabling or Disabling Form Controls
When you work in most programs and dialog boxes, Windows disables dims controls that aren't relevant at the moment. You can add that same capability to your Access databases by using some VBA code. For example, you might create a form that allows a user to choose from among different payment options. When the user chooses Credit Card, you want all the fields for entering credit card information to be enabled. When the user selects any other payment method, you want to disable those same...
Changing the Appearance of Objects
A form, and each object on a form, contains certain properties that describe the general appearance of the object. Different types of objects have different combinations of appearance properties. When you're working in the form's Design view, the Format tab of the property sheet shows the properties that the selected object or objects support. For example, Figure 6-5 shows some of the appearance properties available for the selected TextBox control on the form. Some appearance properties for a...
Understanding Object Models
An object model is a road map, or the view from 30,000 feet, of all the objects and properties that VBA can manipulate. Because there are so many thousands of objects, you need a sort of road map to find them, just like you need a map to navigate unfamiliar territory. When you view an object model or portion of an object model , all you see are color-coded boxes arranged in a vertical hierarchy. For example, Figure 5-1 shows a graphical representation of the Access object model. Notice the...
Using VBA with Multiple Databases
Importing data from external tables and queries Linking to external Access tables Creating recordsets from external data Importing, exporting, and linking to anything sually an Access database an ACCDB file contains all the tables, queries, forms, reports, and other objects that make up a single database. However, you find situations where it's to your advantage to split things into two or more database files. For example, you might want to put some tables for a database into a single ACCDB...
Considering Types of Program Errors
Errors in code can happen at any time in the create-compile-execute sequence. Programmers generally categorize the types of errors that they have to deal with in three ways 1 Compile errors Any problem that prevents the VBA Editor from translating a line of source code to something executable generates a compile error, like the example shown in Figure 12-1. Such errors are usually syntax errors, which means that you didn't obey the rules of syntax for that statement when typing the code. 1...
Making Decisions in VBA Code
Decision-making is a big part of programming because most programs need to be smart enough to figure out what to do, depending on the circumstances. Often, you want your code to do one thing if such-and-such is true but do something else if such-and-such is false. You use conditional expressions to determine whether something is true or false. A conditional expression is one that generally follows this syntax where Value is some chunk of information, and the ComparisonOperator is one of those...
A Function to Print Check Amounts
Suppose you want to use Access to print checks from a table of payable amounts. You have your printer and your preprinted checks, and maybe you already created a report format to print the checks. What about the part of the check where you're supposed to write out the dollar amount, such as One Hundred Thirty-Five and 49 100 How can you get that part of the check printed No built-in function is capable of doing that for you. And heaven knows you don't want to type all those words The solution...
Dealing with Logical Errors
Even if your code compiles and runs without generating an error message, the code isn't necessarily perfect. It can also contain logical errors. Unlike a compile error, which is an error in syntax or a typographical error, a logical error is an error in your thinking logic . The computer always does exactly what the code tells it to do, even if you tell it to do the wrong thing. Suppose that you intend to write a line of code to open some form, but you accidentally write the code to close the...
Calling a Procedure from an Event
At this stage of the game, your database contains a standard module that contains a Sub procedure named SkipLabels . Because you haven't yet tied the SkipLabels procedure to any event, nothing is in the database yet to take advantage of SkipLabels . Recall that earlier in this chapter, we show a form with a control named LabelsToSkip it stores the number of labels to be skipped over as well as a Cancel button and a Print button refer to Figure 8-6 . If the user clicks Cancel, you just want...
Closing a form
To close a form that's already open, use the Close method of the DoCmd object and the syntax DoCmd.Close objectType, objectName, SaveOptions 1 objectType Describes the type of object being closed. Use acForm for forms acReport for reports . 1 objectName The name of the form or other object to close. 1 SaveOptions Specifies whether to save changes made to the object by using one of these constants acSaveYes The current object is saved automatically. acSaveNo Any changes made to the object are...
Closing recordsets and collections
To close an open recordset, use the Close method with the recordset name. For example, to close an open recordset named myRecordSet, use the statement You should also close the connection after you close the recordset because the recordset uses the connection object with the similar statement The preceding statements close the recordset and connection only in terms of being able to manipulate data from VBA. The recordset and its connection, which you originally defined by using Dim and Set...
See Whether a Form Is Already Open
Sometimes it's useful for a procedure to know whether a form is open or closed. The CurrentProject.AllForms collection contains the name of every form in the current database. Each form has an IsLoaded property that's True if the form is open or False if the form is closed. The syntax for using IsLoaded is where formName is the name of a form in the current database. For example, the following expression returns True if the form named Customers is open or False if the form is closed A practical...
Copy and paste code from the Web
Many programmers start their careers not so much by writing code from scratch but rather by using code that others have written and adapting it to their own needs. You can use your favorite search engine to find a wealth of code on the Web. For example, to search for code examples for an If Then statement, type If Then in the search engine. You'll probably get all kinds of useless results. To narrow the results to something more useful, add the words Access VBA to your search. For example,...
Looking at how NumWord Works
NumWord is a fairly lengthy procedure mainly because the rules for converting numbers to words, in English, are a little complicated. But like any procedure, NumWord is just a series of small decisions and steps needed to get the job done. The first line of the procedure, as follows, defines the name of the procedure, NumWord , and declares that it will accept a number Currency value number as an argument. Whatever number gets passed to the argument is referred to as AmountPassed in the rest of...
Creating new tables from existing tables
The easiest way to use VBA to create a new table from an existing table is to first design a Make-Table query in Access. In Query Design view, click the Make Table command in the Query Type group on the Query Tools Design tab, and then specify the name of the table to create. Refer to Figure 7-5. Figure 7-7 shows an example of a Make-Table query that selects fields from a couple of related tables, where the Paid field contains False. This query creates a new table named...
Using DoLoop to create a loop
The Do Loop block is one method of setting up a loop in code to execute statements repeatedly. The loop requires two lines of code one at the top and one at the bottom. You have a lot of flexibility when defining a Do Loop. In fact, there are two forms of syntax for creating these loops. The first is the following Do While Until condition statements Exit Do statements Loop The second form of syntax provides the option of defining the condition at the bottom of the loop, like this statements...
Creating Your Own Dialog Boxes
Asking questions, responding to answers Storing dialog box settings Creating custom dialog boxes Creating spin box controls Detecting a right-click ou see dialog boxes in Windows and other programs all the time. Each dialog box presents some options for you to choose from. The name dialog box stems from the fact that the user and the dialog box carry on a sort of conversation. The dialog box presents some options, and the user makes selections from those options and then clicks OK. When you're...
Using ForNext to create a loop
A third pair of commands for creating loops in code is the For Next block of statements. The syntax for a For Next loop is shown here For counter start To end Step step statements Exit For 1 counter is any name that you want to give to the variable that keeps track of passes through the loop. 1 start is a number that indicates where the loop should start counting. 1 end is a number that indicates when the loop should end. 1 step is optional and indicates how much to increment or decrement...
Customizing Combo Boxes and List Boxes
Programming combo boxes and list boxes Linking lists Updating one form's control from another form Discovering cool combo box tricks 7yping information into forms takes time, and typing always means the possibility of making typographical errors. Whenever you can eliminate typing by giving the user something to click, you're making your data entry quicker and more accurate. Combo boxes and list boxes are both good tools for giving the user options to choose from when typing would otherwise be...
Controlling boldface italics and such
If a control displays text or numbers, you can change the font or style of text by using VBA. The property names are self-explanatory, as are most of their settings. As always, controlName stands for the name of a control on a form. Wherever you see a pipe separating options, you can use one or the other The .ForeColor property described in the earlier section Changing colors determines the color of text in a box. In other words, the .ForeColor property defines the font color. For example, to...
Refer to an Open Form
VBA keeps track of all open forms in the Forms collection. To refer to an open form from a standard module, use the syntax where formName is the name of the open form. For example, when the Products form is open, use in VBA to refer to that form as a whole. However, you typically want to refer to a specific control on the open form or perhaps a property of the form as a whole. To refer to a form property, use the syntax where propertyName is the name of the property. For example, every form has...
Applying VBA in the Real World
We're here to clean the code We're here to clean the code Some of the programming techniques in these chapters are a bit trickier than techniques from previous chapters, but they're not just stupid pet tricks. They're useful tricks. What makes them tricky has more to do with the way you use VBA to trick Access into doing things it couldn't possibly do on its own. In the real world, people rarely write code that works perfectly right off the bat. Even experienced programmers have to spend some...
Backend server
Here's a downside to the whole business of splitting the tables from the other objects network traffic. It takes time to get things across a network. The heavier the traffic on the network, the longer it takes. You might have situations where a certain external table needs to be accessed only occasionally. Perhaps only a snapshot of some data is all that's required. In such cases, you can use VBA to open and close external links as needed. For example, you can attach code to a form's On Load...
Update a List Box or Combo Box
ListBox and ComboBox controls can show lists of data from tables or queries. A common problem with such controls occurs when the row source for the list changes while the control is open and visible on the form. List boxes and combo boxes don't recheck their recordsources after the form is open, so it's easy for a list to get out of sync with what's in its source table or query. VBA can force a list box or combo box to update its list immediately, via the syntax Forms formName controlName...
Introducing VBA Programming
Once I told Hona that Access -was an 'argument' based program, she seemed to v arm up to it. rn BA lets you do some pretty amazing stuff in an Access yr database. With VBA, you can make Access do boring, repetitive jobs that you might otherwise have to do on your own. You can even get Access to do things that it couldn't possibly do on its own. Before you dive right in and try to make such things happen, you need to step back a moment and get a feel for how VBA fits into the whole Microsoft...
Query to append one record
You can create a query that appends a single record to a table, although the way you create the query is a little weird. The resulting SQL statement doesn't exactly match the syntax that we described earlier, either. But it all works and would definitely be easier than trying to write a lengthy SQL statement by hand. The trick is to create a new query that doesn't have any tables at the top of the Query design window. Or, if a table is at the top of the query, right-click the table and choose...
DAO is not DOA but ADO is AOK
Originally, Access offered only one way to create a recordset DAO Data Access Objects . DAO used different words, like DBEngine and WorkSpace, to create recordsets. You might still see that in other code examples but not in this book . In this book, we use the ADO ActiveX Data Objects technology to create and manipulate recordsets. Ever since the introduction of ADO, a battle has taken place over which one is best to use. Some developers swear by ADO others prefer DAO. Many developers thought...
Print a Report
If you want VBA to print a report from the current database, use the syntax DoCmd.OpenReport reportName, acViewNormal where reportName is the name of any report in the current database. For example, the following statement prints a report named MyLabels DoCmd.OpenReport MyLabels, acViewNormal In case you hadn't noticed, the DoCmd pronounced do command object shows up quite a few times in this chapter. That's because the DoCmd object lets you do lots of useful things with the tables, queries,...
Passing Data to Procedures
You write a procedure to perform a series of steps. The exact object on which the procedure performs its task can vary. For example, you might inherit a database table of names and addresses, with everything typed in uppercase letters, as in JOHN SMITH. You want to convert all that text to proper case John Smith , but you don't want to retype it all. Boring technical stuff on the Decimal data type When perusing the VBA Help and drop-down menus, you might come across the Decimal data type. We...
Get to Know the DoCmd Object
The DoCmd object is one of your most potent programming allies because it can do virtually anything you can do in the Access program window. When you type DoCmd. into the Code window, the hefty list of items that appears on the little menu see Figure 16-3 represents various methods of the DoCmd object. Each method, in turn, represents something that the DoCmd object can do. The DoCmd methods that you're most likely to use, especially as a beginning programmer, are summarized in Table 16-1. As...
Click OK
If you want to automate the export so that a user can do it with the click of a button, your best bet is to create a macro that uses the OutputTo action to export the data to a file. Here's how 1. Open the Access database that contains the database to export. 2. In the Other group on the Create tab, click the Macro button. 3. Choose OutputTo as the action argument, and then fill in the action arguments as summarized in Table 13-1. Press F1 while the cursor is in any action argument for more...
Select queries Versus action queries
To this point in this chapter, we talk only about Access select queries. That type of query gets its name from the fact that it only selects fields and records from a table. A select query never alters the contents of a table. An action query is different from a select query in that an action query changes the contents of a table. In Access, you create action queries in much the same way you create select queries. You start off by creating a new, regular query so that you're at the Query Design...
Adding Records to a Table
VBA can also append add records to any table that already exists in the database without deleting or changing any records that might already be in the table. If the records to be appended to the table already exist in some other table, you can use a simple append query in Access to generate the appropriate SQL statement. For example, Figure 7-8 shows an append query that selects several fields and records from two related tables in a database. The name of the destination table,...
More Combo Box Tricks
In this section, we show you a few more combo box tricks, starting with an explanation of why what you see in a combo box isn't always what you get in VBA. For example, the CustID control on the Orders form shown in Figure 10-12 is bound to a Long Integer field in its underlying table, yet its combo box shows a bunch of names and addresses. How can that be Using hidden values in combo and list boxes A combo box or list box can show any data from a table or query even though the control contains...
Storing data in Variables and constants
All programming languages, including VBA, have a means of storing little chunks of information data in temporary cubbyholes called variables. Obviously, the contents of the cubbyhole can vary. For example, a variable named LastName might contain Smith, Jones, McDougal, or whatever. The VBA code can operate on whatever value happens to be in the variable at the moment. Creating a variable is a two-step process 1. Declare the variable's name and data type with a Dim statement. 2. Assign a value...
Using the NumWord function
For the sake of example, assume that you already put NumWord into a standard module in your database. You already have a table that contains data to be printed on checks. Just to give this whole example some context, suppose that you have a table with field names and data types similar to those shown in the sample Payables table in Figure 11-6. The top-left side of the figure shows the table's structure, and the bottom-right side of the figure shows some sample data in the table. Sample field...
Move the Cursor to a Control
To move the cursor to a specific control on an open form from within a standard module , use this syntax For example, the following VBA statements open a form named NewCust and move the cursor to a control named TaxExempt on that form 'Open my NewCust form in Form view. DoCmd.OpenForm NewCust, acNormal 'Move the cursor to the Tax Exempt control. Forms NewCust Tax Exempt .SetFocus
Square brackets represent names
The rules for referring to field names in VBA are the same rules used in Access expressions. When referring to a field name that contains blank spaces, you must enclose the field name in square brackets, like this Sales Tax Rate . If the field name contains no blank spaces, the square brackets are optional. For example, the name SalesTaxRates in VBA refers to a field named SalesTaxRates, even without the square brackets. Many programmers put square brackets around all field names for a couple...
Working with Class Procedures
Every form in a database has a class module in which you can store code that's used only by that form. To get to a form's class module, you first have to click the Forms group in the Navigation pane and then open an existing form in Design view or create a new form. Typically, you want to tie your code to an object and event. For example, a button on a form is an object. Every button has an On Click event that occurs whenever a user clicks the button in Form view. If you want to write code that...
Looping through Collections
As we mention in Chapter 5, Access contains objects and collections whose properties and methods can be controlled through VBA. Each collection has a specific name. For example, the CurrentProject.AllForms collection contains the names of every form in the current database. Every collection has a .Count property that describes how many objects are in the collection. For example, CurrentProject.AllForms.Count represents the number of forms in the current database. For example, if you type...
Creating a new empty table from VBA
You can also create tables programmatically from VBA by using a SQL CREATE TABLE statement with the syntax CREATE TABLE tableName field type size , 1 tableName is the name of the table to create. 1 field specifies the name of one field in the table. 1 type specifies the data type of the field. 1 size indicates the size of the field. 1 indicates that you can repeat the field type size combination for each field you want to define in the table. For example, the following SQL statement creates a...
Displaying and Responding to Messages
When you want your database to give the user a little feedback or have the user answer a simple Yes No question, you can use a message box. The message box can be a simple feedback message with a single OK button, like the example shown at the left side of Figure 9-1. Or, the message box can ask a question and wait for an answer, as in the right side of Figure 9-1. There are two syntaxes for the MsgBox keyword. If you just want the message to show some text and an OK button, use the syntax...
Getting a reports recordsource
To work with data from a report, SkipLabels needs to figure out where that report is getting its data. Every form and report has an exposed .RecordSource property that VBA can query to find out the name of the table or query to which the form or report is attached. However, VBA can get that information only if the report or form is open in Design view. In SkipLabels, this next statement opens LabelsTempReport in Design view 'Open LabelsTempReport in Design view. DoCmd.OpenReport...