Seeking help with properties and methods

When you're typing VBA statements that involve objects, properties, and methods, you get all the usual quick-reminder Help onscreen. You can always get more help, though. For example, as soon as you type DoCmd. (remember to type the period as per the syntax for DoCmd), you see a menu of methods that DoCmd provides, as in Figure 5-7. It's a lengthy menu, so you have to use the scroll bar to see all the available methods of the DoCmd object.

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, 2005 needs to be expressed as #12/31/05# 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/05# 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 isLastName = Smith and that whole thing needs to be in quotation marks, the following statement will not work:

"LastName = "Smith" "

The reason why it won't work is that the computer always reads one character at a time, left to right. When it "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 makes. It 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 stuff 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 left to right, it "sees" the first quotation mark to the left of LastName, as always. 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 through left to right, it "sees" the second single quotation mark as the end of the first one that started Smith. By the time it gets to the second double quotation mark, it really is at 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 on Access or search Access's Help (not VBA's Help) for the keyword literal. Optionally, you can search Access's Help for the word expressions and get more information about literal values from the Help page titled About Expressions.

After you type a method name and a blank space, you see the entire syntax for the method in a Quick Info screen tip, as in Figure 5-8. For the lowdown on how to read Quick Info tips (what all the brackets, bold, italics, and so on mean), check out Chapter 3.

Figure 5-7:

Menu of valid entries for the first word after DoCmd.

Figure 5-7:

Menu of valid entries for the first word after DoCmd.

Figure 5-8:

Get Quick Info syntax help.

Figure 5-8:

Get Quick Info syntax help.

As always, the quick reminders don't provide any detail. When you're first learning, frequent visits to the VBA Help are necessary. There are far too many objects, properties, methods, and keywords to list them all in this book (or even a 1,000-page book). The best skill that you can learn in VBA is how to get exactly the help you need, when you need it.

Fortunately, all the Help methods that work with other VBA keywords work with objects, properties, and methods as well. For example, for help with the OpenForm method of the DoCmd object, you can do the following:

1 In the Code window: Type DoCmd.OpenForm into the Code window, double-click OpenForm to select it, and then press F1 for Help.

1 In the Object Browser: Find DoCmd in the left column, click OpenForm in the right column, and click the Help (?) button in the Object Browser.

As always, a Help window pops up, as in the example shown in Figure 5-9, where you can get more information on the OpenForm method.

To summarize (once again), an Access database is a collection of many objects. Most objects have properties (characteristics) and methods (acts that can be performed on the object) that you can manipulate through VBA. To refer to an object, property, or method from your VBA code, you must use exact names and syntax provided by the application's object model.

Trying to figure out how to write a line of new code, or modify an existing line of code just by guessing, is likely to turn into an exercise in hair-pulling frustration. Nobody was ever born already knowing VBA syntax, and even the experts have to make frequent visits to Help to get specific information when they need it. The Help in VBA is your best friend. Learn to use it well!

Selected keyword Help with selected keyword

Selected keyword Help with selected keyword

Figure 5-9:

Help for the OpenForm method.

Figure 5-9:

Help for the OpenForm method.

0 0

Post a comment