combobox - MS Access 2007: Filtering selection list for a combo box -

background: record source form query ("bigquery") combines several related tables. setting combo boxes edit fields; control source these combo boxes field bigquery. 1 of fields unittype, , unitsubtype. there 100 distinct entries unitsubtype, many of them make no contextual sense when paired particular unittype: if unittype="car", unitsubtype="18 wheeler" makes no sense, , i'd not give client opportunity make mistakes.

question, part a: when user chooses value unittype on form, limit combo box unitsubtype unitsubtype values paired unittype values in database. how done?

example: if 1 or more instances of record containing unittype="truck" , "unitsubtype="18 wheeler" exist in table, assuming user has selected "truck" in combo box unittype 1 of choices presented in combo box unitsubtype should "18 wheeler".

question, part b: user able add new unitsubtype typing combo box: if user has selected "truck" in combo box unittype , manually types "flatbed" in combo box unitsubtype, edited record should have "flatbed" in unitsubtype , future editing operations should include "flatbed" unitsubtype choice whenever unittype "truck". in simpler situations setting "allow value list edits" "yes" took care of this, want make sure functionality available in solution provided question part a.

there similar question threads in so, such noob @ access have been unable extrapolate answers fit need. sorry; please, specific possible.

thank much! dave

i've arrived @ 1 solution wanted share. access difficult describe, list exceptions norm , try communicate solution way.

first (primary) combo box picking unit type:

• name: cbunittype
• controlsource: unittype '''a field in main table: maintbl

• row source:

  select distinct maintbl.unittype maintbl order 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 picking unit sub-type:

• name: cbunitsubtype
• controlsource: unitsubtype '''a field in main table: maintbl
• row source: comboqueryunitsubtype '''built query builder, detailed below.
• bound column: 1
• allow value list edits: yes
• locked: no

query builder object: comboqueryunitsubtype

select distinct [maintbl].unitsubtype [maintbl] ((([maintbl].unittype)=[forms]![unit editor]![unittype])) order [maintbl].unitsubtype; 

vba subroutine created selecting "[event procedure] after update event in combo box "cbunittype" described above. vba subroutine automatically placed in microsoft office access class objects folder in vba environment in module named: "form_unit editor". subroutine name pre-chosen be: "comboboxunittype_afterupdate()" if change of these names linkages break horribly. vba code in module is:

option compare database option explicit  private sub comboboxunittype_afterupdate()     forms![unit editor]![comboboxunitsubtype].requery     forms![unit editor]![comboboxunitsubtype].value = "" end sub 

so, effect this: after user updates combo box unit type, vba routine executes , re-queries query combo box unit subtype, , arbitrarily takes .value parameter of unit subtype combo box , clears empty string.

i gratefully acknowledge generous tutorials provided blue claw database design. specifically, vba code re-queries combo box query life-saver, , detailed in tutorial on dependent drop down list box why query comboqueryunitsubtype, specified combo box's row source, not re-run automatically access every time combo box selected user anyone's guess.

i'm sorry both long-winded question , answer. hope heading nice, terse code in near future! dave