Listing text options
A combo box or list box can get its values from a simple string called a Value List. The string just contains each item in the list separated by semicolons. If the items in the list are all text, it's best to enclose each item in quotation marks. For example, Figure 10-4 shows a combo box named OpsCombo added to a form. You can see the items in the open combo box , Like, lt , gt , and so forth. You can also see the properties for the control. Notice that the Row Source Type is Value List, and...
Converting Forms to Dialog Boxes
Message boxes are fine when your code just needs to ask the user a simple question, but sometimes you want to give users several options to choose from. You might want to use a variety of controls, such as check boxes and combo boxes, to present those options. Read about this in the upcoming section, Creating Custom Combo Boxes. When your code needs more than a simple answer to a single question, use a dialog box rather than a message box. A dialog box often called a dialog for short in an...
General VBA Editor Shortcut Keys
The shortcut keys in Table 17-2 are available throughout the VBA editor and are therefore also available in the Code window and Immediate window. The shortcut keys in Table 17-2 are available throughout the VBA editor and are therefore also available in the Code window and Immediate window.
Running code when a form opens
If you want a procedure to execute as soon as a form opens and any data from the form's underlying table or query has been loaded into the form, tie a procedure to the forms On Load event. The name of that procedure, in every form, is Form_Load . The Form_Load procedure for the sample form shown in Figure 10-11 looks something like this Call UpdateValueToFindCombo End Sub In forms Design, make sure that the Properties sheet shows the word Form in the title bar and or drop-down list. Clicking...
Conquering Compile Errors
The error messages that you'll face most often are the compile errors that happen in the VBA editor Code window. Every time you type a complete VBA statement and then move the cursor to some other line in the procedure, VBA quickly compiles that line of code. It doesn't actually run the code it just compiles the one line of code to make sure that it will run when you run the procedure. When you're first learning to program, compile errors might seem incessant and unstoppable. That's only...
Updating a combo box or a list box
A typical combo box or list box gets the values that it shows in its list only once, right after the form opens. For example, the CustID control in the Orders form pictured earlier gets its list of customers from a field in a table named Customers. It gets that list when the Orders form opens. When a user adds a new record to the Customers table via the NewCust form, the Orders table knows nothing of the new record. The drop-down menu in the CustID control just continues to show the same names...
Storing dialog box settings
Although creating a dialog box is easy, you need to first think about how you want to deal with the settings that the user chooses. If you want your dialog box to remember settings from one session to the next, you need to store those settings in some sort of table. Otherwise, all the user's settings will be forgotten by Access each time the user closes the database. The table that you create for storing dialog box settings needs only one record, with a field to store each dialog box setting...
Getting keyword help
Whether you're typing your own code or trying to modify someone else's, you can get information on any keyword at any time. Just select doubleclick the keyword right in the Code window where it's typed. Then press the Help key F1 on your keyboard. The Help window that opens describes the command and its syntax. After you type a keyword into a procedure, it's very easy to get more detailed help. Just select double-click the keyword, right where you typed it, and press the Help key F1 . This is...
Seiect queries Versus action queries
So far in this chapter, I've really only talked 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 will never alter the contents of a table. An action query is different from a select query in that an action query actually does change 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 you're at...
Debug Shortcut Keys
The shortcut keys in Table 17-3 apply when debugging code in the VBA editor.
Changing the Value of a property
To change the value of a property, follow the property name with an equal sign and a valid value for the property. For example, the Visible property of a control can be True Yes or False No . For example, the following statement makes invisible a control named myButton by setting its Visible property to False No Forms myForm myButton.Visible False To make that same control visible again from VBA, set its Visible property back to True Yes , as follows Forms myForm myButton.Visible True Bang...
Designing a message box
You can use the buttons argument of the MsgBox keyword to define the exact appearance and behavior of your message box. Each possible value for the buttons argument can be expressed as either a constant or a number. You can add the constants or numbers together to combine properties. For example, the constant vbYesNo or number 4 tells MsgBox to display Yes and No buttons in the form. The constant vbQuestion or number 32 tells MsgBox to display a question mark icon in the form. Combining the two...
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 actually tied the SkipLabels procedure to any event, there's nothing in the database yet to take advantage of SkipLabels . Recall that earlier in the chapter, I show a form with a control named LabelsToSkip it stores the number of labels to be skipped over as well as a Cancel and a Print button. If the user clicks Cancel, you just want Skip-LabelsForm to...
Getting back to normal in the Code window
When you finish debugging or just want to start over with a clean slate, do one of the following 1 To get out of step mode Press Ctrl Shift F8 or choose DebugOStep Out. Code execution will stop, and things will be back to normal. 1 To remove a breakpoint Right-click the line and choose ToggleO Breakpoint. 1 To clear all breakpoints from your code Choose DebugOClear All Breakpoints. 1 To clear the Locals window of its value Right-click any text within the window and choose the Reset option from...
Testing a custom function
Remember earlier when I said that a public custom function in a standard module can be used anywhere a built-in function After you type in the SalesTax function, you can see that for yourself by testing it the same way that you test a built in function. For example, if you type the following into the Immediate window because the sales tax on 100.00 is 6.75. If you type and press Enter, you get 1.0118 because the sales tax on 14.99 is about 1.02. In case you're wondering why all the numbers...
Other Ways to refer to objects
You don't always have to refer to an object by its specific name. You can use some special names in code to refer to objects in Access, as follows 1 Me In a class module, the term Me refers to the form to which the class module is attached. For example, Me Selling Price is short for The control named Selling Price on the form to which this code is attached. 1 CodeContextObject This refers to the name of the object in which the code is running. In a class module, this is always the same as the...
Listing field names
If you want a list box or combo box to list the names of fields in a table or query, set the control's Row Source Type property to Field List and set its Row Source property to the name of the table or query that contains the fields whose names you want to list. For example, Figure 10-3 shows a ComboBox control named FldNameCombo on a form. As you can see in the Properties sheet, its Row Source Type is set to Field List, and its Row Source is set to Customers. The names in the control's...
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 you have a table with field names and data types similar to those shown in the sample Payables table in Figure 11-6. The left side of the figure shows the table's structure, and the right side of the figure shows some sample data in the table. Sample field names and data...
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...
Getting forms in sync
Requerying the CustID control on the Orders form keeps the combo box's drop-down list up-to-date with the current contents of the Customers table at all times. However, it doesn't change the value that's currently displayed in that control. In other words, requerying a ComboBox control fixes the combo box's hidden drop-down menu, but it doesn't change which option in that menu is currently selected and visible in the control. You can always add some code to take care of that. A perfect example...
A Proper Case Function
Take a look now at a somewhat larger custom function that does more than a simple match calculation. Suppose you have a table filled with names and addresses, but for whatever reason, all the text is in uppercase or lowercase . For example, maybe the table has a Name field containing names like JOE SMITH or joe Smith. You want to tidy that up, but you certainly don't want to go in and retype all the data manually. Technically, you could just use the built-in StrConv string,3 function to solve...
Integrating with Other Office Applications
Understanding Automation objects Sending e-mail via Microsoft Outlook Sending commands to Microsoft Word Interacting with Microsoft Excel JBA isn't just a programming language for Microsoft Access. VBA is a programming language for all the Microsoft Office application programs that support Automation. Automation always with a capital A refers to the ability of a program to expose itself to VBA so that VBA can control it behind the scenes, so to speak. All the major applications in Microsoft...
Changing special effects
Text boxes and some other controls on forms have a Special Effect property that define their general appearance on the form. When you're creating a form in forms Design, you set a control's Special Effect property in the Properties sheet. If you want your code to change a control's special effect, use the syntax where controlName is the name of the control whose effect you want to change, and setting is either the number or constant as shown in Table 6-2. Table 6-2 Using a Constant or Number as...
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 Access's program window. When you type DoCmd. into the Code window, the hefty list of items that appears in 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. Like...
Sending Data to Microsoft Word
There are plenty of ways to print Access data without getting into VBA. The usual method is to just create a report from the Access database window. You can also use the Microsoft Word Mail Merge Wizard to print form letters, envelopes, labels, and catalogs from any Access table or query. There's no VBA involved in any of that. Just learn to use Word's Mail Merge feature, and you're on your way. Yet a third approach would be to create a general Word template that contains bookmarks placeholders...
Importing from External Databases
You can import data from any external Access table or query into a table in the current database. There are a couple of advantages to this approach. When you import, you actually create a table, within the current database, that contains an exact clone of the external table or query. Secondly, the imported data are stored in a normal, local Access table. After the table exists in the current database, all other objects in the database that depend on that table work just fine. No special...
Expected expression
The Expected expression compile error means that while trying to compile the line, things went haywire because the compiler was expecting to find an expression but found nothing. This error happens if you leave one or more dangling commas at the end of a statement. For example, the MsgBox statement in Figure 12-5 generated the compile error shown in that same figure. If you look closely, you might also notice that the closing parenthesis in the code is highlighted. The compile error is trying...
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 some simple value like an Integer. The integer, which is usually a primary key value, can be hidden in the control as the control's actual value while some more meaningful to humans text is shown to the user. This disappearing value act works thanks to multicolumn lists and the Bound Column property. Here, in a nutshell, is how it works 1 Whatever is in the first column of the list is what shows...
Storing SQL statements in variables
You can store SQL statements in variables, just as you can store text in variables. This can help with those extremely long SQL statements that seem to extend out forever past the right margin of the Code window. Many programmers will use this technique of building a long SQL statement out of smaller chunks, storing the statement in a variable. As an example, here is a series of VBA statements that build and execute a single length SQL statement from smaller chunks 'Create string variable...
See Whether a Form Is Already Open
Sometimes it's useful for a procedure to know whether a form is currently 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 currently 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 currently open or False if...
Using For Each loops
The specific number assigned to each item in a collection isn't terribly important. What is important is the fact that VBA provides some special commands for looping through a collection also called enumerating a collection , where the code looks at each object in a collection either to get information about it or to change it. The special code is a slight variation on the For Next loop called a For Each Next loop. The basic syntax for the For Each Next loop is For Each objectType in...
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,...
Code and Immediate Window Shortcuts
You'll use the VBA editor Code window to type all your Access code. The Code window supports all the standard Windows text-editing techniques, as summarized here in Table 17-1. Most keys also work in the Immediate window. The keys that don't work in the Immediate window are marked with an asterisk . Table 17-1 Code and Immediate Window Shortcuts Select all text to top of module window Select all text to bottom of module window Get help with currently selected word Doesn't work in the Immediate...
Setting form properties
Creating a dialog box in Access is similar to creating any other form. You don't even need any VBA code to create the box. Rather, you just create a form and set its Form Properties so that the form looks and acts like a dialog box. Here's how 1. In the database window, click the Forms button and then click the New button. 2. In the New Form dialog box that opens, choose Design View. If you created a table for storing settings, choose the table's name from the Choose the Table or Query Where...
Listing Code to Update a Combo Box from a Table
Private Sub UpdateValueToFindCombo 'Build a SQL statement to pull unique values 'from whatever field name is selected in form. ' If FldToSearch is empty, do nothing If Not IsNull Me FldToSearch.Value Then Dim MySQL As String MySQL SELECT DISTINCT FldToSearch.Value MySQL MySQL FROM LabelRecSource MySQL MySQL WHERE FldToSearch.Value Is Not Null MySQL MySQL ORDER BY FldToSearch.Value 'Now that we have the right SQL statement, make it the 'Row Source for the ValueToFind control. Me...
Listing Building a List of Report Names
'ValueList variable will store a string that can 'be used as the Value List property for a combo box Dim ValueList As String ValueList 'Loop through all report names. For Each AccessObject In CurrentProject.AllReports 'Don't add LabelsTempReport to the ValueList. If Not AccessObject.Name LabelsTempReport Then 'Only add report names that contain the word label. If InStr AccessObject.Name, Labels gt 1 Then 'Add current report name and semicolon to ValueList variable. ValueList ValueList Chr 34 _...
Listing Code to Update a Control on a Separate Form
'Do these steps only if Orders form is open. If Then 'Copy the new customer's CustID to a variable. Dim NewCustID As Integer NewCustID Me CustID.Value 'Close the NewCust form, saving new record. DoCmd.Close acForm, NewCust, acSaveYes 'Make sure were at new, blank record in Orders form DoCmd.GoToRecord acDataForm, Orders, acNewRec 'Copy new CustID into Orders form's CustID control Forms Orders CustID.Value NewCustID 'Move cursor to PaymentMethod control in Orders form. You might notice that none...
Giving users a quick f ind
You can use a combo box as a tool for allowing a user to quickly find a specific record in a table. For example, suppose you have a form that allows a user to find and edit customers. At the top of that form, you could provide a drop-down menu, perhaps named Quick Find or something, as in Figure 10-17. When the user chooses a name from the drop-down menu, the form instantly displays that customer's record. I also point out some of the properties for the QuickFind control in that figure. You can...
Using the continuation character
When writing VBA code, you can break a long line into two lines by using a continuation character, which is just an underscore _ . Many programmers use continuation characters to break lengthy VBA statements into two or more lines. This is especially true with code you see printed in books and such because the code needs to fit within the margins of the book. For example, here's a fairly long line of code that barely fits within the margins in this book Public Sub MySum anyName As String,...
Ask the User a Question
If you want your VBA code to ask the user a question and then perform some action based on the user's answer, you need to use the more complex MsgBox function syntax and an If Else End If block of code with the general syntax as shown here variableName If variableName vbYes Then 'Code to execute if user clicked Yes goes here. Else 'Code to execute if user clicked No goes here. End If I variableName is a name of your own choosing such as Answer . I prompt is the text of the question that the box...
Creating a Standard Module
Before you start writing code, you need a place to put it. Putting your code in standard modules is always a good bet because code in standard modules is accessible to all objects within a database. Creating a new standard module is easy. Just follow these steps 1. In your Access database, click Modules at the left side of the database window. 2. Click the New button, as shown in Figure 3-1. Begin by creating a new standard module. Begin by creating a new standard module. The new module will...
Avoiding Multiple Tables and Links
One of the big tricks to using the TransferDatabase method is being aware of how it names the table or link that it creates. It won't overwrite an existing table. If the current database already contains a table or link with the name that you specify in the localTableName argument, Access creates a new table or link with a number added to the name. For example, if CCSecureTable already exists when you run the code to import its data, Access creates the new table as CCSecurel. Run the code...
Changing colors
Your VBA code can change the color of objects on forms. Such changes can be handy when you use color-coding to call attention to specific items on a form. For example, if your payment is more than 30 days overdue, you might want to choose the amount due to show up in red to call attention to the value . The exact color properties available to you depend on the object for which you're writing code, but some common coloring properties include i BackColor Defines the background color of a text...
Query to append one record
You can actually create a query that appends a single record to a table, but the way you create the query is a little weird. The resulting SQL statement doesn't exactly match the syntax that I 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 up top. Or if there is a table at the top of the query, right-click it and choose Delete so that there are no...
Using the PCase function
Like with any custom function, you can use PCase wherever you would use a built-in function. Look at an example where you have a large table of names and addresses, where everything is in uppercase, as in Figure 11-3. For the sake of example, call this table UglyCustomers which isn't an insult to the customers just the way their names are typed in . Now that you have a PCase function that can convert text to proper case without messing up the Mc's, Mac's and P.O.'s you can use that in an update...
Using VBA to position the cursor
With VBA, you can move the cursor to any control on a form. In programmer jargon, moving the cursor to a control is called giving that control the focus. When you type, your text appears in whatever control on a form currently has the focus. VBA itself doesn't actually use square brackets. In fact, about the only time you see square brackets in VBA is when you're looking at a syntax chart, where square brackets are used to identify optional as opposed to required arguments. Access, however,...
Changing and Deleting Table Records
Any Access update query or delete query will also convert nicely to VBA. For example, suppose you keep track of which new customers you've sent e-mail to by using a Yes No field named SentWelcome in a table. Customers who have been sent the message have True in that field customers who haven't been sent the message have False in that field. For the sake of example, say that this table also has a field named Email that's either the Text or Hyperlink data type that contains each customer's e-mail...
Accessing the Object Library
In order for VBA to manipulate a program or a document within a program VBA first needs to have access to that program's object library. You might envision VBA as sort of a steering wheel that can control any program to which it has access through an object library , as in Figure 14-1. VBA can control any program through that program's object library. To write code for an Office application program, you first need to set a reference to that program's object library. To do so, starting from...
IIThou Shalt Not Use VBA Statements in Vain
Or, to put this another way, Thou shalt not attempt to make up thy own programming language. As a young boy, I was always a little fuzzy on the phrase in vain in the Catholic version of the Ten Commandments. Here, in vain means without a clue. You need to know the exact spelling and syntax of every VBA keyword and every Access object that you name. If the correct thing to type is DoCmd.OpenForm myForm, acNormal, , , acFormEdit don't assume that something reasonably close, like any of the...
Storing data in Variables and constants
All programming languages, including VBA, have a means of storing little chunks of information data in temporary little 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...