Form Design/Multi-Level

[Sprinks]

I have a chemical product DB with the following hierarchical structure:

- ProductVendors. Record for each vendor who carries a given Product.
- Products MfrID, MfrPN, and Name of each Product that may be ordered.
- ProductContainers. Each type and quantity of each Container in a product
- Containers. Name, size, and unit of each Container.
- ContainerSubstances. Percentage of each Substance in a Container.
- Substances. The Name, CAS Registry #s, and Hazard Class of each Substance.

Substances may be in more than one Container, a Container may be in more
than one Product.

When entering a Product that has never been ordered before, I'm unsure of
how to handle cases where the Containers in the Product or the Substances in
the Container are not currently in the DB. I've thought of showing a form
based on Containers with a subform of ContainerSubstances in the first case
and subsequently a form based on ContainerSubstances and a subform of
Substances for the latter, and then requerying the combo boxes on the initial
form, but it seems cludgy. Does anyone have a better idea?

Thank you.
Sprinks

 

[Jeanette Cunningham]

To add a new product, you need a form based on the Product table.
To add a new container you need a form based on the ProductContainers table.
It is probably easier with a simple popup form to add a new product, and
another simple popup form to add a new container.
Then you can open an edit form at the product just added and user can choose
the other details from appropriate subforms/combos.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Larry Linson]

And, assuming you are choosing these items from a Combo Box (which is
certainly the approach I'd use), you should set the Combo Box's Limit To
List property to Yes, and open the popup Form from the Not-In-List event of
the Combo Box. There are some "intrinsic constants" that you need to set
when running a Not-In-List event, so check for examples... there are many
available.

Larry Linson
Microsoft Office Access MVP