Form calculations stopped refreshing

steemin77

New Member
Joined
Nov 23, 2009
Messages
3
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0
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.
 
Upvote 0
Hi JB,

this may have a problem:

+ Nz([TableValue]))

it is best to reference controls or calculations on the database
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top