From: Clarkyboy420 on
Hi,

I'm currently developing a normalised Access Database to record software
problems on my companies products.

I have a normalised structure of related tables, linked together into a
table called Incident via indexes. There is an associated table called Fix,
where related information is stored. These exist as data entry forms also
called Incident and Fix.

The Fix form is a subform of Incident and on the existing information in the
datasbase, works perfectly, relating the correct Fix record to the correct
Incident record. They are linked through the FixID field which exists as
Primary in Fix and Foreign in Incident. There is no occurence of IncidentID
in Fix, but there is FixID in Incident as FK.

THE PROBLEM:

When I create a new incident record, this works fine. The problem is that
when I wish to enter information in the associated Fix subform, it will not
allow me to enter anything. The error message appears saying that I cannot
add or change a record because a related record is required in table 'Fix'.
If I try to move out of the record, the same error appears. It seems the
record requires a fix record to be assigned against it to confirm the
incident.

I'll be the first to admit I'm a little out of my depth here. Obviously, I
need Incidents to be able to be exist without a fix, as the fix is applied
afterwards. This brings the following questions;

- Firstly, is my subform strategy the flaw, or is it that I am just not
implementing it properly due to lack of knowledge?
- Secondly, If my subform strategy is workable, what do I need to do to
allow a fix record to be attached later? FixID and IncidentID are
AutoNumbers, so I assumed the number would be applied automatically, but
obviously the Fix record needs to append its key ref to the Incident table -
can this be done through an event procedure on a 'new fix' button perhaps?

I'm really baffled as to what the issue is and if I'm honest, I don't even
know what topics I should be researching to identify a remedy.

I am more than happy to discuss the details further with any helpful soul
out there with a good samaritan's heart!!!!

From: Tom van Stiphout on
On Wed, 28 Oct 2009 07:38:01 -0700, Clarkyboy420
<Clarkyboy420(a)discussions.microsoft.com> wrote:

You may have an incorrect db design. It sounds like you are creating a
new Fix record for each Incident. However, some fixes apply to several
incidents I would think, and some incidents require several fixes.
Therefore a M:M relationship would be needed.

-Tom.
Microsoft Access MVP


>Hi,
>
>I'm currently developing a normalised Access Database to record software
>problems on my companies products.
>
>I have a normalised structure of related tables, linked together into a
>table called Incident via indexes. There is an associated table called Fix,
>where related information is stored. These exist as data entry forms also
>called Incident and Fix.
>
>The Fix form is a subform of Incident and on the existing information in the
>datasbase, works perfectly, relating the correct Fix record to the correct
>Incident record. They are linked through the FixID field which exists as
>Primary in Fix and Foreign in Incident. There is no occurence of IncidentID
>in Fix, but there is FixID in Incident as FK.
>
>THE PROBLEM:
>
>When I create a new incident record, this works fine. The problem is that
>when I wish to enter information in the associated Fix subform, it will not
>allow me to enter anything. The error message appears saying that I cannot
>add or change a record because a related record is required in table 'Fix'.
>If I try to move out of the record, the same error appears. It seems the
>record requires a fix record to be assigned against it to confirm the
>incident.
>
>I'll be the first to admit I'm a little out of my depth here. Obviously, I
>need Incidents to be able to be exist without a fix, as the fix is applied
>afterwards. This brings the following questions;
>
>- Firstly, is my subform strategy the flaw, or is it that I am just not
>implementing it properly due to lack of knowledge?
>- Secondly, If my subform strategy is workable, what do I need to do to
>allow a fix record to be attached later? FixID and IncidentID are
>AutoNumbers, so I assumed the number would be applied automatically, but
>obviously the Fix record needs to append its key ref to the Incident table -
>can this be done through an event procedure on a 'new fix' button perhaps?
>
>I'm really baffled as to what the issue is and if I'm honest, I don't even
>know what topics I should be researching to identify a remedy.
>
>I am more than happy to discuss the details further with any helpful soul
>out there with a good samaritan's heart!!!!