Fill a list with date values that update automatically

Say you need a combo box that is always populated with the last day of the current month, as well as the last days next five months. For example, in May, you want it to look like this:

Then, in June, you want the choices to automatically roll over so that the first date is 6/30/2009, and the last one is 11/30/2009. How do you do this without manually updating the combo box’s data source? Try a callback function. A callback function lets you use VBA code to fill the list with the appropriate values each time the form is opened, so that the list is always current.

Without getting tied up in the details of how a callback function works, here’s a quick-and-dirty procedure showing how to get one working on your Access 2007 form. Afterwards, I’ll give you a few hints on how you can modify the function to suit your needs. (I’ll rely on commenters to point out improvements as well. :-))

1. If you already have a module that contains miscellaneous or form-related functions, open it in the VB Editor and skip to step 2. Otherwise, create a new module: on the Create tab, in the Other group, click the down-arrow under Macro, and then click Module.
   
2. Paste the following code into the module:

   Function FillList(ctrl As Control, ID, Row, Col, Action)
       Select Case Action
           Case acLBInitialize
               FillList = True
           Case acLBOpen
               FillList = Timer
           Case acLBGetRowCount
               FillList = 6
           Case acLBGetColumnCount
               FillList = 1
           Case acLBGetColumnWidth
               FillList = -1
           Case acLBGetValue
               FillList = DateSerial(Year(Date), _
               Month(Date) + 1 + Row, 1) - 1
           Case acLBGetFormat
               FillList = "mm/dd/yyyy"
           Case acLBEnd
           Case acLBClose
       End Select
   End Function

3. Save the module and switch to your form in Design view.
4. On the form, add a new combo box. If the control wizard starts, click Cancel. (Of course, if you have an existing combo box that you want to use, you can skip this whole step).
5. Select the combo box and press F4 to display its properties.
6. On the Data tab of the property sheet, leave the Control Source and Row Source properties blank, and type the function name in the Row Source Type property box (in this case, FillList).
    
7. Save the form, switch to Form view, and test the combo box. If everything went smoothly, you should get the results shown in the first illustration (adjusted for the current date, of course).

Notes

• Experiment with the arguments for the DateSerial function to get the list of dates you want. For example, remove the “ – 1” at the end of the expression to get the first day of each month, instead of the last day.
• Row equals 0 for the first row, 1 for the second row, and so on. You can do whatever calculation you want, using Row (and Col, in multi-column lists) to calculate different values for each row and column. You’re not limited to date values.
• You can put the code in the form’s module, but if you think you might want to use the code for several different forms, consider leaving it in a standard module.
• This function also works fine for list boxes, I just used a combo box for this example.
• This also works for previous versions of Access.
• If you’re just getting started with adding controls to forms, see this article about adding list boxes and combo boxes.
• A variation on the callback function can be found here.
• Callback functions have many other uses. See this article by Access MVP Allen Browne to learn how to create a list of files in a folder.

Have an Access Power Tip that you want to share? Send it to Mike and Chris at accpower@microsoft.com.