Restricting data updates on Subforms

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

Guest

I have a main form called frmControls which allows the user to enter
controls. This form has a subform called subfrmRisks which allows the user to
select from a drop down list containing predefined risks that the control can
be allocated to. The list of risks are being read from a table called
TBLRisks. This then updates a linking table (TBLRiskstoControls) that
resolves the many to many relationship between TBLRisks and TBLControls that
contains the risk id and the control id. However it is also updating TBLRisks
with the chosen risk therefore making duplicate entries. How can I allow the
user to select the risk but then limit the update to only updating
TBLRiskstoControls.

Thanks

Colette
 
ColetteD

Check that the RecordSource prperty is set to the linking table
(tblRisksToControls), rather than the table tblRisks. also, check that the
primary key for the linking table is comprised of both ControlID & RisksID.

It wounds as though the field RiskDescr (or something similar) for the table
tblRisks should have the index property set to Yes - No Duplicates.

Hope this helps, if not give some more details
 
Hi Ross

Did you mean the record source of the sub form is so this is curerrently the
record source for my subfrmRisks

SELECT TBLRisktoControl.[Control ID], TBLRisktoControl.[Risk ID],
TBLRisks.Risk_desc, TBLRisks.Area, TBLRisks.Issue, TBLRisks.Team
FROM TBLControls INNER JOIN (TBLRisks INNER JOIN TBLRisktoControl ON
TBLRisks.ID = TBLRisktoControl.[Risk ID]) ON TBLControls.[Control ID] =
TBLRisktoControl.[Control ID];

The additional fields for Area, Team & Issue are being selected because the
sub form also allows the user to key in one of these to help filter the list
of risks for ease of searching.

This is done by the row source for the object Risk_id on subfrmRisks being
set to

the row source for the object risk_id on the subfrmRisks is set to

SELECT TBLRisks.ID, TBLRisks.Risk_desc, TBLRisks.Area, TBLRisks.Issue,
TBLRisks.Team
FROM TBLRisks
WHERE (((TBLRisks.Area) Like "*" &
[Forms]![frmControls]![sfrmRisk].[Form]![RiskFilter])) OR (((TBLRisks.Issue)
Like "*" & [Forms]![frmControls]![sfrmRisk].[Form]![RiskFilter])) OR
(((TBLRisks.Team) Like "*" &
[Forms]![frmControls]![sfrmRisk].[Form]![RiskFilter]));


I'm not sure how I can change this. If I make the record source property to
be TBLRiskstoControls the form is just reading the risk_id from
TBLRiskstoControls but then I get no risk_desc


I've tried making a combined primary key in the TBLRiskstoControls but it
doesnt like this as it says there are duplicate values, there are but only in
the Risk_id column where the same risk has been assigned to various controls.

Setting the RiskDesc to not allow duplicates prevents the user allocating
the risk to the control in the main form as it says it cannot update TBLRisks
because of the duplicate but isnt allowing TBLRiskstoControls to be updated.

Not much progress I'm afraid but see if this helps

Thanks

Colette
 
Back
Top