Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2005
    Posts
    30

    Unanswered: Change allowedits property on a subform

    I'm trying to create a button that will allow me to turn properties like allowedits on or off with vba code. I have been able to do it with a "Brute force method" using the with construct and repeating code.

    With Me![well pull subform] 'repeated for each subform 8 times
    .Form.AllowEdits = True
    .Form.AllowAdditions = True
    .Form.AllowDeletions = True

    There has got to be a better way. I tried an array of strings and it worked for do command actions but won't work for allowedits. The error I get is access can't find the form.

    Dim frm as string
    set frm = "well pull subform"

    [Forms]![well work]![frm].Form.allowedits = true

    Is there a way to change "well pull subform" to [well pull subform]
    so it will work or am I trying to turn lead into gold.


    I finally found this code that works for properties like visible but bombs on allowedits.

    Public Function update_controls()
    Dim ctl As Control
    form_name = "well work"
    DoCmd.OpenForm (form_name), acDesign
    With Forms(form_name)
    For Each ctl In Forms(form_name).Controls

    If ctl.ControlType = acSubform Then
    ctl.Properties("allowedits") = yes

    End If
    Next
    End With
    End Function

    This gives me invalid reference to allowedits property error message.

    Does anyone know how to get around this or am I stuck with brute force.
    I would appreciate any help anyone could give me.

  2. #2
    Join Date
    Nov 2002
    Location
    Sydney, Australia
    Posts
    11
    The last code example you quoted should work, I use similar myself to change AllowEditrs etc depending on a user's permissions, except it is missing the ref to the subform control's 'form' property. So it should be:

    If ctl.ControlType = acSubform Then
    ctl.form.allowedits = True
    End If


    HTH
    Max Hugen
    Hugen Enterprises Pty Ltd
    www.hugen.com.au
    Australia

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    Would just Locking the SubForm Control work for you?

    Me.[well pull subform].Locked = True

    Or

    Me.[TheNameOfTheSubFormControlOnParentForm]. Locked = True

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  4. #4
    Join Date
    Jul 2005
    Posts
    30

    changing allow edits

    Thanks for the help. The code did work but wasn't exactly what I was looking for. I came up with what I think is a pretty good solution.

    Private Sub Command45_Click()

    'declare an array of subforms

    Dim arrayName(6) As subform

    'load the array with the subforms

    Set arrayName(0) = Forms![well work]![well pull subform]
    Set arrayName(1) = Forms![well work]![well tests subform]
    Set arrayName(2) = Forms![well work]![chem treat subform]
    Set arrayName(3) = Forms![well work]![fluid level subform]
    Set arrayName(4) = Forms![well work]![pump subform]
    Set arrayName(5) = Forms![well work]![rod string subform]
    Set arrayName(6) = Forms![well work]![tbg string subform]

    'Use a for loop to loop thru the subforms and change properties
    ' intcounter moves through array

    For intcounter = 0 To UBound(arrayName)
    arrayName(intcounter).Form.AllowEdits = True
    arrayName(intcounter).Form.AllowDeletions = True
    arrayName(intcounter).Form.AllowAdditions = True
    Next intcounter
    End Sub

    This works well and doesn't require the form to be open in design view.
    It works for changing back to false, but required a refresh form statement
    to make the form usable again.

    Thanks again
    jim dare

    P.S. if sombody has a better idea please post it.

  5. #5
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Wasn't sure if you were wanting to do this for ALL subforms or only particular ones. This code sets the properties for ALL subforms.
    Code:
    Private Sub cmdAllow_Click()
        'Set .AllowEdits, .AllowAdditions, .AllowDeletions
        'for ALL subforms to TRUE
        FSubAllow True
    End Sub
    
    Private Sub cmdDisallow_Click()
        'Set .AllowEdits, .AllowAdditions, .AllowDeletions
        'for ALL subforms to FALSE
        FSubAllow False
    End Sub
    
    Sub FSubAllow(blnAllow As Boolean)
        Dim ctrl As Control
    
        For Each ctrl In Me.Controls
            If ctrl.ControlType = acSubform Then
                MsgBox ctrl.Name
                ctrl.Form.AllowEdits = blnAllow
                ctrl.Form.AllowAdditions = blnAllow
                ctrl.Form.AllowDeletions = blnAllow
            End If
        Next ctrl
    End Sub
    If it was only particular subforms, I would create a function to call to do it just because I prefer to have reusable code that I can call.
    Code:
    Sub FSubAllow_Individual(strFormName As String, blnAllow As Boolean)
        Dim ctrl As Control
    
        Set ctrl = Me.Controls(strFormName)
        ctrl.Form.AllowEdits = blnAllow
        ctrl.Form.AllowAdditions = blnAllow
        ctrl.Form.AllowDeletions = blnAllow
    End Sub
    You could call it as follows.
    Code:
        FSubAllowIndividual "fsub1", True
        FSubAllowIndividual "fsub2", True
        FSubAllowIndividual "fsub3", True
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    I would go with Max and Cosmos75 on this one by using the For Each method. That is of course if you do intend to restrict all Subforms located on the Well Work Form (providing they're not nested):

    Code:
    Dim Ctrl as Control
    Dim EnableDisable As Boolean
     
    EnableDisable = False
    For Each Ctrl in Me.Controls
       If Ctrl.ControlType = acSubform Then
          With Ctrl
             .AllowEdits = EnableDisable
             .AllowDeletions = EnableDisable
             .AllowAdditions = EnableDisable
          End With
       End If
    Next Ctrl
    This all works fine and well providing you don't have nested SubForms (SubForms within SubForms). The code above will do nothing to a nested SubForm. Sometimes we'll need to go deeper than the root SubForm(s).

    To take care of Nested SubForms (no matter how deep the nest) we'll need to create a Function and utilize a recursion method to process them all. The following code should do this:

    Code:
    Public Function SetSubFormProperties(Frm As Form, EnableDisable As Boolean)
       Dim Ctrl As Control
       Dim NxtFrm As Form
       For Each Ctrl In Frm.Controls
         If Ctrl.ControlType = acSubform Then
             Set NxtFrm = Ctrl.Form
             Call SetSubFormProperties(NxtFrm, EnableDisable)
             With NxtFrm
                ' The Properties You want set...
                .AllowEdits = EnableDisable
                .AllowDeletions = EnableDisable
                .AllowAdditions = EnableDisable
             End With
          End If
       Next Ctrl
    End Function
    To use this function, place this line:

    Call SetSubFormProperties(Me, False)

    into the OnClick event of a Command Button or whatever on the Parent Form. When fired it should set the properties listed within the function to True or False for all the SubForms found on the Parent Form either nested or not depending upon what Boolean parameter is sent to the function.

    Note:
    The function above is just off hand and really should contain error traping and handling.

    Note:
    Although I have never nested this deep with SubForms, I believe you can nest SubForms seven (7) levels deep. This restriction may now be improved with the newer releases of MS-Access.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  7. #7
    Join Date
    Jul 2005
    Posts
    30
    Thanks for all the help. Cyberlynx's code worked the best for my situation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •