Office 2003 VBA References

Here are a couple of links to a chunk of 2003 Office and Access VBA stuff. A lot of it is just what changed in 2003, vs. a comprehensive reference, but there seem to be some useful sections there. I seem to frequently find and then subsequently loose the comprehensive reference stuff from Microsoft that I need when "I don't know exactly what I'm looking for yet." These links held a lot of promise when I first found them, but their lustre has faded somewhat.

• Office 2003 VBA Language reference
• Downloadable Office Access 2003 VBA Reference

Subqueries: include a running total in a query

Here's another great subquery example from UtterAccess. This one uses a simple subquery to add a running total to a query.

Subqueries: find top 25 by group

Here's an interesting post on UtterAccess where one of the VIPs uses a subquery in a pretty clean and simple way to find top 25 by group.

Using SnapShot files to distribute reports

View as
a full page
article

Download
sample
database
A2K3 A97

This article from Microsoft Office Online reviews the use of the Snapshot file format to distribute reports.

I've used snapshot in my organization for a couple of years now and have found them very usable. They provide all of the benefits normally associated with PDF files, and can be easily generated from Access without any additional components. Compared with saving to Word or RTF they have the advantage of preserving all your report formatting.

The article covers the basics of saving a snapshot file manually from a print preview window, but it doesn't cover automating the production of snapshot files. What makes this task harder is that the the snapshot format is not covered in the help or the list of allowed constants for the relevant commands. Despite this, it's really easy once you know how. And in this post I'll tell you how.

Read more »

Send e-mail through Outlook without a security warning

A common feature sought in Access applications is sending e-mail. If you're going to send a bunch of messages, such as reminders to each of the team members with due dates next week, you'll run acrosss Outlook's anti-spam provisions, which will require you to approve each message. There are a number of different solutions posted on the web. We'll look at two here- one from the web and one of my own.

Option one

This article caught my eye: it sees you create a function in Outlook that Access can call to send the message. Since code running within Access is trusted the security warning won't come up.

Option two

Here's another approach that also relies on the fact that code running within Outlook is trusted. Have Outlook open up the Access database to get data for the e-mails and do all the sending. This approach allows you to easily use more of Outlook's features, but may not tie into your user's workflow as it will be launched by the user from within Outlook.

For this exercise here's sample data that will be stored in an Access database:

In this case (which is really simplified for the case of the example) the column "Body" includes the HTML code for the message.

The VBA code for this solution will go into an Outlook VBA module. Just like other hosting applications, you get at the code by pressing Alt-F11. Once you have the code in and tested you can run it from Tools/Macro or you can make a toolbar button that launches it. One more thing- I tested this in Outlook2003 only. Oh, and you'll need a reference to DAO under Tools/References [show me].

Sub SendMessages()
 Dim mailMyMail As MailItem
 Dim rsMessages As DAO.Recordset
 Dim db As DAO.Database
 Dim ws As DAO.Workspace
 
 Set ws = DAO.DBEngine(0)
 Set db = ws.OpenDatabase(--- put the path and name of your database here ---)
 
 Set rsMessages = db.OpenRecordset(Name:="tblMessages", Options:=dbReadOnly)
 
 Do Until rsMessages.EOF
 Set mailMyMail = CreateItem(olMailItem)
 With mailMyMail
 .To = rsMessages!ToList
 .cc = rsMessages!CCList
 .Subject = rsMessages!Subject
 .HTMLBody = rsMessages!Body
 .Send
 End With
 rsMessages.MoveNext
 Loop
 
 Set mailMyMail = Nothing
 
 rsMessages.Close
 Set rsMessages = Nothing
 
 db.Close
 Set db = Nothing
 Set ws = Nothing
End Sub

See also:

• OL98: Developer Information About the Outlook E-mail Security Update
• Outlook Redemption v. 4.4 A product that "works around limitations imposed by the Outlook Security Patch..."
• Getting started with Outlook VBA
• Distributing Microsoft Outlook VBA Code

Cumulative percent: show me the items that contribute to the first 10% of the sales

This is a common business problem, and not one that's easily solved with simple queries or reports. Look around the web and you'll find a number of solutions. Many of those solutions only work in reports and require VBA coding. Here's one that uses only a series of queries and will not require you to get into the muck of SQL or VBA.

Here's sample data for this example:

The question at hand is: how much space is allocated to items that contribute the last 25% of the margin dollars.

Let's show how this would be done in Excel. It may illustrate the concept better:

Having sorted the data and figured out cumulative margin and the percent of the total that represents, we know that in category 2, two items contribute the first 69% of the margin dollars, and the remaining three only contribute 31%. In Excel you have to build formulas by category and so it becomes tedious for large amounts of data. As well, the rest of the analysis you're doing may be in Access so to figure out the cumulative % in Excel would mean moving data back and forth- cumbersome and error-prone!

To do this in Access we need queries that will do the same thing we did using formulas in Excel.

The first query is the hardest. We need to sort the items in descending order and figure out cumulative sales at each item. Here's what it looks like in the query design grid:

The first thing you notice is that tblPerformance is in the query twice. Just add it twice in the Show Table dialog, and Access will automatically give it another name: tblPerformance_1 so that it (and you) can keep the two copies separate.

