MS Access: Infamous 'Access can't find the field..."

[ironfelix717]

What is the point of detail events (or any of the report events) if the report can't find your fields anyhow. A report is built off a recordsource... which as fields... therefore its reasonable to expect the events should capture those fields. Apparently not.

Desired outcome:
- Set a label's visible property (in the report detail) depending on the record's field.

Reality:
- 'Access can't find the field you are referring to'

I will assume this petty feature is somehow impossible.

Which ive tried...

Report_Current
Detail_Paint
Detail_Format

None of which capture this field.


Regards

 

[MajP]

May help

Dynamic Subtotal

I believe I have touched on this before, but am curious if someone can possibly provide an example. On my report, I want to have "dynamic" subtotals. I have messed with grouping, but that is an all or none deal. An example, say I have 3 groups. I want a subtotal for group 1 and 3 but not 2. How...

www.access-programmers.co.uk

Should be able to hide or appear hidden.

 

[ironfelix717]

Now I remember the last time I wasted my time with this simple petty feature...

To reference fields in these events you must capture the field value in a control on the report. Then reference the control value in VBA. Yeah--- very intuitive. Bravo to Microsoft on that peculiar nuance- probably not documented either

So, when you finally wasted an hour on that, you will be sadly disappointed that you 'Can't set the value of this property in the OnPaint event'

Therefore, setting visibility of controls in the detail section of a report (a quite trivial request) is apparently impossible or at the very least so unnecessarily difficult its not worth figuring out. Ludacris right?? I think so too.

- Regards

 

[The_Doc_Man]

Here is an article regarding the sequence of events in reports and report sections.

Order of events for database objects - Microsoft Support

support.microsoft.com

Be aware that either OnFormat or OnPrint will fire but not both. OnPrint only applies to PrintPreview. OnFormat only applies to Report View.

As to capturing field values, you ARE in a report which has class module potential, so the ME. prefix is operable, I believe. You could use syntax such as Me.recordset.fieldname anywhere in the report except the Report_Open event (because the report opens before the recordset). Detail section events cannot fire until after the report is open, so they should be able to see what is there. That syntax would capture any field in the current record of the report's recordset, as far as I recall.

 

[Galaxiom]

To reference fields in these events you must capture the field value in a control on the report. Then reference the control value in VBA. Yeah--- very intuitive. Bravo to Microsoft on that peculiar nuance- probably not documented either

The absence of something does not require documentation.

I believe. You could use syntax such as Me.recordset.fieldname

Unfortunately no.

Access will tell you the story if you try to reference the Recordset property. To put it simply, unlike a Form, Recordset is not usually a Member of the Report object.

 

[isladogs]

Not quite sure I understand your issue with this.
Just tested. Works perfectly for me using the Detail_Format event

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me.House = "Mendip" Then Me.LblHouse.visible = False
End Sub

This screenshot shows the House label hidden as the condition is met

Different student, Condition not met. Label visible

 

[The_Doc_Man]

G., MS documentation says Me.Recordset works.

Report.RecordSource property (Access)

Office VBA reference topic

docs.microsoft.com

Are they lying? While it IS Microsoft we are discussing, it's not like they are used-car salesmen.

 

[gemma-the-husky]

Now I remember the last time I wasted my time with this simple petty feature...

To reference fields in these events you must capture the field value in a control on the report. Then reference the control value in VBA. Yeah--- very intuitive. Bravo to Microsoft on that peculiar nuance- probably not documented either

So, when you finally wasted an hour on that, you will be sadly disappointed that you 'Can't set the value of this property in the OnPaint event'

Therefore, setting visibility of controls in the detail section of a report (a quite trivial request) is apparently impossible or at the very least so unnecessarily difficult its not worth figuring out. Ludacris right?? I think so too.

- Regards

I don't understand your problem. The recordsource is a query with fields. Those fields are bound to controls, which by default will have the same name as the field.

However, to hide a value, you make the CONTROL not visible. (ie not the field, although it may well be the same name)

so me.somecontrol.visible = false
or even more clearly
me.controls("somecontrol").visible = false

 

[isladogs]

Hi Dave
Whilst I also don't understand the problem, the OP stated that he wants to hide the control label rather than the actual control

 

[gemma-the-husky]

