none
prevent expand subdatasheet RRS feed

  • Question

  • Hi,

    I know there is a property called

    form.SubdatasheetExpanded

    I played around with no success. How can I prevent that a user expands the subdatasheet (means clicking the plus sign) when the main form is displayed in datasheet view?


    -- candide

    Thursday, May 19, 2016 8:19 PM

Answers

  • Datasheets as a rule give you very little programming control, and sub-datasheets can be very slow to load.

    A work around instead of a sub datasheet is to make the parent datasheet a continuous form, which gives you a lot more control, and can be formatted to look like a datasheet.

    The child datasheet can be in either view (datasheet or continuous forms).

    Instead of embedding the child datasheet in the parent continuous form, which in fact cannot be done, place them side by side or in some other 'sibling' arrangement on a blank main form.

    In the master/child links of the second (child) subform, use something like the following:

    Master Link:  Subform1Name!IDField

    Child Link:  IDField   (this should be the 'foreign key' that is the link between the two forms)

    Access gets a little grouchy about this at design time, but the link works.

    Additionally, you'll need code in the current event of the first (parent) subform to requery the child:

    Me.Parent.Subform2.Form.Requery

    This does not have the same appearance as your sub datasheet, but I think it would give you the net effect you are looking for:

    - The first subform controls the records displayed in the second

    - You can use additional programming as needed to set the visibility of the second subform, to control when the user can see/edit the data in it.


    Miriam Bizup Access MVP


    • Edited by mbizup MVP Monday, May 23, 2016 10:21 AM Corrected the Subform2 reference in the requery statement.
    • Proposed as answer by David_JunFeng Sunday, May 29, 2016 2:44 PM
    • Marked as answer by David_JunFeng Monday, May 30, 2016 8:55 AM
    Monday, May 23, 2016 10:02 AM

