Results 1 to 3 of 3
  1. #1
    MatthewGrace is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2013
    Posts
    159

    Turn off subdatasheets

    I want to convert my Form into a splitform with the datasheet view on the the bottom. No problem - Access makes this easy.



    But then I notice there's all these little plus (+) signs to the left each row which, when clicked, reveals many related rows. I believe this is termed "Subdatasheet". Is there a way to tell Access "No thank you, eliminate those plus signs"???

    Honestly I wouldn't care either way, had it not been for the fact that people can go in and make erroneous changes to the Subdatasheet. See, in the Form's properties, I specifically set "Split Form Datasheet" to READ ONLY. This way, people can't accidentally make changes to it; I want them to use the main body of the visual form for that.

    And while it initially appears to work by preventing user input from being applied, it still allows the user to expand the little plus sign and make changes to the related records right there in the Subdatasheet view.

    Any thoughts?

    matt

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Subdatasheets are confusing to end users and slow down forms loading.
    You can remove subdatasheets from the Home ribbon as shown below
    Click image for larger version. 

Name:	Capture.PNG 
Views:	16 
Size:	24.6 KB 
ID:	42023
    If you have many forms with subdatasheets, it is also possible to prevent this using code.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I us this function to remove them:
    Code:
    Option Compare Database
    Option Explicit
    
    
    
    
    
    
    Function TurnOffSubDataSheets()
     Dim MyDB As DAO.Database
     Dim MyProperty As DAO.Property
     Dim propName As String
     Dim propType As Integer
     Dim propVal As String
     Dim strS As String
     Dim i, intChangedTables
    Dim response As Integer
    
    
    response = MsgBox("Do you wish to optimize all non-system tables by setting the Sub DataSheetName property to [None]?", vbYesNo + vbQuestion, "Optimize all non-system tables")
    If response = vbNo Then Exit Function
    
    
     Set MyDB = CurrentDb
    
    
     propName = "SubDataSheetName"
     propType = 10
     propVal = "[NONE]"
    
    
     On Error Resume Next
    
    
     For i = 0 To MyDB.TableDefs.Count - 1
    
    
         If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
    
    
             If MyDB.TableDefs(i).Properties(propName).Value <> propVal Then
    
    
                MyDB.TableDefs(i).Properties(propName).Value = propVal
    
    
                intChangedTables = intChangedTables + 1
    
    
             End If
    
    
             If Err.Number = 3270 Then
    
    
                Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
    
    
                MyProperty.Type = propType
    
    
                MyProperty.Value = propVal
    
    
                MyDB.TableDefs(i).Properties.Append MyProperty
    
    
             Else
    
    
                If Err.Number <> 0 Then
    
    
                    MsgBox "Error: " & Err.Number & " on Table " & MyDB.TableDefs(i).Name & "."
    
    
                    MyDB.Close
    
    
                    Exit Function
    
    
                End If
    
    
             End If
    
    
         End If
    
    
     Next i
    
    
    
    
    MsgBox "The " & propName & " value for all non-system tables has been updated to " & propVal & "."
     
    
    
    MyDB.Close
     
    
    
    End Function
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-30-2018, 06:08 AM
  2. can i view two subdatasheets at a time?
    By neha in forum Access
    Replies: 4
    Last Post: 12-16-2011, 01:16 AM
  3. Replies: 3
    Last Post: 10-13-2010, 09:40 AM
  4. Please Help with Subdatasheets!
    By nitinrao in forum Database Design
    Replies: 1
    Last Post: 02-08-2010, 07:23 AM
  5. creating subdatasheets
    By spyter in forum Access
    Replies: 0
    Last Post: 12-07-2008, 10:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums