Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sub Data Sheets (2003 (11.6566.8036) SP2)

    Hi There!

    Is there a way to Set the SubdataSheet Name property to default to [None] rather than [Auto] within a DB? It's quite laborious to have to go into each table and perform the change either by going to the properties or by removing the Subdatasheet, especially if there are a lot of tables involved.

    Haven't seen anything that helps in this respect so far, so if anyone can point me in the right direction, I would be most grateful.

    Cheers,

    Niven

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Sub Data Sheets (2003 (11.6566.8036) SP2)

    I don't know of a way to set the default, but if you go to my website below and download "Liquorman Utilities", there is an option to remove the subdatasheets from a database.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Data Sheets (2003 (11.6566.8036) SP2)

    Many thanks your reply, will check out your site!

    Cheers,

    Niven <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Data Sheets (2003 (11.6566.8036) SP2)

    Aha! Good stuff!

    Thought there maybe a vb way of doing things. Will implement accordingly.

    Cheers again,

    Niven <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Data Sheets (2003 (11.6566.8036) SP2)

    Unfortunately and irritatingly, no. Why did Microsoft make the 'wrong' option the default? (Same for the Track Name AutoCorrect option, which should be off by default).

    Here is a macro you can run to set the Subdatasheet Name to [None] in all tables- it requires a reference to the Microsoft DAO 3.6 Object Library:

    Sub TurnOffSubdatasheets()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Set dbs = CurrentDb

    On Error GoTo ErrHandler

    For Each tdf In dbs.TableDefs
    tdf.Properties("SubdatasheetName") = "<!t>[None]<!/t>"
    Next tdf

    ExitHandler:
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    tdf.Properties.Append tdf.CreateProperty("SubdatasheetName", dbText, "<!t>[None]<!/t>")
    Resume
    End Sub

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Data Sheets (2003 (11.6566.8036) SP2)

    Hi Hans

    Does this code handle Backend tables as well?

    Thanks, John

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Data Sheets (2003 (11.6566.8036) SP2)

    Surely, trying it out yourself would have taken less time than waiting for my reply?

    No, the code sets the Subdatasheet Name property of the link in the frontend to [None], but this doesn't affect the source table in the backend.

    You could run the code in the backend, or instead of

    Set dbs = CurrentDb

    use

    Set dbs = OpenDatabase("...")

    substituting the full path/filename of the backend.

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Data Sheets (2003 (11.6566.8036) SP2)

    Thanks for your help

    I had to add the following in only one of my databases.

    MSysACEs was the only MSys table that complained and in only one database

    ' Runtime errpr 3110, Could not read definitions, no read definitions permission
    ' for table or query 'MSysACEs'

    If Left(tdf.Name, 4) <> "MSys" Then
    tdf.Properties("SubdatasheetName") = "[None]"
    End If

    John

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Data Sheets (2003 (11.6566.8036) SP2)

    Thanks for the code, Hans. I was getting frustrated by the subdatasheets, after resetting them manually many times.

    I was hoping that the code would "magically" fix an additional annoying behaviour about subdatasheets. Once they are set to [none], and then the table's design is modified, the subdatasheet property reverts to [auto]. Is there a way to permanently set the subdatasheet to [none]? It's not a big deal to run the code again, but I wondered whether I am overlooking a configuration setting somewhere?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Data Sheets (2003 (11.6566.8036) SP2)

    > ... the table's design is modified, the subdatasheet property reverts to [auto].

    I haven't seen this happen - once I set Subdatasheet Name to [none], it stays that way. But I always turn off Track Name AutoCorrect Info too - do you leave that on? (Just grasping at straws)

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Data Sheets (2003 (11.6566.8036) SP2)

    I think you grasped at the right straw!! Turning ON the Track AutoCorrect causes the goofy behaviour, and turning it OFF makes Access work the way that I want it. Thanks.

    PS - I had not intentionally left the AutoTrack ON -- that's just the default, and I hadn't thought to turn it off.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub Data Sheets (2003 (11.6566.8036) SP2)

    Track AutoCorrect Name Info causes a lot of problems, so it's a good idea to turn it off in all databases. I do so in each new database I create.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •