MS Access:Automatically Requery to Update a Combo Box

In MS Access, when you want a form containing a foreign key, you typically use a combo box that’s populated with data from the foreign table. The underlying field is the fkey’s value, but the combo box displays another column from that table that is easier for people to read. For example:

The Foo table has an fkey, PeopleID, that refers to the tblPeople table.
TblPeople has a field, Name, that is the person’s name – a human comprehensible value.
The frmFoo form has a combo box that’s bound to the PeopleID fkey.
The combo box will populate the PeopleID fkey with a number, but displays the Name field, so it’s easy for people to use.

The problem is, if you add values to tblPeople, it’s not reflected in the combo box. The combo box lists only the old data. The combo box doesn’t automatically reload its list whenever there’s an update to its source of data.

Searching for a solution to my problem brought up over a hundred posts dealing with the same problem.

http://search.experts-exchange.com/simpleSearch.jsp?sfZoneID=-1&TAFilterID=39&gSearch=0&q=requery+combo+box&rlStart=1&rlOrderBy=3&rlSort=-1

So it’s a common problem. A little experimentation resulted in the picture attached, which shows a sample database with event handlers to force the combo boxes to Requery whenever the tables are updated.

(A few solutions involved requerying whenever the combobox got focus [via the GotFocus event]. That didn’t seem right – because you want to update the list when the underlying data is updated. It also seemed inefficient to requery every time you clicked a combo box.)

The tricky part is that you need to use forms for the two tables that are going to be altered. Adding data to tables, directly, won’t raise the AfterUpdate event. (This seems to be a general principle of MS Access: create forms for your data entry, and don’t enter data into tables or queries.)

The event handler, again, is tricky, because you want the event to force a requery in another control. The one-liner does that.

(I don’t know if MS Access has a “publish/subscribe” system to allow a control to subscribe to events raised by other controls. That would be the best way to do it, because it would avoid the problem where the event handler has to keep a list of all the controls it’ll update.)

Attachment	Size
AccessFormUpdate.jpg	195.08 KB