SubForm #Error when adding a complex PK

Team,

I have been developing a simple access database that records report information. There are two tables. The master and details tables.

In working version of the original version the link between the two tables was just ReportID and the link between the master form and subform was just the ReportID. When a new detail record was prepared, the ReportID was populated in the Subform ready.

The Problem started to happen when I decided that I need to add a BuildingID as well the ReportID in the relationship. I added BuildingID to both Master and detail tables. To ensure the referential integrity I created a PK in the master of RecordID and BuildingID, this is set to be Unique in the Master.

The relationship was changed to link the two fields from the Master table PK to the related fields in the detail table.

The Child and Master links were set to be both RecordID & BuildingID.

When a ReportID and BuildingID combination is selected in the drop down on the detail form. The Master Report information is selected correctly. But when it is tried to created a new Detail record the BuildingID has #Error but the Detail ReportID is correctly populated.

I have tried a wide variety of changes to the PKs in both tables and also the various relationships possible between Master and Subform.

Can anyone tell me what I am doing incorrectly or what changes to the Table / References need to be made ?

Regards,

John de

I have had a look at your question, and I might see the problem but I need to check.
I will download your samlpe and investigate.

Thanks,

If it would help I could upload the working version that only makes use of the ReportID ?

John de

John de,

When a new record is added via the subform, then foreign key in the subform will be automatically filled ONLY if the link is composed by ONE field. Access cannot fill both fields if the foreign key is composed by more than one field. So you need to fill BuildingID from VB code.

Enclosed the database that does this for you.

I simply included a line as the BeforeInsert event handler of your subform called 'AsbestosDataSubMaster'.

When you step to a new record in the subform, the BuildingID field will return #Error, but as soon as you type the first character of the new record the BuildingID field will be filled.

This solution is not the most elegant, but you can use it as a start point.
You can, for example, check if #Error returned, and if yes, then you can set the value of BuildingID.Visible to False in the OnCurrent event of the subform. And, when the first character is typed in a new record, you can set it back to True. Or you can use an unbound field just to hide the field with #Error.

Or, in the OnCurent event, you can check the subform's RecordSet.RecordCount and RecordSet.AbsolutePosition properties. AbsolutePosition is indexed from 0 so when you on the first record then it's 0, when you on the last record, it's RecordCount-1.

Play with it.

Thanks for answering the reason why it was not working. I will give it a go later today and let you know if I was successful.

Regards,

John de

Tried you example database and see what you mean. The idea of trapping the #Error as it appears do the same as you have.

Thanks again.

John de

TBÁrpi,

Thanks for the initial comment about only one field being populated. This helped so much as others had indicated it was a complete change to the db structure !Thanks for the initial comment about only one field being populated. This helped so much as others had indicated it was a complete change to the db structure !

I tried to work on the OnCurrent event and have been able to trap the error and then updated the BuildingID.

This is the code I have created with your original code.

'Private Sub Form_BeforeInsert(Cancel As Integer)
' BuildingID = Forms!asbestosdatamaster.BuildingID1
'End Sub

Private Sub Form_Current()
On Error GoTo Form_error1_err
If Me!BuildingID.Value = "" Then
Me!BuildingID = Forms!asbestosdatamaster.BuildingID1
Else
End If
Form_Exit:
Exit Sub
Form_error1_err:
BuildingID = Forms!asbestosdatamaster.BuildingID1
MsgBox "You are adding a new Record to this report!" 'Err.Description
Resume Form_Exit
End Sub

I would like to add the facility that if the user does not want to add another detail record they can escape and this would go back to the last populated record.

Any thoughts ?

Thanks again a great move forward in my world !

John de