Help on error '2455'

[3gustavo]

Private Sub Report_Open(Cancel As Integer)

Me.subform.Form.Filter = "[ID]= '" & Forms![ID Form]![Person ID] & "' "
Me.subform.Form.FilterOn = True

End Sub

When try to run this code, I am receiving this error: "Your entered an expression that has an invalid reference to/the property Form/Report

Can you help me, please?

 

[June7]

First, if ID is a number field, don't use apostrophe delimiters with parameter.

If you are opening a report, why are you referencing Form.Filter? If this is to filter report, use Report.Filter and Report.FilterOn

 

[3gustavo]

First, if ID is a number field, don't use apostrophe delimiters with parameter.

If you are opening a report, why are you referencing Form.Filter? If this is to filter report, use Report.Filter and Report.FilterOn

There is a subform in the report and I want to filter the subform. This doesn't matter actually, because I want to solve the error 2455

 

[June7]

A form on a report is something I've never seen. Why not a subreport?

Is the subform/subreport container actually named "subform"?

 

[3gustavo]

Doesn't matter if is a subform or subreport. I just need help with the error...

No, the container isn't named "subform"

 

[Gasman]

Doesn't matter if is a subform or subreport. I just need help with the error...

No, the container isn't named "subform"

So why use the name 'subform' ?

 

[Uncle Gizmo]

Doesn't matter if is a subform or subreport

I disagree. Is not something something that's normally done, as pointed out by June.

Hence it is possible that the error is just because you are using a subform on a report.

First step, replace the subform with a subreport and see if you still get the error.

 

[3gustavo]

I disagree. Is not something something that's normally done, as pointed out by June.

Hence it is possible that the error is just because you are using a subform on a report.

First step, replace the subform with a subreport and see if you still get the error.

Also doesn't matter if you disagree. The 2455 error doesn't concern about subform or subreport... Let's make it easier

And yes, it is something that's normally. You just don't know about it

 

[3gustavo]

So why use the name 'subform' ?

Confidentiality agreement

 

[Uncle Gizmo]

The 2455 error doesn't concern about subform or subreport...

Ah! Then I glean from this that you have some idea what the problem is, but you are not telling us.

Or possibly you are running another thread in another forum and have already eliminated this possible problem.

I'm beginning to think that you are some sort of troll.

 

[Pat Hartman]

The name of the subform CONTROL is confidential?

So the two things suggested so far:
1. if the ID is numeric, get rid of the single quotes.
2. Make sure the name you are referencing is the name of the CONTROL, not the name of the subform.

Have you eliminated those potential sources

 

[Pat Hartman]

So as Pat suggests, make sure you are referring to the actual Form, and not its container.

Actually, it's the other way around. You refer to the container name which is the Name property of the control when referencing a subform. But I agree, this can be confusing. Depending how the subform is added to the mainform, you may end up with a subform control with the same name as the subform but not always.

 

[3gustavo]

The name of the subform CONTROL is confidential?

So the two things suggested so far:
1. if the ID is numeric, get rid of the single quotes.
2. Make sure the name you are referencing is the name of the CONTROL, not the name of the subform.

Have you eliminated those potential sources

Yes, like I said, it is confidential. Actually, no reference there is true.

I am using the CONTROL name and gonna check the first bullet.

Thank you, Pat

 

[ByteMyzer]

Private Sub Report_Open(Cancel As Integer)

Me.subform.Form.Filter = "[ID]= '" & Forms![ID Form]![Person ID] & "' "
Me.subform.Form.FilterOn = True

End Sub

When try to run this code, I am receiving this error: "Your entered an expression that has an invalid reference to/the property Form/Report

Can you help me, please?

You can not perform filter operations on a subreport from the main report's OnOpen event, as it has not yet been instantiated in the Microsoft Access workspace. You need to do this from the main report's OnActivate event, like the following example (substitute MySubformName with the actual subform control name):

Private Sub Report_Activate()
    Me.MySubformName.Report.Filter = "[ID]= '" & Forms![ID Form]![Person ID] & "' "
    Me.MySubformName.Report.FilterOn = True
End Sub

 

[June7]

@ByteMyzer, unfortunately, that generates a different error message when report opened in PrintPreview. Would expect same error direct to print.

Run-time error '2191':
You can't set the Filter property in print preview or after printing has started.

Only things I can get to work for PrintPreview is setting Filter and FilterOnLoad properties in form design.

Filter: [ID]= Forms![ID Form]![Person ID]
FilterOnLoad: Yes

Or dynamic parameterized query as form's RecordSource.

Certainly know about placing subform on report, just never seen it put into production. I have worked with subreport on form.

 

[The_Doc_Man]

Good point, Mizer. No controls can be counted on from the Open event, which is why most form customization is done in the Load event. Which thus suggests that the 2455 error is because something hasn't been created yet. Which is why the references are not valid yet.

 

[ByteMyzer]

@ByteMyzer, unfortunately, that generates a different error message when report opened in PrintPreview. Would expect same error direct to print.

Run-time error '2191':
You can't set the Filter property in print preview or after printing has started.

True, it does generate the error from PrintPreview, but it shouldn't from Print (at least, it doesn't for me).

 

[ByteMyzer]

Good point, Mizer. No controls can be counted on from the Open event, which is why most form customization is done in the Load event. Which thus suggests that the 2455 error is because something hasn't been created yet. Which is why the references are not valid yet.

Ahh, yes, the OnLoad event works for this as well (subject to the same PrintPreview bug, but there you have it).

 

[June7]

I tested code as suggested by @ByteMyzer sending report direct to PDF without first opening in PrintPreview. Doesn't error but also doesn't filter. Report View when opening first displays all records then applies filter. So direct to printer/PDF does not get to apply filter.

 

[ByteMyzer]

I tested code as suggested by @ByteMyzer sending report direct to PDF without first opening in PrintPreview. Doesn't error but also doesn't filter. Report View when opening first displays all records then applies filter. So direct to printer/PDF does not get to apply filter.

In any event, using VBA to filter a subreport from the main report does not render consistent results. What would work, consistently, in reference to the example provided by @3gustavo, would be to have the main report RecordSource query refer to Forms![ID Form]![Person ID] as a criterion, and to bind the main report ID field and subreport ID field via the LinkChildFields and LinkMasterFields properties of the subreport object.