Thou Shalt Not Bear False Witness against Thy Object Browser

Everything in an Access database is an object. VBA exists to manipulate those objects programmatically. Thy Object Browser is thy Word and Shepard. It leadeth you to code that actually works. It helpeth to remind you that Forms and Reports refer to open objects and that AllForms and AllReports work with closed objects. For example, the Object Browser helpeth you to discriminate between the DoCmd object and the DoCmd property. It taketh you to the land of truth and facts, and bestoweth upon you...

Understanding Compilation and Runtime

How a machine (like your computer) works and how your brain works are two entirely different things. All machines are basically dumb as rocks because they're just machines. Your computer is nothing more than a mindless machine that can pump a few billion instructions per second through a little toenail-sized wafer. No thought nor thinking nor awareness is involved in any of that. It's all just electrons zooming around at the speed of light in a controlled manner inside a small area. Each of...

Linking to External Data through Code

The TransferDatabase method of the VBA DoCmd object also provides a syntax for linking to an external table (but not a query). Note that the first argument after TransferDatabase is acLink rather than acImport. Other than that, the syntax is basically the same DoCmd.TransferDatabase acLink, Microsoft Access, pathToExternalDB, acTable, _ externalTbl, localTableName For example, the DoCmd.TransferDatabase (as shown here) sets up a link from the current database to an external table named CCSecure...

Skipping Over Used Mailing Labels

Suppose that you often use Access to print mailing labels on individual sheets. Each time you print a partial sheet of labels, you end up with some extra unused labels on the sheet. If you reuse that sheet of labels in the printer, Access will print right on the missing labels. Basically, you can't reuse a sheet of labels that's already partially used. That's not good because labels aren't cheap. A solution to the problem is to pop up a dialog box like the one shown in Figure 8-5 just before...

Importing ExportingLinking to Anything

As you might know, you export data from Access to a variety of formats. You can do so interactively (without code). Here's how. 1. In the database window, click the table, query, or other object that you want to export. 2. Choose FileOExport from the Access menu bar. 3. In the Export dialog box that appears, choose a document type from the Save As Type drop-down list. 4. Navigate to a folder, enter a filename for the exported data, and click the Export button. If you want to automate the...

Interacting with Microsoft Excel

Microsoft Excel is a great program for playing what-if scenarios with data because it lets you plug data and formulas into cells in whatever manner you wish. Excel isn't good, however, at managing large volumes of data. For large volumes of data, you need a database like Microsoft Access. Microsoft Access can certainly do any math calculations that Excel can do. Playing what-if scenarios with data in Access is not so easy, though, because you need to get queries and or forms involved. It's just...

What the Heck is SQL

Although you might not realize it, every time you create a query in Access, you're actually creating an SQL statement. This is a good thing because as a rule, creating a query in Access is a lot easier than writing an SQL statement from scratch. To illustrate how every query is really a SQL statement in disguise, Figure 7-1 shows a basic Access Select query that in Datasheet view displays some fields and records from a table. So where's the SQL statement in Figure 7-1 Well, it's not visible...

Listing Code to Fill a Combo Box Value List Property

'Create a string variable named NewValList Dim NewValList As String NewValList Chr 34 First Item Chr 34 NewValList NewValList Chr 34 Second Item Chr 34 NewValList NewValList Chr 34 Third Item Chr 34 NewValList NewValList Chr 34 Fourth Item Chr 34 'At this point, NewValList contains 'First Item Second Item Third Item Fourth Item 'Make new string the Row Source for value list named OpsCombo Me OpsCombo.RowSourceType Value List Me OpsCombo.RowSource NewValList 'Set selection to first item in...

Seiect queries Versus action queries

Sample Action Query

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...

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...

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 Num Word 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...

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...

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...

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...

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...

More DoCmd methods for forms

The DoCmd object used in the preceding example 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. Table 6-3 Commonly Used DoCmd Methods You don't need to study and memorize them all now because you can easily get detailed information on an as-needed basis. Just type the beginning of the statement into your code, as follows Just double-click the method name such...

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...

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,...

Changing colors

Background Color Codes Excel

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...

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...

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...

Listing Table Query field Values

The third type of combo box list box that you can create gets its values from a field or fields in a table or query. The Row Source Type for such a list is Table Query, and the Row Source is generally a SQL statement that specifies which fields and values to show in the list. Back up a moment and take a look at the bigger picture. Suppose you want to create a drop-down list that shows an alphabetized list of all unique Company names from a table. By unique, I mean that if a given company name...

Working with Select Queries and Recordsets

So far in this chapter, I've focused mainly on Access action queries that you execute from VBA by using DoCmd.RunSQL. Select queries, which only display data and never change the contents of a table , are a completely different story. In Access, you don't run a select query. You simply switch from Query Design view to Datasheet view to see the records returned by that query. And in VBA, you don't use DoCmd.RunSQL to execute a select query. Rather, you store the results of the query in a weird,...

Responding to a Msg Box button click

If your dialog box asks a question, you presumably want your VBA code to respond to whatever button the user clicked. That's fairly easy to do because when the user clicks a button, the variable to the left side of the MsgBox function returns a value indicating which button the user clicked. Each button that you can show in a message box returns a unique value. For example, when the user clicks the Yes button, MsgBox returns 6 which also equals Access's built-in vbYes constant . If the user...

Sending Email via Outlook

Access Vba Mail

Suppose you want to be able to send e-mail messages to people listed in a table named Customers in an Access database. You are absolutely certain that you can send and receive e-mail with Microsoft Outlook. Important None of the code described here will work with Outlook Express or a Web browser. For this example, say you want to create a standard form letter-type e-mail message to whatever customer a user chooses from a drop-down menu, as in Figure 14-4. There, the controls named MsgAddress...

Copyandpaste code from the

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. When that code is displayed onscreen, as in a Web page, the first step is to copy and paste the code into a module, where you select what you want to copy, copy it press Ctrl C , click in your module, and paste press Ctrl V . Suppose you're browsing the Web, and you come across a sample procedure in a Web page. You don't want to...

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 you have your preprinted checks, and maybe you've already created a report format to print the checks. But 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 are you going to get that part of the check printed There's no built-in function capable of doing that for you. And heaven knows you don't want to type...