MS Access 2007: Filtering selection list for a combo box

0
votes

Background: The Record Source for my form is a query ("BigQuery") that just combines several related tables. I am setting up combo boxes to edit fields; the Control Source for these combo boxes is always just a field from BigQuery. One of the fields is UnitType, and another is UnitSubType. There is about 100 distinct entries for UnitSubType, but many of them make no contextual sense when paired with a particular UnitType: If UnitType="Car", then UnitSubType="18 wheeler" makes no sense, and I'd just as soon not give the client the opportunity to make mistakes.

Question, Part A: When the user chooses a value for UnitType on the form, I would like to limit the combo box for UnitSubType to those UnitSubType values already paired with UnitType values in the database. How is this done?

Example: If 1 or more instances of a record containing UnitType="truck" and "UnitSubType="18 wheeler" already exist in the table, then assuming that the user has already selected "truck" in the combo box for UnitType one of the choices presented in the combo box for UnitSubType should be "18 wheeler".

Question, Part B: I would also like for the user to be able to add a new UnitSubType simply by typing it into the combo box: if the user has already selected "truck" in the combo box for UnitType and manually types "flatbed" in the combo box for UnitSubType, then the edited record should have "flatbed" in the UnitSubType and future editing operations should include "flatbed" as a UnitSubType choice whenever the UnitType is "truck". In simpler situations setting "Allow Value List Edits" to "Yes" took care of this, but I want to make sure this functionality is available in the solution provided to Question Part A.

There are similar question threads already in SO, but I am such a noob at Access that I have been unable to extrapolate the answers to fit my need. I am sorry; please, be as specific as possible.

Thank you so much! Dave

1
ms-accesscomboboxms-access-2007

1 Answers

0
votes

I've arrived at one solution that I wanted to share. Access is difficult to describe, but I will list exceptions to the norm and try to communicate the solution that way.

First (primary) Combo Box for picking the Unit Type:

  • Name: CBUnitType
  • ControlSource: UnitType '''A field in the Main Table: MainTbl

  • Row Source:

    SELECT DISTINCT MainTbl.UnitType
FROM MainTbl
ORDER BY MainTbl.UnitType;

  • Bound Column: 1

  • Allow Value List Edits: Yes
  • Locked: No
  • After Update: [Event Procedure] '''See subroutine: ComboBoxUnitType_AfterUpdate() shown below.

Second (dependent) Combo Box for picking the Unit Sub-Type:

  • Name: CBUnitSubType
  • ControlSource: UnitSubType '''A field in the Main Table: MainTbl
  • Row Source: ComboQueryUnitSubType '''Built by Query Builder, detailed below.
  • Bound Column: 1
  • Allow Value List Edits: Yes
  • Locked: No

Query Builder object: ComboQueryUnitSubType

SELECT DISTINCT [MainTbl].UnitSubType
FROM [MainTbl]
WHERE ((([MainTbl].UnitType)=[Forms]![Unit Editor]![UnitType]))
ORDER BY [MainTbl].UnitSubType;

VBA subroutine created by selecting "[Event Procedure] for the After Update event in combo box "CBUnitType" described above. The VBA subroutine is automatically placed in the Microsoft Office Access Class Objects folder in the VBA environment in a module named: "Form_Unit Editor". The subroutine name is also pre-chosen to be: "ComboBoxUnitType_AfterUpdate()" Almost certainly if you change any of these names the linkages will break horribly. The VBA code in the module is:

Option Compare Database
Option Explicit

Private Sub ComboBoxUnitType_AfterUpdate()
    Forms![Unit Editor]![ComboBoxUnitSubType].Requery
    Forms![Unit Editor]![ComboBoxUnitSubType].Value = ""
End Sub

So, the effect is this: After the user updates the combo box for Unit Type, the vba routine executes and re-queries the query for the combo box for Unit SubType, and then it arbitrarily takes the .value parameter of the Unit SubType combo box and clears it to the empty string.

I would like to gratefully acknowledge the generous tutorials provided by Blue Claw Database Design. Specifically, the VBA code that re-queries the combo box query was a life-saver, and is detailed in their tutorial on Dependent Drop Down List Box Why the query ComboQueryUnitSubType, specified as the combo box's Row Source, is not re-run automatically by Access every time the combo box is selected by the user is anyone's guess.

I'm sorry for both the long-winded question and answer. I hope to be heading back to some nice, terse code in the near future! Dave