MS Office Access DB can't find a record....

[martinhough]

If I had any, I'd have pulled it out by now!

I need some expert help please.

I have a main form (unbound) and a main form (bound) and then a subform (or a main form;subform;subform).

The main form has the following SQL;-

SELECT Detail.*, Hazards.*, HowHarmed.*, Section.*
FROM PSR RIGHT JOIN (Hazards INNER JOIN ([Section] INNER JOIN (HowHarmed INNER JOIN Detail ON HowHarmed.ID = Detail.HowHarmedID) ON Section.ID = Detail.[Subject Area]) ON Hazards.ID = Section.detailID) ON PSR.ID = Detail.PSR;

This form appears to work fine. Essentially, there is a control on the unbound form whereby the user selects the section and it populates the respective form with the various 'detail' of the various records.

My tables are as follows;-

Risk Assessments
Detail
Section
HowHarmed
Hazards
PSR
PLR
Staff

In the subform there are records from Riskassessments, which is the table in the source property of this subform (and not on the main form) with a link to PSR of which is on the mainform. Again, these appear to work if the record is entered via the tables - all the tables appear to be linked correctly from their respective dropdowns (+ sign) but when I go to add a new record in this subform, I get the error "The link masterfields property has produced this error: 'The object doesn't contain the automation object ID'." When I try to update from this entry the following message is presented;-

"The ms Office access database engine cannot find a record in the table 'Detail' with key matching field(s) 'DetailID'.

The SQL for this subform is as follows;-

SELECT Detail.*, PLR.*, RiskAssessments.*
FROM (Detail INNER JOIN RiskAssessments ON Detail.ID = RiskAssessments.DetailID) LEFT JOIN PLR ON RiskAssessments.PLR = PLR.ID;

The most frustrating thing is that it did work previously but I've been looking at it for too long and for the life of me can't unravel what I've done to 'unfix' it!

Help? :banghead:

 

[Old Man Devin]

I'm not too sure about this one, but I'll take a stab with an idea.

I think this could be related to the way in which Access tries to link a subform and its parent. When you create a subform through the wizard, as some point it will ask you to tell it a criteria to apply to the records on the subform to make it relevant to the parent e.g. if parent is 'Clients' and subform is 'Orders', you make it only show the orders for the selected client.

My feeling is that this system has been in some way confused in your multiform situation, and when you create a new record, Access is trying to automatically fill in some information about the parent (.e.g., I create a new order and access assumes I mean it to be from the client in the parent form, so automatically fills in that field in the table). In some way, that information is incorrect or not available.

Were I in this situation I would probably remove the subform and carefully set them up again, making sure the thing that links them to the parent is something that will ALWAYS be present in the parent and subform, in every possible incarnation of them.

You might be able to find a way to sort it out without this if my ideas have inspired any ideas of your own! Hope this in some way helps!

 

[martinhough]

Thanks OMD.

Yes, I did consider this - your suggestion gave me the 'push' needed - I've now deleted the subform and re-created it. Same issue though, sadly -but that might throw more light on it? Anyone?

 

[martinhough]

Here's a capture of the relationships.

The problem is definitely with the relationship of the two forms. If I take the Linkmaster field properties out, I get no errors (but obviously the records don't relate correctly).

 

[spikepl]

You do not have any consistent naming convention - do not use spaces in field names. Further, it is normally advantageous to use the same name for the same field - you apply different names to the same field , depending on which table it is in. Also, calling everyting ID makes everything much more difficult. Customarily, one would for example name things as below

tblWidgets
----------
WidgetID (PK)
WidgetName
WidgetSize
WidgetIssueDate
etc ...

Finally, your table Section is connected with the other tables in some weird fashion - just look at it.

I would recommend that you sort out the naming before doing anything else.

 

[martinhough]

Ok - Thanks for the tips. Still the same though. Here's the relationships now;-