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

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

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

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

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

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

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

Excel 2007 User Forms Vba

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

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

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

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

Creating a Spin Box Control

Spin Box Design

Many Windows dialog boxes offer a spin box control that lets you change a number without typing. Oddly enough, there is no spin box control in the form's Design Toolbox to let you create such a control on your Access forms. If you want to add a spin box control to an Access form, you have to fudge it. Writing the code for the spin buttons is easy creating the little buttons is the real challenge. I've used numerous techniques to create the spin buttons. I've imported ActiveX controls, used...

Programming Combo and List Boxes

When working with combo and list boxes through VBA, you'll often want to start with just a simple unbound control one that's not attached to any field in the form's underlying table or query and then let VBA control the properties. To add an unbound ComboBox or a ListBox control to a form, first make sure that the form is open in Design view and that the Toolbox see Figure 10-2 is open. To prevent the Control Wizards from helping you create the control, click the wizard's button until it's no...

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