People often have a problem entering dates, so the more help you can give your users the better. One solution is to provide them with a built-in calendar as described in my tutorial: A Pop-up Calendar for your Access Forms. Whilst a calendar is my preferred option it isn't always suitable. In order for it to work the users computer has to have a copy of the appropriate ActiveX component. But most often my reason for not using a calendar is that it doesn't fit in with the design of my form... it doesn't look right. So sometimes I use an alternative solution, a set of combo boxes.
Making a date by picking day, month and year from separate combo boxes is a familiar method to anyone who has had to enter a date into an online form on a web page because, until recently, the technology didn't exist to display an interactive calendar on a web page.
This tutorial shows you how to create a combo box based date chooser on an Access form, how to combine the chosen day, month and year into a date, and link that date to a field. Importantly, it also shows you how to make the date chooser "intelligent" so that the user can't make up an impossible date.
The tutorial is in two parts. The first part (this one) deals with building and configuring the combo boxes and is written in some detail. If you are experienced in building forms you may prefer to skip this section and go straight to the second part of the tutorial which deals with writing the VBA code that powers the form.
Open your form in design view and display the Toolbox (View > Toolbox) if it isn't already on view. Make sure that the Control Wizards button on the Toolbox is not selected. You do not need the help of the combo box wizard (if you forget to do this and the wizard starts, just click its Cancel button to close it).
 |
 |
