none
macro to requery other form RRS feed

  • Question

  • Access 2013.accdb

    If one is in form A; and changes a value in control X

    and form B is definitely open but not the active window, and B's record source is query with criteria Forms!A.X

    Can one requery form B as part of a macro (not VBA) within form A?  I do not see a way as of yet....

    ...or must one wait for the requery until one activates form B?

    Tuesday, February 2, 2016 5:45 PM

Answers

  • The Requery action only has a Control argument, not a Form argument. Also the description of Requery says "forces a Requery of a specified control ON THE ACTIVE OBJECT"

    So in the control's AfterUpdate write one line of VBA:
    Forms!Form2.Requery


    -Tom. Microsoft Access MVP

    Tuesday, February 2, 2016 8:21 PM

All replies

  • The Requery action only has a Control argument, not a Form argument. Also the description of Requery says "forces a Requery of a specified control ON THE ACTIVE OBJECT"

    So in the control's AfterUpdate write one line of VBA:
    Forms!Form2.Requery


    -Tom. Microsoft Access MVP

    Tuesday, February 2, 2016 8:21 PM
  • Well you can certainly include a Requery in a Macro that affects another Object in either the same database or another database.

    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Tuesday, February 2, 2016 8:21 PM
  • Right now I'm with TvS' advice - in macro world the requery is only within the form object - - am not seeing a way to do it to another form using the macro feature set.....   which I guess is why I posted this just to sanity check my own findings. 

    While the requery macro is there - macro world doesn't allow it to work on a control beyond the active form object....attempts to precede it with 'GoTo' macros also did not allow me to escape the active form object....

    In this case the control is a combobox in the header of an established form - and is already a macro - so if it could be done by adding a macro action then that is ideal....  

    I'm not out of business; rather than redo the combobox in form A the path of least resistance was to deal with the form B - I have used the On Activate event with VBA to do the requery and this seems ok.... it strikes me as somewhat inefficient in that it will trigger unnecessarily if one goes back and forth between form tabs but that isn't a deal breaker

    Live & learn.

    Tuesday, February 2, 2016 11:17 PM
  • Hi msdnPublicIdentity,

    >> in macro world the requery is only within the form object - - am not seeing a way to do it to another form using the macro feature set

    Yes, we could not requery other form in active form. If you want, you must use the Requery method in a VBA module just as suggestion from Tom. For more information about this, you could refer the link below:
    # Requery Macro Action
    https://msdn.microsoft.com/en-us/library/office/ff195544.aspx?f=255&MSPPError=-2147217396

    >> I have used the On Activate event with VBA to do the requery and this seems ok
    If you want macro instead of VBA, I think On Active event would work. For unnesessary, I think you could add a value to indicate whether the value in Form A is changed, if it changes, run the requery with On Activate.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, February 4, 2016 7:06 AM
  • I do something similar for requerying combo box row sources in a form. Any form needing that service has a public function:

    Public Function RequeryCombos(strChangedTable As String) As Boolean
    'Comments  : Requery any combo boxes. This is a public method callable by other forms.
    'Parameters: strChangedTable: The name of the table that has changed.
        Select Case strChangedTable
        Case "PERSON"
            Me.subAuthor.Form.cboPerson.Requery
        Case "ABSTRACT"
            Me.subAuthor.Form.cboAbstractID.Requery
        End Select

    Any form that makes a data change which should result in any other forms updating data sources is responsible for calling those forms' function in its own Form_AfterUpdate event handler:

            If IsFormLoaded("frmAbstract") Then
                Set frmAbstract = Forms![frmAbstract]
                Call frmAbstract.RequeryCombos(strChangedTable:="SESSION")
            End If


    Paul

    Thursday, February 4, 2016 12:24 PM
  • yes that confirms TvS's post.
    Thursday, February 4, 2016 3:09 PM
  • appreciated.  this post was all about macro - - which still has its mysteries to some of us...

    have verified that one cannot macro requery another form object... so that is a limitation to what I view as a legitimate need -

    but one can requery the 2nd form object when activating which typically is sufficient - at least if a human is to be looking that form 2; although if one had to reference a value of a control of the 2nd form without activating it then there is a dilemma.

    the premise is that form 2's criteria is based upon a value in form 1 - - and form 1 has changed records per original post. 

    Thursday, February 4, 2016 3:13 PM
  • If you feel strongly about it, create a suggestion at http://access.uservoice.com/ and rally support for it.


    -Tom. Microsoft Access MVP

    Friday, February 5, 2016 4:38 AM