[AccessD] Turn Off Sub Datasheets

To quickly turn off the subdatasheets in tables, run this code in a module
in your database where the actual tables are located.

 

'---------------------------------------------------------------------------
--------

Private Sub TurnOffSubDataSheets()

    On Error GoTo EH

 

    '-- Before running this module, set Error Trapping to 'Break on
Unhandled Errors'

 

    Dim dbs As DAO.Database

    Dim prop3270 As DAO.Property

    Dim propName As String, propVal As String, rplpropValue As String

    Dim propType As Integer, i As Integer

    Dim intCount As Integer

    

    

    Set dbs = CurrentDb

    propName = "SubDataSheetName"

    propType = 10

    propVal = "[None]"

    rplpropValue = "[Auto]"

    intCount = 0

    

    For i = 0 To dbs.TableDefs.Count - 1

        If (dbs.TableDefs(i).Attributes And dbSystemObject) = 0 Then

            If dbs.TableDefs(i).Properties(propName).Value = rplpropValue
Then

                 dbs.TableDefs(i).Properties(propName).Value = propVal

                 intCount = intCount + 1

            End If

        End If

        

XH:

    Next i

    

    dbs.Close

    

    MsgBox "The " & propName & " value for " & intCount & " non-system
tables has been updated to " & propVal & "."

    

    Exit Sub

    

EH:

    If Err.Number = 3270 Then

        Set prop3270 = dbs.TableDefs(i).CreateProperty(propName)

        prop3270.Type = propType

        prop3270.Value = propVal

        dbs.TableDefs(i).Properties.Append prop3270

        intCount = intCount + 1

        Resume XH

    Else

        MsgBox Err.Description & vbCrLf & vbCrLf & " in TurnOffSubDataSheets
routine."

    End If

    

End Sub