modelling M:M relationship in a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a table that allows me to look up a cost factor based on 3 criteria.
So the table has 3 foreign keys and another column that contains the cost
factor.
I am trying to design a form to maintain the cost factor data. So what I
would like to do is create a form with a drop down that contains one of the
three criteria and also a data sheet type table whether the row and column
headings would be the other 2 criteria.

Once the user chooses a value in the drop down, the table will be populated
with all the cost factors corresponding to what was chosen in the drop down.
Then by looking at the row/column headings the user would be able to update
the exact cost factor.

Does Access support such functionality? How would I go about creating such a
table?

Thanks.
 
Ok, I figured out that a Pivot Table is exactly what I needed. I set one of
the criteria as the filter, and the other 2 as rows and columns. The problem
now is that I cannot edit anything. I tried setting AllowEdits,
AllowAdditions, AllowDeletions, DataEntry to Yes, but to no avail.

I feel that this should work, but for some reason it doesn't. Any comments
would be much appreciated.
 
A pivot table query AFAIK always gives a read-only recordset. I do not know
of any way to influence this.

Pete
 
suggest you use a mainform/subform setup. first, create an *unbound* form,
and put an *unbound* combobox control (dropdown) in it; i'll call it
cboFactorA. for the combobox RowSource, use the table that supplies the
foreign key values for the first foreign key field in the cost factors
table.

next, create a form bound to the cost factors table, and set its'
DefaultView to Datasheet; this will be used as the subform, i'll call it
sfrmCostFactors. make sure the first foreign key field is included in the
form's RecordSource, but do *not* bind a control to the field in form Design
view.

next, open the main form in Design view, and add a subform control to the
form. set the control's SourceObject property to sfrmCostFactors. set the
LinkChildFields property to the name of the first foreign key field (the one
that is not bound to a control on the subform), surrounded by brackets; do
*not* include the table name or anything else but the fieldname. set the
LinkMasterFields property to [cboFactorA].

now, when you open the mainform in Form view and choose a value in the combo
box control, the subform should populate with all the records in the cost
factors table that have that value in the first foreign key field.

note that if you want to further "filter" the records in the subform, you
can use two combobox controls on the mainform, setting the RowSource of the
second combobox (i'll call it cboFactorB) to the table that supplies the
foreign key values for the second foreign key field in the cost factors
table. remove the control in the subform that's bound to that second foreign
key field, and add the fieldname to the LinkChildFields property in the
mainform's subform control, and add the combobox name to the
LinkMasterFields property, as
[cboFactorA]; [cboFactorB]

hth


next, go back to the
 
Back
Top