Populate Table Using Combo Box WITHOUT FORM

[jmaher196]

Hello All!

I have been looking around for awhile for an answer to this question, but have been unsuccessful. I currently have a DB with multiple tables (Components, Formulations, Formulation_Details, and Component_Categories). The Components table contains the following headers: Product, JRHPart, Component, MW, and Component Category. All of the information has been filled in, and this table will not change in the future. I have a form that I am using to populate the Formulations table that contains a subform. This subform is linked to the Formulation_Details table that contains these headers: Component, Component Category, MW, mg/L, and Mm.

I would like to set up the subform so the user can select the Component from a drop down list, then the Component Category and MW fields will automatically retreive the desired values from the Components table. They can then move down to the next line and select another component, and so on. I would set it up as seperate boxes on the form to populate each item, but each formulation can have hundreds of components. Any ideas?

The mg/L and Mm fields will be filled in by the user every time. I just want the system to retrieve the value for MW and Component Category.

Thanks!

[Rabbit]

You can use the AfterUpdate event of the component drop down to populate those two other fields. However, there's no reason to store those two other fields if they're derived from the component. You'll just be wasting disk space and breaking the rules of normalization. Whenever you need to see those other fields, you can just join to the components table to retrieve it.

[jmaher196]

The Mm field is a Calculated field based on (MW)/(mg/L). MW being molecular weight, and mg/L being the amount used in the formulation. Will the AfterUpdate property work if the subform is in table view? Sorry, I don't work with subforms very often.

[zmbd]

Jmaher196:
it is not normally best practice to store calculated values such as you describe for [Mm] as these should normally be derived on demand via query/report.

With that said, depending on how the form is designed, so long as the values are already available, the field should be able to be updated with the desired result.

Before you go much farther, I really think that you should consider: 171.Database Normalization and Table Structures
and you might find: http://www.applecore99.com/tut/tutin...ial for Access very usefull.

IMHO, you'll find that the most common trap in database design is trying to use it as a spreadsheet. Although the tables resemble a spreadsheet, they are worlds apart and there are a lot of descussions about that topic by people that know more than I.

[NeoPa]

Certainly the calculable values should be determined by a query rather than being stored. It seems you also need to filter (and possibly cascade-filter) the records from your form though. If so, Cascaded Form Filtering may help.