If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"...can't find the field 'FieldName' referred to in your expressio
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 |
#2
|
|||
|
|||
"...can't find the field 'FieldName' referred to in your expressio
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "PaulF" wrote in message ... 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 |
#3
|
|||
|
|||
"...can't find the field 'FieldName' referred to in your expre
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 "Allen Browne" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "PaulF" wrote in message ... 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 |
#4
|
|||
|
|||
"...can't find the field 'FieldName' referred to in your expre
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "PaulF" wrote in message ... 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 "Allen Browne" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "PaulF" wrote in message ... 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 |
#5
|
|||
|
|||
"...can't find the field 'FieldName' referred to in your expre
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 "Allen Browne" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "PaulF" wrote in message ... 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 "Allen Browne" wrote: 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "PaulF" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|