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.

My Treeview Project Episode 7 | Another Way To Read Through Your Data

In Episode 7 of My Treeview Project we're going to look at another approach to reading through our data and adding nodes to the treeview.

Read more »

Search Criteria Form Example From Allen Browne

Many Access developers set out to create a search form: a form where the user can enter or pick criteria and matching records will be shown. Often they find themselves trolling the Internet for a good example to get them going.

You'd be hard pressed to find a better starting point than this article by Allen Browne: Search criteria. The example works you through creating a form with criteria entry boxes at the top and matching records at the bottom. Once you understand the methodology, you can easily extend it to forms with different layouts, pop-up forms, reports, etc.

A Utility to Help With Ensuring Uniform Control Width on Forms and Reports

Tony Toews has posted a neat little utility that grabs the control widths and a few other details for all the controls on your forms to help you make sure that you've sized things consistently throughtout your application. This will be especially useful when you expand a field and have to change all the controls it's used in.

See Ensuring uniform control width on forms and reports

Treeview Example- Filling Recursively, Populating With Names of Folders

Here's another working Treeview example from the code archive at UtterAccess.

Quote: This little database illustrates the implementation of Microsoft Knowledge Base Article Q20981 (How to Fill a Treeview Control Recursively). In addition, frmFolderExamples illustrates one way to fill the Treeview Control with the names of the folders located on a drive. It also shows how to return some of the properties of the treeview nodes as well as how to clear a treeview. I use some code harvested from Candace Tripp’s Browse and Open Files Database as well as ideas from Luiz Cláudio’s Treeview Control for Database Objects.

Roll-Your-Own “Traffic Light” Icon Set

Quote: Here’s a great tip suggested by Clint Covington. Using an expression and a Rich Text text box, you can create a traffic light indicator in an Access form or report, as in the following illustrations:

I really like this use of a Rich Text text box. We used it in my shop successfully- I think it was the first Access 2007 specific feature we used. (Rich Text text boxes became part of Access in the 2007 version, I think.) Take a look at http://blogs.msdn.com/access/archive/2009/04/16/roll-your-own-traffic-light-icon-set.aspx

This solution gives more options for colouring controls on your forms and reports than conditional formatting does.

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 »

Dymanic Forms: Moving Controls Along With The Tab Control They're Part Of

Lately I've built some really neat forms where the controls are moved based on the size of the form or in response to use input. It's not so hard to change the position of a control and it can make for a really well optimized user interface.

One of the particular challenges when moving controls is keeping the controls on a tab control in their proper place. If you move a tab control the controls on the tabs don't move on their own. A.D. Tejpal recently posted this sample database on Rogers Access Library that demonstrates enumerating the controls on each page of the tab control and moving them all so that they all stay together and stay in the right place on the tab control.

Access 2007 Sample Database Demomstrating Cascading Combo Boxes

"I have two combo boxes on my form. When the user makes a choice in one I want the choices in the other on to change. For example the user will pick a country in the first combo box and the second combo box will now show only cities in that country. How do I do that?"

That's commonly called Cascading Combo Boxes and is one of the most commonly asked about techniques. I ran across a new article on this topic the other day. This a blog post from the Microsoft Access team that points out two different ways to get an Access 2007 sample database that demonstrates and explains two simple approaches to cascading combo boxes.

See also: all my Cascading Combo Box posts.

My Treeview Project | Episode 6: Images for the Nodes

This (at long last) is Episode 6 of My Treeview Project.

• In Episode 1 we started with the "Hello World" treeview
• In Episode 2 we built a treeview that showed some simple data from the Northwind Traders sample data
• In Episode 3 we learned how to change the appearance of the treeview
• In Episode 4 we added Expand All and Collapse All buttons that interact with the treeview
• And in Epsiode 5 we finally got the treeview to do something useful- we used it to select records to view and edit.

Here's this episode's treeview:

Download the finished database

In Episode 6 you'll see two things. The main focus of the episode is the use of images for the nodes in the treeview. As well, I'll demonstrate a different approach to the code that reads records from the tables and creates the nodes.

Read more »

Unbound Form With Undo and Redo

Access is great at forms that are bound to tables in data. But sometimes there's value in creating an "unbound form". The data is stored in the controls on the form and your code saves it to the table (or tables) when the user clicks the save button. You might do this when the structure of your data is not ideal for your form- a survey database for example.

This post provides a working example of an unbound form complete with undo and redo buttons.

More calendars (date pickers)

Here's another couple of links with calendars you can add to your forms.
An ActiveX-free pop-up calendar posted in the code archives at UtterAccess.com.
Another one from the code archive at UtterAccess.com, this one also ActiveX-free, with a large and small calendar you can incorporate into your forms.
And lastly a pop-up calendar from Allen Browne. Like the others, it's non-ActiveX ad can be easily imported into your database. Allen's page includes links to a couple of others if those are not enough.
See all my calendar form posts.


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

Sample Access Databases From Office Online

I find that looking at a demonstration or sample database is a great way to learn more about Access. Microsoft has some great samples databases here on Micorsoft Office Online. that demonstrate some really neat techniques for forms, reports, and queries.

Read more »

Appointment Calendar From the UtterAccess Code Archive

A lot of questions a posted on discussion boards about building forms with embedded calendars. Some are just to select a date, some are to show information on a calendar. This post in the code archive at UtterAccess.com is a neat working example of an appointment calendar, with both a monthly view showing all appointments in a month and a daily view showing appointments in time slots. All accomplished without ActiveX controls!
See all my calendar form posts.

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 »

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.

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 »

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.

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 »