MS Access: Infamous 'Access can't find the field..." (1 Viewer)

[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

 

[Pat Hartman]

o reference fields in these events you must capture the field value in a control on the report.

Since you know the rule, follow it.

The reason that fields "disappear" this way is because Access rewrites the the RecordSource for reports to include ONLY the fields bound to controls. Once you know this. Work around it. It isn't obvious and I don't like it but no one at MS asked me.

 

[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.

 

[Pat Hartman]

The thing about this "feature" is that Access fools you. You create the report and add the code that references the record source and it works so you save the changes and close the report. Then you open it and your code no longer works??? I'm not sure at what point Access rewrites your record source but once it does, you have to bind the field to a control (hidden is fine) to make it work as the others have mentioned.

The other mystery that knowing this tidbit solves is - why when I change the order by clause in my query does the order by not change on the report? Same reason. Access apparently discards your order by in the query and replaces it with the one defined in the report properties.

The intention of course, is to make the report more efficient. It's just hard to find this information and it is certainly mysterious until you know it. I think MS should warn you when it is saving that it is rewriting the recordSource. That might make you unhappy but at least you would know.

 

[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.