Solved Error 2455 requering from another subform

[Gaztry80]

Goodmorning,

Last year i posted the following question about error 2455.

Error 2455 requering another subform

Good evening, I have a form called frmMainTable with two sub forms (frmFirstTable & frmSecondTable), in the subform frmFirstTable I have placed in the on-current event the following code: Forms![frmMainTable]![frmSecondTable].Form.Requery However, when I open the frmMainTable I get the...

www.access-programmers.co.uk

I now get stuck with the same problem and still don't understand what I should do differently (feels stupid ) .

Scenario: I have a main form "frmMain" with 3 different subform within it > "frmCars", "frmCarColors", "frmIngredient".
I want that the user is able to click a car in "frmCars", then see the corresponding available colors in "frmCarColors" and when clicking a color that the user sees the necessary ingredients in "frmIngredient".

I am able to fix this by placing in the on current event of "frmCars" and "frmCarColors" a "Requery code" of the relative subform.
However, I keep getting the error 2455 code and after reading again my other post, i still don't get it.

Hopefully, somebody can help me understand what to do.
My apologies for asking a similar question.

Thank you.

 

[Gasman]

It was explained by @MarkK why.

If you just want to ignore it, add an error section and test for that error.?

Alternatively, make sure it is open first?

AccessObject.IsLoaded property (Access)

Office VBA reference topic

learn.microsoft.com

 

[MajP]

Access makes this real easy to do if you leverage what a subform is capable of. No need to requery or have complicated queries. You can add some conditional formatting to make it clearer what is selected.

Everything in red can be hidden, left visible for demonstration. Look at the subform links and on current code of subforms.

 

[MajP]

Doing this using your method of control references in the query would be a real pain. Even if you added error checking to ignore the requery you will still get a parmater input for each of the queries because each subform loads before the parent. To avoid that you would have to remove the recordsource from the color and ingredient subforms. Then on the main forms onload event iset the recordsources of those subforms.
These recordsources cannot be loaded until after the main form loads.

 

[Gaztry80]

Thank you for your reactions. I am familiar with Parent & Child linking of subforms.
The reason I am walking this path is that I want to determine the recordsource of "frmCarColors" based on a variable from "frmCars" and the recordsource of "frmIngredient" based on a variable of "frmCarColors".

 

[MajP]

And you do not like my working demo??? Clearly it does that.

How about this version then. Again avoiding control references.

 

[Gaztry80]

I like this .
Both versions give me some new learnings.
Is it correct that the method used in TEST3 is a tiny bit faster regarding loading / processing?

 

[MajP]

Is it correct that the method used in TEST3 is a tiny bit faster regarding loading / processing?

I do believe that in general controlling a subform using the subform controls "Parent Link Fields", "Child Link Fields" is slower than changing the recordsource of the subform dynamically. I have seen cases where this can be noticeable and this appears to be one of those cases; however, the second example did not do conditional formatting which also can slow the forms down.

 

[MajP]

I tested without the conditional formatting and it was much faster. So the conditional formatting was the cause of the delay. The other nice thing about the subform control it handles new records. If not you have to add code to ensure you add a new foreign key. See update.

 

[Gaztry80]

Thanks @MajP , your test files have really helped me today.
Will play tomorrow again with it, enjoy the evening !

 

[MajP]

However, you can fix the problem with the way you did it if you add some kind of error checking. A simple resume next or trap the specific error

 On Error Resume Next
  Forms![frmMAIN]![frmIngredient].Form.Requery

This is because a form actually loads from inside out. So the subforms load before the main form. So on the initial current event the main form is not loaded and the above would cause an error.