ACCESS FEED: RE: [MS_AccessPros] Calculation field in form display "#Error"

Oh, I'm using 2007.

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Tuesday, November 01, 2011 6:47 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Calculation field in form display "#Error"

David-

The filter is in the form's Filter property - it will never show up in the
Record Source property.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of David
Tolson
Sent: Tuesday, November 01, 2011 11:17 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Calculation field in form display "#Error"

Oh John, I was assuming that I did the filter correctly. Although the form
seems to comply with the filter when the appropriate button is clicked, I
thought I'd better show you the code. This is the code for filtering only
Repair Orders:

Me.frmScopeOfJobSub.Form.Filter = "RepairOrder=" & -1

Me.frmScopeOfJobSub.Form.FilterOn = True

The other filters are done the same way with the only thing changing being
the criteria in parens. And here is the code for turning off the filters:

Me.frmScopeOfJobSub.Form.FilterOn = False

Please note that when I go to Design View and go to the subform Record
Source, the filter does not display in the query even though the records are
correctly displaying.

r/David

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of David
Tolson
Sent: Tuesday, November 01, 2011 6:02 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Calculation field in form display "#Error"

John, Whoa! Perhaps my celebration was a tad premature J. After inserting
all of the filters, which work fine BTW, I am having the trouble that you
seemed to suggest with you remedy: The total field (txtRepairTotal) of the
"WorkRequestedCost" field is displaying blank, even when I changed the NZ.
And for reason, although no change was made, the parts cost total field
(txtPartsSubTotal) in frmPartsUsedSub is also display blank, but the
contents is zero (I did a msgbox and although it shows "0" the subform
control is blank. These two issues have a rippling affect because the field
in the main form that combines the parts cost and work requested cost is
displaying 0.

Any suggestions?

Thanks,

David

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of David
Tolson
Sent: Tuesday, November 01, 2011 3:20 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Calculation field in form display "#Error"

Thank you John, Give me an A for following instructions, and then since the
filters were established, thanks to you, it corrected the other issue with
the sum.

Thanks again and have a great rest of the day.

Sincerely,

David

From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of John
Viescas
Sent: Tuesday, November 01, 2011 9:07 AM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: RE: [MS_AccessPros] Calculation field in form display "#Error"

David-

Well, for starters, I wouldn't use four separate subforms. You could simply
have each button apply a filter to the one subform to display the different
categories. Set Me.SubformControl.Form.Filter to the filter you want to
apply
and set Me.SubformControl.Form.FilterOn to True (apply the filter) or False
(remove all filters).

When you show all rows, are there any that have a "blank" WorkRequestedCost?
If
so, you have the NZ in the wrong place. Try this:

=Sum(NZ([WorkRequestedCost], 0))

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
djsdaddy531
Sent: Tuesday, November 01, 2011 12:58 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: [MS_AccessPros] Calculation field in form display "#Error"

Great Day, I have a subform on a form that creates a repair order for a
vehicle
repair shop. The subform - Repair List (things that need repair) has a total
field (txtRepair Total) - permits the user to enter the repairs that need to
take place and the cost of each repair. I've been using the following code
in
the Control Source of txtRepairOrder for a while with no problem
-"=Nz(Sum([WorkRequestedCost]))" but now I am getting "#Error" when the form
opens. I didn't begin to get this error until I added a new feature to the
form.
Each repair item and associated cost can be one of three categories (all
Yes/No):

1. Items part of estimate
2. Items part of repair order
3. Items recommended by technician

An Item that was part of the estimate may not be part of the repair, but
during
the repair, the technician may discover something else that needs repair. I
originally had all of these categories being summed in txtRepairTotal, which
may
not be an accurate view of the repair order. I have successfully
distinguished
them in the print reports, however, so I placed four buttons on the main
form
that will filter each of the three category, along with a button that
permits
the user to view all categories. This works by overlaying 4 subforms on one
another and depending on the button that is clicked, the appropriate form
with
its associated criteria will display.

I am not sure why that will cause the field not to sum properly, but that is
the
only change to the form. However, when I click the buttons for "View
Estimate",
"View Repair Order", and "View Recommended Work," it sums properly. It is
just
when the parent form is originally opened, the subform views all categories
and
that is where the error occurs, as well as when the button that shows all
categories is clicked. But the form has always opened to show all
categories,
and I never received this error before.

Thanks in advance for your assistance,

R/David

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

Yahoo! Groups Links

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

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

Yahoo! Groups Links

[Non-text portions of this message have been removed]