Results 1 to 14 of 14
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    subform requery with update on main form

    (...ought to be simple; just having a "moment")

    I have a control {txtBaseDescript_PU} on a related subform {fsubSpec_BaseDescript_PopUp} whose record source is the same a control {txtBaseDescript} on the main form.


    I need for it (the control on the subform) to reflect any changes made to the control on the mainform.

    ...
    Code:
    Private Sub txtBaseDescript_AfterUpdate()
    
            If Me.fsubSpec_BaseDescript_PopUp.Visible Then
    '          Me.[fsubSpec_BaseDescript_PopUp].Form.Requery 'didn't update
    '          Me!fsubSpec_BaseDescript_PopUp.Form!txtBaseDescript_PU.Requery 'didn't update
            End If
            
    End Sub
    any help will be greatly appreciated in advance.
    be well, stay healthy, m.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    {fsubSpec_BaseDescript_PopUp}
    That is the name of the subform, or subform control? Or both? The syntax is

    Forms!frmNameHere.subformCONTROLnameHere.Form.cont rolNameOnSubform

    I suppose you could use Me.subformCONTROLnameHere as well if the code is on the main form.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Hipromark is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    Bogota, Colombia
    Posts
    15
    If de control in the main form and the control in the subform are both reading from the same table then try a Macro with>
    saverecord
    followed by
    sendkeys {F9}
    and execute this Macro after you update the control in the main form

  4. #4
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    {fsubSpec_BaseDescript_PopUp} is the name of the subform AND subform control

    this code updates the information on the subform, but only when the control on the subform is given focus; until then, it remains unedited

    Code:
    Me!fsubSpec_BaseDescript_PopUp.Form!txtBaseDescript_PU.Requery

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm not following. Your 1st code is trying to requery the form. Your comment there says it doesn't update. If this is a continuous or datasheet form you can tell it if it requeried because the record pointer will go to the first record. If the data looks the same, then that doesn't mean the form didn't requery, it means you are not getting the effect on the recordsource that you expect.

    Your second post shows you requerying a control on the subform, which is not the same thing. That and the terminology is why I'm not following.
    Can you not fix this by setting the Master/Child link properties of the main and subform, or have you already done that?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    the parent form is a single record form and there is a link between it and the subform.

    the data source of the controls on the parent form and the subform is the same text field in the same table; one control should mirror the other.

    the subform was requested so that the end user could see this information (and edit it) when it would otherwise not be visible on the parent form because the tab page its control is located on (along with other information) would be out of view (only this one field on that tab page is of concern.)

    there is a toggle on the mainform that sets the subform visible or not (depending on the user's need.) allowing for the idiosyncrasies of different users, (the route they might take across the form and through the table's records is hardly consistent, nor predictable.) it will be possible for both the parent form control and the subform control to be visible at the same time.
    when the user makes a change to the field's information on either the main form or the subform, it should be mirrored or the other (this does not have to happen keystroke-by-keystroke, but when the user leaves the control, the other should reflect the change.)

    right now, it my efforts to try this, and try that...
    i've landed in a total mess where a change to either is reflected in neither. =: -(

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, if you are lost and you have the db, imagine trying to figure it out without seeing it! If you could copy, compact and zip the db and post here with instructions on how to replicate the problem and what the result should be, that might enable someone to get a grip on what's going on.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    "imagine trying to figure it out without seeing it!"
    oh.. i get it

    open form1
    click command button "see the description while you work..."

    edit description (on parent form)
    click description on subform
    description in subform now reflects change in parent form =:-)

    edit description again (on parent form)
    click any other control on parent form
    description in subform does not reflect edit made on parent form <- this is where my quandary is (i need the subform to reflect the change made once the control on the parent form loses focus ... and visa-versa)

    TEST_202012.18.accdb
    Last edited by markjkubicki; 12-18-2020 at 09:02 AM. Reason: completely revising my comments to be opposite

  9. #9
    Hipromark is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    Bogota, Colombia
    Posts
    15
    I tested out your file, I changed information on the Descript_base control in Form1, then clicked your button 'see description while your work on other stuff' and yes, Descript_base control in fsubForm1 didn't update, but then I just added this line to the vba procedure: DoCmd.Save and it resolved the issue. You can also use: SendKeys {F9}.

  10. #10
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    thnx, that did it!

    (...was hoping to do so without the record save, but no matter ...it really will not be an issue AND... problem solved)

    BTW, in lieu of "DoCmd.Save", i typically use: "Me.Dirty = False";
    Is there reason / preference for one over the other?

  11. #11
    Hipromark is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    Bogota, Colombia
    Posts
    15
    The reason is I hate to use me.something, haha. I just tend to avoid VBA programing as much as I can, in Access. I prefer to use regular Macros and in regular macros you donīt use Me.something kind of formulas

  12. #12
    HotelierDream is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Dec 2020
    Posts
    31
    Credit to Davegri for this solution!

    No sure if this would help but...

    Variable Define:
    [Input Date] = Field on main form that controls records show on sub forms.
    [Daily Input Report] = Main Form name

    This is designed for dates but it might help with your need... also see code below for a VBA option that uses the nav buttons to update.


    without VBA you could use:
    If(isnull([Input Date],*select*From[Daily Input Report]","Select*From[Daily Input Report]Where[Input Date]=#"&[Input Date]&"#")


    VBA Code:

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub Form_Open(Cancel As Integer)
        DoCmd.Maximize
        If DCount("*", "[Daily Input Report]", "[Input Date]=" & Date) = 0 Then
            DoCmd.GoToRecord , , acNewRec
        End If
    End Sub
    
    
    Private Sub NavigationButton21_Click()
        If IsNull(Me.[Input Date]) Then
        Else
            Me.NavigationSubform.Form.RecordSource = "Select * from [Daily Input Report] Where [Input Date] = #" & Me.[Input Date] & "#"
        End If
    End Sub
    
    
    Private Sub NavigationButton25_Click()
        If IsNull(Me.[Input Date]) Then
        Else
            Me.NavigationSubform.Form.RecordSource = "Select * from [Daily Input Report] Where [Input Date] = #" & Me.[Input Date] & "#"
        End If
    End Sub

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    BTW, in lieu of "DoCmd.Save", i typically use: "Me.Dirty = False";
    Is there reason / preference for one over the other?
    Using Me.Dirty=False is more efficient than DoCmd.Save
    Better still, use If Me.Dirty Then Me.Dirty=False then it only saves where necessary

    See https://www.techrepublic.com/blog/mi...turns%20False.

    @hipromark
    Why on earth would you want to avoid using the Me. notation? Its a huge time saver over writing out the current object name in full
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    Hipromark is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    Bogota, Colombia
    Posts
    15
    Very simple reason my K-9 friend. If I use an Access Macro I donīt need to use me.something because I am not working with a VBA procedure. Off course, when I do create a procedure, I would use me.something if necesary. My point is, I only use VBA when the Access macros cannot do it by themselves.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Requery Subform from combo box on Main Form
    By Topflite66 in forum Forms
    Replies: 6
    Last Post: 04-03-2020, 01:13 PM
  2. Requery subform from main form
    By nswhit in forum Forms
    Replies: 7
    Last Post: 05-13-2013, 02:22 PM
  3. Requery subform after update of main form
    By gemadan96 in forum Forms
    Replies: 3
    Last Post: 10-17-2012, 02:33 PM
  4. Replies: 3
    Last Post: 04-17-2012, 10:28 AM
  5. Replies: 3
    Last Post: 10-10-2011, 06:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums