"...can't find the field 'FieldName' referred to in your expressio

[Guest]

MS Access 2003/XP Professional/.mdb is in 2000 format.

Get the above error message when opening/previewing report that has code in
the on format event Code (code below). The error will occur with both
calculated fields and "real" fields (but other similar expressions work fine).

I have deleted the report recordsource, saved and closed the report,
reopened and reassigned the recordsource property to the saved query, I have
tried using an SQL statement as the recordsource, I have compacted and
repaired, I have decompiled the database, I have compiled it (compile runs
without error). The field names DO appear in the field list and the field
name(s) will autocomplete when I create the code--but won't recognize when I
run the code.

Tearing out what little hair I had remaining.

Paul

Code Below:
NOTE-This first if works fine
If Detail.BackColor = 12632256 Then
Detail.BackColor = vbWhite
Else
Detail.BackColor = 12632256
End If
NOTE: This is the if that blows up--"...can't find field
'IsNotedAsComplete'..."
If Me.IsNotedAsComplete Then
{do stuff}
Else
{do other stuff}
End If

NOTE: This if works fine, and FLGD_Flagged is a field in the dB/query, not a
report control
If Me.FLGD_Flagged = True Then
Me.txtFlagReason.Visible = True
Else
Me.txtFlagReason.Visible = False
End If

 

[Allen Browne]

The error message indicates it cannot find the *field*, not that it cannot
find the control. We will therefore assume that Access is looking for a
field by that name.

Why would it be doing that? Are you using this name in the Filter or OrderBy
properties of the report? In the Sortin'n'Grouping box? In the Control
Source of a control? Perhaps trying to aggregate in a footer, e.g.:
=Sum([Fieldname])

If none of those apply, you could try:
Me!IsNotedAsComplete
or
Me.Controls("IsNotedAsComplete")

Open the report in design view.
Open the Immediate Window (Ctrl+G).
Enter this kind of expression:
? TypeName(Reports![Report1]!IsNotedAsComplete)
Does it report it as a text box (or whatever control)?

If you are programmatically changing the RecordSource of the control, try
giving the Control a different Name than its ControlSource. That will ensure
Access knows you are talking about the Control and not the Field, at design
time, so the code compiles correctly.

Hopefully that will give you a hint at what is going on.

 

[Guest]

Allen--Thanks for the response (we have communicated a couple of time over
the years--I think at Utter Access--and I used to visit your website for
"personal growth" on Access issues like self joins years ago).

The code is refering to a field, not a control (there is no corresponding
control on the report). The field that I initially had the problem crop up
with was actually a field brought into a query through a Left Join to a
summary query. When the problem cropped up I tried, just to test, switching
the reference to another field in the query (a field that exists in the
original dB)--same problem. Fields are visible in the field list for that
report, and do pop into the code using the autocomplete functionality.

The field I am trying to use does not have any correpsonding control on the
report, is not used in any order by, sorting or grouping. I have also tried
using Me.[Field Name] and Me![Field Name] syntaxes, to no avail.

I just tried tying that field to a control on the form, and the control
displays correctly. I could always, I guess, work around this by placing an
invisible checkbox in the detail section, then refering to the control in the
code rather than the field, but this seems sloppy (and irritating) to me.

Thanks,
Paul

The error message indicates it cannot find the *field*, not that it cannot
find the control. We will therefore assume that Access is looking for a
field by that name.

Why would it be doing that? Are you using this name in the Filter or OrderBy
properties of the report? In the Sortin'n'Grouping box? In the Control
Source of a control? Perhaps trying to aggregate in a footer, e.g.:
=Sum([Fieldname])

If none of those apply, you could try:
Me!IsNotedAsComplete
or
Me.Controls("IsNotedAsComplete")

Open the report in design view.
Open the Immediate Window (Ctrl+G).
Enter this kind of expression:
? TypeName(Reports![Report1]!IsNotedAsComplete)
Does it report it as a text box (or whatever control)?

If you are programmatically changing the RecordSource of the control, try
giving the Control a different Name than its ControlSource. That will ensure
Access knows you are talking about the Control and not the Field, at design
time, so the code compiles correctly.

Hopefully that will give you a hint at what is going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

MS Access 2003/XP Professional/.mdb is in 2000 format.

Get the above error message when opening/previewing report that has code
in
the on format event Code (code below). The error will occur with both
calculated fields and "real" fields (but other similar expressions work
fine).

