Daipayan Sarkar February 2016

Unable to add record. Joinkey of table not in recordset?MS Acess 2007

I have been trying to have a form where I can enter all my patient details along with the medicines prescribed. I have created 5 tables for this.

  • tblpatients for patient details with Patient ID as primary.
  • tblvisit for visit details like visitdt,tests,etc, with Visit_ID as primary.
  • tblmedicines for list of medicines(which I can add dynamically).
  • tblqnty for specific doses and qnty mentioned during a specific visit.

I have created a form with all patient and visit info and a subform inside the form with medicines and qnty(in tabular form).

Now I am facing 2 issues:

  1. I am unable to set the subform to blank by default on load the mainform as new record. I have tried that even on subform but not working.

  2. While trying to add a record in the subform I receive the error message "Unable to add record. Joinkey of table tbkqnty not in recordset?"

This is killing me .Any help will be appreciated.

Database relationship

Data entry Form and subform

Answers


Cisco February 2016

1) If with 'blank' you mean 'empty': look at point 2)

2) In the recordset of your subform you MUST have the field Visit_ID. If you don't want to show it in the subform set it to Visible: No

3) Link the two forms (main- and sub-) with Visit_ID (Table tblVisit) as Link Master Field and Visit_ID (Table tblmedicines) as Link Child Field in the Main form 'Data' Property Sheet TAB.

4) In the subform, in the before_Update event set

Sub Form_BeforeUpdate(Cancel As Integer)

   Visit_ID = Me.Parent!Visit_ID

End Sub

(supposed that Control Names are equal to the Field Names).

This will ensure that all records in the child table tblMedicines are related to one record in the table tblVisit.

Hope this helps.

Post Status

Asked in February 2016
Viewed 3,141 times
Voted 9
Answered 1 times

Search




Leave an answer