Visual Basic for Applications Tips #40


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

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

Proudly presents:
Visual Basic for Applications

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


*1. RETURNING THE USER'S NAME

by Susan Sales Harkins

A frequent reader question is how to return the user's network
login identification (user's name). There's a quick and easy API
that will handle this task for you. Simply enter the declaration
statement in a module's General Declarations section. Then, add
GetName() to the module and call it as required:  

Declare Function WNetGetUser Lib "mpr.dll" Alias "WNetGetUserA" _  
(ByVal lpName As String, ByVal lpUserName As String, _  
lpnLength As Long) As Long  

Function Getname() As String  
Dim strUserName As String  
strUserName = Space(255)  
WNetGetUser "", strUserName, 255  
Getname = Trim(strUserName)  
End Function


*2. API SITE

by Susan Sales Harkins

Some of us avoid APIs because we don't like to work that hard.
Fortunately for us, there's a great site that lists many of the
most commonly used APIs. You'll find a list of the supporting 
platforms, a description of the API, an example of how to use 
it, and much more. The next time you need an API, 
visit this site: 

http://www.vbapi.com/ref/index.html 

You may just save yourself a lot of time and heartburn.


*3. AVOID LIBRARY CONFLICT

by Susan Sales Harkins

Now that Office 2000 supports ADO, you might find working 
between the different versions difficult as older versions 
still run DAO. Converting an earlier file to an Office 2000 
file can also cause a bit of confusion. You can eliminate this 
problem by specifying the library name before the object 
variable in the form 

Dim db As DAO.Database 
Dim rst As DAO.Recordset 

or 

Dim rs As ADODB.Recordset

As long as you specify the library name, there won't be a 
question as to which library is in use.


*4. RETURN THE NUMBER OF UPDATED RECORDS

by Susan Sales Harkins

When you run a fixed action query, Access tells you how many 
records you're about to change. You can do the same thing 
programmatically using ADO's Execute method. This method runs 
action queries and will also tell you the number of records 
affected by the action. Simply pass a Long variable as the 
method's second parameter in the form 

cnn.Execute "SQL statement", lngRecords 

For instance, the following code runs an Update query on a table
named Orders and returns the number of records updated via the 
variable lngRecords: 

Dim cnn As Connection 
Dim rs As New Recordset 
Dim lngRecords As Long 
Set cnn = CurrentProject.Connection 
cnn.Execute "UPDATE Orders SET Total = Total * .10", lngRecords 
MsgBox lngRecords 

The variable lngRecords stores the number of updated records.


*5. CHECKING DATE ENTRIES

by Susan Sales Harkins

There's a running debate about formatting entries. Some 
developers force users to enter data in a specific way or the 
application rejects the entry--that's one way to avoid errors.
Others accept entries and then attempt to format the data 
correctly. For instance, the following procedure checks an 
entry and attempts to create a date. If the passed entry is 
convertible, VBA accepts it. If not, VBA will return an error,
so you'll need some kind of error handling if you decide to go
this route. 

Function CheckDate(pdate As Variant) 
   If pdate <> "" Then 
      CheckDate = Format(CDate(pdate), "mm/dd/yyyy") 
   End If 
End Function


*6. EXCEL DATE AND TIME STAMP

by Susan Sales Harkins

Need to document your workbooks? You can with a simple macro.
Every time someone saves your workbook, the following 
procedure will update the time and date in the active 
sheet's header.  

To create the procedure, first open the VB Editor by pressing 
Alt-F11. Double-click ThisWorkbook in the Project Explorer and 
select Workbook from the Object control. Choose BeforeSave 
from the Procedure control, then insert the following code: 

ActiveSheet.PageSetup.RightHeader = Now 

To see the results, simply return to the workbook and save it.
Then, preview the active sheet. The one issue you need to be 
aware of is that the new date and time stamp appears only on the
active sheet. This works well if you want to stamp each sheet as
users update it.


*7. DISPLAY FILE NAME IN SHEET FOOTER OR HEADER

by Susan Sales Harkins

One of the most frequently asked questions we receive is how to
print the file's name in the sheet's header or footer. We 
covered this issue last year, but because it's such a common 
question, we'll repeat the information. There's no built-in 
feature for printing the file's name, but you can do so with a 
simple macro. To create this macro, open the VB Editor by 
clicking Alt-F11. Then, select the correct project in the 
Project window (which you'll find in the upper-left corner). 
Next, select Insert, Module. In that module, enter 
this procedure: 

Sub NameInFooter() 
ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName 
End sub 

To close the VB Editor, simply click the Close button (X) at the
top-right corner of your screen. 

When you're ready to run the macro, choose Tools, Macro, Macros.
Then, select NameInFooter and click Run. You won't see the 
effects immediately. If you click the Print Preview button, you
can see the file name in the footer. 

You might be wondering if there's a way to print the file name 
in one of the other footers or even a header. For instance, what
if you want to print the name in the center of the footer or in 
the right portion of the header? The solution is simple--replace
the LeftFooter property in the macro with one of 
these properties:

- CenterFooter 
- RightFooter 
- LeftHeader 
- CenterHeader 
- RightHeader 

To avoid a few keystrokes, attach this macro to the workbook's 
BeforeSave event. If you change the file's name, be sure to 
update the header after the initial save.


*8. PUT BUGS ON NOTICE!

by Susan Sales Harkins

If you're having a problem getting Access or VBA to behave the 
way you know it should, don't assume you've made a mistake. The
problem might not be you--you might be struggling with a bug. 
Try checking this comprehensive site to see whether others have 
encountered the problem and how they resolved it: 

http://www.mvps.org/access/bugs/index.htm


*9. OPEN FAVORITES  
  
by Susan Sales Harkins  
  
You can set the Default database folder option to the Favorites
folder if you want to default to that folder when creating, 
saving, and opening files. Such an arrangement isn't always 
appropriate, and fortunately, there is a VBA solution--use the 
RunCommand method in the form

DoCmd.RunCommand acCmdFavoritesOpen

Doing so will open the Open dialog box to the Favorites folder 
without changing any settings or defaults.


*10. CALLING ALL PRINTERS

by Susan Sales Harkins

Before printing reports, you may need to offer your users the 
opportunity to set print options. If so, you could solicit 
information by using input boxes, but why not just display the 
Print dialog box and let them set those options themselves? If 
this sounds like a good idea to you, try the procedure shown 
here to display the Print dialog box. The users can choose from 
default printers, change the default print range, specify the 
number of printed copies, and so on.

Public Function PrintOut() 
On Error GoTo ErrorTrap 
DoCmd.RunCommand acCmdPrint 
Exit Function

ErrorTrap: 
    If Err.Number = 2501 Then 
        Exit Function 
    Else 
        MsgBox Err.Number & vbCrLf & Err.Description 
    End If 
End Function
