Check for field value from within a report OnFormat?

[MLH]

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If LaborCost > 0 Then Me!LaborCost.Visible = True
If MatlsCost > 0 Then Me!MatlsCost.Visible = True
If OtherCost > 0 Then Me!OtherCost.Visible = True
End Sub

I use procedures similar to the above sub in forms to make controls
on the form visible when desired. I'm unable to accomplish this in a
report's OnFormat property code. How can I do this on a report?

[fredg]

On Thu, 10 Nov 2005 12:06:42 -0500, MLH wrote:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If LaborCost > 0 Then Me!LaborCost.Visible = True
If MatlsCost > 0 Then Me!MatlsCost.Visible = True
If OtherCost > 0 Then Me!OtherCost.Visible = True
End Sub

I use procedures similar to the above sub in forms to make controls
on the form visible when desired. I'm unable to accomplish this in a
report's OnFormat property code. How can I do this on a report?

Your code should work assuming that the values of the fields are in
fact >0. But you would then need code to turn the controls Visible to
false once the value then becomes <=0.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If LaborCost > 0 Then
Me!LaborCost.Visible = True
Else
Me!LaborCost.Visible = false
End If
If MatlsCost > 0 Then
Me!MatlsCost.Visible = True
Else
Me!MatisCost.Visible = false
End If
If OtherCost > 0 Then
Me!OtherCost.Visible = True
Else
Me!OtherCost.Visible = false
End If
End Sub

A simpler method would be to use:

Me!LaborCost.Visible = Me!LaborCost >0
Me!MatlsCost.Visible = Me!MatisCost >0
Me!OtherCost.Visible = Me!OtherCost >0
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[MLH]

Thx, FredG. My problem seems to start before that.
I get an error when I type something like this in the
immediate window:

?Reports![MyReport]![text147]

I get runtime error #2427 (I've entered an expression
that has no value, it says - like a form or a report or a
label control). But its not. It really is a textbox control.
And it does have a value.

[David W. Fenton]

fredg <fg******@example.invalid> wrote in
news:11*****************************@40tude.net:

A simpler method would be to use:

Me!LaborCost.Visible = Me!LaborCost >0
Me!MatlsCost.Visible = Me!MatisCost >0
Me!OtherCost.Visible = Me!OtherCost >0

For clarity, I think it's better to do:

Me!LaborCost.Visible = (Me!LaborCost >0)

Otherwise, you're depending on the VBA parser/compiler to guess
correctly about what you intended. Yes, there are rules for order of
operations and in this example, VBA would get it right, but I think
it's always better to be explicit about it, and force evaluation of
the Boolean expression.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:d5********************************@4ax.com:

Thx, FredG. My problem seems to start before that.
I get an error when I type something like this in the
immediate window:

?Reports![MyReport]![text147]

I get runtime error #2427 (I've entered an expression
that has no value, it says - like a form or a report or a
label control). But its not. It really is a textbox control.
And it does have a value.

Report controls don't really exist in the same way that form
controls do, so using the Debug window doesn't really prove
anything.

Try using Debug.Print statements in your OnFormat event to see
what's going wrong.

I've done plenty of reports that do major reformatting in the
report
detail's OnFormat event, and have never had any real problems. But
I
generally do this based on a value in the underlying recordsource,
not based on a control value (which may not yet fully exist at the
time the OnFormat event is executing).

I also note that you haven't really specified very clearly what
you're testing. Your original example had this as one line:

If LaborCost > 0 Then Me!LaborCost.Visible = True

What is LaborCost? A control? A field in the report's recordsource?
Both? If it's BOTH, then that's your problem.

NEVER, NEVER, NEVER name a control the same as its underlying
controlsource *IF* you're going to refer to that control/field in
code.

This will probably work:

If Me!LaborCost > 0 Then Me!txtLaborCost.Visible = True

Or, of course, better still:

Me!txtLaborCost.Visible = (Me!LaborCost > 0)

I never think it's a good idea to not fully qualify anything that
you're referring to, so I never using anything like "LaborCost" by
itself. It's always "Me!LaborCost" because that makes it clear that
it's not a variable. And if it's a variable, you should use a
naming
convention that makes it clear that it's a variable and not a field
or control.

(that's another argument for not putting reverse Hungarian prefixes
on field names in tables)

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Wayne Gillespie]

On Thu, 10 Nov 2005 15:48:38 -0600, "David W. Fenton" <dX********@bway.net.invalid> wrote:

NEVER, NEVER, NEVER name a control the same as its underlying
controlsource *IF* you're going to refer to that control/field in
code.

This is rubbish IMO.
I do this all the time and have never had a single problem with it.
Many others have also stated in previous threads when you bring up this argument, that they have had no problems using
this method.
Can you provide a reproducable scenario where this causes a problem?

[MLH]

Report controls don't really exist in the same way that form
controls do, so using the Debug window doesn't really prove
anything. Alas. Debug.print returned the same error.
Try using Debug.Print statements in your OnFormat event to see
what's going wrong.

I've done plenty of reports that do major reformatting in the
report
detail's OnFormat event, and have never had any real problems. But
I
generally do this based on a value in the underlying recordsource,
not based on a control value (which may not yet fully exist at the
time the OnFormat event is executing).

I also note that you haven't really specified very clearly what
you're testing. Your original example had this as one line:

If LaborCost > 0 Then Me!LaborCost.Visible = True