All replies

  • >>>I know there is a property called

    form.SubdatasheetExpanded

    I played around with no success.<<<

    According to your description, I have tried to reproduce this issue, unfortunately, I can't. Could you provide sample code or screenshot?

    >>>How can I prevent that a user expands the subdatasheet (means clicking the plus sign) when the main form is displayed in datasheet view?

    Based on my searching and testing, I am not able to find any good solution to overcome this issue, so I suggest that you could submit any feedback to Access UserVoice:

    https://access.uservoice.com/

    Thanks for your understanding. 
    Monday, May 23, 2016 9:10 AM
  • Datasheets as a rule give you very little programming control, and sub-datasheets can be very slow to load.

    A work around instead of a sub datasheet is to make the parent datasheet a continuous form, which gives you a lot more control, and can be formatted to look like a datasheet.

    The child datasheet can be in either view (datasheet or continuous forms).

    Instead of embedding the child datasheet in the parent continuous form, which in fact cannot be done, place them side by side or in some other 'sibling' arrangement on a blank main form.

    In the master/child links of the second (child) subform, use something like the following:

    Master Link:  Subform1Name!IDField

    Child Link:  IDField   (this should be the 'foreign key' that is the link between the two forms)

    Access gets a little grouchy about this at design time, but the link works.

    Additionally, you'll need code in the current event of the first (parent) subform to requery the child:

    Me.Parent.Subform2.Form.Requery

    This does not have the same appearance as your sub datasheet, but I think it would give you the net effect you are looking for:

    - The first subform controls the records displayed in the second

    - You can use additional programming as needed to set the visibility of the second subform, to control when the user can see/edit the data in it.


    Miriam Bizup Access MVP


    • Edited by mbizup MVP Monday, May 23, 2016 10:21 AM Corrected the Subform2 reference in the requery statement.
    • Proposed as answer by David_JunFeng Sunday, May 29, 2016 2:44 PM
    • Marked as answer by David_JunFeng Monday, May 30, 2016 8:55 AM
    Monday, May 23, 2016 10:02 AM
  • I use this function to disable automatic subdatasheets. Maybe it will do what you want? You'll need to remove or replace the external error handling. I think you can also test it by manually setting the property in the Access UI.

    Function pjsTurnOffSubDataSheets( _
     dbProcessed As DAO.Database _
    ) As Boolean
    'Turn off the automatic search for sub-datasheets. _
     It should be run against BOTH the front-end and the back-end databases if the db is split.
    On Error GoTo ErrorHandler
        Dim tdfs As DAO.TableDefs
        Dim tdfTemp As DAO.TableDef
        
        Dim propName As String
        Dim propType As Integer
        Dim propVal As String
        Dim strS As String
        Dim fSuccess As Boolean
        
        Set tdfs = dbProcessed.TableDefs
        
        propName = "SubDataSheetName"
        propType = 10
        propVal = "[NONE]"
        
        fSuccess = True 'Assume it works
        For Each tdfTemp In tdfs
            If (tdfTemp.Attributes And dbSystemObject) = 0 Then
                fSuccess = fSuccess _
                 And pjsSetObjectProperty( _
                 MyObject:=tdfTemp, _
                 strPropertyName:=propName, _
                 intPropertyType:=propType, _
                 varPropertyValue:=propVal _
                )
            End If
        Next
        
        'MsgBox "The " & propName & _
        " value for all non-system tables has been updated to " & propVal & "."
        
    ExitHandler:
    On Error Resume Next
        Set tdfs = Nothing
        Set tdfTemp = Nothing
        pjsTurnOffSubDataSheets = fSuccess
        Exit Function
    
    ErrorHandler:
        Set objError = New pjsError
        objError.pjsErrorCode Procedure:="pjsTurnOffSubDataSheets", ModuleName:=mconStrModuleName
        Set objError = Nothing
        fSuccess = False
        Resume ExitHandler
        Resume
    End Function

    I forgot, there is also a SetObjectProperty function:

    Public Function pjsSetObjectProperty( _
     MyObject As Object, _
     strPropertyName As String, _
     intPropertyType As Integer, _
     varPropertyValue As Variant _
    ) As Boolean
    'Purpose: Set field properties which may or may not already exist, like Description
    'Written: PJS 6/13/95
    On Error GoTo ErrorHandler
        Dim MyProperty As DAO.Property, intSaveError As Integer
        
        If Not (IsEmpty(varPropertyValue) Or IsNull(varPropertyValue) Or _
         IsMissing(varPropertyValue)) Then
    On Error Resume Next
            MyObject.Properties(strPropertyName) = varPropertyValue
            intSaveError = Err.Number
    On Error GoTo ErrorHandler
            
            Select Case intSaveError
            Case 0  'No error
            Case 3001, 3219          'Invalid operation- Property is probably not writeable
                Err.Raise intSaveError  'Pass the error to the caller?
            Case 3033   'No permissions for requested operation
                Err.Raise Number:=intSaveError, _
                 Description:="No permission to perform this operation"  'Pass the error to the caller
            Case 3268           'Invalid operation- Can't write to property once it's appended
                Err.Raise intSaveError  'Pass the error to the caller?
            Case 3270           'Property does not exist
                'Create Property object, setting its Name, Type, and Value properties.
                Set MyProperty = MyObject.CreateProperty(strPropertyName, intPropertyType, varPropertyValue)
                
                'See if we can append the new property
    On Error Resume Next
                MyObject.Properties.Append MyProperty
                intSaveError = Err.Number
    On Error GoTo ErrorHandler
                Select Case intSaveError
                Case 0    'No error
                Case 3219 'DAO cannot append Access-defined user-defined properties _
                           if the tabledef isn't yet appended
                Case Else
                    Err.Raise intSaveError
                End Select
                MyObject.Properties.Refresh
            Case Else
                Err.Raise intSaveError
            End Select
        End If
        pjsSetObjectProperty = True
    
    ExitHandler:
    On Error Resume Next
        Set MyProperty = Nothing
        Exit Function
    
    ErrorHandler:
        Select Case Err.Number
        Case 3001, 3219          'Invalid operation- Property is probably not writeable
            Err.Raise Err.Number 'Pass the error to the caller?
        Case 3268           'Invalid operation- Can't write to property once it's appended
            Err.Raise 3268  'Pass the error to the caller?
        Case 3421           'Data type conversion error- maybe a change in Access version?
            Err.Raise 3421  'Pass the error to the caller?
        Case Else
            Set objError = New pjsError
            objError.pjsErrorCode Procedure:="pjsSetObjectProperty", ModuleName:=mconStrModuleName
            Set objError = Nothing
            pjsSetObjectProperty = False
        End Select
        Resume ExitHandler
        Resume
    End Function


    Paul

    Monday, May 23, 2016 11:59 PM