[AccessD] Change SubDatasheetName to [None] in code?

Thanks Gustav! (and Kath)!

Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Saturday, November 20, 2004 12:42 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Change SubDatasheetName to [None] in code?

Hi Dan
 
Here is how Kath Pelletti did a year ago:
 
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
  
  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

/gustav

>>> dwaters at usinternet.com 20-11-2004 18:43:34 >>>

I want to write a utility sub to change the SubDatasheetName property
in
tables to [None].  I'm trying to use the code below, but get an error
of
'The object is closed or does not exist' at the code line beginning
Set
prp =.  The table is open and in Design view.

    Dim obj As AccessObject

    Dim prp As DAO.Property

    Dim stgName As String

      For Each obj In CurrentData.AllTables

        stgName = obj.Name

        DoCmd.OpenTable stgName, acViewDesign, acEdit

        Set prp = obj.CreateProperty("SubDatasheetName", dbText,
"[None]")

        DoCmd.Close acTable, stgName, acSaveYes

    Next

Can this be done?

Thanks,

Dan Waters

ProMation Systems, Inc.

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com