Results 1 to 9 of 9

Thread: linking (2000)

  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 (2000)

    Hello, I have a one to many relation and the many records set on a different form opened by a button on the one form. Trouble is when I come to add records to the many. the relation primary key does not auto appear in the many record. Is there some code I can add in to correct the problem?

    Thanks Daren.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking (2000)

    The usual way is to put the many records in a subform of the main form, then use the Master/Child Linking fields property of the subform to automatically populate the fields. Without using the form/subform method, you would need to use VBA code to look up the keyfield from one form and populate it into another form, which could get quite messy.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  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 (2000)

    Thanks for that, I could do with the relevant VB code if anyone is able to help.

    Thanks Darren.

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

    Re: linking (2000)

    I have attached a simple example based on the Categories and Products tables from the Northwind sample database.

    There is a command button cmdProducts on the frmCategories form. The On Click code is:

    Private Sub cmdProducts_Click()
    ' Get out if no category
    If IsNull(Me.CategoryID) Then
    Beep
    Me.CategoryName.SetFocus
    Exit Sub
    End If
    ' Save record if necessary
    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If
    ' Open products form
    DoCmd.OpenForm FormName:="frmProducts", _
    WhereCondition:="CategoryID = " & Me.CategoryID, _
    WindowMode:=acDialog, _
    OpenArgs:=Me.CategoryID
    End Sub

    First, the current record is saved if necessary, them frmProducts is opened. The WhereCondition argument is used to filter frmProducts, and the OpenArgs argument passes the current CategoryID. This is picked up in the On Open argument of frmProducts to set the default value for CategoryID to that from frmCategories:

    Private Sub Form_Open(Cancel As Integer)
    ' Set default value for CategoryID if provided
    If Not IsNull(Me.OpenArgs) Then
    Me.CategoryID.DefaultValue = Me.OpenArgs
    End If
    End Sub

    Note 1: I have left ProductID and CategoryID visible in frmProducts so that you can see what is happening. Normally, you would probably hide these controls by setting their Visible property to No.

    Note 2: If you use a main form and subform, you don't need any code at all, the master and child link fields will take care of everything.

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

    Re: linking (2000)

    Thanks Hans.

    Darren.

  6. #6
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking (2000)

    I am trying to accomplish the same thing. In my main form the user is to input (4) Quantity fields, then in the subfrom the user is to input different information and there are fields calculated against the Quantity fields of the main form. I set up the second form as a subform and linked (3) of the quantity fields, the wizard would not allow anyone more fields to be linked, is there away around this so I can get all four fields to link over.

    Thanks,
    Melanie

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

    Re: linking (2000)

    You can manually add more fields to the Link Master Fields and Link Child Fields properties of the subform (as a control of the main form). But you shouldn't link on calculated fields, I don't see why you want want to bring them over.

  8. #8
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking (2000)

    I created a query and have the subform based off of the query, the calculated fields do not show up unless i link the quanity fields linked, is there a way around this?

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

    Re: linking (2000)

    I'm not sure I understand. If the calculated fields are in the query that acts as record source of the subform, you should be able to display the calculated fields in the subform.

Posting Permissions

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