The two copies of the table are first joined on CategoryNumber, so that the sorting and summing is done within each category. The last column is where the magic happens. Access looks at the two copies of the table, and for each item finds all the items with margin greater than  or equal to that item. Then the sales for those items are summed. This is similar to the Excel example where we sum all the items in the list above the item we're calculating for. Because tblPerformance has two different names we can tell it which table to sum the values from and how to compare the value in one table to the other.

The rest of the queries are easy. You need one that finds the total margin by category:

And one that uses those two queries to turn cumulative dollars into cumulative percent:

Now you can use that query as a filter to find values to sum to answer the original question (how much space is allocated to items that contribute the last 25% of the margin dollars)

Performance:
A query like this can be quite resource intensive. If you run it on a large table you may find it takes too long to run. Consider making the first two queries make table queries and building the third query off the tables. Or try putting indexes on your data table. For this example I built indexes like this:

Getting the path for the "Temp" folder

Way back in the days of DOS, and the early days of windows, you could usually put temporary files in C:\TEMP. Today Windows provides TEMP folders, but they won't be in the root of C:. In XP for instance the path will be C:\Documents and Settings\username\Local Settings\Temp. If you want to use that folder in your program, such as to save a copy of a report for sending via e-mail, you'll need to get the specific path name. In this post I'll show you two ways to ask Windows for the path for the TEMP folder.

Using an API

Application Programming Interfaces (APIs) provide a way for your program to call Windows functions that aren't otherwise available. There's an API called GetTempPath that does just what we want. You'll usually need to build a function around API calls to make them easier to use in your program. Paste this code into a standard module and you'll be able to use the function GetWindowsTempPath() to return the Temp folder name.

' delaration of API to get the special folder name for temp objects
' (in XP this is, for example, C:\Documents and Settings\user.name\Local Settings\Temp\)
Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
    (ByVal nBufferlength As Long, ByVal lpBuffer As String) As Long


Const MAX_PATH = 260 ' maximum length of path to return- needed by API call to provide a memory block to put result in


' function GetTmpPath
' return windows special folder for temp objects
' path will not include a backslash on the end
' path will be expressed using short (8.3) names for folders
    
Public Function GetWindowsTempPath()
    Dim strFolder As String ' API result is placed into this string
    
    strFolder = String(MAX_PATH, 0)
    If GetTempPath(MAX_PATH, strFolder) <> 0 Then
        GetWindowsTempPath = Left(strFolder, InStr(strFolder, Chr(0)) - 1)
    Else
        GetWindowsTempPath = vbNullString
    End If
    
    ' remove any trailing backslash
    If Right(GetWindowsTempPath, 1) = "\" Then
        GetWindowsTempPath = Left(GetWindowsTempPath, Len(GetWindowsTempPath) - 1)
    End If
    
End Function

Using the FileSystemObject

The FileSystemObject is provided to interact with files and folders from VBA and from Windows Scripts. One of the many things the FileSystemObject can do for you is give you the paths to some of Windows special folders. Like the previous example, paste this code into a standard module and you'll be able to use the function GetWindowsTempPath() to return the Temp folder name. Because of the use of the FSO you'll need to go into Tools/References and add a reference to the Microsoft Scripting Runtime.

Public Function GetTempFolderName() As String
    ' use a FileSystemObject to returns the name of the user's temp folder
    ' in this case FSO is early bound- it's common to late bind with this library but
    ' this works well and provides compile time checking
    ' requires a reference to Microsoft Scripting Runtime
    ' path will not include a slash
    ' path will be expressed using short (8.3) names for folders
    
    Dim fso As Scripting.FileSystemObject ' an instance of the FileSystemObject
    
    Set fso = New FileSystemObject

    GetTempFolderName = fso.GetSpecialFolder(TemporaryFolder).Path
    
    Set fso = Nothing

End Function

Additional reading:

• Working with Files, Folders, and Drives: More VBA Tips and Tricks Includes a great overview of the FileSystemObject.
• Programming the FileSystemObject
• Windows API Microsoft reference for APIs.
• Getting The Path To Windows Special Folders

Working with Files, Folders, and Drives

I just ran across this article from Microsoft. It covers off a whole list of techniques for accessing files and folders. It overviews the VBA functions available and discusses a couple of libararies that you can access for more capabilities. It doesn''t go into detail on all the functions and methods, but provides links to further information on many of the functions. Some sample code is included.

Quote:
If you want to work with files or the file system, you have several options available—and the best choice depends on what specifically you want to accomplish. The available options include using VBA functions, the Microsoft Scripting Runtime Object Library, the Office FileSearch object, and file system-related Windows API functions. The only things I will not cover are the Windows API functions.

MouseOver Status Labels

Take a look at this neat sample database posted by John Mishefske at UtterAccess. It shows some really nice object and event techniques. The result is a form with a pretty effective UI behaviour that goes way beyond “bread-and-butter” Access forms.

events, forms, object-oriented

Synchronizing Combo Boxes on Forms

This Microsoft Visual How-To walks through a solution to a very common scenario: make the available choices in one combo box depend on the selection made in another combo box.

Read more »