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.
Sunday, November 25, 2007
Friday, November 16, 2007
Here's another great subquery example from UtterAccess. This one uses a simple subquery to add a running total to a query.
Wednesday, November 14, 2007
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.
Monday, November 12, 2007
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.
Friday, November 9, 2007
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.
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.
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:
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
- 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
Thursday, November 8, 2007
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)
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:
Posted by Stephen at 9:09 PM | Permalink | I'm reading: Cumulative percent: show me the items that contribute to the first 10% of the salesTweet this! | Add This! | Blog This |
Wednesday, November 7, 2007
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
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
- 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
Tuesday, November 6, 2007
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.
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.
Friday, November 2, 2007
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.
Thursday, November 1, 2007
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.