I have deleted the report recordsource, saved and closed the report,
reopened and reassigned the recordsource property to the saved query, I
have
tried using an SQL statement as the recordsource, I have compacted and
repaired, I have decompiled the database, I have compiled it (compile runs
without error). The field names DO appear in the field list and the field
name(s) will autocomplete when I create the code--but won't recognize when
I
run the code.

Tearing out what little hair I had remaining.

Paul

Code Below:
NOTE-This first if works fine
If Detail.BackColor = 12632256 Then
Detail.BackColor = vbWhite
Else
Detail.BackColor = 12632256
End If
NOTE: This is the if that blows up--"...can't find field
'IsNotedAsComplete'..."
If Me.IsNotedAsComplete Then
{do stuff}
Else
{do other stuff}
End If

NOTE: This if works fine, and FLGD_Flagged is a field in the dB/query, not
a
report control
If Me.FLGD_Flagged = True Then
Me.txtFlagReason.Visible = True
Else
Me.txtFlagReason.Visible = False
End If

 

[Allen Browne]

Ah, that clarifies it, and yes: an invisible control of the same name is the
solution.

I understand that the issue is caused by the optimizer. Access tries to be
too clever. It has to re-write the SQL anyway to perform whatever kind of
sorting'n'grouping you requested for the report, so it actually drops out
fields that are not used anywhere in the report. So, if the only reference
is in code, it can indeed find that the FieldName is not present, even
though it is in the RecordSource statement.

All versions of Access (at least as far back as 2) seem to have done this,
and placing a hidden control in the report solves the problem because Access
will then fetch the field's data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen--Thanks for the response (we have communicated a couple of time over
the years--I think at Utter Access--and I used to visit your website for
"personal growth" on Access issues like self joins years ago).

The code is refering to a field, not a control (there is no corresponding
control on the report). The field that I initially had the problem crop up
with was actually a field brought into a query through a Left Join to a
summary query. When the problem cropped up I tried, just to test,
switching
the reference to another field in the query (a field that exists in the
original dB)--same problem. Fields are visible in the field list for that
report, and do pop into the code using the autocomplete functionality.

The field I am trying to use does not have any correpsonding control on
the
report, is not used in any order by, sorting or grouping. I have also
tried
using Me.[Field Name] and Me![Field Name] syntaxes, to no avail.

I just tried tying that field to a control on the form, and the control
displays correctly. I could always, I guess, work around this by placing
an
invisible checkbox in the detail section, then refering to the control in
the
code rather than the field, but this seems sloppy (and irritating) to me.

Thanks,
Paul

The error message indicates it cannot find the *field*, not that it
cannot
find the control. We will therefore assume that Access is looking for a
field by that name.

Why would it be doing that? Are you using this name in the Filter or
OrderBy
properties of the report? In the Sortin'n'Grouping box? In the Control
Source of a control? Perhaps trying to aggregate in a footer, e.g.:
=Sum([Fieldname])

If none of those apply, you could try:
Me!IsNotedAsComplete
or
Me.Controls("IsNotedAsComplete")

Open the report in design view.
Open the Immediate Window (Ctrl+G).
Enter this kind of expression:
? TypeName(Reports![Report1]!IsNotedAsComplete)
Does it report it as a text box (or whatever control)?

If you are programmatically changing the RecordSource of the control, try
giving the Control a different Name than its ControlSource. That will
ensure
Access knows you are talking about the Control and not the Field, at
design
time, so the code compiles correctly.

Hopefully that will give you a hint at what is going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

MS Access 2003/XP Professional/.mdb is in 2000 format.

Get the above error message when opening/previewing report that has
code
in
the on format event Code (code below). The error will occur with both
calculated fields and "real" fields (but other similar expressions work
fine).

I have deleted the report recordsource, saved and closed the report,
reopened and reassigned the recordsource property to the saved query, I
have
tried using an SQL statement as the recordsource, I have compacted and
repaired, I have decompiled the database, I have compiled it (compile
runs
without error). The field names DO appear in the field list and the
field
name(s) will autocomplete when I create the code--but won't recognize
when
I
run the code.

Tearing out what little hair I had remaining.

Paul

Code Below:
NOTE-This first if works fine
If Detail.BackColor = 12632256 Then
Detail.BackColor = vbWhite
Else
Detail.BackColor = 12632256
End If
NOTE: This is the if that blows up--"...can't find field
'IsNotedAsComplete'..."
If Me.IsNotedAsComplete Then
{do stuff}
Else
{do other stuff}
End If