aah. Thanks Colin. But it's still the same issue, isn't it? You have to find the label attached to the control bound to the field. The label is even more remote to the field itself, isn't it? The label may not even be attached at all.

 

[MajP]

I do not understand the issue but maybe to hide an attached label
me.SomTextBox.controls(0).visible = false

 

[MajP]

Now I finally understand the original question.

 

[isladogs]

I provided a solution in post #6. It will work whether or not its an attached label.
If the field is not included in the report, add it, hidden if necessary.
Or use a DLookup or a function to get the value and, from that, the label can be hidden.
I still don't see that there is a problem.

 

[The_Doc_Man]

The reason that fields "disappear" this way is because Access rewrites the the RecordSource for reports to include ONLY the fields bound to controls.

Thanks, Pat. I had forgotten that little "gotcha" about reports. I had used .RecordSource before, but never for a field from the recordsource that wasn't used on the form.

 

[isladogs]

There is some sensible logic to these decisions:
1. If a field in the record source is not actually included as a control on a report, it wouldn't be possible to reference it in e.g. the Detail_Format event as you can't base formatting on something that 'doesn't exist'
2. Reports have additional sorting and grouping features which provide far more 'power' than that in a query or SQL statement. The record source sorting is discarded as it could conflict with that done on the report itself.
Its a consistent feature in Access: another example is sorting in a union query only depends on the sort value after the final union statement
Wherever two or more 'things' create potential conflicts, Access prevents this by always ignoring one of them

 

[Galaxiom]

G., MS documentation says Me.Recordset works.

Report.RecordSource property (Access)

Office VBA reference topic

docs.microsoft.com

Are they lying? While it IS Microsoft we are discussing, it's not like they are used-car salesmen.

Maybe it is a version thing. I have not tried it since Access 2007 where it said the feature is only available in an ADP.

 

[Gasman]

Maybe it is a version thing. I have not tried it since Access 2007 where it said the feature is only available in an ADP.

That is exactly the error I was getting.

 

[Shecky]

I have a similar issue and I do not have the skill to figure out how to solve it. I believe I am just not accessing the actual record somehow.

I want a image to become visible if a boolean field is true
My code is:

I have the "seal" in a footer section of a report (invoice) called OrderSelect the name of the fied in table is SignSeal
Access will ethier say that it can not find the field or you can not do that in an ADP or something like that.

Can anyone help with this issue?

 

[MajP]

I am not sure if this is your only problem but that syntax is incorrect.
the dot "." is used before a property and SignSeal is not a property of the recordset.

Either
me.Recordset.Fields("SignSeal")
or
Me.Recordset!SignSeal

If you want to understand why ! works, see discussion

Bang Vs Dot, Me, and Default Properties

This is a cleaned up version of a long thread with several sidebar conversations originally located at the link at the bottom of this thread. There is a lot of confusion about the use of Me. or Me! and Bang vs Dot in general. A lot of the discussion on the Internet is wrong or incomplete. The...

www.access-programmers.co.uk

When you add a field to form it gets added automatically to a controls collection even without a visible control. Now it is a property of a form or report.
This is my you can do
Me.someField
but not
Me.recordset.somefield

 

[GPGeorge]

I have a similar issue and I do not have the skill to figure out how to solve it. I believe I am just not accessing the actual record somehow.

I want a image to become visible if a boolean field is true
My code is:
View attachment 116688
I have the "seal" in a footer section of a report (invoice) called OrderSelect the name of the fied in table is SignSeal
Access will ethier say that it can not find the field or you can not do that in an ADP or something like that.

Can anyone help with this issue?

That's not really valid VBA. I suggest two things. (As MajP already noted)

Add Option Explicit to the top of this and every other module in your accdb. And compile your VBA before trying to run it so that Access can report syntax errors.

Here, there is no reason to refer to a recordset. This should do it, assuming that "SignSeal" is the name of a field in the report's recordsource and that there is a control named "signed_sealed" in the report.

Private Sub Report_Load()

Me.signed_sealed.visible = Me.SignSeal

End Sub

On the other hand, I think a good case can be made for prefixing controls on forms and reports to make the references to them clearer than if they have the same name as a field.



Private Sub Report_Load()

Me.txtsigned_sealed.visible = Me.SignSeal

End Sub