locked
Update New Record with Main Form ID RRS feed

  • Question

  • I'm using Access 2010

    I have a main form, with multiple subforms in datasheet view. I don't know how to update the subform ID to the main form ID when creating a new record. For example, My Main form is Location with LocationID as primary key. My subform is OwnerInfo. When I click to add a new Owner, it opens a form in window mode dialog. I can enter the information and it saves it to the OwnerInfo table but I don't know how to link that new owner to the location table, when entering a new owner.

    if i enter a new owner in the main form using the subform it links them just fine, but It's easier to use a popup form to enter owner info. That's the part I don't udnerstand how to link.

    I hope this made sense. Thank you for the help!

    Thursday, February 2, 2012 2:05 PM

Answers

  • If the OwnerInfoDetails form is only opened from the other form you can do this very easily without any code by setting the DefaultValue property of the WellID control in the OwnerInfoDetails form to a reference to the WellID in the main form:

    Forms!Main!WellID

    How you open the OwnerInfoDetails form from the Main form is then immaterial.

    However, if the OwnerInfoDetails  form can be opened independently of the Main form then, rather than setting it in the control's properties sheet, you'd do it in code in the Open event procedure of the OwnerInfoDetails  form, ignoring the error if the Main form is not open.

    On Error Resume Next
    Me.WellID.DefaultValue = """" & Forms!Main!WellID & """"

    Again, how you open the OwnerInfoDetails form from the Main form is immaterial.

    This may not be an entirely bulletproof solution however, as there could conceivably be situations where you open the OwnerInfoDetails form while the Main form is open, but don't want to set the default value of WellID to that of the main form. It's for you to judge whether that is an issue.  If it is, then passing the value via the OpenArgs mechanism is the solution, though I would suggest that the DefaultValue property be set in the form's Open event procedure to the value passed as the OpenArgs argument, or  the value be assigned to the WellID control in the form's BeforeInsert event procedure rather than its BeforeUpdate event procedure.  The value will then be inserted as soon as the user begins to add data for a new record, rather than when the user attempts to save the record.

    BTW the reason for the literal quotes characters delimiting the value for the DefaultValue property in the above code is that the DefaultValue  property is always a string expression regardless of the data type of the column to which the control is bound.  Delimiting the value in this way can be crucial in some circumstances, less so in others, but should always be done regardless of just how crucial this may be.

    One other thing you should do is requery the owners subform after inserting the new record via OwnerInfoDetails.  The new owner will then be shown in the subform.

    Ken Sheridan, Stafford, England
    • Marked as answer by Bruce Song Tuesday, February 14, 2012 9:20 AM
    Friday, February 3, 2012 11:49 PM

All replies

  • I have a main form, with multiple subforms in datasheet view. I don't know how to update the subform ID to the main form ID when creating a new record.
    Set the Master/Child links between the form/subform and when a new record is added to the subform the ID is automatically added to the table.
    Thursday, February 2, 2012 3:31 PM
  • Hi,

    You'll want to pass the Parent ID to the popup form via OpenArgs, then use it in the Popup's BeforeUpdate event.  OpenArgs can only be strings, so if it's a numeric ID, you'll have to convert it to/from as required.  Something like this:

    DoCmd.OpenForm "MyPopup", , , acDialog, Me.fldID
    
    --------------
    
    Option Compare Database
    Option Explicit
    
    private ParentID As Long
    
    Private Sub Form_Open(Cancel As Integer)
    
      'validate an ID was passed
      If Len(Nz(Me.OpenArgs)) = 0 Then
        MsgBox "no openargs passed!"
        Cancel = True
      End If
    
      ParentID = CLng(Me.OpenArgs)
    
    End Sub
    
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
      Me.FKField = ParentID
    
    End Sub
    


    (aircode...)

    hth


    Jack D. Leach (Access MVP)
    UtterAccess Wiki: (Articles, Functions, Classes, VB7 API Declarations and more)
    Thursday, February 2, 2012 3:36 PM
  • Hello,

    Thank you yes, this is what I want to do. I didn't know how to explain it.

    I'm in the Macro Builder and can not find the openArgs action. I have this for my openForm...

    OpenForm (OwnerInfo,Form,,,Add,Dialog) I'm not sure where to pass the parent ID in here.

     

    Thank you so much!!!


    • Edited by URWA Thursday, February 2, 2012 7:55 PM
    Thursday, February 2, 2012 7:55 PM
  • I don't use macros, not sure they have OpenArg ability (I would guess not).

    You can do it through VBA (you'll have to for the popup side of things).

    hth


    Jack D. Leach (Access MVP)
    UtterAccess Wiki: (Articles, Functions, Classes, VB7 API Declarations and more)
    Thursday, February 2, 2012 8:13 PM
  • I went to VBA and I'm kind of confused on which forms to pass the id to.

    I have my "Main" form that contains the WellID I need to pass to the popup form "OwnerInfoDetails". Inside of "Main" form I have a subform called "sbFrmOwnerInfo" that gives me a list of owners for that well in datasheet view. When I have a hyperlink for the "New" field, when clicked opens the popup form "OwnerInfoDetails" which I need the "Main" form WellId passed to.

    I really hope I'm making sense.

    Thank you so much for your help!

    Thursday, February 2, 2012 8:21 PM
  • I forgot to add that I tried the VBA code from above (which my attribute information) in the BeforeUpdate action for the "new" field hyperlink. I'm sure this is the wrong place to put it, correct?

    thank you!

    Thursday, February 2, 2012 8:23 PM
  • where does this code go? Does it go with a button or does it go in with the popup subform? I can't seem to get it working.

     

    Thank you for your help!

    Friday, February 3, 2012 3:37 PM
  • If the OwnerInfoDetails form is only opened from the other form you can do this very easily without any code by setting the DefaultValue property of the WellID control in the OwnerInfoDetails form to a reference to the WellID in the main form:

    Forms!Main!WellID

    How you open the OwnerInfoDetails form from the Main form is then immaterial.

    However, if the OwnerInfoDetails  form can be opened independently of the Main form then, rather than setting it in the control's properties sheet, you'd do it in code in the Open event procedure of the OwnerInfoDetails  form, ignoring the error if the Main form is not open.

    On Error Resume Next
    Me.WellID.DefaultValue = """" & Forms!Main!WellID & """"

    Again, how you open the OwnerInfoDetails form from the Main form is immaterial.

    This may not be an entirely bulletproof solution however, as there could conceivably be situations where you open the OwnerInfoDetails form while the Main form is open, but don't want to set the default value of WellID to that of the main form. It's for you to judge whether that is an issue.  If it is, then passing the value via the OpenArgs mechanism is the solution, though I would suggest that the DefaultValue property be set in the form's Open event procedure to the value passed as the OpenArgs argument, or  the value be assigned to the WellID control in the form's BeforeInsert event procedure rather than its BeforeUpdate event procedure.  The value will then be inserted as soon as the user begins to add data for a new record, rather than when the user attempts to save the record.

    BTW the reason for the literal quotes characters delimiting the value for the DefaultValue property in the above code is that the DefaultValue  property is always a string expression regardless of the data type of the column to which the control is bound.  Delimiting the value in this way can be crucial in some circumstances, less so in others, but should always be done regardless of just how crucial this may be.

    One other thing you should do is requery the owners subform after inserting the new record via OwnerInfoDetails.  The new owner will then be shown in the subform.

    Ken Sheridan, Stafford, England
    • Marked as answer by Bruce Song Tuesday, February 14, 2012 9:20 AM
    Friday, February 3, 2012 11:49 PM