NOTE: This if works fine, and FLGD_Flagged is a field in the dB/query,
not
a
report control
If Me.FLGD_Flagged = True Then
Me.txtFlagReason.Visible = True
Else
Me.txtFlagReason.Visible = False
End If

 

[Guest]

I had just tried that to confirm--and the code ran fine (actually used a
different name on the control--I dislike same named fields and controls)--and
the other field reference I had in the code does have a bound control
(cbxFLGD_Flagged bound to FLGD_Flagged) associated with it.

Thanks,
Paul

Ah, that clarifies it, and yes: an invisible control of the same name is the
solution.

I understand that the issue is caused by the optimizer. Access tries to be
too clever. It has to re-write the SQL anyway to perform whatever kind of
sorting'n'grouping you requested for the report, so it actually drops out
fields that are not used anywhere in the report. So, if the only reference
is in code, it can indeed find that the FieldName is not present, even
though it is in the RecordSource statement.

All versions of Access (at least as far back as 2) seem to have done this,
and placing a hidden control in the report solves the problem because Access
will then fetch the field's data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen--Thanks for the response (we have communicated a couple of time over
the years--I think at Utter Access--and I used to visit your website for
"personal growth" on Access issues like self joins years ago).

The code is refering to a field, not a control (there is no corresponding
control on the report). The field that I initially had the problem crop up
with was actually a field brought into a query through a Left Join to a
summary query. When the problem cropped up I tried, just to test,
switching
the reference to another field in the query (a field that exists in the
original dB)--same problem. Fields are visible in the field list for that
report, and do pop into the code using the autocomplete functionality.

The field I am trying to use does not have any correpsonding control on
the
report, is not used in any order by, sorting or grouping. I have also
tried
using Me.[Field Name] and Me![Field Name] syntaxes, to no avail.

I just tried tying that field to a control on the form, and the control
displays correctly. I could always, I guess, work around this by placing
an
invisible checkbox in the detail section, then refering to the control in
the
code rather than the field, but this seems sloppy (and irritating) to me.

Thanks,
Paul

The error message indicates it cannot find the *field*, not that it
cannot
find the control. We will therefore assume that Access is looking for a
field by that name.

Why would it be doing that? Are you using this name in the Filter or
OrderBy
properties of the report? In the Sortin'n'Grouping box? In the Control
Source of a control? Perhaps trying to aggregate in a footer, e.g.:
=Sum([Fieldname])

If none of those apply, you could try:
Me!IsNotedAsComplete
or
Me.Controls("IsNotedAsComplete")

Open the report in design view.
Open the Immediate Window (Ctrl+G).
Enter this kind of expression:
? TypeName(Reports![Report1]!IsNotedAsComplete)
Does it report it as a text box (or whatever control)?

If you are programmatically changing the RecordSource of the control, try
giving the Control a different Name than its ControlSource. That will
ensure
Access knows you are talking about the Control and not the Field, at
design
time, so the code compiles correctly.

Hopefully that will give you a hint at what is going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

MS Access 2003/XP Professional/.mdb is in 2000 format.

Get the above error message when opening/previewing report that has
code
in
the on format event Code (code below). The error will occur with both
calculated fields and "real" fields (but other similar expressions work
fine).

I have deleted the report recordsource, saved and closed the report,
reopened and reassigned the recordsource property to the saved query, I
have
tried using an SQL statement as the recordsource, I have compacted and
repaired, I have decompiled the database, I have compiled it (compile
runs
without error). The field names DO appear in the field list and the
field
name(s) will autocomplete when I create the code--but won't recognize
when
I
run the code.

Tearing out what little hair I had remaining.

Paul

Code Below:
NOTE-This first if works fine
If Detail.BackColor = 12632256 Then
Detail.BackColor = vbWhite
Else
Detail.BackColor = 12632256
End If
NOTE: This is the if that blows up--"...can't find field
'IsNotedAsComplete'..."
If Me.IsNotedAsComplete Then
{do stuff}
Else
{do other stuff}
End If

NOTE: This if works fine, and FLGD_Flagged is a field in the dB/query,
not
a
report control
If Me.FLGD_Flagged = True Then
Me.txtFlagReason.Visible = True
Else
Me.txtFlagReason.Visible = False
End If