Many-to-many subform setup

[Jon Young]

First database attempt. Struggling with how to build/code a subform
(rowsource, event procedures and any other properties) to be able to
add/modify records in my many-to-many intersection table. So far I have:

TABLES/FIELDS:
tblProduct: lngProductID (PK), strProduct
tblProcess: lngProcessID (PK), strProcess
tblLINKProduct_Process: lngProductID (PK), lngProcessID (PK),
lngProcessOrder.
Referential integrity enforced.

FORMS:
frmProduct
fsubProcess

EXAMPLE:
ProductA - 1) cut, 2) drill, 3) cut, 4) paint
ProductB - 1) drill, 2) paint, 3) cut, 4) sand

I want to be able to select a product record on the form and then add/modify
processes and/or the process order on the subform. In the example above, for
instance, change ProductA to 1) drill, 2) paint, 3) sand, 4) cut, 5) assemble
in the subform datasheet. I've tried various ways, but no joy. Thanks in
advance for any guidance.

 

[Al Camp]

Jon,
I would have a ProductID in each table, and use that as the link for a
One to Many relationship. Each Product has a series of operations that must
be tracked, so as far as I can see... this is a One to Many relationship.
You could also add a ProcessID as a unique identifier for each
process/operation record.
The subform would display multiple processes against each ProductID.

Base the main frmProducts query/table on tblProduct, the sub fsubProcess
on table/query tblProcess. Now your form will display all the Product
records in one recordset, and allow you to Add/Delete/Update any Product.

I want to be able to select a product record on the form and then
add/modify
processes and/or the process order on the subform.

Now... place an "unbound combo" on your main form. Populate it with all
Products, and when you select a Product or ProductID (use AfterUpdate and
use FindRecord), you could zoom right to the specific record, and
edit/add/delete any sub records you want.
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions