assign multiple fabrics to window treatments

  • Thread starter Thread starter EvilSewingMachine
  • Start date Start date
E

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?
 
How have you set your tables up and what are the relationships?
Click to expand...

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?
 
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.
 
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
 
Back
Top