UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> SetSubDatasheetNone    


Synopsis

Set Subdatasheet to [None] in all tables

CODE
' SetSubDatasheetNone
' http://www.utteraccess.com/wiki/SetSubDatasheetNone
' Code courtesy of UtterAccess Wiki
' Licensed under Creative Commons License
' http://creativecommons.org/licenses/by-sa/3.0/
'
' You are free to use this code in any application,
' provided this notice is left unchanged.
'
' rev  date                          brief descripton
' 1.0  2013-07-25                    
'
Public Sub SetSubDatasheetNone()
  '
  '  click HERE
  '     press F5 to Run!
  '     (or choose 'Run, Run Sub/Userform' from the menu)
  '
  'set the Subdatasheet property to [None] in all (non-MSys) tables
  '
  '----------------------------
     'NEEDS reference to Microsoft DAO Library
     'or
     'Microsoft Office ##.0 Access Database Engine Object Library
  '----------------------------

   Dim tdf As DAO.TableDef _
     , prop As DAO.Property
     
   Dim nCountDone As Integer _
     , nCountChecked As Integer _
     , mBoo As Boolean _
     , sStr As String
 
   'cheap but it works <g>
   On Error Resume Next
 
   nCountDone = 0
   nCountChecked = 0
   For Each tdf In CurrentDb.TableDefs
     'skip Microsoft System tables
     If Left(tdf.Name, 4) <> "Msys" Then

        mBoo = False
        nCountChecked = nCountChecked + 1
        Err.Number = 0
        sStr = tdf.Properties("SubdatasheetName")
        If Err.Number > 0 Then

           Set prop = tdf.CreateProperty( _
              "SubdatasheetName", dbText, "[None]")

           tdf.Properties.Append prop
           mBoo = True
        Else
           'thanks, Allen!
           If tdf.Properties("SubdatasheetName") <> "[None]" Then
              tdf.Properties("SubdatasheetName") = "[None]"
              mBoo = True
           End If
        End If
        If mBoo = True Then
           nCountDone = nCountDone + 1
        End If
     End If
   Next tdf
 
   Set prop = Nothing
   Set tdf = Nothing
 
   MsgBox nCountChecked & " tables checked" & vbCrLf & vbCrLf _
     & "Reset SubdatasheetName property to [None] in " _
     & nCountDone & " tables" _
     , , "Reset Subdatasheet to None"
 
End Sub

Creative Commons License
SetSubDatasheetNone by UtterAccess Wiki is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Editing and revision of the content is freely encouraged; for details, see Expected Usage.

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 6,771 times.  This page was last modified 15:13, 8 September 2013 by Mark Davis. Contributions by Strive4peace and Glenn Lloyd  Disclaimers