Use the Form property when referencing subforms in your code

There's a good post from back in May on the Microsoft office team Access blog that talks about some best practices for referring to forms and controls in code: Power Tip: Use the Form property when referencing subforms in your code. If you're serious about building applications using Access forms you should understand the concepts in the article.
The title is a little misleading. As well as talking about using subforms, it talks about using from variables to make referring to your controls easier.

API: Copy variables/control contents to memory

I used to have this bookmarked but somewhere along the line I lost it: API: Copy variables/control contents to memory from The Access Web. I stumbled across it today when reading this post on UtterAccess.com.

I have found copying things to and from the clipboard to be a useful feature in several of my applications. Here's one I built in Word. This little macro copies the current document path to the clipboard.

Public Sub PathToClipboard()
    With Application.ActiveDocument
        ClipBoard_SetText .Path & "\" & .Name
    End With
End Sub

(You'll need the code from The Access Web link above in addition to this little snippet.) I found I often had to give a colleague the path to a document I had just updated on a shared drive somewhere. Using this macro I would just hit the keyboard shortcut I had set up, pop over to email, and hit ctrl-v to paste. And there I'd have the full path to the document in my email.

There are lots of other applications for the putting data on the clipboard, so it's a good thing to have in your toolkit- bookmark the post on The Access Web and you'll have it when you need it.

Using Right-Click With Treeview Nodes

Lets say you have a treeview and you want a user to be able to right-click a node and then be able to either delete it, copy it, or view details about it. How do you trap the right-click event? How do you show a context menu? And how do you know what node was clicked?

There's no OnRightClick event for Treeviews- that would have been the easiest solution. There are MouseUp and MouseDown events that know what mouse button was pushed- maybe that would help.

It turns out the secret is MouseUp.

Read more »

Programming The VBA Editor

Programming The VBA Editor describes methods for programmatically interacting with the VBE editor. Quote:"You can use these features to write custom procedures that create, change, or delete VBA modules and code procedures." The article is written for Excel but I think it should be adaptable to Access. (I haven't tried it yet- if you try it let me know how it works!)

Use WShell to Retreive the Path to Windows Special Folders

Sometimes in your program you'll need to find the path of the user's MyDocuments folder, or to the Desktop, or to the Favorites folder. Because different versions of Windows will place these folders in different places, it's important to ask Windows for the location of the file, instead of hard coding it or figuring it out from the user name.

This post describes a method for using the Windows Script Host to retrieve these names. The Microsoft documentation is here: SpecialFolders Property.

Here's an example of how to build a VBA function that uses this method within MS Access to find the path to the user's MyDocuments folder.

Read more »

How Many Legs? An Exercise in Classes and Objects

So I ran across a puzzle about cats on a bus. It's here. As you'll see if you follow the link there are seven girls, each with seven backpacks, each with seven large cats, etc. The question is how: many legs in total?

As I looked at it it struck me as an interesting problem for objects and properties. If I created an Access database with a Backpack class and a Cat class and so on I could just instantiate a Bus object and then interrogate its Legs property.

Well it worked. It's not elegant, and there aren't a lot of comments, but it's an interesting exercise on classes and objects. You can download the database here.

File and Folder Dialogs: Even More Sophistication

Here's another version of "how do I prompt the user for a folder name/file name? This one adds additional sophistication. On the folder dialog you can pre-select a folder. On the file dialog you can specify the X and Y coordinates for the dialog box. It's at http://www.lebans.com/callbackbrowser.htm.

See also How to display and to use the File dialog box in Microsoft Access (Oct 2007).

Getting the path to windows special folders

Sometimes your VBA program needs the path to the users desktop, or to a temp folder where working files can be placed, or to the user's documents folder. There are several ways to do this.

There's some great API code here at The Access Web.

You can also do it using the Windows Scripting Host. That method, as well as another API method, is described here, in a previous blog post. That post talks specifically about getting the path to the temp folder. I can't see that choice within the Access Web code above. Perhaps no one approach offers all the special paths. In the previous blog post linked earlier in the paragraph I use GetSpecialFolder() to get the temp folder path. You can get a couple of other special folders with that method as well- see the Microsoft documentation for details.

Office VBA and the Windows API

Here's a great overview of using the Windows API with VBA applications: Office VBA and the Windows API

**Edit Sept 16 '08: The link above doesn't seem to work anymore, but I think the same article is at http://msdn.microsoft.com/en-us/library/aa201293.aspx.

My Treeview Project | Episode Three: Changing How the Treeview Looks

Here's what we'll end up with
after this episode.

In Episode 1: The Hello World! Treeview we built the simplest of treeviews, just to introduce some of the concepts. In Episode 2: Northwind Categories and Products we created a treeview that displayed products in categories based on data from the Northwind sample database.

In this episode we'll take the treeview from epsiode 2 and add some tweaks that affect how it looks. This will let us look at a number of useful techniques and concepts. If you haven't done episode 2 you should go back to it now and build the database because you'll be working from there in this episode.

Read more »

My Treeview Project | Episode Two: Northwind Categories and Products

In Episode 1: The Hello World! Treeview we built the simplest of treeviews, just to introduce some of the concepts. In this episode we'll build a treeview to display products in categories.

We'll use data from Microsoft's Northwind Traders sample database. If you don't have a copy you can download it here .

Start by creating a blank form in the Northwind Traders database and add a Treeview control, the same way we did in episode 1.

Read more »

MS Project- Use the TaskTable to Get the Fields Project Displays



If you're building an application that uses data about tasks in an MS Project project plan, you may simply need all the properties of all the tasks, or you may wish to use the data as it's currently displayed in MS Project. A task has dozens of pieces of data associated with it; only some are displayed in the current view.

Read more »

Excel: Using Custom Functions in Dynamic Ranges

This is neat. Define ranges in Excel not as a static group of cells but using a function which will dynamically determine which cells to include.

My Treeview Project | Episode One: The Hello World! Treeview

The hello world program is a common first exercise when learning to work in a new programming environment: a program that simply displays or prints "Hello World!". In the first episode of this new series on treeviews we'll build the hello world treeview in an Access form.

Read more »

A Working Treeview Example with Drag and Drop

Here's another working treeview sample from UtterAccess.com. This one's pretty sophisticated, with a drag and drop function used to change who staff report to and another to drag unassigned orders from a listview into the treeview where you drop them on the staff that will work on them. A nice implementation indeed.
The author, Brent Spaulding, is one the the treeview experts at UtterAccess.
See also: other treeview posts on my blog.

Using a TreeView Control as a Record Selector

Treeview continues to be one of the most common of the search keywords that bring folk to MyMSAccessBlog. I love developing with treeviews and if I can narrow down the specific questions folk are searching for answers to I'll post some detailed tips or solutions. For now, here's here's a zip file from Helen Feddema's Access Archon page with a working example:

• original version circa 2002 with an article describing how it works
• new version from Feb 2008 with increased functionality including a neat "copy record" function

The example uses a treeview to select books or authors to display information about.

Quote:
A TreeView control makes a better record selector than the standard combo box for situations where you are working with hierarchical data, and want to allow selection of an item from a branch of the data hierarchy.

For all my treeview posts click here.

Calculating elapsed time is more than just numbers

Here's a neat post on the Access Team Blog. It's a function you can use in your application to display elapsed time in a more "friendly" way. The units used and the precision applied are determined by the magnitude of the interval- take a look!

Quote:
I didn’t just want to show the hours or even days elapsed, but something more in sync with the way I want the information given to me- specifically that when dates are closer to the current time they are represented more precisely and dates that are farther away are shown generically.

***Update 2009-04-04: This article has also been posted on MSDN as: Constructing Modern Time Elapsed Strings in Access 2007.

Restore a Deleted Microsoft Access Table with VBA

I ran across this post: Restore a Deleted Microsoft Access Table with VBA which uses simple code to un-delete a table. I tried it in Access 2007 and it seemed to work fine. It looks like it relies on the fact that the table will still be in the tables collection until you close the database. It builds and executes a simple SQL query to copy from the (now invisible) table to a new table. Sounds like a good reference to keep handy!

Count letters, words, sentences and paragraphs

Here's a nice little database in the code archive at UtterAccess with functions to count the number of letters, words, sentences and paragraphs in text.

Download the Automation Help File from Microsoft

Automation is the term used to describe one application using another to do its work- for instance Access using Excel to create a new spreadhseet and to put data from a into the cells of the worksheet. Automation is tremendously powerful, but can seem like black magic to the "uninitiated".

Microsoft provides references which help you get started with automation using Microsoft Office products. They are distributed as compiled help files. The most recent version of the help file is here.