SubDatasheet Name

[Dave]

What is the correct process for turning off SubDatasheet's?

 

[Jeff Boyce]

If you are referring to the presence of those sometimes annoying and
performance-hogging "+" buttons on the left of the rows when you view the
raw tables, I believe you can turn those off by:

* open the table's definition
* right-click the titlebar and select Properties
* the subdatasheets Property seems to default to Auto ... change it to
None

If I recall, Alan Browne's website has a procedure that sifts through all
table defs and turns off this property.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Dave]

Thanks for the quick reply, but that doesn't get it to stop. I've
manually gone through each table...still shows up.

 

[John Spencer]

Here is some VBA code from MS that works in Access 2003. As far as I know it
should also work in Access 2007. Copy this into a code module and call the sub.

'Source: MS Knowledge Base #275085
'http://support.microsoft.com/Default.aspx?id=275085

Sub TurnOffSubDataSheets()
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String, propVal As String, rplpropValue As String
Dim propType As Integer, i As Integer
Dim intCount As Integer

On Error GoTo tagError

Set MyDB = CurrentDb
propName = "SubDataSheetName"
propType = 10
propVal = "[None]"
rplpropValue = "[Auto]"
intCount = 0

For i = 0 To MyDB.TableDefs.Count - 1
If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
MyDB.TableDefs(i).Properties(propName).Value = propVal
intCount = intCount + 1
End If
End If
tagFromErrorHandling:
Next i

MyDB.Close

If intCount > 0 Then
MsgBox "The " & propName & " value for " & intCount & _
" non-system tables has been updated to " & propVal & "."
End If

Exit Sub

tagError:
If Err.Number = 3270 Then
Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
MyProperty.Type = propType
MyProperty.Value = propVal
MyDB.TableDefs(i).Properties.Append MyProperty
intCount = intCount + 1
Resume tagFromErrorHandling
Else
MsgBox Err.Description & vbCrLf & vbCrLf & " in TurnOffSubDataSheets
routine."
End If
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dave]

Do I call this on the opening of the Database...or call it on the
opening of the form?

Here is some VBA code from MS that works in Access 2003.  As far as I know it
should also work in Access 2007.  Copy this into a code module and callthe sub.

'Source: MS Knowledge Base #275085
'http://support.microsoft.com/Default.aspx?id=275085

Sub TurnOffSubDataSheets()
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String, propVal As String, rplpropValue As String
Dim propType As Integer, i As Integer
Dim intCount As Integer

On Error GoTo tagError

Set MyDB = CurrentDb
propName = "SubDataSheetName"
propType = 10
propVal = "[None]"
rplpropValue = "[Auto]"
intCount = 0

For i = 0 To MyDB.TableDefs.Count - 1
     If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
         If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
              MyDB.TableDefs(i).Properties(propName).Value = propVal
              intCount = intCount + 1
         End If
     End If
tagFromErrorHandling:
Next i

MyDB.Close

If intCount > 0 Then
     MsgBox "The " & propName & " value for " & intCount & _
        " non-system tables has been updated to " & propVal & "."
End If

Exit Sub

tagError:
If Err.Number = 3270 Then
     Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
     MyProperty.Type = propType
     MyProperty.Value = propVal
     MyDB.TableDefs(i).Properties.Append MyProperty
     intCount = intCount + 1
     Resume tagFromErrorHandling
Else
     MsgBox Err.Description & vbCrLf & vbCrLf & " in TurnOffSubDataSheets
routine."
End If
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks for the quick reply, but that doesn't get it to stop.  I've
manually gone through each table...still shows up.

- Show quoted text -

 

[Jeff Boyce]

Hmmm? Works for me.

Any other characteristics?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Thanks for the quick reply, but that doesn't get it to stop. I've
manually gone through each table...still shows up.

 

[John W. Vinson]

Do I call this on the opening of the Database...or call it on the
opening of the form?

You should be able to call it once to permanently change the definition of the
tables to exclude subdatasheets.