Results 1 to 6 of 6

Thread: Form calculations stopped refreshing
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2009
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Form calculations stopped refreshing

    Hello,

    I was putting together a form that utilizes a template that several of us can use over a network drive.

    The form has several calculated fields and I noticed when I was testing, it quit updating the calculated fields unless I exit the form and go back in. Because having the automatic refresh is critical to using this, I am hoping someone on here can help me.

    The only thing I could think of is that there is a calculated field based on another calculated field, but I wouldn't see why this would matter. Also, I have added a couple of macros that extract the data to Excel files on a button press, but I wouldn't think this would impact the form itself.

    Does anyone know what the problem may be?

    Regards,
    JB

  2. #2
    Board Regular strive4peace's Avatar
    Join Date
    Jul 2009
    Location
    Colorado
    Posts
    678
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Form calculations stopped refreshing

    hi JB,

    what are examples of the ControlSource?

    If you click in a control and press F9, the whole record will recalculate (same shortcut as Excel )

    "I have added a couple of macros"

    one not so good thing about macros is that you can't compile them ... so if any of them have a problem, it could affect your form. Better to write VBA code

    If you do actually have code and not macros:

    '~~~~~~~~~ Compile ~~~~~~~~~

    Whenever you change code, references, or switch versions, you should always compile and save before executing.

    from the menu in a VBE (module) window: Debug, Compile

    fix any errors on the yellow highlighted lines

    keep compiling until nothing happens (this is good!) -- then Save

    ~~
    if you run code without compiling it, you risk corrupting your database

    ~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up

    -------------------

    If you use VBA instead of macros, you will have a lot more flexibility to do what you want in a better way. It is easy to convert macro actions to VBA

    for instance, if you have a macro that uses OpenForm, the VBA code would be

    DoCmd.OpenForm "formname", etc if you have more parameters

    You can convert any macro command to code using

    DoCmd.MacroAction

    To convert a macro to VBA code (pre-2007),
    select the macro in the Database Window,
    then, from the menu --> Tools, Macro, Convert Macros To Visual Basic,
    then click "Convert"

    ~~~~~~~~~``
    in Access 2007+:

    select your macro in the Navigation Pane (or be in the design view)

    click the Microsoft Office Button and choose --> Save As --> Save Object As

    the dialog box that appears has a combobox to choose "Module" instead of "Macro" -- and then it would be good to give it a better name. It would also be good to combine your converted macros as opposed to having one module for each of them
    Warm Regards,
    Crystal
    Microsoft MVP, Access

    Remote Programming and Training ~ let's connect!
    http://msaccessgurus.com/

    Free Access Tools
    http://msaccessgurus.com/freetips.html

    free book
    http://www.accessmvp.com/strive4peace


    *
    have an awesome day
    *

  3. #3
    New Member
    Join Date
    Nov 2009
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Form calculations stopped refreshing

    Hello,

    Thanks for your response. Just for the sake of testing, I removed all the macros in backup copy. I still am having problems.

    All I am trying to do is pull values from a table into the form textboxes and add some of those values together on the form. (e.g., =Nz([Text2])+Nz([Text4]) + Nz([TableValue])). I also tried an event procedure like [Forms]![Form].Refresh, but that did nothing.

    I believe it was working when I first started, so I am not sure what I did to break it. The form is too large for me to want to start from scratch again. It seems like a simple fix, I just can't find it.

    Thanks again for you assistance.

  4. #4
    Board Regular strive4peace's Avatar
    Join Date
    Jul 2009
    Location
    Colorado
    Posts
    678
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Form calculations stopped refreshing

    Hi JB,

    this may have a problem:

    + Nz([TableValue]))

    it is best to reference controls or calculations on the database
    Warm Regards,
    Crystal
    Microsoft MVP, Access

    Remote Programming and Training ~ let's connect!
    http://msaccessgurus.com/

    Free Access Tools
    http://msaccessgurus.com/freetips.html

    free book
    http://www.accessmvp.com/strive4peace


    *
    have an awesome day
    *

  5. #5
    New Member
    Join Date
    Nov 2009
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Form calculations stopped refreshing

    That is the problem. I had to change all references to the Textbox instead of the table fields. Sorta common sense, but I swore it was working before with those references, but looks like I was wrong.

    Thanks for the help,
    John

  6. #6
    Board Regular strive4peace's Avatar
    Join Date
    Jul 2009
    Location
    Colorado
    Posts
    678
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Form calculations stopped refreshing

    Hi John,

    Sometimes you can reference fields directly in form and report equations but it is best not to as Access doesn't always keep track of fields that aren't in controls. As a general rule, I never refer to a field directly in an equation -- always a control. I also name controls the same as the field they contain.

    With reports, for instance, if you want to limit by particular fields that don't display on the report, put them in controls that are not visible even though the filter is applied to the RecordSource.
    Warm Regards,
    Crystal
    Microsoft MVP, Access

    Remote Programming and Training ~ let's connect!
    http://msaccessgurus.com/

    Free Access Tools
    http://msaccessgurus.com/freetips.html

    free book
    http://www.accessmvp.com/strive4peace


    *
    have an awesome day
    *

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •