Click Convert and then click OK when the conversion is finished

To get to your converted code, click Modules in the Navigation pane. The converted macro is in a module named Converted Macro -yourMacroName where yourMacroName is the name of the macro that you converted. Double-click that module name to see the converted code. The converted code is inside a pair of Function End Function statements, as in the example shown in Figure 13-8. There, you see the results of converting the macro shown in Figure 13-7 to VBA. (You can really get a sense here of how a...

Copy and paste 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. 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 Num Word 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...

Applying VBA in the Real World

Tennant Real Programmers

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

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

Access Query Table Images

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

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

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

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 letters. 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 edit all the data manually. Technically, you could just use the built-in StrConv string, vbProperCase...

Changing and Deleting Table Records

Any Access update query or delete query also converts nicely to VBA. For example, you might 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 SentWelcome set to True customers who haven't been sent the message have SentWelcome set to False. 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...

Using For Each loops

The specific number assigned to each item in a collection isn't terribly important. What is important is that VBA provides some special commands for looping through or 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 collectionName ' code to be performed on...

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 doesn'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 CCSecureLocal already exists when you run the code to import its data, Access creates the new table as CCSecureLocall. Run the...

Creating Your Own Functions

Acadlwpolyline Vba

In VBA, you can create your own, custom functions to add to those that are built into Access. As a rule, put all custom functions in a standard module rather than in a class module because putting a custom function in a standard module makes the function available to all the objects in the current database. In other words, any function that you create in a standard module can be used just as though it were a built-in function throughout the current database. Work through the whole process,...

Checking on Variables with Debug Print

Debug Fenster Access 2007

In earlier chapters, you can see examples of using the VBA Editor Immediate window to test procedures and try out expressions. For example, typing a simple expression like 1 1 What is one plus one results in 2, which is the sum of one plus one. Typing the expression CurrentProject.AllForms.Count displays the number of forms in the current database. You can also force your code to display information in the Immediate window. However, in code, you use a Debug.Print expression rather than a...

Accessing the Object Library

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

The continuation character

When writing VBA code, you can break a long line into two or more 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, especially in code you see printed in books and such because the code needs to fit within the margins of the book. For example, this fairly long line of code barely fits within the margins in this Public Sub MySum anyName As String, anyNum as Number Here's...

Storing data in arrays

If you think of a variable or constant as one little cubbyhole in which you can tuck away information, a collection of cubbyholes is an array. Each cubbyhole is an element of the array, although each is just a variable in which you can store information. The cubbyholes in an array, however, all have the same name. You use a subscript in parentheses, which defines an element's position in the array, to refer to a specific item in the array. Declaring an array is a lot like declaring a single...

Expected list separator or

The Expected list separator or error message tells you that the compiler was expecting to find either a list separator such as the comma that separates arguments in a function or a closing parenthesis in the statement. In most cases, it highlights where the problem began. For example, the following statement, when compiled, generates an Expected list separator or error message with the word World highlighted Answer MsgBox Hello World, vbInformation,Test The problem with the preceding line is...

Client Server Microsoft Access

Mouse Pointer Names

As you might or might not know, you can use Access's built-in Database Splitter to split any existing database into two separate databases two separate ACCDB files . The Database Splitter is a wizard that takes you through the steps necessary to split the database. In the process, the wizard creates a database that contains only the tables. To open the Database Splitter, click the Database Tools tab, and then click the Access Database button in the Move Data group. For example, you might want...

Changing colors

Your VBA code can change the color of objects on forms. Such changes can be handy to use color-coding to call attention to specific items on a form. For example, if a 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. Some common coloring properties include 1 BackColor Defines the background color of a text box, combo box,...

Detecting a Right Click

You might have noticed that just about every control has an On Click event to which you can tie code. The On Click event occurs only when the user points to the item and then presses and releases the left mouse button. There's no On Right-Click event that you can use to detect whether the user right-clicks an item. If you want to write different code for different types of clicks, you have to use the On MouseDown event. When you click an object's On MouseDown event in the property sheet and...

Working with Select Queries and Recordsets

To this point in this chapter, we focus 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...

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 want. 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 with what-if scenarios with data in Access isn't so easy, though, because you need to get queries and or forms involved. It's...

Changing special effects

Text boxes and some other controls on forms have a Special Effect property that defines their general appearance on the form. When you're creating a form in the form's Design view, you set a control's Special Effect property in the property 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...

Creating a Spin Box Control

Many Windows dialog boxes offer a spin box control, which lets you change a number without typing. Oddly enough, there's no spin box control in the Controls group 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. We've used numerous techniques to create the spin buttons. We've imported ActiveX controls, used command...

Figure

Vba Opening Forms Excel

Sample query to total sales from orders in a database. ProducEWa.me Prosta . ' Group By Ascending 0 Next, we create a form that's bound to Order Summary Totals Qry. In Design view, we set the Form's Default View property to Continuous Forms so that the detail band displays all the records from the underlying query. In the Form Footer of that form, we add a calculated control named GrandTotal that contains the expression Sum TotalSales to display the grand total of all the TotalSales values. We...

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

Jumpstart Creating a Simple VBA Program

Starting out with a standard module Creating procedures Getting help with VBA keywords Editing existing code m Jisual Basic for Applications VBA is a programming language for writing yr instructions that tell Office applications in this book, that means Access the steps needed to perform a task. You store code in Access modules. The tool that you use to create and edit VBA code is the Visual Basic Editor, which opens automatically whenever you open an Access module. If you need a refresher on...