Using an objects methods

Methods are actions that you can perform on objects. The syntax for referring to an object's methods in VBA varies. In some cases, referring to a method is the same as referring to a property. You simply follow the object or collection name with a period and the method that you want to apply.

For example, the DoCmd (do command) object in the Access object model exposes commands on Access menus and other capabilities to VBA. One of the simplest methods exposed by the DoCmd object is the Beep method. When applied, it simply makes Access sound the default beep sound. In your own code, you might use DoCmd.Beep to sound a beep when a form opens — or when the user makes a mistake — to call attention to the screen.

You can try out the DoCmd.Beep method right now via the Immediate window. Just type the following line into the Immediate window and then press Enter:

DoCmd.Beep

The Beep method is straightforward in that it's just one word: beep. Some methods support one or more arguments, acting as placeholders for information that you want to pass to the statement later. For example, one of the many methods offered by the DoCmd object is OpenForm. The syntax for using the OpenForm method of the DoCmd object looks like this:

DoCmd.OpenForm FormName, [View], [FilterName],

[WhereCondition], [DataMode], [WindowMode], [OpenArgs]

The first argument, FormName, is required. The remaining arguments, enclosed in brackets, are all optional. (As in the syntax charts you see in Help and the Quick Info screen tip, we use square brackets to indicate optional parameters in this book.) For example, if the current database contains a form named Customers, the following VBA statement opens it:

DoCmd.OpenForm "Customers"

Multiple arguments must be separated by commas. For example, the following VBA statement uses the View argument and the acDesign constant to open the form named OpenForm in Design view:

DoCmd.OpenForm "Customers", acDesign

Single versus double quotation marks

VBA uses the same syntax as Access expressions, where literal numbers are just typed as numbers (like 10), but literal text and dates must be delimited (surrounded by characters). Literal dates need to be enclosed in # characters. For example, the date December 31, 2007, needs to be expressed as #12/31/07# in an Access expression as well as in VBA. Literal text, like the name Smith, needs to be enclosed in either double quotation marks ("Smith") or single quotation marks ('Smith').

When the syntax of a VBA statement requires its own quotation marks, like the WhereCondition argument in DoCmd. OpenForm, the literal needs to be contained within the entire argument. For example, the following entire expression StartDate = #12/31/07# is an entire WhereCondition, enclosed within quotation marks to satisfy the syntax rules:

It gets tricky when the expression itself contains quotation marks because you need one pair to delimit the literal text and another pair to delimit the entire expression. You need to use single quotation marks for one pair and double quotation marks for the other pair. Otherwise, Access can't tell which quotation mark belongs to which chunk of text. For example, if the WhereCondition is LastName = "Smith" and that whole part needs to be in quotation marks, the following statement does network:

"LastName = "Smith" "

The reason it doesn't work is that the computer always reads one character at a time, from left to right. When the computer "sees" the first quotation mark, to the left of LastName, it "knows" that this is the start of some chunk of text enclosed in quotation marks. The computer keeps reading one character at a time, left to right. When it then sees the double quotation mark in front of Smith, it "thinks" that's the end of the whole chunk and then gets all befuddled and stops working when it sees more characters after that second quotation mark.

Alternating the single and double quotation marks, as follows, solves the problem:

When the computer reads the preceding line, one character at a time from left to right, it "sees," as always, the first quotation mark to the left of LastName. When it gets to the first single quotation mark before Smith, there's no confusion with the first double quotation mark. Access just "knows" that this single quotation mark is the start of some new literal within the current chunk of text.

As the computer continues from left to right, it sees the second single quotation mark as the end of the first one that started with Smith. By the time it gets to the second double quotation mark, it really isat the end of the whole chunk of text, so it doesn't get befuddled and fail.

For more information on using literals in Access, refer to a book about Access or search the Access Help (not the VBA Help) for the keyword literal. Optionally, you can search the Access Help for the word expressions and get more information about literal values from the Help page titled About Expressions.

If you want to use multiple arguments and skip over others, you need to type enough commas to get to the right place. For example, the optional WhereCondition argument lets you specify records to display in the form.

The following VBA statement opens the Customers form, displaying only records that have CA in the field named State:

DoCmd.OpenForm "Customers", , ,"[State]='CA'"

The empty commas leave the optional View and FilterName arguments empty, ensuring that [State] = 'CA' is passed as the fourth argument,

WhereCondition.

0 0

Post a comment