Visual Basic for Applications Tips #17


----------------------------------------------

TipWorld - http://www.tipworld.com
The Internet's #1 Source for Computer Tips, News, and Gossip

Proudly presents:
Visual Basic for Applications

----------------------------------------------

*1. WHEN NOTHING ISN'T NOTHING                 
        
Setting an object to Nothing once you're done with it is a good idea 
because it frees up memory. However, if you use the New keyword in 
your declaration statement, as in 

Dim obj As New objecttype 

you can't set that object variable to Nothing. Well, you can, but VBA 
will immediately create a new instance.  

The solution takes an extra step, but it's worth it in the long run. 
Don't use the New keyword in the declaration statement, as in 

Dim obj As objecttype 
Set obj = New objecttype 

Using this method to declare and define your object variable will 
allow you to terminate it later.


*2. USING SQL TO QUERY FOR THE FIRST n RECORDS                 
        
You probably know that you can use a query's Top property to return 
only a specific number of records, instead of all the records. For 
instance, after sorting all your orders by the total value, you may 
want to select only the top ten sales. To do so, you'd specify 10 as 
the query's TOP property. 

You can do the same thing with SQL using the TOP keyword in the form 

SELECT TOP n field(s) FROM table 

More than likely, you'll need to also include an ORDER BY clause. For 
instance, to select the top ten best sales from a table named 
tblSales, you might use a statement similar to the following: 

SELECT TOP 10 * FROM tblSales ORDER BY TotalOrder 

where TotalOrder is the name of the field with the total sales value 
for each order. If you don't specify an argument for ORDER BY, SQL 
defaults to ascending order. 

When using the TOP keyword, don't confuse it with a value, as in the 
greatest or highest values. The keyword simply returns the first 
records in the query's results. 

You might be wondering how to return the last n records in a query, 
but there isn't one. The trick is to use the TOP keyword and sort the 
records in the opposite order--usually that means sorting in 
descending order. As a result, the records are reversed. Consequently, 
the TOP query still returns the first records in the query result. 

If you decide to use SQL to return the last records in a query, use a 
statement in the form 

SELECT TOP n field FROM table ORDER BY table.field DESC;


*3. ADDING CONTROL TIPS WHEN THE FORM LOADS                 
        
You can use a form's (or userform's) Load event to add or modify a 
control's tip text. Depending on the circumstances, you may want to 
modify all of the form's controls or just one. To do so, simply add a 
statement in the form 

Me!controlname.ControlTipText = "new control tip text" 

to the form's Load event. For instance, if you want a control named 
cboNames to display the following text as its control tip, "Choose a 
name," you'd use the following statement 

Me!cboNames.ControlTipText = "Choose a name"


*4. NEW TOOLBARS IN THE VBE                 
        
The most recent versions of the Visual Basic Editor sport new 
toolbars. Now there's a Debug toolbar, which includes many commands 
that were on the Standard toolbar. The Edit toolbar includes commands 
for writing better code. A UserForm toolbar has quick formatting 
tools. To open one of these new toolbars, simply right-click any open 
toolbar and make a choice from the context menu.


*5. ODBCDIRECT                 
        
DAO 3.5 offers a new client/server connection mode. You can use this 
connection--ODBCDirect--to establish a connection with an ODBC data 
source, without hitting the Jet engine. There are a few other 
advantages when using ODBCDirect: 
 - Your code runs faster and more efficiently since you don't load the 
Jet. 
- You can run asynchronous queries. 
- Batch changes are cached locally and sent to the server as a single 
batch.


*6. MISSING OBJECT TYPES                 
        
If you've converted any DAO code from Access 97 to Access 2000, you 
may have run into trouble with the Database and Recordset object 
types, because they've both been replaced in ADO. That means the 
simple statements 

Dim dbs As Database 
Dim rst As Recordset 

would both return errors if you try to run them in Access 2000. 
Instead, you'll want to use the ADODB object in the form 

Dim dbs As ADODB.Connection 
Dim rst As New ADODB.Recordset 

Now, if you want to keep your DAO code or use DAO code in your Access 
2000 modules, you should reference a DAO library. In the VBE, choose 
Tools, References; select a Microsoft DAO Object Library; and then 
click OK. Once you reference the correct library, the module will work 
with the DAO object references Database and Recordset.


*7. USING SET                 
        
When you declare an object variable, you must also use the Set 
statement to define that variable. Failing to do so will produce an 
error when you compile your code. Since you don't have to use Set with 
all variables, it can be easy to forget about Set when working with 
object variables. One way to help you remember is to use the obj 
prefix when you name all your object variables. The prefix will help 
you remember to use the Set statement when you define the variable.


*8. WHEN YOU FORGET SET                 
        
In our previous tip, we discussed an easy way to help you remember to 
use the Set statement when defining object variables--use the obj 
prefix when naming all object variables. If you do forget the Set 
statement, VBA will return a rather unhelpful error message: Invalid 
use of property. Anytime you see this property and there's an equal 
sign in the offending line of code, you've probably run into a 
forgotten Set statement.


*9. VBSCRIPT AND OUTLOOK                 
        
Outlook forms support VBScript. However, they don't support the 
intrinsic Outlook constants. You can still use them, though, by 
explicitly declaring those constants. Or if you plan to use the 
constant only once, simply use the constant's literal value. 

If you'd like to learn all the intrinsic constants and their literal 
values, use the Object Browser.


*10. OUTLOOK SHORTCUT                 
        
When referring to a control in a form, you usually reference the form 
or use the Me identifier--even when the code is running in the form's 
module. For instance, if you want to reference a control named 
txtNames in a form named frmEmployees, you'd use the form 

frmEmployees!txtNames 

or 

Me!txtNames 

If you're adding code to an Outlook form, you can omit the form 
reference and simply refer to the control, as long as you're working 
in that form's module. If the code is in a separate module or behind 
another form, you must include the form's name in the reference.
