Form calculations stopped refreshing

[steemin77]

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

 

[strive4peace]

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 <smile>)

"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

 

[steemin77]

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.

 

[strive4peace]

Hi JB,

this may have a problem:

+ Nz([TableValue]))

it is best to reference controls or calculations on the database

 

[steemin77]

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

 

[strive4peace]

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.