Calculated Controls on Reports

[chris1 via AccessMonster.com]

I have several calculated controls on a report.

I have a command button on my form. Here is the VBA line as everyone is well
aware of:

DoCmd.OpenReport reportname:"report_name", view:=acPreview

When I view my report the calculated fields have the #Error message. When I
change the VBA line to the following (the view:=acNormal prints the report)
the calculated controls work.

DoCmd.OpenReport reportname:"report_name", view:=acNormal

Why do the calculated controls work when I print the report and not when they
are previewed. Please help and thank you for your time!

 

[Duane Hookom]

Could you share anything about your calculations?

 

[chris1 via AccessMonster.com]

Hi Duane,

Here are my calculated controls

=DSum("[FieldName]","TableName","[FieldCriteria]=" & chr$(34) & [Forms]!
[frm_FrmName]![FieldCriteria]& chr$(34))

and

=iif([FieldName]=0,Null,[FieldName])

Again when I use the argument acNormal in the DoCmd.OpenReport VBA line the
figures are shown with no #Errors but when I use the acPreview the calculated
controls are shown with errors.

Thank you!

 

[Duane Hookom]

Are you closing the form with code? What happens if your modify your control
sources like:
=DSum("[FieldName]","TableName")

 

[chris1 via AccessMonster.com]

Hi Duane,

I close the report using VBA obviously after I open it. It's a command button
on my form. The DSum function worked without the criteria but the =iif
conditional still had an #Error when I previewed it. I am stepping through my
code line by line and I also have a break on the DoCmd.Close statement.

Do I need to close my form before I view the report. That wouldn't make sense
to me since my criteria in the DSum function needs a field input from the
form to link to the table I have.

What am I doing incorrectly?

 

[Duane Hookom]

"I close the report using VBA obviously after I open it." Since I asked if
you closed the form, I assume you meant "form" not "report".
Remove all code that would close the form.

 

[chris1 via AccessMonster.com]

Hi Duane,

Actually I am stepping through my code line by line and I stop at the preview
before the report and form are closed. The code is as follows:

DoCmd.GotoRecord ObjectType:=acForm, ObjectName:="ObjectName", record:
=acNewRec
DoCmd.GotoRecord ObjectType:=acForm, ObjectName:="ObjectName", record:=acPrev
DoCmd.OpenReport ObjectType:="ReportName", View:=acPreview
*DoCmd.Close ObjectType:=acReport, ObjectName:="ReportName"
DoCmd.Close ObjectType:=acForm, ObjectName:="FormName"

*break

I break here to view the report and I notice the #Error on the calculated
controls but if I change the View argument of the DoCmd.OpenReport VBA line
to acNormal then the calculated controls show the correct numerical values.

I take all the close commands for both Reports and Forms out of the code and
end up with the same result as before. Viewing it with #Errors and printing
with the correct values.

The second VBA line at the top is to go back to the record after it was
appended by the user. As you know when a record is added it moves to the next
record waiting for input.

I hope this all makes sense and thank you for your help. It just frustrating.

 

[Duane Hookom]

Why do you move to a new record and then back? Is this to save the record? If
so, just use
Me.Dirty = False

 

[chris1 via AccessMonster.com]

Hi Duane,

My form's DataEntry property is set to True .... After the DoCmd.GotoRecord
line I use the DoCmd.GotoRecord ,,acprev because after I add the new record
the report is blank as it moves to the next record (blank in this case). Do i
just insert the Me.Dirty = false after the appending and will it solve my
calculated control problems. I am afraid this really doesn't solev that error.
With the statement I had it brings up the current inputted record into the
report but again with #Errors in the calculated controls.

 

[Duane Hookom]

Try this code:
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport ObjectType:="ReportName", View:=acPreview

 

[chris1 via AccessMonster.com]

Hi Duane,

I have tried what you offered me and unfortunately it doesn't work. Again
when I use acpreview in the DoCmd.OpenReport then I have the #Error message.
My calculated control on the report is =iif([Myfieldname]=0,1,2). I use this
for simplicity. [Myfieldname] is a single datatype. When I change the
acNormal then it prints out the correct result. My textbox's name doesn't
conflict with the actual field name. I also have a DSum function that works
by the way. I am out of gas on this.

Try this code:
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport ObjectType:="ReportName", View:=acPreview

Hi Duane,

[quoted text clipped - 5 lines]

