Question Strange Error (1 Viewer)

[khwaja]

I have a safety incident form where we record an incident against a project, sometimes this incident may involve a person and when this happens we record injuries in a sub form. So have one to many relationship between projects to incidents, and incidents to persons and persons to injuries. The whole thing worked very well and still does except for the the injuries table where I am getting an error when I am trying to select an injury type from the drop down.

The LinkMasterFields property setting has produced this error: 'A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control

I am completely stumped as I have tried deleting linked table and adding back, deleting form and adding back, deleting master/child link, created a new form and re established link, but none of this seems to work. Compacted the db too. Checked refs and compiled the db.

The only oemenous signal I have is that the master/chil link is visible even when I created a new form and tried to link which usually disappears on such occasions. If I dismiss the above message, I am able to populate with the value but then this followed by the error message:

You cannot add or change a record because a related record is required in the tablePerson. This is quite perplexing as it has worked previously and the relationship is correct.

All my tables are accurately linked with PK and FK. Will appreciate if someone can provide insight. I have attached a screenshot of the form itself and deleted some personal details for privacy.

 

[CJ_London]

Two possibilities come to mind, one is the form has become corrupted and the other is that you have added or changed a record in your injuries table and included an illegal character.

If you have a corrupted form you can either rebuild it or perhaps first try a decompile/recompile.

If your db is not split, this can also lead to problems eventually.

 

[khwaja]

Thanks. The db is split. And I did recreate the form. By looking at the values in the tblnjury, all seems good because it has only one field (apart from PK and FK) and that too has a handful of records. The only difference however is that in this table there are some values that were added using a different combo box drop down. I changed that value list sometimes back. So historical records are not matching with the current combo values. But that should not be an issue.

 

[The_Doc_Man]

I changed that value list sometimes back. So historical records are not matching with the current combo values. But that should not be an issue.

This is an "aside" because I'm not fully up on SQL-server backends. However, I believe this has denormalized or otherwise damaged your database. If you are using a combo box, normally you want the PK of the of table driving that box to be part of the solution, but what you have is inconsistent. That means the PK isn't really a PK. OK, it's not a hard-and-fast rule, but it is generally a good idea. The problem with data inconsistency is that you can't depend on the data anymore. I note that you are using a value list, but basically what you did when you changed the list is you broke with past records that had used that combo box. I doubt that fixing this will fix your other problem - but it couldn't hurt.

 

[khwaja]

Thanks. It is a MS Access back end. I do take your point that this might denormalised. It is a huge database and not too sure how to deal with this. May be recreate the table altogether.