Same control source yields different results

  • Thread starter Thread starter TC
  • Start date Start date
T

TC

I think (from memory) that in a report, you can not refer to a field in the
recordsource of the report, unless that field is explicitly bound to sa
control. So in the one that does not work, make sure that the two name
fields are each bound to an invisible textbox (or whatever). This is unlike
the case of a Form, where you can refer to >any< fields in the form's
recordsource, from a calculation such as yours, >without< having to bind
them to a control on the form.

HTH,
TC
(off for xmas)
 
2 different reports have this as the control source.

=[Patient_first_name] & " " & [Patient_last_name]


The field names exist in both report queries. In one report, I get the
patient names. In the other I get:

# Error


What could be causing this. I tried everything I could think of.

Nick
 
Nick Mirro said:
2 different reports have this as the control source.

=[Patient_first_name] & " " & [Patient_last_name]


The field names exist in both report queries. In one report, I get
the patient names. In the other I get:

# Error


What could be causing this. I tried everything I could think of.
Click to expand...

Odds are that in the "#Error" report the text box has the same name as
one of the fields. A calculated or unbound control must not have the
same name as a field in the report's RecordSource.
 
There was another control sharing the field name. I also added the two
hidden fields and good riddance to that headache!

Thanks. Nick
 
TC said:
I think (from memory) that in a report, you can not refer to a field in the
recordsource of the report, unless that field is explicitly bound to sa
control. So in the one that does not work, make sure that the two name
fields are each bound to an invisible textbox (or whatever). This is unlike
the case of a Form, where you can refer to >any< fields in the form's
recordsource, from a calculation such as yours, >without< having to bind
them to a control on the form.
Click to expand...

Not quite, TC. A field must be used somewhere in a control
expression (what Nick had was OK) before you can refer to
the field in VBA code. E.g. A text box with an expression
like =[A]++[C] (where A, B and C are fields in the record
source table/query) is sufficient to permit the use of code
like:
Total = SQR(Me!A ^ 2 + Me!B ^ 2 + Me!C ^ 2)
--
Marsh
MVP [MS Access]


Nick Mirro said:
2 different reports have this as the control source.

=[Patient_first_name] & " " & [Patient_last_name]


The field names exist in both report queries. In one report, I get the
patient names. In the other I get:

# Error
Click to expand...
Click to expand...
 
Ah, ok. So much for posting "off the top of one's head"!

I've never understood why it is different for reports (compared to forms).

Cheers,
TC


Marshall Barton said:
TC said:
I think (from memory) that in a report, you can not refer to a field in the
recordsource of the report, unless that field is explicitly bound to sa
control. So in the one that does not work, make sure that the two name
fields are each bound to an invisible textbox (or whatever). This is unlike
the case of a Form, where you can refer to >any< fields in the form's
recordsource, from a calculation such as yours, >without< having to bind
them to a control on the form.
Click to expand...

Not quite, TC. A field must be used somewhere in a control
expression (what Nick had was OK) before you can refer to
the field in VBA code. E.g. A text box with an expression
like =[A]++[C] (where A, B and C are fields in the record
source table/query) is sufficient to permit the use of code
like:
Total = SQR(Me!A ^ 2 + Me!B ^ 2 + Me!C ^ 2)
--
Marsh
MVP [MS Access]


Nick Mirro said:
2 different reports have this as the control source.

=[Patient_first_name] & " " & [Patient_last_name]


The field names exist in both report queries. In one report, I get the
patient names. In the other I get:

# Error
Click to expand...
Click to expand...
Click to expand...
 
TC said:
Ah, ok. So much for posting "off the top of one's head"!

I've never understood why it is different for reports (compared to forms).
Click to expand...


As best I can figure out, the reason is that Access goes to
great pains to make reports as fast as possible. The point
of interest here is that when Access creates its own
internal query to drive the report, it optimizes the query
by only retrieving fields that are referenced in a control,
either bound directly or used in a control source
expression.

Forms do not have that kind of optimization, so all the
fields in your record source table/query are available for
use in VBA in the form's class module.
--
Marsh
MVP [MS Access]


TC said:
I think (from memory) that in a report, you can not refer to a field in the
recordsource of the report, unless that field is explicitly bound to sa
control. So in the one that does not work, make sure that the two name
fields are each bound to an invisible textbox (or whatever). This is unlike
the case of a Form, where you can refer to >any< fields in the form's
recordsource, from a calculation such as yours, >without< having to bind
them to a control on the form.
Click to expand...
"Marshall Barton" wrote
Not quite, TC. A field must be used somewhere in a control
expression (what Nick had was OK) before you can refer to
the field in VBA code. E.g. A text box with an expression
like =[A]++[C] (where A, B and C are fields in the record
source table/query) is sufficient to permit the use of code
like:
Total = SQR(Me!A ^ 2 + Me!B ^ 2 + Me!C ^ 2)

2 different reports have this as the control source.

=[Patient_first_name] & " " & [Patient_last_name]


The field names exist in both report queries. In one report, I get the
patient names. In the other I get:

# Error
Click to expand...
Click to expand...
Click to expand...
 
Marshall Barton said:
TC said:
Ah, ok. So much for posting "off the top of one's head"!

I've never understood why it is different for reports (compared to
Click to expand...
forms).


As best I can figure out, the reason is that Access goes to
great pains to make reports as fast as possible. The point
of interest here is that when Access creates its own
internal query to drive the report, it optimizes the query
by only retrieving fields that are referenced in a control,
either bound directly or used in a control source
expression.

Forms do not have that kind of optimization, so all the
fields in your record source table/query are available for
use in VBA in the form's class module.
--
Marsh
MVP [MS Access]


TC wrote:

