Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    linking PK in subforms (2000)

    Hi, Ihave created a form and a seperate related form using the wizard, after entering detail into the main form (1 side of relation) I then attempt to enter the many side info in the link stand alone form yet it won't allow me to add data giving the message to the end of 'you can't update, record required in main form'. When you do a subform as a control within the main form the FK populates automatically on the sub form. Why doesn't Access do this when the linked form is a stand alone form, or does it do it but I have done something wrong?

    Many thanks Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: linking PK in subforms (2000)

    This is an omission of the wizard. I don't know if the code generated by the wizard is exactly the same in Access 2000, but in Access 2002, there is a procedure FilterChildren:

    Private Sub FilterChildForm()
    If Me.NewRecord Then
    Forms![frmChild].DataEntry = True
    Else
    Forms![frmChild].Filter = "[LinkID] = " & Me.[MainID]
    Forms![frmChild].FilterOn = True
    End If
    End Sub

    where:

    frmChild is the name of the "child" form,
    MainID is the PK of the "main" table, and
    LinkID is the corresponding field in the "child" table.

    You need to insert a line immediately above End If to set the default value of the linking field:

    Forms![frmChild]![LinkID].DefaultValue = Me.[MainID]

    I have assumed here that MainID and LinkID are numeric; if they are text fields, the code changes slightly.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking PK in subforms (2000)

    Thanks Hans,

    Kindest regards Darren.

  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking PK in subforms (2000)

    Hello, this is an old thread where I was given a tweak to the wizard code to make a master Id appear in a sub form related ID automatically where the sub form is a stand alone form,. I am just in the process of revisiting this issue and it works fine except on opening a new record in the main form, the ID key is auto number, I can activate the main record fine but the linked form doesn't update to correspond. I have to use a convulted navigate back a record then forward to the current record on my main form for the mainlink ID to appear in the linked form. Is there a slicker way?

    Thanks Darren.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: linking PK in subforms (2000)

    You will have to change several things in the code to make it work. The code as provided by the wizard is geared towards browsing records, not really towards entering new ones. The code could look like the attached, using the same names as earlier in this thread.
    Attached Files Attached Files

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking PK in subforms (2000)

    Thank you Hans

    Darren.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •