How use variables in form code…

[olaa]

I’m new with Access 2010 and need some help vid Visual Basic cod.
I have three forms FormA, FormB, FormC. They all have several sub forms. Each sub form contains a table. I want to handle the setting for this tables like the commando below… Lock the table for edit, deletion etc.
Because I have so many tables I want to put a sub routine in a module and use variables for the form name… to reuse the cod... Here I’m stuck.
If I use the code below everything works but I’m stuck to use variables for “Me”,” subform1” so it works…

Me.Subform1.Form.AllowAdditions = False
Me. Subform1.Form.AllowEdits = False
Me. Subform1.Form.AllowDeletions = False

Please help.

Regard
Ola A.

 

[JHB]

Paste the following code into a module:

Sub SetAllowToFalse(FormName As String)
  Forms(FormName).Form.AllowAdditions = False
  Forms(FormName).Form.AllowEdits = False
  Forms(FormName).Form.AllowDeletions = False
End Sub

To run the code, paste the below line where you want it.

Call SetAllowToFalse("YourFormName")

 

[Galaxiom]

JHB's code will prevent edits, deletes and additions on the main form.

A simpler alternative is to lock the subform control object. This could be done in a loop. If all the subforms are to be locked:

Public Sub LockSubforms(ByVal Lock as Boolean)
 
Dim ctrl as Control
 
    For each ctrl in Me.Controls
        If ctrl.ControlType = acSubform Then ctrl.Locked = Lock
    Next
 
End Function

Making the Sub Public means it is a Method of the form. Hence it can be called from anywhere in the VBA Project like this:

Forms!formname.LockSubForms True

Also note that it is possible to refer directly to an object rather than via its name. This technique does not require referencing via the main form.

So to do what you originally asked but refering to a Form object:

Sub PreventChanges(frm As Form, ByVal Allow as Boolean)
    With frm
        .AllowEdits = Allow
        .AllowAdditions = Allow
        .AllowDeletions = Allow
    End With
End Sub

This can be used to disable a form at any level of a nested subform structure by adjusting the call appropriately.

Disable the edits of a subform from the main form with:

PreventChanges Me.subformcontrol.Form, False

You can apply this in a loop too as I showed above.

 

[JHB]

JHB's code will prevent edits, deletes and additions on the main form.

Not quiet right, it will prevent edits, deletes and additions to the form which name is applied in the call to the Sub, (and all the controls in the form like, text boxes, check boxes, sub forms etc.).

 

[Galaxiom]

Not quiet right, it will prevent edits, deletes and additions to the form which name is applied in the call to the Sub, (and all the controls in the form like, text boxes, check boxes, sub forms etc.).

Yes. Effectively it disables the entire form. Not particularly useful and not what olaa asked for.

 

[ChrisO]

The requirement is not well specified.
We do not know if the sub-forms are in series or parallel.
Nor do we know how deep the series is or how wide the parallel is.

If we make the assumptions that there is a starting point in the form's structure, let's say the outermost parent form, and we wish to enable/disable all sub-forms in, or below, that structure then the process needs to be recursive.

By using recursion we can process all sub-forms, below some starting point, without having to know the number or distribution of the sub-forms, be they in series or parallel.

With recursive procedures I think it is important that they be kept as simple as possible. To that end I think it helps to split the mechanism of selecting, in this case the form to act upon, from the action to be taken on that form.

So, Public Sub EnableSubForms recursively determines which forms to process. It does not process those forms but rather calls Private Sub EnableTheForm to handle the processing.

Public Sub EnableSubForms(ByRef frmParentForm As Form, _
                          ByVal blnEnabledState As Boolean)
                    
    Dim ctl As Control
    
    [color=green]' Recursively process all sub-forms below this parent Form.[/color]
    For Each ctl In frmParentForm
        If ctl.ControlType = acSubform Then
            [color=green]' Enable or disable this Form.[/color]
            EnableTheForm ctl.Form, blnEnabledState
            
            [color=green]' Look for sub-forms in this sub-form control.[/color]
            EnableSubForms ctl.Form, blnEnabledState
        End If
    Next ctl

End Sub


Private Sub EnableTheForm(ByRef frmThisForm As Form, _
                          ByVal blnEnabledState As Boolean)

    With frmThisForm
        .AllowEdits = blnEnabledState
        .AllowAdditions = blnEnabledState
        .AllowDeletions = blnEnabledState
    End With

End Sub

Could be called like this from the topmost Form of interest:-

Private Sub cmdDisable_Click()
    EnableSubForms Me, False
End Sub

Private Sub cmdEnable_Click()
    EnableSubForms Me, True
End Sub

Hope that makes some sense.

Chris.

 

[olaa]

Hello! Thank you for all examples. I have to try them out for best fit. I think every example is exactly what I was looking for…
You have a question about serial and parallel sub-forms.
Each sub-form I use today are connected to one table. The table is a “ground” data table for the main table. They all are in relation coupling…

What is serial and parallel sub-forms…?

Regard
Ola A.

 

[ChrisO]

Sub-forms can be regarded as being in parallel if they share a common parent else they can be regarded as being in series if directly connected.

From an electrical perspective:-
http://en.wikipedia.org/wiki/Series_and_parallel_circuits

With forms and sub-forms it makes a difference to know the exact layout. To simply say that a form contains, say, three sub-forms is somewhat ambiguous. It could be that there are two sub-form containers on the parent form and one of which contains a form which contains another sub-form.

In the above case there are two sub-forms in parallel and two sub-forms in series but only three sub-forms in total.

Most people asking a question will not accurately specify the exact layout of the forms and sub-forms. However, it does make a difference when we need to write code.

If we wish to write code for an unspecified layout of forms and sub-forms, or tree-view controls or directory walks or ... then the code needs to be recursive.

Chris.

 

[olaa]

In my case I have a form with six subforms. Each subform are dedicated and connected to a single table. Simple as that.

IT this den parallel connected subforms…?

/Ola A.