Linked SubForm Issues - cannot edit in subform or move from main r

[Clarkyboy420]

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!!!!

 

[Tom van Stiphout]

On Wed, 28 Oct 2009 07:38:01 -0700, Clarkyboy420

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

 

[Clarkyboy420]

Hi Tom,

Well, I've been doing some looking into this problem and I think you are
right. The main problem is that I have the many side of the 1:m in the parent
table... Figured out this wont work at all... You are correct, I need a many
to many relationship, but I am unsure how to do this in Access, I'm aware
that a 1:m or m:1 relationship is formed by the position of the Primary and
Foreign Keys (ie the PK denoting the one side), but how would I do a
many:many? Would I need to have FK and PK in both tables, both linked or
something?

Apologies if my understanding is poor, I have done some theoretical study
into relational databases and normalisation, but this is the first time I
have been able to apply it to a real db. I'm finding it's not as easy as I
thought to get access to get my existin data into the model designed through
ERDs.

I appreciate any help/advice you can offer