Visual Basic for Applications Tips #33



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

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

Proudly presents:
Visual Basic for Applications

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


*1. IF FINDNEXT DOESN'T WORK                 
        
You can use the Recordset object's FindNext method to locate 
records that match criteria. Simply use the form 

rst.FindNext criteria 

where criteria is a SQL WHERE clause without the WHERE. For 
instance, you might use the statement 

rst.FindNext "[LastName] = 'Smith'" 

to find the next record with the string Smith in the 
LastName field. 

However, occasionally these statements don't work, and you can 
waste a lot of time trying to figure out what's wrong with the 
criteria component. Most likely, there's nothing wrong with your 
criteria component. Rather, you've not defined the correct 
recordset type. You need to define the recordset as a dynaset 
using the form 

db.OpenRecordset("tablename", dbOpenDynaset) 

If you omit the type argument for your statement, Access opens a 
table-type recordset (dbOpenTable) unless you specify a linked 
table or a query. In those cases, the default is the 
dynaset-type. Before you waste a lot of time trying to restate 
your criteria expression, check the recordset's type.


*2. THE ADO FIND METHOD                 
        
DAO's Find method can evaluate multiple criteria connected with 
the And operator, but the ADO Find method can't. When you need 
to search on multiple criteria in ADO, use the Filter property 
as shown here: 

Function MultiSearch() 
Dim cnn As New ADODB.Connection 
Dim rst As New ADODB.Recordset 
Set cnn = CurrentProject.Connection 
rst.ActiveConnection = cnn 
rst.Open "Products", cnn, adOpenKeyset, adLockOptimistic 
rst.Filter = "SupplierID = 15 AND CategoryID = 2" 
MsgBox rst!ProductID 
rst.Close 
End Function 

DAO also has a Filter property, but it works differently from 
the ADO property. The DAO Filter property works only on 
subsequent recordsets; the ADO Filter property will filter the 
current recordset.


*3. UNDERSTANDING NULLS                 
        
Don't let Null values intimidate you--they can be confusing, even 
for the experts. Fortunately, sticking to a few basic principles 
should help you out. A Null value indicates one of two conditions: 

- The value is missing or unknown. 
- The value doesn't apply. 
 
When a value is missing or unknown, it means the information may 
exist, but we simply don't know it. That doesn't mean it does 
exist; we simply don't know. Occasionally, the information 
doesn't apply to a particular record and you must leave a field 
blank. Let's suppose you've got a table of phone numbers and two 
fields are blank. In one case, you know the person has a phone; 
you just haven't been able to acquire the number yet. Eventually, 
you may fill that field. In the second case, you know the person 
doesn't have a phone; that field will remain empty (unless the 
person gets a phone). In each case, the field returns a 
Null value.


*4. MORE ON NULLS                 
        
In our last tip, we talked about Null values as values that are 
unknown or don't apply. We used phone numbers to illustrate our 
point. If the phone number is unknown or if someone doesn't have 
a phone, you'll leave the phone field blank for that person. 

However, if you know the person has no phone, you might want to 
consider an alternative to Null--to avoid confusion. When there 
is no phone (versus no phone number), consider using an empty 
string ("") or an actual string value, such as "no phone" or 
"N/A", so you can readily tell the difference between an unknown 
phone number and no phone.


*5. MORE ON RECORDSETS                 
        
We've already discussed Recordset objects a bit this month. But 
there's one more detail you should know about. The RecordCount 
property requires a Long data type. If you assign anything else, 
your code will return an error. The problem is, this necessity 
simply isn't intuitive, but the explanation makes sense. An 
Integer data type handles values from -32,678 to 32,767, but a 
table can store many more records than 32,767.  

By enforcing the Long data type, VBA ensures there will be no 
problems returning the correct record count.


*6. RUNNING AN EVENT WITH THE EVENT                 
        
When you're adding VBA code to an event, you'll probably want 
to test the code often. This can be a nuisance if you have to 
execute the event each time you want to test the subprocedure. 
Fortunately, you don't have to. When you're ready to test event 
code, simply press F5. This button has the same effect as 
choosing Go/Continue from the Run menu. You can also click the 
Go/Continue button on the Visual Basic toolbar.


*7. PROPERTY OR METHOD?                 
        
If you're fairly new to VBA, you may be a little confused about 
properties and methods, since the syntax for both is similar. 
By that, we mean you separate a property and a method from its 
object with the dot identifier (.). For instance, to set a 
property, you'd use the form 

object.property 

To execute a method, you'd use the form 

object.method 

They look pretty much the same, don't they? However, their 
functions are very different. 

Properties represent an object's attributes, and methods 
represent that object's behavior. In other words, if you want 
a font to be blue, you'd set the object's Fore or Font property 
to a value that represents blue in the form 

cmdButton.Fore = 16711680 

Methods, on the other hand, generally execute some behavior or 
action. For instance, the Requery method updates the data 
underlying a specific form (or object). Requerying a form might 
require a statement similar to 

frm.Requery


*8. FASTER SEARCHES                 
        
You probably know that you can combine the LIKE operator with 
the * character to find approximate matches. For instance, all 
of the following are legitimate search strings: 

LIKE "rabbits" 
LIKE "rab*" 
LIKE "*abbits" 
LIKE "*abb* 

and will match the string rabbits. However, LIKE "rabbits" and 
LIKE "rab*" are both faster than the other two examples. That's 
because Access sets a temporary index when you use the LIKE 
operator with an asterisk at the end of a string. When possible, 
use the asterisk character at the end of a LIKE search string.


*9. THE FASTEST COUNT                 
        
You probably know that you can count records in a recordset by 
using the RecordCount property. However, to do so, you must 
populate the entire recordset by executing the MoveLast method. 
If your recordset is large, this is inefficient and unnecessary, 
because the SQL Count function is faster. For instance, 
the procedure 

Function GetCount() 
Dim db As Database, strSQL As String, rst As Recordset 
Set db = CurrentDb 
strSQL = "SELECT Count(*) FROM table3" 
Set rst = db.OpenRecordset(strSQL) 
Debug.Print rst(0) 
End Function 

is faster than 

Function GetCount() 
Dim db As Database, rst As Recordset 
Set db = CurrentDb 
Set rst = db.OpenRecordset("table", dbOpenDynaset) 
rst.MoveLast 
Debug.Print rst.RecordCount 
End Function 

In a small database, you may not notice the difference. However, 
if you have thousands of records, you should definitely notice 
an improvement.


*10. THE TWO COUNTS                 
        
In our last tip, we showed you how to use the SQL Count(*) 
statement to return the number of records in a recordset quickly. 
If you're tempted to replace the * character with a field name 
because you think referencing a specific field will be faster, 
don't. You see, the Jet has special optimization rules for the * 
character, and it actually runs faster than specifying a field 
name. Specify a field name in a SQL Count function only when you 
need a count of that particular field.
