Form property of a sub sub form (1 Viewer)

Andy74

Registered User.
Local time
Today, 03:11
Joined
May 17, 2014
Messages
117
Hello everybody,

I have form with a subform in datasheet view and a subsubform also in datasheet view. I have a routine that once the main form is open applies permissions to each control (like hiding colums) and when the control is a subform it goes one level below, i.e. runs throught the subform's controls. So it's a nested kind routine.

For some reason I get the error 2455 when trying to get the SubSubFrom Form property from the control which is the SubSubform. It works on the subform, but it stops when going to the SubSubfrm.

Any idea where I am doing wrong?

Code:
' this a general code on a module
Sub ApplyOptionsOnForm(frm as Form)
    ....
    For Each ctr In frm.Controls
           If ctr.Properties("controltype") = acSubform Then
                If ctr.Properties("SourceObject") <> "" Then
                         call ApplyOptionsOnForm(ctr.Form)
                End If
            End If
        End If
    Next ctr
   .....
End Sub
 

isladogs

MVP / VIP
Local time
Today, 02:11
Joined
Jan 14, 2017
Messages
18,186
I'm confused. Do you mean you have a datasheet subform which contains a Subdatasheet.
In other words it has a + adjacent to each datasheet record which can be expanded to show the Subdatasheet

If not that, can you show a screenshot.
 

Andy74

Registered User.
Local time
Today, 03:11
Joined
May 17, 2014
Messages
117
yes, correct, it has the + on the subform which expands into the subsub
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:11
Joined
Oct 29, 2018
Messages
21,358
yes, correct, it has the + on the subform which expands into the subsub
Hi Andy. How did you add the subsubform to the subform? If you didn't add it, then you probably can't use your code to set the permissions to it because it doesn't exists.What does error 2455 message say?
 

Andy74

Registered User.
Local time
Today, 03:11
Joined
May 17, 2014
Messages
117
Hi DbGuy!

sure, the SubSubform is added to the Subform, i.e. I added in design view. In fact the SubSubform is working ok. The only thing is that the code above is not setting the propoerties of the SubSub as I would like to. I have attached a screenshot.

The error 2455 says "Unvalid reference to property Form/Report in the expression". Sorry it's a translation, I have the Italian version of Access 2010.
 

Attachments

  • Cattura.PNG
    Cattura.PNG
    19.5 KB · Views: 140

isladogs

MVP / VIP
Local time
Today, 02:11
Joined
Jan 14, 2017
Messages
18,186
As I thought.
Using a Subdatasheet is not the same as a subform within a subform.
You only have one subform and there is very little you can do to manage the Subdatasheet using vba.
Basically 4 commands only:
AcCmdSubdatasheetExpandAll/CollapseAll/Remove & AcCmdInsertSubdatasheet
 

Andy74

Registered User.
Local time
Today, 03:11
Joined
May 17, 2014
Messages
117
Thanks, though not very clear to me: I still have 3 objects in my form list (frmMain, frmSub, frmSubSub). But the frmSub and the frmSubSub are by default in DataSheetView: is this making the frmSubSub difficult to control? Should I change it maybe in continuous form View?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:11
Joined
Oct 29, 2018
Messages
21,358
Hi DbGuy!

sure, the SubSubform is added to the Subform, i.e. I added in design view. In fact the SubSubform is working ok. The only thing is that the code above is not setting the propoerties of the SubSub as I would like to. I have attached a screenshot.

The error 2455 says "Unvalid reference to property Form/Report in the expression". Sorry it's a translation, I have the Italian version of Access 2010.
Hi. Sorry to not believe you, but in my experience, it's impossible to put a sub-subform in a datasheet (or continuous) subform. I'd like to see how you managed to do it. I may have to give it a try, just in case I remembered wrong, since I haven't tried it in a while (knowing what I know, which may be hindering me at the moment).
 

Andy74

Registered User.
Local time
Today, 03:11
Joined
May 17, 2014
Messages
117
Well, in fact for me this is the first time I am doing this. Below is the screen shot of the main form in design view. The Sub and the SubSub appears in FormView but they are set to start with DataSheetView when you open them normally.
 

Attachments

  • Cattura_2.jpg
    Cattura_2.jpg
    64.1 KB · Views: 120

Micron

