assign multiple fabrics to window treatments

[EvilSewingMachine]

My Main form has a two subforms, frm_Jobs is a list of jobs and
frm_Tabs holds 4 tabs with, each tab has a form; frm_Drapery,
frm_Cornice, frm_Valance, frm_Shade. The field [ActiveJobID] on the
main form is controlled by frm_Jobs. The queries for the 4 forms
filter based on [ActiveJobID]. On each of the 4 forms I have 3 combo
boxes; Cbo_Main, Cbo_Lining and Cbo_Trim, each one's record source is
qry_Materials, that only allows the selection from a list of fabrics
that have been entered for that particular client, again, based on
[ActiveJobID].
How can I set it up so the combo box changes which fabric is
assigned and displays the data pertaining to that fabric from
qry_Materials?
The problem I'm having is in the Queries I'm building to
calculate what cuts to make out of each fabric. I can't figure out how
to get the dimensions of the fabrics that have been selected into the
queries to calculate cuts. I can get the name to of the fabric that is
selected to show up, but not the dimensions associated with it.

Make sense?

 

[scubadiver]

How have you set your tables up and what are the relationships?

 

[EvilSewingMachine]

How have you set your tables up and what are the relationships?

Tables are as follows:
tbl_Customers, PK_CustomerID
one to many
tbl_Jobs, PK_JobID
one to many
tbl_Specs, PK_TreatmentID

tbl_Materials does not have a relationship to tbl_Specs. I figured I
could use combo boxes in the order entry forms to select records from
tbl_Materials and store the MaterialID in tbl_Specs, then refer to
that MaterialID in tbl_Specs later to do my calculations with. (which
by the way doesn't seem to be working)

Should I have made a many to many relationship between tbl_Specs and
tbl_Materials, because multiple fabrics can be assigned to a treatment
and multiple treatments can have a fabric?

 

[scubadiver]

All definite relationships have to be defined so you should have a junction
table between specifications and materials.

Create a form and subform between the two and put the spec in the main form
and the materials in the subform. I would put the combo box in the subform
that use the spec main form as the source.

 

[EvilSewingMachine]

Ok, I set up a M2M relationship between tbl_Specs and tbl_Materials
via the junction table like you suggest. I put a combo box on
frm_Specs to select the MaterialID. The record source for the combo
box is [Fabric1] on tbl_Specs. Then I added a subform based on a query
that uses [Fabric1] as it's criteria to find the right fabric. That
all seems to be working just fine.

My problem before was a result of putting the MaterialID combo box on
sfrm_Specs without having the fabric information appear on another
subform. I was trying to get it to all come from the record source of
sfrm_Specs.

Now that I have the fabric information showing in the correct place, I
need to make calculations on the report with it. I'll work on that
after I get everything tidied up with this issue.

Thanks Scuba