Use 1 form to add new items to 2 databases and update junction tab

[Michael_Randall]

Hello,

I'm having trouble with the concept of junction tables and forms (Access
2003).

Suppose I am in charge of entering data into the databases and we have a new
product with at least 1 new feature (never been entered into the base tables)
- This is a rare instance but could happen.

I would like to create 1 form that would allow me to enter a new ProductName
and a new FeatureName. After I click the enter button, the tblProduct,
tblFeature, and tblProductFeature tables will all be updated with the new
values.

Current Tables and structure
tblProduct (ProductID, ProductName) - ProductID is autonumber
tblFeature (FeatureID, FeatureName) - FeatureID is autonumber
tblProductFeature (ProductID, FeatureID) - one product can have many
features and a feature can be included in many different products.

I've already created the relationships with cascading updates.

Questions
Would this require programming or can this be done using queries designed
with the query wizards and forms designed with the form wizards?

If I already have the relationships set up between the base tables and the
junction table, and cascading updates is selected, do I need to take an extra
step to update the junction table, or will it be updated when I enter the
values in the base tables?

Do you have recommendations or best/common practices for this situation? For
example, is it better to create a seperate form with lookup columns to update
the junction table. (I read an article that mentioned that lookup columns are
not good)?

Eventually, it would be good for me to type a product name and if it is
already in the database, I would receive a message, otherwise, I would be
able to click enter and it would be entered into the database.

 

[Arvin Meyer [MVP]]

Normally 2 forms and 1 or 2 subforms are used.

On frmProduct, you have a subform for ProductFeature
Likewise on frmFeature you can have the same subform.

Often there will be 2 somewhat identical subforms because the link field is
updated automatically, and the 2 forms use opposite link fields (which do
not have to be displayed).

If the record is completely new to both tables you need to open one of them
and update the main record, then close it and open the second form. On the
second form, you can then update both the main and subform records.