Printing calculations

[at]

I have a form that has two subforms on it. The form has two fields that
contain the totals from each of the two subforms. The total fields are then
used in a calculation. This value is then stored in a table. I wouldn't
normally keep a calculated field in a table, but in this case it works best.
I have a print button on the form that prints out the information on the
form. However, sometimes when the user clicks on the print button, the
report prints out with zeros in the field that comes from the calculation.
It's like the report is printed before the value is saved in the table. I
have code to do a save before it prints the report and sometimes it works and
sometimes it doesn't. The database is on a network with the FE on the users
local drive and the BE on the network. Any ideas on how I can make sure the
value has been saved in the table before the report prints??

Thanks for any advice you can give me.

at

 

[Allen Browne]

Several steps here.

1. The save.
To force the save, use:
If Me.Dirty Then Me.Dirty = False
That should generate an error if the save fails for any reason.

2. The update
Presumably you use the AfterUpdate event of the *form* to execute an Update
query statement to write the calculated value to the other table. Hopefully
you are not merely updating a control on the form (which means it would not
be saved.)

3. The value to update
If you are relying on writing the value from the calculated text box to your
table, the text box probably won't be up to date yet. Alternatives are to
use a domain aggregate function such as DSum() to get the total, or to
perform a Me.Recalc (and hope that gets completed in time.)

4. Catch up.
If that still doesn't work, you might give Access a chance to catch up by
adding this line before your OpenReport:
DoEvents

 

[at]

#2. I was having the value write to the table on the controlled field, I'll
put it on the afterupdate of the form. You're right that makes much more
sense.
#3. I wasn't able to get the DSum to work when I tried previously. I'm not
using a recalc so I'll try that too.
#4. I've also added DoEvents before opening the report. And before that I
added another save command. I didn't have that in there before.

So thanks for your help. I think these things will work.

at