Link to home
Start Free TrialLog in
Avatar of beef137
beef137

asked on

Subdatasheet Name keeps going back to [Auto] in table properties

I have a linked SQL table in my Access application and I am changing the subdatasheet name in the table's properties. If I keep the table open and go to datasheet view I can see that the properties are working correctly. But as soon as I close the table the subdatasheet name I specified is gone and has been replaced with "[Auto]". How can I properly save the subdatasheet name in my table's properties?
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Run this code.  You actually have to set the property for new tables, else the keep going back to Auto.

Function SSF_TurnOffSubDataSheets()

    Dim db As DAO.Database
    Dim prp As DAO.Property
    Dim prpName As String, prpVal As String
    Dim prpType As Integer
    Dim iCnt As Integer, iCntPrpChg As Integer, iCntPrpNoChg As Integer, iCntTotTbl As Integer, iCntPrpCreated As Integer
    Dim fPropCreated As Boolean
   
    Set db = CurrentDb
   
    prpName = "SubDataSheetName"
    prpType = 10
    prpVal = "[NONE]"
    iCntPrpChg = 0
    iCntPrpNoChg = 0
    iCntPrpCreated = 0
    iCntTotTbl = 0
    fPropCreated = False
   
   '------
    On Error GoTo SSF_TurnOffSubDataSheets_Err
    With db
        For iCnt = 0 To .TableDefs.Count - 1
            If (.TableDefs(iCnt).Attributes And dbSystemObject) = 0 Then
                fPropCreated = False
                Call SysCmd(acSysCmdSetStatus, "Updating " & .TableDefs(iCnt).Name & " ...")
                iCntTotTbl = iCntTotTbl + 1
                If .TableDefs(iCnt).Properties(prpName).Value <> prpVal Then
                    .TableDefs(iCnt).Properties(prpName).Value = prpVal
                    iCntPrpChg = iCntPrpChg + 1
                Else
                    If fPropCreated = False Then iCntPrpNoChg = iCntPrpNoChg + 1
                End If
            End If
        Next iCnt
    End With
    MsgBox "The " & prpName & "property for all non-system tables has been updated to " & prpVal & "." & Chr(13) & Chr(13) & _
           Space(3 - Len(CStr(iCntPrpCreated))) + CStr(iCntPrpCreated) & "  New Property(s) Created and Set" & Chr(13) & _
           Space(3 - Len(CStr(iCntPrpNoChg))) + CStr(iCntPrpNoChg) & "  Tables Already Set" & Chr(13) & _
           Space(3 - Len(CStr(iCntPrpChg))) + CStr(iCntPrpChg) & "  Tables Changed" & Chr(13) & _
           Space(3 - Len(CStr(iCntTotTbl))) + CStr(iCntTotTbl) & "  Total Non-System Tables", 64, "Table Sub Datasheet Property"

SSF_TurnOffSubDataSheets_Exit:
      Err.Clear
      On Error GoTo 0
      Set db = Nothing
      Set prp = Nothing
      Call SysCmd(acSysCmdClearStatus)
      Exit Function
     
SSF_TurnOffSubDataSheets_Err:
     'Property does not exist ...
      If Err.Number = 3270 Then
         Set prp = db.TableDefs(iCnt).CreateProperty(prpName)
         prp.Type = prpType
         prp.Value = prpVal
         db.TableDefs(iCnt).Properties.Append prp
         iCntPrpCreated = iCntPrpCreated + 1
         fPropCreated = True
         Resume
      Else
         If Err.Number <> 0 Then
             MsgBox "Error: " & Err.Number & " on Table " & db.TableDefs(iCnt).Name & "."
             GoTo SSF_TurnOffSubDataSheets_Exit
         End If
      End If

End Function

-------------------
You can put the code in a module, call the code SSF_TurnOffSubDataSheets() from the vba immediate window ... the delete the code.
Sorry ... I missed 'linked SQL table'

mx
Avatar of beef137
beef137

ASKER

Will this code not work with a linked table?
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial