Visual Basic for Applications Tips #39


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

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

Proudly presents:
Visual Basic for Applications

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


*1. ABOUT RESET

by Susan Sales Harkins

If you're using the Open and Close statements to work with 
disc files, you should know about the Reset statement. 
Normally after opening a file, you use the Close statement to 
close those files when you're done with them. However, you 
should consider including an emergency procedure that uses the 
Reset statement to close all the open files at once. 

Besides closing all the open files, Reset will also write the 
contents of the current file buffer to disk before closing the 
files. Just remember that Reset works only with files opened by 
the Open statement.


*2. WARNING ABOUT WITH                     
 
by Susan Sales Harkins  
             
The With statement has been around for a while now, and most of
us are taking full advantage of its functionality. We do have 
one word of warning when including the With statement in a 
procedure. Don't include code that branches from the With block.
Likewise, don't use code that flows into the With block. Doing 
either will cause VBA to execute a With statement without the 
End statement, or vice versa, and result in an error.


*3. THE YEAR IN QUESTION

by Susan Sales Harkins

The Year() function returns the year portion of a date as a 
Variant or Integer data type. What you might not realize is 
that this function will accept two-digit year components. For 
instance, the function 

Year(#11-27-00#) 

will return the value 2000. 

There are a few rules to remember when passing two-digit year 
components:

- All two-digit year values equal to or greater than 30 are 
  considered part of the 20th century--for example, 30 returns
  1930, 57 returns 1957, and 99 returns 1999.

- All two-digit year values less than 30 are considered part of
  the 21st century--for example, 00 returns 2000, 05 returns
  2005, and 29 returns 1029.


*4. EOF STUFF

by Susan Sales Harkins

VBA has two EOF nametags, and confusing them is easy:

- The EOF() function tests the position of the file pointer in a
  file opened with the Open statement. 
- The Recordset object property, EOF, returns 0 or -1 to indicate
  the state of the record pointer in a recordset. If the cursor
  is at the EOF (end of file) position, the property
  is -1 (True).


*5. MORE EOF() TRIVIA

by Susan Sales Harkins

When working with the EOF() function, you're used to thinking 
in terms of True and False. What you might not realize is that 
this function isn't a true Boolean data type. In fact, this 
function returns an Integer that's either -1 (True) or 0 
(False). However, this doesn't pose a problem or limit your code
in any way, because VBA recognizes 0 and -1 as Boolean values 
even when you're working with non-Boolean data types.


*6. MOVING RECORDSETS

by Susan Sales Harkins

Working with recordsets in Excel isn't your normal 
run-of-the-mill VBA task. If you need this functionality, check
out the code on Dev Ashish's site at 

http://home.att.net/~dashish/modules/mdl0035.htm 

You'll find three examples of using Excel's CopyFromRecordset 
method to copy records using Automation.


*7. SEND REPORT TO PRINTER  
  
by Susan Sales Harkins  
  
When opening an Access report, you probably use the DoCmd 
object's OpenReport method in the form 

DoCmd.OpenReport reportname, view, filter, wherecondition 

where reportname is the valid name of a report in the current 
database; view is one of three intrinsic constants--acNormal, 
acDesign, or acPreview; filter identifies a valid query; and 
wherecondition is a SQL WHERE clause. All arguments determine 
how and what is displayed in the resulting report. 

By default, VBA assumes the constant acNormal if you omit this 
argument, which means Access prints the report automatically. 
If you want only to view the report, use the constant acPreview.


*8. CONSIDER FUTURE RESERVED WORDS

by Susan Sales Harkins

Avoiding reserved words is tricky because Microsoft 
continually adds new words to the list with each upgrade. That 
means a field or table name that once worked can suddenly cause 
problems when you convert to the next version. If Access 
suddenly balks at a table or field name it accepted before, 
check for a reserved name. Try a simple test--change the field 
or table name and see how well it works. If the problem 
disappears, you'll know you have to change a field or table's 
name permanently.

Another consideration when naming tables and fields is whether 
your database may someday be upsized to SQL, since this language
has its own list of reserved words. If upgrading is a 
possibility, you'll want to avoid these reserved words when 
naming your fields and tables, even when they're not 
reserved in Access. 


*9. POPULATING LIST BOX WITH QUERY RESULTS  
  
by Susan Sales Harkins  
  
Richard L. sent in this useful DAO procedure for populating a
list or combo box with the results of a query. Simply attach the
procedure to the appropriate event--the control's GotFocus event
or the form's Current event: 

Dim rs As Recordset 
Dim strSQL As String 
Dim strResult As String 
Set rs = CurrentDb.openrecordset("queryname", dbOpenDYNASET) 
strResult = "" 
If rs.RecordCount <> 0 Then 
    Do Until rs.EOF 
        If strResult <> "" Then strResult = strResult & ", " 
        strResult = strResult & rs("fieldname") 
        rs.MoveNext 
    Loop 
End If 
rs.Close 
Set rs = Nothing 
Me!List0.RowSourceType = "Value List" 
Me!List0.RowSource = strResult 

Be sure to update queryname and fieldname accordingly, where 
queryname identifies the query results you're after and 
fieldname is the field that contains the data you want to list 
in your control. 

If you're using ADO, try the following code: 

Dim cnn As Connection 
Dim rs As New Recordset 
Dim strSQL As String 
Dim strResult As String 
Set cnn = CurrentProject.Connection 
rs.Open "queryname", cnn, adopendynaset, adLockOptimistic 
strResult = "" 
If rs.RecordCount <> 0 Then 
    Do Until rs.EOF 
        If strResult <> "" Then strResult = strResult & ", " 
        strResult = strResult & rs("fieldname") 
        rs.MoveNext 
    Loop 
End If 
rs.Close 
Set rs = Nothing 
Me!List0.RowSourceType = "Value List" 
Me!List0.RowSource = strResult


*10. FOLLOWING EVENTS

by Susan Sales Harkins

Confused about the order that events are triggered? Welcome to 
the club. Perhaps the easiest way to follow the trail of events
is to add a Debug.Print statement to each event in the form 

Debug.Print "event"

Then, run the form or report in question. When you're done, open
the Immediate window by pressing Ctrl-G and review the results--a
list of each event triggered in the order those events were 
triggered by your actions. Of course, if you don't trigger an 
event, it won't appear in the list.
