Visual Basic for Applications Tips #19


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

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

Proudly presents:
Visual Basic for Applications

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

*1. OPENING A WORD DOCUMENT                 
        
You probably know that you can launch Word from inside another Office 
application. There's a lot of theory to the necessary code--accessing 
the right objects, and so on. If you'd just like to do the work and 
you're not interested in the science behind the task, you can use the 
following procedure to open a Word document: 

Sub OpenDocument() 
Dim wrd As Object 
Set wrd = GetObject(, "Word.Application") 
wrd.Visible = True 
Documents.Open "documents complete path" 
End Sub 

This procedure will work with just a wee bit of tweaking--simply 
specify your document's complete pathname in the Documents.Open 
statement.


*2. A TERMINAL DO LOOP                 
        
An easy way to advance through a recordset is to use the Do...Loop 
statement. For instance, the following Do...Loop stops at each record 
in a recordset named rst and adds the string "OK" to the Check field. 

Do Until rst.EOF 
     .Edit 
     !Check = "OK" 
     .Update 
     .MoveNext 
Loop 

(If you're using ADO, you can omit the Update method.) 

One problem that we see occasionally is the accidental omission of the 
MoveNext statement. The Do...Loop statement won't automatically select 
the next record at the end of its task. You must do that by including 
the MoveNext statement. Without that statement, your statement will 
run forever.


*3. OUTLOOK DATES                 
        
VBA Date/Time fields or variables can handle a large range of date 
values. However, Outlook forms are a little more limited. A date on an 
Outlook form must fall between April 1, 1601, and August 31, 4500 
(inclusive of both dates). Fortunately, this isn't a problem for most 
users. Unfortunately, we don't know of an easy workaround.


*4. BYREF ERROR                 
        
If you get a ByRef Value Type Mismatch error, you might know what the 
error is but have a hard time finding its cause. Generally, this 
message means that a passed argument doesn't match the variable's 
declared data type. The most logical place to begin your search is in 
your Dim statement. Make sure that the variable's declared data type 
matches the argument's data type in the calling procedure. The 
following procedure declares the variable i as an Integer, then passes 
i to the procedure DoThisNow(). Unfortunately, DoThisNow() expects a 
string--as you can see in the function's name statement. When MyWork() 
tries to pass DoThisNow(), the integer variable DoThisNow() will 
return a mismatch error. (Compiling this code should catch the error.) 

Function MyWork() 
Dim i as Integer 
DoThisNow i 
End Function 

Function DoThisNow (arg As String) 
End Function 

The only way to resolve this error is to declare i as a String or 
Variant or to change the argument's declaration to an Integer or 
Variant.


*5. ONE DIFFERENCE BETWEEN COLLECTIONS AND CONTAINERS                 
        
If you want to loop through all the forms in your database, be sure to 
specify the Forms Container and not the Forms Collection. Collections 
contain only open forms. This is true of all Collections and 
Containers. The following procedure will loop through all the forms in 
the current database, open that form, change a property, and then 
close the form. You'll want to use this procedure when you want to 
change the same property in all your forms or anytime you want access 
to all the forms in your application, not just the opened forms. 

Function ChangeProperty() 
Dim db As Database, cnt As Container, doc As Document 
Dim frm As Form 
Set db = CurrentDb 
Set cnt = db.Containers!Forms 
For Each doc In cnt.Documents 
DoCmd.OpenForm doc.Name, acDesign 
      ...property changing code... 
DoCmd.Close acForm, doc.Name, acSaveYes 
Next doc 
End Function 

Our next tip will offer an updated version of this procedure for 
Office 2000 users. (This procedure will work in Access 2000.)


*6. ABOUT ALLFORMS                 
        
In our previous tip, we talked about the difference between 
Collections and Containers in respect to open objects. Collections 
contain only open objects while Containers contain all the respective 
objects in the application. However, there's a new kid in town--the 
AllForms collection. This collection contains an AccessObject object 
for each form in the CurrentProject or CodeProject object. 

Function ChangeProperty() 
Dim obj As AccessObject 
Dim dbs As Object 
Set dbs = Application.CurrentProject 
For Each obj In dbs.AllForms 
DoCmd.OpenForm obj.Name, acDesign 
      ...property changing code... 
DoCmd.Close acForm, obj.Name, acSaveYes 
Next obj 
End Function


*7. HOW TO REDIM                 
        
We've offered several tips about arrays over the past few months, 
including instructions for changing an array's dimensions. 
Specifically, you can declare an array and then use the ReDim 
statement to set the array's dimensions. You might take this 
explanation to mean that you can change an array's dimensions, but 
that's not quite true. Using ReDim, you can only declare a dynamic 
array's dimensions--you can't ReDim a fixed array. 

Let's revisit the difference between a fixed and a dynamic array. 
First, a fixed array specifies the array's dimensions during the 
declaration process using the form 

Dim arr(1 To 3) As Integer 

When you declare an array in this manner, you can't change its 
dimensions. On the other hand, a dynamic array doesn't specify the 
dimensions--it simply declares the array in the form 

Dim arr() As Integer 

Once you know your array's dimensions, you use ReDim to specify those 
limits in the form 

ReDim arr(1 To 4) As Integer 

In addition, you can change the dimensions for a dynamic array simply 
by using the ReDim statement again.


*8. A SQL DEBUGGING TRICK                 
        
There's an easy way to debug your SQL statements--simply copy the SQL 
statement from its module to a query SQL window and run it. Chances 
are the query design grid will return a much more specific error 
message that will better help you pinpoint the error. Unfortunately, 
concatenated variables kind of put a damper on this debugging method, 
because you must replace the concatenated variables with real data 
before running the statement in the SQL window. Sometimes this is a 
bigger nuisance than just debugging the statement on your own. 
Fortunately, help is available--if you're willing to plan a little 
better. 

When executing a SQL statement in VBA, always assign the statement to 
a variable. Then, immediately after the defining statement, include a 
Debug.Print statement that prints the SQL statement to the Debug 
window. When there's an error in the statement, simply open the Debug 
window and paste that version of the statement to the query SQL 
window. The reason? The Debug version won't contain the concatenated 
components. 

To demonstrate, let's look at a quick example. The statement 

"SELECT * INTO tblNumberOfLocations FROM tblMinimum WHERE Gen = 'Gen" 
& strYear & "';" 

selects all the fields from tblMinimum where the Gen field equals the 
concatenated result of the string 'Gen' and the value of the variable 
named strYear. The Debug.Print statement in SQLTest() prints a 
concatenated version of the SQL statement in the Debug window. 

Function SQLTest() 
Dim db As Database, strSQL As String, strYear As String 
Set db = CurrentDb 
strYear = "1" 
strSQL = "SELECT * INTO tblNumberOfLocations FROM tblMinimum WHERE Gen 
= 'Gen" & strYear & "';" 
Debug.Print strSQL 
db.Execute strSQL 
End Function 

If there's an error in the statement, simply open the Debug window and 
copy the statement 

SELECT * INTO tblNumberOfLocations FROM tblMinimum WHERE Gen = 'Gen1'; 

to the query design grid's SQL window and run it. You won't have to 
modify a thing first.


*9. THE VISIO OBJECT MODEL                 
        
If you work with Visio, you're probably interested in learning more 
about the application's object model. You can view a graphical 
representation of the Visio object model at 

http://msdn.microsoft.com/voices/news/object.asp 

From this page, you can download the graphic file. If you'd rather 
view the model on screen, you can click the graphic for a larger view.


*10. MAIL MERGE SOURCE                 
        
Microsoft Word handles mail-merge tasks with ease. However, you might 
like to know what's going on behind the scenes. The Fieldnames 
Collection tracks the field names in your mail merge. You'll find this 
collection in the MailMerge DataSource object. You'll use the 
ActiveDocument object in the form 

ActiveDocument.MailMerge.DataSource.FieldNames 

If you want to loop through all the fields, you can use a simple 
For...Each statement similar to the following: 

Dim fld as Variant 
     For Each fld In ActiveDocument.MailMerge.DataSource.FieldNames 
Msgbox fld 
Next fld
