Run Time error 2455 invalid reference

[XV1957]

Hello Access experts.
I have a main unbound form frmArticle on which there is a tab control with 2 pages.
On the first page is a subform frmArticle1, on the second another subform frmArticle2.
Both subforms are bound to the same table tblArticle, and both subforms show the Recordkey Artno and the description. Apart from that, the info on both pages is different.

On the current event of subform frmArticle 1 I have following code (from Andrew Couch):
Me.Parent.frmArticle2.Form.RecordsetClone.FindFirst "Artno = '" & Me!Artno & "'"
Me.Parent.frmArticle2.Form.Bookmark = Me.Parent.frmArticle2.Form.RecordsetClone.Bookmark

On the current event of subform frmArticle2 I have the following code:
Me.Parent.frmArticle1.Form.RecordsetClone.FindFirst "Artno = '" & Me!Artno & "'"
Me.Parent.frmArticle1.Form.Bookmark = Me.Parent.frmArticle1.Form.RecordsetClone.Bookmark

When I open the main form I get the following error message : “Run-time error 2455: you entered an expression that has an invalid reference to the property Form/Report”.
The line highlighted by the debugger is the first on subform frmArticle1.

The strange thing though is that, when I click “end” on the error, the form opens and behaves correctly. Both pages update each other correctly when I navigate the subforms.
I can get rid of the error by entering “on error goto exithandler” which exithandler is situated just before the end sub. But I hate to do that without understanding what caused the error in the first place.
Thanks in advance for shedding some light, cause I can't find it.

 

[Uncle Gizmo]

If you comment out both current events what happens?

 

[vbaInet]

I'm sure Uncle Gizmo won't mind

Both subforms are bound to the same table tblArticle,...

They shouldn't be in subforms if that's the case.

When I open the main form I get the following error message : “Run-time error 2455: you entered an expression that has an invalid reference to the property Form/Report”.

A subform loads before the parent form so at that point the parent form doesn't exist.

 

[XV1957]

Hi Uncle Gizmo and VBAInet, thanks for your help.
Have written two replies and they both vanished cause I did not check I was logged properly.
When I take out the lines in the current event, the form opens, but of course the functionality does not work.
If I understand you correctly VBAInet, I should have put my fields straight on one of the pages, instead of going through a subform. I will give it a try at once.
I still am a bit puzzled because in one of his example forms, Andrew Couch puts two subforms on a main form, and has a link between the main form and the subform (I actually got the code in question right from his form). Why then does it work in his case, and not in mine, as the subform opens before the main form, and referring in the subform to the recordclone and the bookmark of the main form, should theoretically also raise an error? The difference with my case is that each subform is bound to a different table, and the link is not between two subforms.

Thanks again for getting me out of the mud. Most helpful!

 

[Uncle Gizmo]

Always happy to get some help VB thank you.

I think you misspoke in your answer I think what you meant to say was that the other subform has not loaded yet, as the main form must load or you'd be in serious trouble!

 

[XV1957]

Sorry yes, I used the term open instead of load, should have been more accurate. Forgot to say that in Andrew's example, the main form has three tab pages, and page two and three each have a subform. Thanks for signalling my mistake.

 

[vbaInet]

If I understand you correctly VBAInet, I should have put my fields straight on one of the pages, instead of going through a subform. I will give it a try at once.

Precisely! If they link to separate tables then it makes sense to have them in separate subforms. If you want to know the reason why, I'll explain.

@Uncle Tony: That's it!

 

[vbaInet]

And you might also not be referring to the subform control itself.

 

[XV1957]

And you might also not be referring to the subform control itself.

No, that was the object of our first contact, and I triple checked that this was not the case here.
I am certainly interested in the reasons, but only if you have a good link at hand, you must be busy enough. Thanks again.

 

[Uncle Gizmo]

I often use subforms to display information from the same table, hence I have run into this problem before.

The problem is the other subform has not yet finished loading therefore your subform current event is looking for something that isn't there.

However you still want to use that current event, so you cannot delete it, you just need to suppress it until the other subform has loaded.

To do this, I use a property statement on the main form:- ("main form" referred to as "Parent" in the Code)

Private mSubLoaded As Boolean '???
'############## ---------------- Property Statements ----------------- ########
Property Let prpSubLoaded(lngSubLoaded As Boolean)
    mSubLoaded = lngSubLoaded
End Property      'prpSubLoaded Let

Property Get prpSubLoaded() As Boolean
    prpSubLoaded = mSubLoaded
End Property      'prpSubLoaded Get

and when the subform causing the issue loads I use its Load event:-

Private Sub Form_Load()
    Me.Parent.prpSubLoaded = True

End Sub

to set this property to true.

Now in the current event of the first sub form:-

Private Sub Form_Current()

If Not Me.Parent.prpSubLoaded Then
    Exit Sub
End If

'TESTING
'MsgBox " >>> " & Me.Parent.subFrmWinfrmData1.Form.Name
'MsgBox " >>> " & Me.Parent.txtMasterDescription
'MsgBox " >>> " & Me.Parent.subFrmWinfrmData2.Form.Name
End Sub

I have an if statement that interrogate this property on the main form and if it has not been set to true then it exits the current event subroutine preventing the problem.

 

[XV1957]

Uncle Gizmo, thanks for your update. I will try it at once, though I must say I will have to re-read your reply a couple of times, as I have only recently discovered the beauty of Access.

 

[vbaInet]

I am certainly interested in the reasons, but only if you have a good link at hand, you must be busy enough. Thanks again.

The main reason being, if you're editing data on a subform, once it loses focus those changes are saved. So for subforms that are linked to the same table, this isn't ideal.

Private Sub Form_Current()
If Not Me.Parent.prpSubLoaded Then
    Exit Property
End If
End Sub

You can also get away with not needing an extra check in the Current event, i.e. by setting subform 1's OnCurrent property to "[Event Procedure]" in the Load event of subform 2.

In any case XV, what you're trying to do isn't the right way. And you'll end up with a circular reference because when subform1 fires it's Current event, subform2 will fire it's Current event which in turn will fire subform1's Current event... etc.

 

[Uncle Gizmo]

VB ... That "Exit Property" should be "Exit Sub"

 

[vbaInet]

VB ... That "Exit Property" should be "Exit Sub"

Yeah I noticed.