including Subform primary key into main form foreign key

Question

The purpose of the main form that I have created is to allow my fellow co-workers to be able to have one place where they can enter in the information about various complaints we get. We have three types of complaints, each having their own tables, of which one is a subform in my main form. The primary key of the table of the subform is entered in by the user. I haven't found a way to have that primary key be added to the record that is produced for that entry. I am working with MS Access 2007 and the only code that came close to doing what I wanted was:

    Private Sub Booking_Reference_Number_Exit(Cancel As Integer)
     If Not IsNull(Form![Booking Reference Number]!PK_Booking_Ref_Number) Then
      Dim mydb As Database
      Dim rst As DAO.Recordset

      Set mydb = CurrentDb()
      Set rst = mydb.OpenRecordset("Incident_Report")

      rst.AddNew
      rst![FK_Booking_Ref_Number] = Form![Booking Reference Number]!PK_Booking_Ref_Number
      rst.Update

      Set rst = Nothing
      Set mydb = Nothing

     End If
    End Sub

Thank you for your help.

And what is the question? What happens when code executes - error message, wrong results, nothing? I don't understand data structure nor the process you are trying to code. Why do you need code to create record? Why not bound forms? — June7
Having the fields linked through the parent child link doesn't fill the foreign key with the value of the primary key for the record that's relevant. Having the fields be in a one-to-many relationship doesn't seem to work either. My understanding that since the fields are from another table, it has to be in a subform. I'm trying to insure that my users only worry about filling out the information on the main form. I would like help with figuring out a code that will accomplish this for me. — thendez
If these tables are intended to have parent/child relationship and the subform container control Master/Child LInks properties are set then the PK from main record will populate the FK field in the subform as soon as any other data is input to subform record. Why would there not be any data for users to input on subform? Sounds like very unusual situation. — June7
The PK is in the subform and the FK is in the main form. I set this up so that the record in the table for the subform is created while filling out the main form — thendez
That's totally backwards to conventional structure and really can't envision any situation where that is practical. — June7

AdzzzUK AdzzzUK · Accepted Answer · 2017-07-21T09:26:47

Have you tried setting the default value of the FK field (on the subform) to be the name of the PK on the main form? For example - [MainForm]![ControlName]

including Subform primary key into main form foreign key

1 Answers