What is LaborCost? A control? A field in the report's recordsource?
Both? If it's BOTH, then that's your problem. Yes, it is BOTH. Damn! I just assumed that since Access' default
behavior was to name the control the same string as the source
data field name - that would be OK. Forever and a day, it has
been - but then, this is the first time I've tried this with reports.

NEVER, NEVER, NEVER name a control the same as its underlying
controlsource *IF* you're going to refer to that control/field in
code.

This will probably work:

If Me!LaborCost > 0 Then Me!txtLaborCost.Visible = True

Or, of course, better still:

Me!txtLaborCost.Visible = (Me!LaborCost > 0)

I never think it's a good idea to not fully qualify anything that
you're referring to, so I never using anything like "LaborCost" by
itself. It's always "Me!LaborCost" because that makes it clear that
it's not a variable. And if it's a variable, you should use a
naming
convention that makes it clear that it's a variable and not a field
or control. It was my intention to refer to the data source field and not to the
control. I'm unsure as to whether I'm referring to the data FIELD
correctly.

(that's another argument for not putting reverse Hungarian prefixes
on field names in tables)

[MLH]

Who would-a-believed it! I renamed the control and the error went
away! To be sure, I renamed it to the original default name (same as
the query field feeding it data), opened the report and the error
resurfaced. I cannot explain it, but David is right on target.

Can you provide a reproducable scenario where this causes a problem?

[MLH]

On Thu, 10 Nov 2005 15:48:38 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

I've done plenty of reports that do major reformatting in the
report
detail's OnFormat event, and have never had any real problems. But
I generally do this based on a value in the underlying recordsource,
not based on a control value (which may not yet fully exist at the
time the OnFormat event is executing).

David, for future reference, how do I refer to the underlying
recordsource in a report - explicitly - to remove ALL ambiguity
between the control name and the underlying field name.
I WILL be changing the names, from this point forward, when
dealing with reports on which I'm trying to glean information
from within OnFormat event property code. But, for argument's
sake, say I was using the same name (both control and record
source) - is there an explicit syntax that would remove ambiguity?

[David W. Fenton]

Wayne Gillespie <be*****@NOhotmailSPAM.com.au> wrote in
news:od********************************@4ax.com:

On Thu, 10 Nov 2005 15:48:38 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

NEVER, NEVER, NEVER name a control the same as its underlying
controlsource *IF* you're going to refer to that control/field in
code.
This is rubbish IMO.

It is rubbish if you don't value clarity in your coding, if you
don't give a rat's ass about code that can be instantly understood.
I do this all the time and have never had a single problem with
it. . . .

I'm very happy for you.

It is, nonetheless, an extremely poor coding practice, in my
opinion, not because it fails all the time or in any particular
scenario, but because it confuses things that can eas8ily be clear.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:7m********************************@4ax.com:

On Thu, 10 Nov 2005 15:48:38 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

I've done plenty of reports that do major reformatting in the
report
detail's OnFormat event, and have never had any real problems. But
I generally do this based on a value in the underlying
recordsource, not based on a control value (which may not yet
fully exist at the time the OnFormat event is executing).

David, for future reference, how do I refer to the underlying
recordsource in a report - explicitly - to remove ALL ambiguity
between the control name and the underlying field name.
I WILL be changing the names, from this point forward, when
dealing with reports on which I'm trying to glean information
from within OnFormat event property code. But, for argument's
sake, say I was using the same name (both control and record
source) - is there an explicit syntax that would remove ambiguity?

Well, as I said -- if you're going to refer to the control in code,
give it a name that makes it clear it's a control, txtMyControl for
textboxes, chkMyCheckbox for checkboxes (I don't think there are any
other data-bound control types that should be used on reports).

Anything that doesn't have a prefix that is referred to in code is
going to be a field.

This makes code very clear and easy to work with, and avoids all
problems with disambiguation.

Oh, and the other case where you want to change the control name is
if you are referring to the control in the controlsource of another
control.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Wayne Gillespie]

On Fri, 11 Nov 2005 21:00:08 -0600, "David W. Fenton" <dX********@bway.net.invalid> wrote:

Wayne Gillespie <be*****@NOhotmailSPAM.com.au> wrote in
news:od********************************@4ax.com :

On Thu, 10 Nov 2005 15:48:38 -0600, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

NEVER, NEVER, NEVER name a control the same as its underlying
controlsource *IF* you're going to refer to that control/field in
code.

This is rubbish IMO.

It is rubbish if you don't value clarity in your coding, if you
don't give a rat's ass about code that can be instantly understood.

This is rubbish IMO.

I do this all the time and have never had a single problem with
it. . . .

I'm very happy for you.

It is, nonetheless, an extremely poor coding practice, in my
opinion, not because it fails all the time or in any particular
scenario, but because it confuses things that can eas8ily be clear.

This is rubbish IMO.

[MLH]

So, in a report, if I have one named txtMyControl - its perfectly
acceptable to refer to it as Me!txtMyControl? How about
Reports!MyReport!txtMyControl?

[David W. Fenton]

MLH <CR**@NorthState.net> wrote in
news:eo********************************@4ax.com:

So, in a report, if I have one named txtMyControl - its perfectly
acceptable to refer to it as Me!txtMyControl? . . .
Naturally. Why would you think otherwise? Have you not noticed that in
A97 forms can be saved as reports?
. . . How about
Reports!MyReport!txtMyControl?

Well, the only reason to refer to an item through its parent object's
collection is if you're referring to it from outside itself, and I
can't think of a reason to be referring to a report control from
anywhere outside the report's own module. I've seen it done, but it
was almost always a bad kludge for something that should have been
done in a different fashion.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc