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 »

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 »

SQL Tutorial From W3Schools.com

If you work in Access you need to know SQL, but where do you go to learn it? Here's a tutorial from W3Schools.com. What I like about the W3Schools tutorials is the "try it" box that, in this case, lets you type some SQL and see what it does. Neat!

Controlling a How a ListView Control Sorts Items Using Callbacks

Next to the Treeview, I think the Listview is my favourite control to program with in Access. Like the treeview, it's ActiveX. There's a set of pages about it in the MSDN library at http://msdn2.microsoft.com/library/aa443482.aspx.

I have a really neat treeview implementation in one of my applications. There's one user request I haven't been able to satisfy, though: sort by price. I have a column for price and one for description (plus several others.) I have it set up so that when you click a column header it sorts by that column. But under the covers the sort is always alphabetic, as the control treats all the values as text. So $11.00 sorts ahead of $2.00.

I wasn't looking for a solution for that this afternoon when I stumbled across this article:

Read more »

Filtering Using Multi-Select List Boxes

Another really common Access question goes like this:

• How do I use a multi-select list box to filter my report?
• or How do I let my user select multiple items and then open a form or report showing those items?

List boxes allowing multiple selections are a pretty natural user interface. Think "print a report for three different departments" or "include these four employees in the sales graph". Creating and using criteria for your form or report with a multi-select list box is a lot different than a combo box or single select list box, but it's not hard at all once you've seen it in action and done it a couple of times.

Read more »

Lookup Fields- Good or Bad?

Lookup fields in tables can be really convenient for casual users. But most serious users agree that the disadvantages outweight the benefits. Here's an article on The Access Web outlining the disadvantages.

How to Synchronize Two Combo Boxes on a Form in Access

"I have two combo boxes (or list boxes) on my form. When the user picks an items in one list I want the choices in the second list to be narrowed down".

This remains one of the most frequent questions Access database users run up against early in their work with Access forms. It is sometimes referred to as cascading combo boxes, or dependant combo boxes, or synchronizing combo boxes.

Read more »