With the statement I had it brings up the current inputted record into the
report but again with #Errors in the calculated controls.

 

[Duane Hookom]

I would try open the report in preview and then open the debug window (press
Ctrl+G) and enter:

?DSum("[FieldName]","TableName","[FieldCriteria]=" & chr$(34) & [Forms]!
[frm_FrmName]![FieldCriteria]& chr$(34))

See if this produces an error.

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

I have tried what you offered me and unfortunately it doesn't work. Again
when I use acpreview in the DoCmd.OpenReport then I have the #Error message.
My calculated control on the report is =iif([Myfieldname]=0,1,2). I use this
for simplicity. [Myfieldname] is a single datatype. When I change the
acNormal then it prints out the correct result. My textbox's name doesn't
conflict with the actual field name. I also have a DSum function that works
by the way. I am out of gas on this.

Try this code:
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport ObjectType:="ReportName", View:=acPreview

Hi Duane,

[quoted text clipped - 5 lines]

With the statement I had it brings up the current inputted record into the
report but again with #Errors in the calculated controls.

 

[chris1 via AccessMonster.com]

Hi Duane,
I tried on both the DSUM as well as the iif([Fieldname]=0,Null,[fieldname])
in the debug window and they worked fine by displaying the correct values.
Any suggestions? Weird isn't. I appreciate your helps so far and continued
help!

 

[Duane Hookom]

I can't think of other tests. I would probably start re-building the report
adding one calc'd control at a time and see when/if the new report breaks.

 

[chris1 via AccessMonster.com]

Hi Duane,

Here is what is happening just to make things clear. I have a form that a
user inputs data that is linked to a table. I have a button on the form that
saves the form and then proceeds to open a report through the acViewPreview
mode. My calculated control's "=iif(...." displays an #Error however if I
change the view to acViewNormal and then run the code then the calculated
control works when it is printed from the command button: here is the code:

DoCmd.Save ObjectType:=acForm, ObjectType:=Me.Name
DoCmd.OpenReport ReportName:=strRptName, view:=acViewPreview (acViewNornal)

The form is open with the user inputs while the code runs through the VBA
code.

When I just open the report through the Reports section then the calculated
controls work. So now I know I have the correct syntax for my calculated
controls. Do you know what is happening while I try to solve the sitaution as
well. Thank you for all your help.

 

[Duane Hookom]

There is no reason to save the form. The following would only have any
relevance if the form was in design view which it isn't:
DoCmd.Save ObjectType:=acForm, ObjectType:=Me.Name
That is why I suggested the code to save the current record:
DoCmd.RunCommand acCmdSaveRecord
Do you try this code?

 

[chris1 via AccessMonster.com]

Hi Duane,
I took out the DoCmd.Save and instead placed the DoCmd.RunCommand acCmdSave
Record and recieved the following:
Run-time error '2046' The command or action "SaveRecord' isn't available now.

The cycle property of the form is set to Current Page so when I tab through
the fields the information is not updated to the table. I have multiple pages
on my form as an fyi...

What seems to be the problem?

 

[John Regan]

Your not closing the form straight after opening the report in print preview
are you?
If you are then the form's control will not be available for the report's
calculated control.

I would suggest closing the form in the reports close event something like
this:

On Error Resume Next
DoCmd.Close acForm, "FormName"

Hope this helps

Regards
John

 

[chris1 via AccessMonster.com]

Hi John,
Thank you for your response. I am not closing the form after the report
preview. I need to have the inputted data on the form appeneded to my table
with the form still OPEN. How do I do that?
Thank you for your help.

 

[John Regan]

Hi Chris

Presuming the form is bound to the table in question, the Allow
Additions=Yes and the RecordSet Type is not Snapshot, to save the current
record I always use:
DoCmd.RunCommand acCmdSaveRecord
which has already been mentioned in this thread.
Incidentally I've found that when stepping through code this line will throw
the error you mentioned, I always put a breakpoint just after this line if I
want to step through the code.

As for the report error I've occasionally had similar problems.
To get round it I would suggest you add a control (in the same section as
your calculated control) bound to [Myfieldname] and call it txtMyfieldname,
change your formula to read:
IIf([txtMyfieldname]=0,1,2).
If this works then change the new txtMyfieldname control's Visible property
to false.
If it doesn't work does the new txtMyfieldname control display the correct
value?

Hope this helps
Regards
John