Handling No Existing Record

[Sprinks]

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

- ProductVendors. Each Vendor who carries a given Product.
- Products. MfrID, MfrPN, and ProductName of each Product.
- ProductContainers. The ContainerID and Qty of each Container in a Product.
- Containers. The Name, Size, and Unit of each Container.
- ContainerSubstances. The ID and % of each Substance in a Container
- Substances. The Name, CAS #s and Hazard Class of each Substance

When entering a new Product, I am unsure of how to handle cases when there
is no existing Container record, or subsequently if there is no existing
Substance record in the DB. I’ve thought of displaying another form/subform
based on Containers/ContainerSubstances for the former and Substances for the
latter, then requerying the upstream combo boxes, but it seems kludgy. Does
anyone have a better idea?

Thank you.

Sprinks

 

[Jeanette Cunningham]

One thing that is often done (I think), is to put a small button with a plus
sign on it next to the combo where you choose a container.
If user wants to add a new container they click the button and a popup form
opens to add a new container. When the popup form closes, you requery the
combo for containers and put the just-added container name in the combo
ready for user to use.

You could also use the Not In List event of the combo to let user add a new
combo.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Tom van Stiphout]

On Mon, 8 Feb 2010 11:20:06 -0800, Sprinks

If Containers and Substances are in dropdowns, you could set
LimitToList to True, and then handle the NotInList event in case the
user enters a value that does not exist.
In A2007 there is a ListItemsEditForm property that can be helpful as
well.

-Tom.
Microsoft Access MVP