I think (from memory) that in a report, you can not refer to a field
Click to expand...
in
the
recordsource of the report, unless that field is explicitly bound to sa
control. So in the one that does not work, make sure that the two name
fields are each bound to an invisible textbox (or whatever). This is unlike
the case of a Form, where you can refer to >any< fields in the form's
recordsource, from a calculation such as yours, >without< having to bind
them to a control on the form.
"Marshall Barton" wrote
Not quite, TC. A field must be used somewhere in a control
expression (what Nick had was OK) before you can refer to
the field in VBA code. E.g. A text box with an expression
like =[A]++[C] (where A, B and C are fields in the record
source table/query) is sufficient to permit the use of code
like:
Total = SQR(Me!A ^ 2 + Me!B ^ 2 + Me!C ^ 2)


2 different reports have this as the control source.

=[Patient_first_name] & " " & [Patient_last_name]


The field names exist in both report queries. In one report, I get the
patient names. In the other I get:

# Error
Click to expand...
Click to expand...
Click to expand...
 
I gather there is a big difference between how forms get to their base data,
& how reports do. Forms use recordsets, that we all know & love. Reports use
"segmented virtual tables" (SVTs), which I gather are a very different kind
of beast. I'll bet that's what causes the difference. :-)

Cheers,
TC


TC said:
Marshall Barton said:
TC said:
Ah, ok. So much for posting "off the top of one's head"!

I've never understood why it is different for reports (compared to
Click to expand...
forms).


As best I can figure out, the reason is that Access goes to
great pains to make reports as fast as possible. The point
of interest here is that when Access creates its own
internal query to drive the report, it optimizes the query
by only retrieving fields that are referenced in a control,
either bound directly or used in a control source
expression.

Forms do not have that kind of optimization, so all the
fields in your record source table/query are available for
use in VBA in the form's class module.
--
Marsh
MVP [MS Access]


TC wrote:

I think (from memory) that in a report, you can not refer to a field in
the
recordsource of the report, unless that field is explicitly bound to sa
control. So in the one that does not work, make sure that the two name
fields are each bound to an invisible textbox (or whatever). This is
unlike
the case of a Form, where you can refer to >any< fields in the form's
recordsource, from a calculation such as yours, >without< having to bind
them to a control on the form.

Not quite, TC. A field must be used somewhere in a control
expression (what Nick had was OK) before you can refer to
the field in VBA code. E.g. A text box with an expression
like =[A]++[C] (where A, B and C are fields in the record
source table/query) is sufficient to permit the use of code
like:
Total = SQR(Me!A ^ 2 + Me!B ^ 2 + Me!C ^ 2)


2 different reports have this as the control source.

=[Patient_first_name] & " " & [Patient_last_name]


The field names exist in both report queries. In one report, I
Click to expand...
Click to expand...
get
the
Click to expand...
Click to expand...
 
TC said:
I gather there is a big difference between how forms get to their base data,
& how reports do. Forms use recordsets, that we all know & love. Reports use
"segmented virtual tables" (SVTs), which I gather are a very different kind
of beast. I'll bet that's what causes the difference. :-)
Click to expand...


Right. That would probably also explain why reports don't
have a RecordsetClone property. (I've ocassionally thought
it would be nice to be able to analyze the fields collection
of a report based on a messy crosstab query.)
--
Marsh
MVP [MS Access]


Ah, ok. So much for posting "off the top of one's head"!

I've never understood why it is different for reports (compared to forms).

"Marshall Barton" wrote
As best I can figure out, the reason is that Access goes to
great pains to make reports as fast as possible. The point
of interest here is that when Access creates its own
internal query to drive the report, it optimizes the query
by only retrieving fields that are referenced in a control,
either bound directly or used in a control source
expression.

Forms do not have that kind of optimization, so all the
fields in your record source table/query are available for
use in VBA in the form's class module.


TC wrote:

I think (from memory) that in a report, you can not refer to a field in
the
recordsource of the report, unless that field is explicitly bound to sa
control. So in the one that does not work, make sure that the two name
fields are each bound to an invisible textbox (or whatever). This is
unlike
the case of a Form, where you can refer to >any< fields in the form's
recordsource, from a calculation such as yours, >without< having to bind
them to a control on the form.

Not quite, TC. A field must be used somewhere in a control
expression (what Nick had was OK) before you can refer to
the field in VBA code. E.g. A text box with an expression
like =[A]++[C] (where A, B and C are fields in the record
source table/query) is sufficient to permit the use of code
like:
Total = SQR(Me!A ^ 2 + Me!B ^ 2 + Me!C ^ 2)


2 different reports have this as the control source.

=[Patient_first_name] & " " & [Patient_last_name]


The field names exist in both report queries. In one report, I
Click to expand...
get
the
patient names. In the other I get:

# Error
Click to expand...
Click to expand...
Click to expand...
 
Marshall Barton said:
Right. That would probably also explain why reports don't
have a RecordsetClone property. (I've ocassionally thought
it would be nice to be able to analyze the fields collection
of a report based on a messy crosstab query.)
Click to expand...

Yes! Big annoyance. I wrote a general purpose function that takes a report
instance as a parameter, looks at all the relevat props of the report, &
returns an SQL SELECT statement that replicates the missing recordsetclone.
But that is fairly inefficient, since you have to generate a new recordset
from the SQL statement. Gak!

Cheers,
TC

(snip)
 
TC said:
Yes! Big annoyance. I wrote a general purpose function that takes a report
instance as a parameter, looks at all the relevat props of the report, &
returns an SQL SELECT statement that replicates the missing recordsetclone.
But that is fairly inefficient, since you have to generate a new recordset
from the SQL statement. Gak!
Click to expand...


Inefficient? You call that inefficient? Man, you're being
more than a little polite tonight, aren't you ;-)
 
Back
Top