MS Access bound combobox on table linked form

Question

I currently have a 2003 MS Access database that has a Form where the recordset is based on a Table. There is also a Combobox on this Form that has the control-source of one of the fields in the bound Table. The recordset is the result of a query that pulls all the available 'services' from another table, with the ID of the selected 'service' being stored in the form bound table. The Combobox has three columns with columnwidth defined as 0cm,1cm,1cm with column 1 being the bound (ID) column.

this allows me to select from the drop down list a 'service' and also type it in, with the ID of the service being stored in the table. Naturally this form can have many rows in it that are tied to the rows in the table.

This works fine and is great. However there is now a requirement for the values in this drop down to be based on another field. So instead of having one set of 'services' there can be several sets of 'services' in the drop down that will appear based on another combobox.

I've managed to get it to work by modifying the recordset query for this service drop down to take into account the other field and then on the onenter() event of the service list combo box, requery itself (servicelist.requery()) however there is a snag where if i've got five rows in the form, each service list will be based on the current rows other field, not its respective one, causing the combo box to be blanked out. this doesn't affect the underlying table however it's quite annoying.

Any suggestions?

I've tried setting the "limit to list" property to "No" however this can not be done when the first field (which is the bound field) in the combobox has a width of 0CM. when i set it to not equal to 0CM then the display value for this combobox becomes the ID field, which is not what i want.

Answer 1

The workaround that I use in these situations is to add a second bound combobox. The second combobox is the exact same as the first, with a few important differences.

First combo box (used for display)

• Top of z-order (set via Bring to Front)
• Less restrictive rowsource (ie, not based on any other current field's value)
• OnEnter: =[OtherCombo].[SetFocus]

Second combo box (used for editing)

• Bottom of the z-order (set via Send to Back)
• Rowsource restricted based on some other control (eg, CategoryControl)
• Rowsource gets updated on the form's Current event
• Rowsource gets updated on CategoryControl's AfterUpdate event

So the first combo box is never blanked out because the RowSource is less restricted. However, the second combo box lets you control what values a user chooses based on some other field.

If you set each combo box with a different BackColor it becomes obvious what is happening.

Answer 2

Instead of using the filtered combo box's OnEnter event, you need to requery it in these two events:

1. the form's OnCurrent.

2. the first combo box's AfterUpdate event.

This is an Access FAQ (cascading combo boxes) and isn't really all that hard. You just have to think through what it is you're trying to do carefully -- you only want to change the contents of the second combo box when the value in the first combo box changes, and that's why you do it in the two events above, not in the second combo box's OnEnter (which will fire when there's no reason to do so).

EDIT:

I missed the continuous form aspect. In my opinion, this is exactly the type of situation where a continuous form should not be editable at all. Instead, I'd recommend making the continuous form a static list of the records, and binding another subform with LinkMaster/LinkChild to the continuous form's PK field and using that other form (a single form) to edit the detail. You'd use the continuous form to select a record, and the single form to do display the full detail and do the actual editing.