Public Sub SetSubDatasheetNone()
'crystal (strive4peace) 061027, 160905, 200423
'strive4peace
'based on code written by Allen Browne
'set the Subdatasheet property to [None]
'in all user tables
'needs reference to
'Microsoft DAO Library
Dim tdf As DAO.TableDef _
, prop As DAO.Property
Dim iCountDone As Integer _
, iCountChecked As Integer _
, bChanged As Boolean _
, sName As String
'cheap but it works <g>
On Error Resume Next
iCountDone = 0
iCountChecked = 0
For Each tdf In CurrentDb.TableDefs
'skip Microsoft System tables
If Left(tdf.Name, 4) <> "Msys" Then
bChanged = False
iCountChecked = iCountChecked + 1
Err.Number = 0
sName = tdf.Properties("SubdatasheetName")
If Err.Number > 0 Then
Set prop = tdf.CreateProperty( _
"SubdatasheetName", dbText, "[None]")
tdf.Properties.Append prop
bChanged = True
Else
'thanks, Allen!
If tdf.Properties("SubdatasheetName") <> "[None]" Then
tdf.Properties("SubdatasheetName") = "[None]"
bChanged = True
End If
End If
If bChanged = True Then
iCountDone = iCountDone + 1
End If
End If
Next tdf
Set prop = Nothing
Set tdf = Nothing
MsgBox iCountChecked & " tables checked" & vbCrLf & vbCrLf _
& "Reset SubdatasheetName property to [None] in " _
& iCountDone & " tables" _
, , "Reset Subdatasheet to None"
End Sub