none
Retrieving a value from a control on the mainform to a variable in a routine in the subform RRS feed

  • Question

  • I've tried everything I've read on numerous forums...and nothing works!

    All I'm trying to do is get a value from the mainform to a variable in a routine attached to the subform.

    I'm getting information from the user to update a database (obviously).  Lookup information is entered on the mainform.  When the effective date on the mainform is filled in and a record is found, the subform is populated and displayed.  The user can edit whatever fields need editing then presses the update button which reads all the textbox controls on the form and updates the record with the information.  Problem is, one of those fields in on the mainform.

    I've tried:  [Product Code] = Me.Parent.ProductCode.Value and other derivations, but nothing works.  I know I've got to me doing something wrong, but with all I've read and done, I can't figure it out.

    Please help!!

    Friday, March 16, 2018 3:56 PM

Answers

  • Actually it doesn't create an error.

    It solved the problem.

    Thanks!!

    Now on to the next problem!!

    Have a nice day!!

    • Marked as answer by CSC Robert Friday, March 16, 2018 5:11 PM
    Friday, March 16, 2018 5:11 PM

All replies

  • Hi,

    There are two ways to refer to a control on a form: absolute and relative

    What you're doing when using Me.Parent is the relative approach.

    Have you tried using the absolute approach where you would use something like?

    Forsm!FormName.ControlName

    If you have and it still doesn't work, maybe you can consider using TempVars.

    In the AfterUpdate event of the control on the main form where you need the information for the table, you could try something like:

    TempVars.Add "GiveItANameHere", Me.ControlName.Value

    Then, in your subform routine, use the TempVar for the field assignment. For example:

    SET FieldName = TempVars!GiveItANameHere

    Hope it helps...

    Friday, March 16, 2018 4:04 PM
  • Hi there!  Thanks for coming to the rescue...again!

    I used the following syntax:   [Product Code] = Forms!Form_VendorItemInfo1.ProductCode

    The error I get is:  #2452  The expression you entered has an invalid reference to the parent property.

    I've tried it with .value and .text and get the same error.

    Friday, March 16, 2018 4:18 PM
  • Please post the entire subroutine, if possible.

    In the meantime, try the TempVars approach to see if you get better results.

    Friday, March 16, 2018 4:31 PM
  • Private Sub UpdRecVIT_Click()
        'Update columns in a table

        Dim thename As String

        'Forms!vendoriteminfo1.Form!ProductCode.SetFocus

    'Issue is with statement below.

        thename = Parent!ProductCode.Value
        [Product Code] = Forms!Form_VendorItemInfo1.ProductCode.Text
        [Effective Date] = RevDate(vendoriteminfo1.effDateVIT.Text)
        [Base Cost] = CInt(CDec(vendoriteminfo2.BaseVIT.Text) * 100)
        [Base Discount] = CInt(CDec(vendoriteminfo2.DiscVIT.Text) * 100)
        [Base Rebate] = CInt(CDec(vendoriteminfo2.RebateVIT.Text) * 100)
        [Frt Chrg 0-50] = CInt(CDec(vendoriteminfo2.FChrg050VIT.Text) * 100)
        [Frt Chrg 50+] = CInt(CDec(vendoriteminfo2.FChrgOLVIT.Text) * 100)
        [Fuel SCpercent] = CDec(vendoriteminfo2.[Fuel_SCpercent].Text)
        [Fuel SC] = CInt(CDec(vendoriteminfo2.fuel_sc$) * 100)
        [Tarp Chrg] = CInt(CDec(vendoriteminfo2.TarpVIT) * 100)
        [Scrap Chrg] = CInt(CDec(vendoriteminfo2.[Scrap Chrg]) * 100)
        [additional cost] = CInt(CDec(vendoriteminfo2.AddCostVIT) * 100)
        [Title Add Cost] = CInt(CDec(vendoriteminfo2.AddCostTitleVIT.Text) * 100)
        [Remarks] = Remarks.Value

        VendorName.SetFocus
        [Vendor Name] = VendorName.Text

        If recordaction = 1 Then
          'do an insert
        Else
          MsgBox "Update done"
          'do an update
        End If

    Friday, March 16, 2018 4:34 PM
  • Hi,

    What is the actual name of the main form (as you see it on the Navigation Pane)?

    Are you saying the line for assigning a value to [Product Code] works?

    What is vendoriteminfo2?

    Friday, March 16, 2018 4:42 PM
  • Form names are:  form_newvendorinfo1 and form_newvendorinfo2

    And no, [product code] is the problem. The rest of the fields are on the current form.

    Friday, March 16, 2018 4:45 PM
  • Form names are:  form_newvendorinfo1 and form_newvendorinfo2

    And no, [product code] is the problem. The rest of the fields are on the current form.

    Hi,

    Just want to make sure, you are seeing "form_" in the names of the forms in the Navigation Pane?

    Is that how you named them? The reason I'm asking is it's usually the format of the names Access assigns to Class Modules for forms, and you would see those in the Project Explorer - not the Navigation Pane.

    Can you post a screenshot of your Navigation Pane? Thanks.

    Friday, March 16, 2018 4:51 PM
  • If your Sub-form control needs to be updated from your main form control then:

    Forms![Main Form Name]![Sub-Form Name]![Control Name] = Forms![Main Form Name]![Control Name]

    You didn't say which form module you are attempting to update from. If the command button code is in the main forms module then:

    Forms![Main Form Name]![Sub-Form Name]![Control Name] = Me.Control Name

    If the command button code is in the Sub-forms module then:

    Me.Control Name = Forms![Main Form Name]![Control Name]

    Friday, March 16, 2018 4:53 PM
  • Even if I exclude the "form_" I still get an error

    Friday, March 16, 2018 5:00 PM
  • I'm trying to update a field in a row of my database, not on another form.
    Friday, March 16, 2018 5:03 PM
  • So, you are saying changing the line from this:

    [Product Code] = Forms!Form_VendorItemInfo1.ProductCode.Text

    into this:

    [Product Code] = Forms!VendorItemInfo1.ProductCode

    still produces an error? If so, is [Product Code] a Textbox on the subform? If so, how about trying it this way?

    Me.[Product Code] = Forms!VendorItemInfo1.ProductCode

    How are you doing with using the TempVars approach?

    Friday, March 16, 2018 5:09 PM
  • Actually it doesn't create an error.

    It solved the problem.

    Thanks!!

    Now on to the next problem!!

    Have a nice day!!

    • Marked as answer by CSC Robert Friday, March 16, 2018 5:11 PM
    Friday, March 16, 2018 5:11 PM
  • Actually it doesn't create an error.

    It solved the problem.

    Thanks!!

    Now on to the next problem!!

    Have a nice day!!

    Hi,

    Glad to hear you got it sorted out. Good luck with your project.

    Friday, March 16, 2018 5:21 PM