|
| Control Wizards button | Combo Box tool |
Click the Combo Box tool on the Toolbox then click on an empty space on your form to create an unbound combo box ("unbound" means that it is not currently linked to a table field).
To the left of the combo box is a label that was created automatically. You don't need it so click the label to select is and press the Delete key on your keyboard to remove it. Access has assigned the combo box a name (in this example Combo7) but you will change this to something more meaningful later.
Right-click the combo box and choose Properties from the context menu. Click the Data tab of the Properties Window and change the Row Source Type to Value List. For the Row Source enter the values 1 to 31 (representing the days of the month) separated by semicolons, like this: 1;2;3;4;5;6;7;8;9;10;11;12;13;14 etc. Finally, set the Limit To List property to Yes to prevent the user from entering a number outside the specified range.
Switch your form into Form View and test your combo box. The numbers you entered for the Row Source property now make up the combo box's list. If you enter a number not on the list a warning message will be displayed. The combo box is far wider than it needs to be so switch back to design view and open the combo box's properties window. Click the Format tab and change the Width property to 1cm (there's no need to type the "cm") or 0.4in. If your form has sufficient space, you can also change the List Rows property so that the displayed list is longer, reducing the user's need to scroll. In the example illustrated the List Rows property has been set to 15.
It is a good idea to give your form controls meaningful names to avoid confusion when referring to them in your VBA code. Most developers also use a short prefix (in this case "cbo") to identify the type of control. Find the Name property on the Other tab of the properties window and change it to cboDay.
Build two more combo boxes using the same method, setting their properties as follows:
| Tab | Property | Value |
| Data | Row Source Type | Value List |
| Data | Row Source |
1;January;2;February;3;March;4;April;5;May;6;June; 7;July;8;August;9;September;10;October; 11;November;12;December |
| Data | Limit To List | Yes |
| Format | Width | 2cm (0.8in) |
| Format | Column Count | 2 |
| Format | Column Widths | 0cm;2cm (0in;0.8in) see note below |
| Format | List Rows | 12 |
| Other | Name | cboMonth |
Note that for the Row Source I have entered both the month numbers and their names, alternating the data. When this is combined with a Column Count of 2 (in this case), Access divides the data logically between the columns. The Bound Column is 1 (i.e. the first column - the month numbers) but as this is the default I didn't need to change it. You can decide whether or not the user can see all the columns. Do this by adjusting the Column Widths property, setting a column to a width of 0 (zero) to hide it. The bound column passes its value to the combo box regardless of whether or not it is visible.
 |
 |
|
| Width: 2cm Column Widths: 0cm;2cm |
Width: 2.5cm Column Widths: 0.5cm; 2cm |
| Tab | Property | Value |
| Data | Row Source Type | Value List |
| Data | Row Source |
1996;1997;1998;1999;2000;2001;2002;2003;2004;2005 see note below |
| Data | Limit To List | No |
| Data | Validation Rule | Between 1900 and 2020 |
| Data | Validation Text | Please enter a 4-digit year between 1900 and 2020 |
| Format | Width | 1.5cm (0.6in) |
| Other | Name | cboMonth |
It may not be practicable to list all the years in the range that you users might need (although it is possible). If the range is great it might be easier for your users to type the year directly into the box. Here I have given an example of what you could do in such circumstances. I have listed the most likely range of years in the Row Source property but set Limit To List to No. This allows the user to enter a year that is not contained in the list, but I still want to prevent them from making an error, so I have set a Validation Rule. Any entry the user makes must satisfy this rule. If the entry violates the rule Access rejects it and displays a message, which is specified in the Validation Text property.
Some people are content to place their form controls by dragging them into position and aligning them visually. Having created a lot of forms (and also being rather picky about such things!) I have come to the conclusion that the best way to arrange objects on a form is accurately, using measurements in the properties window. I hate to see an untidy form! So here's how I do it...
Drag the three combo boxes to approximately their correct positions. Don't try to align them neatly yet. In my example, I want to align the combo boxes with their corresponding date field control (here called HireDate).
Open the Properties Window for the control to which you want to align the combo boxes and note the value Top property (you'll find it on the Format tab). My HireDate text box has a Top value of 1.711cm. Now Use the mouse pointer to draw a rectangle around the three combo boxes (you don't need to select a tool to do this). This creates a multiple selection of the combo boxes.
Use the properties window to assign a Top property the same as the object to which you want to align them. Because multiple objects are selected the the value you enter will be assigned to all the selected objects.
NOTE: If you have room on your screen, you can leave the properties window open. When you select different objects on your form the properties window changes to show information about the selected object.
You might also have noticed that the height of a standard combo box (0.423cm) is less than that of a standard text box (0.45cm). This sounds a tiny amount but it is enough to make them look different, so I have also chosen to change the Height property of the multiple selection to match the text box.
Now the objects are all correctly aligned vertically, but they probably need aligning horizontally. This is something that can normally be done by eye (although I don't trust myself to drag them into place). Select each combo box in turn and use the left or right arrow keys on you keyboard to nudge them into position so that their horizontal alignment is equal.
I believe that interface design is one of the most important aspects of building a database. This means making forms as user-friendly and intuitive as possible. What seems obvious to you as the designer isn't necessarily so for the user. Of course you know what the combo boxes are for... you built them. But does the user know? Here are some ideas for enhancing the date chooser:
 |
The Option Group tool is also handy for quickly creating a ready-labelled frame. |
 |
Add a few more labels of start from scratch by drawing a rectangle, giving it an etched effect then adding the labels. |
 |
Use your imagination. Access has the tools... you just need to figure out how to use them! |
In this example the date specified by the combo boxes will be entered into another control on the form, the HireDate text box, which itself is bound to a table field. You need to decide whether or not you are going to allow the user to type directly into the text box. My decision is almost always a firm NO!
To ensure that this is the case, go to the Data tab of the text box's properties and set the Locked property to Yes. It is also helpful to set the Tab Stop property of the text box to No so that it is skipped when the user moves through the form using their Tab key.
Finally, switch the form into form view and test the tab order (the order in which the controls are visited when the user moves through the for using their Tab key. Check that the order is logical and, if not, return to design view and adjust it by going to View > Tab Order.
The design stage of the form is now complete. Now
would be a good time to save the form. The next step is to add the
VBA code that will translate the user's selections into a date and
place it in the date field text box. Follow
the instructions for this in the Part 2 of the tutorial.
©2003 Martin Green - www.fontstuff.com - martin@fontstuff.com - All rights reserved