Consulting and Resources for Excel and Access

Letting users add a new record to a combo box

Often your users will need to add new items to a combo box. How
you do that will depend on several factors.

If the items in the combo are made up of a value list, you
will need to add new items to that list.

If the combo is based on a query or table, you will need to
add a new record to that underlying table. That can be done
behind the scenes, or by popping up a form for the users to fill
out; the second option is best if the new record requires
information in several fields (such as a new client).

Using value lists

Using a table or query

Adding new items to a value list

In most cases you will use a table as the RowSource for a combo
box. That simplifies filtering and sorting the RowSource, for
example when you create cascading (dependent) combo boxes. However,
you don't always know what data needs to be displayed. For example,
scheduling systems will often require several dates from the current
date (the next 4 Mondays or the next 14 days).

Using a list-filling callback function

To create a list of the next n Mondays, Access provides
a callback function called ListMondays. You can find it in the Help,
but the documentation isn't all that good. The function is shown
below: it needs to be pasted into a standard module.

The expression in parentheses sets the day of the week. For
example, the 9 in the above expression ensures that you will get a
Monday as the first date in the sequence. The logic goes like this:

Weekdays go from Sunday (1) through Saturday (7). By adding
7 to the weekday you make sure that subtracting Weekday(Now)
from your target weekday, you will always have a positive
number. In case the result is >=7, dividing by 7 and taking the
remainder (the Mod 7 part) will give a value between 0 and 7.

Offsetting by -28 moves you back 4 weeks. You will need to
adjust this value to get the correct first date.

For example, assume that today is Friday 2 November. The
intOffset calculation is Abs((9 - 6) Mod 7) - 28, or -25. If I
pick Monday 5 November instead (3 days later), the calculation
becomes Abs((9 - 2) Mod 7) -28, or -28. The starting point is
the same day for any day from Tuesday to the following Monday.

It's not obvious from the construction of this function but this
is the loop that builds the list. Change the date format to suit
your requirements.

To use this function, the Row Source Type of the combo box needs
to be changed so that it reads ListMondays -- the name of the
callback function. Leave the Row Source blank.

Using the AddItem method

In Access XP and later, you can use AddItem to build a value
list. Typically, you would create the list when the form loads, as
in the sample code below which populates a combo box with the months
of the year.

Building a string for the RowSource

If you are using an older version than Access XP, you can't use
the AddItem method. In that case you need to build the list by
adding the delimiting semi-colons, and then setting the RowSource to
the new list. The equivalent code to the last example is shown
below:

Some things to note about value lists:

The first line of code ensures that the correct rowsource
type is used. Without this the code will fail, if for any reason
the rowsource type has been changed to Table / Query or a custom
list-filling function.

The second and third lines of code are housekeeping. They
ensure that the list is empty before we start adding items, and
that only 1 column is displayed.

There is a 2048 character limit in value lists for Access XP
and earlier. This restricts the number of items that you can
display; if you need more than this, you will need to use a
table or query as your rowsource.

If you want to start the month list somewhere other than
January (eg, for a financial year), you can easily modify the
code by adding an offset value to intMonth:

In the example above, the first month in the list is July and the
month names wrap around to June: suitable for the Australian
financial year.

Adding a record to a table

If you are only populating a single field, this routine will
create a new record in the table and refresh the combo box to
display the new entry. It has been written to be generic: you will
need to change the names of four items in the code to make it work
for your situation.