Error 2467 when trying to access to subform

[hirex]

Hello,

I have a problem on an Access database linked to SQL Server 2014 database.

I'm going to try to describe my problem as it's quite strange...

In the database part where I'm getting the bug, I have 3 cascading tables, linked with an ID.
Table 1 contains 3 records.
Table 2 contains 2 records (linked to TB1 ID2 and TB1 ID3)
Table 3 contains 4 records (linked to TB2 ID1 and TB2 ID2)

So I have 3 cascading forms.
The main form Form1 (linked to Table1), SubForm2 in Form1 (linked to Table2) and SubForm3 in SubForm2 (Linked to Table3).

My problem is with the last Subform (SubForm3, linked to Table3).

All of the 3 forms are linked to a stored procedure in SQL Server.

I can open the main form to see all data without any problem.


1) As I have no record on Table2 for the first record in Table1, when I'm opening Form1 on the first record, Form2 only display header and footer, and as a consequence, Form3 doesn't appears at all.

2) When I'm moving to the second record on table1 with Form1, I'm getting all data in Form1, Form2 and Form3.

3) If I'm going back to the first record on Form1, I'm getting the same as point 1, that's ok.

4) But, if I'm going back to second record on Form1, I'm getting the message Error 2467 (The expression you entered refers to an object that is closed or doesn't exist) on Form3. Data are ok in Form1 and Form2. All lines refering to Form3 are crashing with error 2467.

I'm unable to find the difference between step 1 to 2 and step 3 to 4.

Many many thanks in advance for ideas and help.

Christian.

 

[MajP]

Can you provide some more detail?
I interpret this to mean you can go from record 1 to 2 and back to 1. You can go 1 to 2 to 3. You cannot go 3 to 2. Is that correct? If not when you say "go back", explain back from where.

All lines refering to Form3 are crashing with error 2467

What does that mean? Is there code referencing subform 3? Where is that code? What is it and what event. I definitely would think in the case where no records in the second subform and hence no third subform loaded you can throw this error if you try to reference it. Need to see the order of operation.

 

[hirex]

Thanks for your response.

Ok, these are steps I'm doing on Form1 :

1) Open Form1 on the first record
2) move to Record2 OK (Using GoToRecord)
3) Move to Record1 OK (Using GoToRecord)
4) Move again to Record2 (Using GoToRecord) make error 2467 appears.

For me, Step 4 is exactly the same thing than Step 2 but it seems that's not the case...

On step 4, error appears when trying to Set recordset on SubForm3 (or just when having a look on the Property "Name" of the Subform3 of course).

I agree with you when you say that I'm getting the error when trying to reference SubForm3 when SubForm2 has no record, this case is ok. When there are no record on SubForm2, I don't set SubForm3 Recordset. As Access hide SubForm2 details, everything is ok, SubForm3 is not displayed.

Example of code is :

Function FormCurrent_SubForm2(SubForm2 As Form)
SetSubForm3Recordset SubForm2!SubForm3.Form
End Function

Sub SetSubForm3Recordset(SubForm3 as form)
Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "pr_StoredProc"
cmd.Parameters.Refresh
cmd.Parameters("@Operation") = "SELECT"
cmd.Parameters("@QuotationProcessID") = SubForm3.Parent.Parent!TableID
Set SubForm3 .Recordset = cmd.Execute
Set cmd = Nothing
End Sub

thanks again.

 

[MajP]

I assume you are calling the code from the subform current event. What happens if you just error check if the subform is loaded? Untested check typos

Function FormCurrent_SubForm2(SubForm2 As Form)
  dim subForm3 as access.form
  on error goto errlabel
  set subForm3 = SubForm2!SubForm3.Form
  'above should throw error if not loaded
  SetSubForm3Recordset subForm3
   exit function
errlabel:
   if err.number = 2467 then 
      msgbox "Form 3 not loaded"
    else
       msgbox err.number & " " & err.description
    end if
End Function

Does it fail to load when it is supposed to?

 

[MajP]

