Results 1 to 7 of 7
  1. #1
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142

    Can anyone explain this unexpected behavior?

    This is really just a question for discussion and not a problem that needs solving.

    I have a form bound to a table, this table has related tables. My form has various buttons that manipulate records in the related tables. When I click these buttons I'm forced to run requeries on relevant listboxes, subforms, etc to show the results of the manipulations, this is what I would fully expect.



    Here's the funny part I did not expect: I have a textbox on the main form that has a control source linking back to a vba function I've defined in the form's module. This vba function runs an aggregate query on related tables and returns the result, the textbox displays the result. What's funny is the textbox automatically refreshes without me having to requery it...?

    It doesn't seem to matter how the related tables were manipulated, from my main form or from a subform, the textbox automatically runs the vba function I defined and refreshes itself.

    Where is the trigger here exactly? Is this auto-refresh reliable or should I hard code refreshes at appropriate times?

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Nothing unexpected there! A unbound form with formula is calculated, whenever the record changes, or when the form is refreshed. When you don't want uncontrolled refresh, then you have to remove the formula calling the function from textbox, and add a button to refresh textbox. On-click event of button writes the value returned by your function into textbox.

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by ArviLaanemets View Post
    Nothing unexpected there! A unbound form with formula is calculated, whenever the record changes, or when the form is refreshed.
    But the form IS bound, and the record bound to the form is not changed, only records in associated tables were changed. The form ISN'T being refreshed. Note that the form is bound to a table directly and not to a query that brings in those associated tables...

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I did a little more investigating, it seems that the textbox is requering itself whenever my code requerys a List Box control. However, the textbox does not requery itself when my code requerys a subform. Again, at no time am I requerying/refreshing the form itself, I usually specify the control to requery.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I think Arvil meant to say "A unbound textbox with formula is calculated ..."

    A textbox with an expression is not 'requeryed', it is 'recalculated'. A dataset is requeryed as in a RecordSource or RowSource.

    So the textbox calculation is dependent on the same query the listbox uses as RowSource?

    What is this function returning to the textbox?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    John_G is offline VIP
    Windows 10 Access 2016
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Again, at no time am I requerying/refreshing the form itself, I usually specify the control to requery.
    Any change to the form - and requerying a list box is changing the form - will cause the textbox to be recalculated.

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by June7 View Post
    I think Arvil meant to say "A unbound textbox with formula is calculated ..."

    A textbox with an expression is not 'requeryed', it is 'recalculated'. A dataset is requeryed as in a RecordSource or RowSource.

    So the textbox calculation is dependent on the same query the listbox uses as RowSource?

    What is this function returning to the textbox?
    We'll my function that the text box calls runs a query

    Quote Originally Posted by John_G View Post
    Any change to the form - and requerying a list box is changing the form - will cause the textbox to be recalculated.
    I see. Thanks for the information gents!

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

Similar Threads

  1. Split database - unexpected behavior
    By Lynn Cohen in forum Database Design
    Replies: 5
    Last Post: 09-16-2016, 06:53 AM
  2. Unexpected datatype
    By George in forum Access
    Replies: 6
    Last Post: 03-23-2015, 08:00 PM
  3. Replies: 2
    Last Post: 09-25-2013, 12:40 PM
  4. Unexpected results with DAO recordset
    By GraeagleBill in forum Programming
    Replies: 1
    Last Post: 10-07-2012, 07:37 PM
  5. Replies: 3
    Last Post: 12-26-2011, 10:45 AM

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