Visual Basic for Applications Tips #26


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

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

Proudly presents:
Visual Basic for Applications

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


*1. CONTROLLING RANDOM VALUES                   
          
Many tasks call for random values, and you can easily provide these 
values using the Rnd() function. This function always returns a value 
between 0 and 1 (in other words, a decimal value).  

If you need to limit random values to a particular range, you can do 
so using the simple formula 

Int((highest - lowest + 1) * Rnd + lowest)  

where highest and lowest represent the top and bottom values in the 
range.


*2. TRIMMING DATA ENTRY--PART 1 OF 2                   
          
Many people trim imported data because it often drags along unwanted 
space characters. Before committing data to a field in your table, run 
it by one of the three trimming functions: 
 - Trim: Removes both leading and trailing spaces. 
- RTrim: Removes trailing spaces. 
- LTrim: Removes leading spaces.


*3. TRIMMING DATA ENTRY--PART 2 OF 2                   
          
In our previous tip, we recommended you trim imported data using Trim, 
Rtrim, or LTrim before committing your data to a field. Imported data 
isn't the only data that might contain unnecessary space characters. 
Sometimes people enter space characters during the data entry process 
without even realizing it. For instance, a data entry operator might 
rest his or her thumb a bit too heavily on the Spacebar. For this 
reason, you might want to trim string entries by attaching Trim, 
RTrim, or LTrim to each control's update event.


*4. TESTING ERR                   
          
Many developers use the Err object to return the current error. In 
fact, using Err is the same as using the statement 

Err.Number 

since the Number property is the Err object's default.  

When working with OLE servers, remember that they often return 
negative error values, so any error-handling code should accommodate 
negative error values. (Actually, these values are unsigned longs, but 
VBA doesn't support this data type.)


*5. ABOUT FILE.MOVE                   
          
The new File object allows you to move files easily using its Move 
method. Simply use the syntax  

oFile.Move destination 

where oFile is the File object and destination identifies where you're 
moving the file. When using this method, keep in mind the lack of any 
rollback capability. This means that an error--in particular a fatal 
error (like a power outage)--could disrupt this process. You won't 
lose the file, but the method might fail to move the file properly.


*6. WHEN TO ELSE OR ELSEIF                   
          
The block If allows any number of conditional checks. First, the If 
statement itself specifies a condition. If that condition isn't met, 
control will pass to an ElseIf or Else clause. (You can also omit both 
and simply end the statement with an End If clause.) How do you know 
which to use? If you want to specify additional conditions, use ElseIf 
in the form 

If condition1 Then 
... 
ElseIf condition2 Then 
      ... 
ElseIf condition3 Then 
      ... 
End If 

You'll use the Else clause when you want to catch what falls through 
the cracks. That's because the Else clause doesn't accept a condition. 
For instance, we could add an Else clause to our example:  

If condition1 Then 
... 
ElseIf condition2 Then 
      ... 
ElseIf condition3 Then 
      ... 
Else 
      ... 
End If 

Any value or expression not caught by condition1, condition2, or 
condition3 will be handled by the Else statement.  

The thing to remember is that you can't add an ElseIf clause after the 
Else clause. Every If statement can have numerous ElseIf clauses, but 
only one Else.


*7. WHITE PAPERS FOR DATA ENGINE                   
          
If you're an Access 2000 user, you probably know that this latest 
version offers more choices than ever for working in a client/server 
environment. To help you choose between the new Data Engine (MSDE), 
the Jet, or Access 2000 features, read "The Data Engine Links to the 
Access 2000 Data Engine Options" white paper. You'll find this 
up-to-date information at  

http://www.microsoft.com/office/access/MSDtaEng.htm


*8. CALLING EVENT PROCEDURES                   
          
Most VBA applications are loaded with event procedures. Occasionally, 
you may want to reuse these event procedures at other times--and you 
can, simply by calling the event. For instance, you may want to run a 
command button's procedure from another event or procedure. When this 
happens, you'd call the event in the form 

commandbuttonname_Click 

Calling an event procedure in this fashion isn't wrong. However, you 
might consider writing a function procedure and calling it from the 
button's Click event and any other procedures or event. We find this 
arrangement a bit easier to debug and maintain.


*9. DO IT AT LEAST ONCE                   
          
The Do loop repeatedly executes specific codes. Unless you supply 
either the Until or While keywords, the loop will execute 
indefinitely. Most of us are used to seeing either conditional keyword 
expressed at the beginning of the loop in the form 

Do Until  
      ... 
Loop 

or  

Do While 
      ... 
Loop 

Placing the keyword at the beginning of the loop has the sometimes 
unwanted limitation of inhibiting the loop altogether. If you need to 
execute the loop at least once, you can position the keywords at the 
end of the loop in the form 

Do 
      ... 
Loop Until 

or 

Do 
      ... 
Loop While 

Using this form, VBA will execute the loop's code, at least once, 
regardless of the current conditions.


*10. A QUERYDEF BY THE SAME NAME                   
          
The Access QueryDef object is a useful tool for automating queries, 
but a QueryDef object stores a query definition--not the results. The 
results are held in a Recordset object.  

When you want to save a QueryDef, you give it a name when you first 
create it. For instance, the following code declares and then sets a 
QueryDef object, which we'll save as "qryExample": 

Dim qdf As QueryDef 
Set qdf = CurrentDb.CreateQueryDef("qryExample") 

When creating QueryDef objects, be careful about the names you give 
them. If you try to create a QueryDef object using a name already 
given to an existing query, Access will return a run-time error.