I do not think this is your problem, because it usually occurs in the other direction when a subform tries to reference the parent form, but something to keep in mind depending how this is set up. When you have subforms the order of events gets switched. Forms with subforms load from the inside out. In otherwords the subform events occur before the main form. So if you are using the on load of the subform to reference the parent it will not yet be loaded.
https://accessexperts.com/blog/2013/12/18/prevent-subforms-in-access-from-loading-before-main-forms/
My point with nested subforms you have a lot of events happening in somewhat strange order, so this is likely a timing issue and if you trap the error you may be ok.

 

[hirex]

Hi MajP,

Yesss, it fails to load but only when I'm going from Step3 to Step4 decribed in my first message, going for the second time from record1 to record2 of the MainForm Form1.

For the order of form events, yess I have seen that SubForms events fire before MainForm events. But events are in the good order when we move from one record to another (Access fire FormCurrent event beginning with Form1, then SubForm2 and SubForm3).

So, I wonder what could I do to load SubForm3 manually at the beginning of SubForm2 Formcurrent event to ensure that everything will work like a charm ?
I don't know if it's a good idea but...:banghead:

 

[hirex]

I think that I have founded the problem. Now I have to find a solution.

As you say MajP, Access change the order of events when loading a form.
As a consequence when I'm opening Form1 on the first record, even if there is no record in SubForm2, SubForm3 is loaded by Access because it loads it first.

So, when I'm moving Form1 to Record2, it works.

If I go back to Record1, Access unload SubForm3 because there no record in SubForm2.
And, when I'm moving to Record2, I'm getting Error 2467 because SubForm3 is not loaded.

So I really need to find a way to load Subform3 before FormCurrent of SubForm2 is raised.

I don't how... If you have an idea...
Many thanks again.

 

[hirex]

I have founded a solution. Don't know if it's the best but..it works...

Idea is to set Subform3 Recordset to nothing before setting recordset property of SubForm2.
As a consequence, if there are no record in SubForm2, Access will not unload SubForm3.

Code is :
SetSubForm2Recordset(SubForm2 as form)
If Not SubForm2 .Recordset Is Nothing Then
If SubForm2 .Recordset.RecordCount > 0 Then
Set SubForm2!SubForm3.Form.Recordset = Nothing
End If
End If

Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "pr_SubForm2"
cmd.Parameters.Refresh
cmd.Parameters("@Operation") = "SELECT"
cmd.Parameters("@ID") = SubForm2.Parent!ID
Set SubForm2.Recordset = cmd.Execute
Set cmd = Nothing
End Sub

This sub is called in FormCurrent event of Form1.

Hope this help.

 

[MajP]

These event order of operation things get tricky to diagnose with out actually playing with moving around the code, because you start chasing your tail with the different events happening. You change something and then cause another problem. Often the solution is to use a different event, another objects event, or use multiple events checking if conditions have been done or can be done.
Example of this may be the same call in the load event and in the current event. So in the load event of the subform you may check if the parent is loaded and then do x. In the subforms current event you check if x was done if not do it. So you end up not being able to do X on the subforms load event, but end up doing it on the first current event of the subform.
So I do not now if you tried my suggestion with error checking if the subform is loaded and if not do not try to load it. Not sure if it would help but before trying to set subform3 recordset try do events to allow pending events to process. Another thing to consider would be to let subform3 make the call to load itself if the parent is loaded and no recordset.
This is my own personal opinion and not suggesting you should go this way, but if you did it may makes things easier. I hate nested subforms for aesthetic and comprehension reasons and rarely use them. They make my head hurt, and I rarely see good looking ones. I prefer to do synchronized subforms. An example would be having Systems with Assemblies and assemblies have parts. I would likely build a form with three subforms on it. Normally I do this in tabular view. If I click the System subform, it shows a list of the Assemblies in the other subform, and whatever record is in the Assembly subform it shows a list of parts for that Assembly. Normally this is not editable information, but I can click on any of these records in any subform to bring up a details to edit that record. I prefer to separate navigation, entry, and edit. I many Access forms with too much going on for my taste.

 

[MajP]

Disregard my last. I did not see the solution. I also kind of forgot that you were binding your own recordsets so the behavior would likely be much different if these had their own recordsource.

That solution seems pretty logical, especially if it works.