My Treeview Project | Episode Five: Using The Treeview To Select Records to View/Edit

In episode 5 we'll make the treeview really do domething. We'll use it to select records from the products and categories tables that we want to see or edit. The work you'll have to do in this episode is a little more than in the last two, but we'll go through it step-by-step. We're building on the work you've done in previous episodes.

Read more »

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).

Adding a Calendar to Your Froms: Stephen Lebans' Code with Detailed Instructions by Jeff Conrad

How do I put a calendar on my form so users can pick dates? There are so many different ways to do this. Here's one that has the benefit of relying on no activeX controls- which makes it more portable and more distributable. It does that while still providing a really slick interface because it uses APIs rather than just text boxes on forms.

In this case Jeff Conrad has written detailed step-by-step instructions for implementing Stephen Lebans' solution. You can never go too far wrong with Stephen's solutions, and Jeff's instruction make it much easier to implement.

See all my calendar form posts.

List of programming resources for Visual Basic for Applications

Take a look at this article from Microsoft. They've pulled together a whole pile of resources that will be useful for the VBA programmer. If you're getting started with VBA, or are just looking for more VBA references and resources, this looks like a great page to check out.

Quote:

This article lists some of the resources that are available to help you learn Microsoft Visual Basic for Applications programming. The article contains the following categories:

• Online Help
• The Object Browser
• Microsoft Press and third-party reference and learning materials
• Microsoft Knowledge Base on the World Wide Web
• Helpful Knowledge Base articles
• Internet newsgroups
• Other Web resources

Microsoft How-To: Removing a Subdatasheet

Subdatasheets are a neat feature added to Access in the 2000 version, but for anyone building applications, especially when deploying over a network, they should probably be turned off. You'll use your own forms instead of this "free" database feature, and they can cost you in terms of performance.
This article shows how to remove them in Access 2007. In 2003 the remove option is under Format/Subdatasheet.

Also see this great UtterAccess post by Crystal at UtterAccess.com on Subdatasheets, which includes links to articles that discuss the performance issue.

**Links cleaned up 2011-09-05**

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.

My Treeview Project | Episode Four: Expand All and Collapse All Buttons

In episode 4 we'll take the treeview we designed in episode 3 and add buttons for "collapse all" and "expand all". This will let us get started working with the nodes in a treeview.

In previous episodes I told you to make sure you'd built the treeview from the last episode so we could add to it. This time you can download the completed treeview including this episode.

Read more »

Preventing Page Breaks In The Middle Of Your Subreports

Today I was working with a large report with six or seven subreports. Since the subreports are all set to "grow" the page lengths vary and sometimes the page breaks come where I don't want them.

ACC2000: Keeping Subreport or Text Box Data Together on a Page shows how to solve this by creating artificial report sections.

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.

Programming combo box and list box controls in Microsoft Access

Here are a couple of posts from the MSDN archive. They had been taken out of general circulation, but have since been reintroduced to the library. Together they make up one of the most comprehensive reviews of developing with combo boxes and list boxes I've seen.

Programming Combo Box and List Box Controls in Microsoft Access, Part 1
Part one reviews the various options for supplying a list of values to a combo box or list box (all but one), including one I didn't know existed, the Field List setting. It also covers off how to find out what the user selected, including in a multi-select list box. It talks about using the NotInList event to make your lists dynamic. The only thing I can see missing is cascading combo boxes, which I cover off in these posts.

Programming Combo Box and List Box Controls in Microsoft Access, Part 2
Part two goes into using a function to provide values to the list. This one's a little more complex, but really not difficult, and provides functionality the other options don't.

Demo: Use the Expression Builder

I think the expression builder is an under-used tool in Access. Microsoft has put up a demo that shows what it can do. See Demo: Use the Expression Builder.

Quote:
"You can use expressions throughout Access — for example, to calculate values, act as query criteria, or validate data. The Expression Builder helps you create expressions by providing easy access to the names of the fields and controls in your database, and to many of the built-in functions that are available to you when you write expressions. This demo shows you how to use the Expression Builder for one common task —calculating a date value on a report."

My Treeview Project

Treeviews are really powerful interface components. I love building with them. They aren't easy to get started with, though, for a few reasons: they can't be bound to data so you have to fill them using VBA code, they're not part of Access and so aren't covered in the Access help (they're ActiveX controls), and they are often used with complex data, such as hierarchical relationships.

I see lots of questions on the web asking "How do I get started with treeviews- is there a tutorial?". My Treeview Project is just that- a tutorial. Seasoned VBA developers may find it goes a little slow, but be patient! It's aimed at folk for whom the Microsoft online references aren't enough. It goes step-by-step, starting with a simple "Hello World" treeview.

Here's the current table of contents.

Read more »