Working with deeply nested subforms

[mrb783]

I have a form with 4 additional nested tables, for a total of 5 forms, each representing a tiered layer of my database. For the sake of conversation, let's call them Form_Master, sub1, sub2, sub3, sub4. What I want to do is disable editing on each form when Form_Master loads and whenever the form changes records (Form_Load and Form_Current). In order to edit records displayed in the forms, the user will need to click a command button. Here is the bulk of the code that will do the changes:

Private Sub AllowEdits_Change(val As Boolean)

'    Me.AllowEdits = val
'    Me.sub1.Form.AllowEdits = val
'    Me!sub1.Form!sub2.Form.AllowEdits = val
'    Me!sub1.Form!sub2.Form!sub3.Form.AllowEdits = val
'    Me!sub1.Form!sub2.Form!sub3.Form!sub4.Form.AllowEdits = val

End Sub

This function is then called during the Form_Load and Form_Current functions in Form_Master; they are also called when the two designated buttons are clicked.

Now for the issue: so far as I can tell, Access only allows the first 3 forms (up to sub2) to be changed in this manner. The subforms sub3 and sub4 will remain editable. I want to avoid this if at all possible. Any suggestions? Thanks in advance!

 

[Banana]

I don't know why you can't edit edit sub3 and sub4 like that; I'm pretty sure there should be no problem doing that, and may be a separate problem.

But just in case this is really the case, perhaps this alternative could work?

Form_MyMasterForm.AllowEdits=val
Form_MySubForm1.AllowEdits=val
Form_MySubForm2.AllowEdits=val
Form_MySubForm3.AllowEdits=val
Form_MySubform4.AllowEdits=val

This access the objects directly without going through the Forms collection and the Master form's controls collection, and of course has its quirks, but I don't think it should apply here.

HTH.

 

[mrb783]

Negative. I get an "Object Required" error message. I am stumped as to why it isn't working as well. From what I have been able to determine, older versions of Access didn't allow more than 2 nested subforms, so that could be something related to that (I am running 2003, so I don't believe that it would be an issue)...however, if I change the name of the subform object to something else, it still throws an error.

 

[Banana]

Yes, that's right; it used to be limited to 2, but I think it's now 7 or something like that.

To be clear, are you getting a "Object Required" error on your code or my suggested code?

If mine, verify that the naming is correct. It must match the naming that VBA uses in the project, which takes the form of "Form_<TheNameOfForm>". You can see how it is named in the VBA Editor's sidebar listing all modules.

 

[mrb783]

The code you suggested, only modified to include ".Form.AllowEdits", as otherwise it throws debug errors. Here's what I was using:

Me.AllowEdits = val
sub1.Form.AllowEdits = val
sub2.Form.AllowEdits = val
sub3.Form.AllowEdits = val
sub4.Form.AllowEdits = val

 

[Banana]

No, the syntax is not correct.

It should be "Form_<TheNameOfForm>", and you shouldn't need .Form. A hint that you got it right is if you see intellisense pop up when you finish typing out the name and enter the dot.

 

[mrb783]

For further clarification, I get no errors when I run the code I had listed in my original post. It just only changes the .AllowEdits value for the Master form and sub1 and sub2 subforms. The sub3 and sub4 subforms simply do not have that change occur.

 

[mrb783]

The same issue occurs with your code when I write it correctly *cough*. And apparently, even though I remember it working for the sub2 subform, it now does not, even though Master and sub1 are locked properly.

 

[mrb783]

Okay, to clarify, the "same issue" refers to my original issue...not the error message. Sorry for that, was in the "end of the day" mode and my brain was scattered.

 

[mrb783]

Okay, so I figured out a couple ways to do this. However, it spans across the forms and is not an all-in-one fix. Both methods have these tests and statements in the Form_Current function:

• The first way is to have each subform test the parent's .AllowEdits value and then set the Me.AllowEdits value accordingly.
• The other way would be to have the Master set it's own and it's subform's .allowEdits values, then have each subform (but not the last one) do the same for the next level subform.

Whether or not you want to be able to open the subforms on their own will dictate which method you wish to use...as if you use the first method, it will throw an error when opening any of the subforms.

Follow-up: I'm not sure if this will work with my buttons that will change the states, but I will be testing this as well.