AWF VIP
Local time
Yesterday, 22:11
Joined
Oct 20, 2018
Messages
3,476
If a subform typically holds the many in a one to many relationship, then a sub-subform must be the many side of a many to many relationship. That I have never seen, let alone created. So this is just a stab at getting the syntax for a form on a form on a form. You would have to substitute you control and form names. Note that subformControl represents the name of the subform control that contains the subform. It may or may not be the same as the subform name. In my db's it never is.

Code:
Forms!frmMain!subformControlName.Form!subsubformControlName.Form.ControlName
 

Andy74

Registered User.
Local time
Today, 03:11
Joined
May 17, 2014
Messages
117
Thanks Micron. The names are consistent, i.e. the name of the control is the same name of the subform object. The reason I came out with form, sub and sub sub is this: the main form is "Offers", the sub form contains "OfferLines" (i.e. each offer is made of many lines, i.e. many products), and each line contains different product Options (this is the SubSub).
 

Micron

AWF VIP
Local time
Yesterday, 22:11
Joined
Oct 20, 2018
Messages
3,476
Well it's not clear if it really needs to be done that way as I think everything depends on the nature of the business at hand. F'rinstance, maybe the options distinguish one "product" from another, thus the same item with different options is really a different product. Regardless, if it works for you then that's usually the deciding factor. You didn't say if the syntax was right. I'd like to know since I was just guessing by applying the same principle as what I know for a single subform/form relationship.
 

isladogs

MVP / VIP
Local time
Today, 02:11
Joined
Jan 14, 2017
Messages
18,186
To confirm an earlier point, despite your form design it will only work as a subsubform when the subform itself is in single view.

You can't place a subform in the Detail section of a continuous form, but you can place it in the form footer (or header) of a continuous form. As you move from record to record in the continuous form, the subform in the footer will display the related subrecords.

Another option is to arrange all subforms on the main form.

Hope that helps
 
Last edited:

Andy74

Registered User.
Local time
Today, 03:11
Joined
May 17, 2014
Messages
117
your syntax looks ok to me, but I am not using that type of syntax. If you look at the code on the top of the thread I pass the frm as an object and the run through the controls. The error comes out at the central line "call ... ". By debugging I found out that when the Ctr variable (declared as a Control) is the SubSubform control: the name is correct, the ctr.controlType is acSubform, the ctr.SourceObject is correctly the form object in the database but the ctr.Form is not accepted as a Form and gives the Error 2455. I have no idea why!
 

Micron

AWF VIP
Local time
Yesterday, 22:11
Joined
Oct 20, 2018
Messages
3,476
I realize what you're code does, but what it doesn't do is drill down far enough from what I can see. If you find the subform control and the sourceobject property is not "" then you are calling the sub again with that object (form). When that is the subform, you need to drill down 2 more levels from that form: Form > subsubformcontrol > Form to get at its controls. Not sure how you'd do that. I suppose that if I'm on the right track someone would be willing to play with a db if you can post a copy.
 

Andy74

Registered User.
Local time
Today, 03:11
Joined
May 17, 2014
Messages
117
Micron: I just tried your syntax from the immediate Window and it gives exactly the same result, i.e. error 2455, when try to access data of controls on the SubSubfrm.

But it work to access properties of a control on the SubForm. See the screenshot of the immediate Window.
 

Attachments

  • Cattura3.PNG
    Cattura3.PNG
    32.1 KB · Views: 226

Andy74

Registered User.
Local time
Today, 03:11
Joined
May 17, 2014
Messages
117
Thanks. I have tried to use your syntax from the immediate window but I get to the same problem. It works till the SubForm level but it doesn't work to the SubSubForm level.

See screenshot below (I have used the real names of the forms)
 

Attachments

  • Cattura3.PNG
    Cattura3.PNG
    32.1 KB · Views: 216

Andy74

Registered User.
Local time
Today, 03:11
Joined
May 17, 2014
Messages
117
sorry I just realized I posted the same thing twice!
Anyway I will try to post the accdb file, but at the moment is very big and I should make a much cleaner version
 

isladogs

MVP / VIP
Local time
Today, 02:11
Joined
Jan 14, 2017
Messages
18,186
Andy
Did you see my reply in post #13?
I don't believe you can get this to work with your current setup
 

Andy74

Registered User.
Local time
Today, 03:11
Joined
May 17, 2014
Messages
117
Isladogs: in fact I missed it and I think you hit the point: it will not work in the way I designed the thing. I think I have to go back and redesign the SubForm and the SubSub. Thank you!
 

Users who are viewing this thread

Top Bottom