Assigning Values to unbound Controls?

[Guest]

I'm working on an inventory of chemicals, where the user selects from a set
of pre-defined hazards.

These records are to be displayed on a subform, which links the primary key
of the chemical record with a linking table (ChemID, HazardID).

The problem is, there are lots of different hazards and they're classified
according to type. I'd like the user to first select the hazard category an
then select the hazard from a list filtered by the selection in the first
combo box.

I've tried using an unbound control whose values are used in the source
query for the hazard list, however, when I come back to the form, only the
hazards are displayed and not the categories used to provide the filter.

Any suggestions welcome!

Thanks,

Scott A

 

[Guest]

Scott:

Utilize an unbound combo box cboHazardCategory and set it Row Source equal
to a query you have built or to an SQL statement you have built using the
building brick to the right of the Row Source property of the combo box.
Have the query read from where you have all the Hazard categories stored.
Set the query's sorting as desired and possibly it Unique Values property is
your original data source requires it so you only each Hazard category only
once in the resulting combo box.

Next, modify the query/SQL in the Row Source of the Hazard combo box in the
subform by adding a filter to the Hazard Category (hidden field most likely -
the Show box will not be checked, it is there only to act as a filter for the
resulting list) of Forms!MainFormName!cboHazardCategory or
Forms!MainFormName!SubformControlName.Form!cboHazardCategory if the Hazard
Category combo box is located inside the subform control.

Next, place some code on the AfterUpdate event of the Hazard Category combo
box which requeries the Hazard combo box and thus only shows 'hazards' of the
category currently showing in the Hazard Category combo box.

Once again, this code will vary according to where you have place your
Hazard Category combo box.

Me!Hazard.Requery (if Hazard Category combo is in subform)
or
Me!SubformControlName.Form!Hazard.Requery (if Hazard Category combo is in
main form)

There are other issues which will most likely come into play here. But
perhaps this will get you started and provide hope and a direction toward
your answer.

Seth Schwarm

 

[Guest]

Dear Seth,

Thanks for your suggestions - that's basically what I had to begin with.

The problem that I still have is that when the user comes back to the form,
only the hazards are displayed and not the categories to which they belong,
as the category field is unbound.

Is it possible to set the unbound combo on the subform so that it will
display the category associated with the hazard when the form is refreshed or
reloaded?

Thanks,

Scott A