locked
What "Option settings" can be done via code? (Confirm action queries False?) RRS feed

  • Question

  • Hi

    If possible I want some code to run as a part of the AutoExec macro.

    Like:

    - Confirm action queries False
    - Name AutoCorrect Off
    - Show navigation pane False
    - etc

    Some one have some examples?


    Cheers // Peter Forss Stockholm

    Saturday, October 10, 2020 7:30 AM

Answers

  • I would advise you to put any such code in a standard module procedure

    You can then use that whether you choose to use a startup form or an autoexec macro.

    If needed, I can provide some example code later but Allen Browne has something ready made which will do most of what you may need e.g. switch subdatasheets off. See Set Standard Properties.

    It is easy to extend his code for additional properties not covered.

    My example app Control the Application Interface includes code to show/hide/minimise the navigation pane, ribbon & taskbar if you wish to do so. Going one (big) step further, it also demonstrates how to hide the entire application interface


    • Marked as answer by ForssPeterNova Saturday, October 10, 2020 11:10 AM
    Saturday, October 10, 2020 8:26 AM
  • If possible I want some code to run as a part of the AutoExec macro.

    Hi Peter,

    Instead of an AutoExec macro you can also use a form, e.g. Startup_form. In this form you have the complete VBA at your disposal.

    You can define it in (Dutch menuitems):  Extra > Opstarten.

    Imb.

    • Marked as answer by ForssPeterNova Saturday, October 10, 2020 11:10 AM
    Saturday, October 10, 2020 8:05 AM
  • The following is a cut down version of code from one of my apps. Its not the simplest coding approach but it easily allows me to set/clear properties for different types of user

    I use variations on this for most of my commercial apps

    First add these two functions to a standard module:

    Function StartUpProps(strPropName As String, Optional varPropValue As Variant, _
            Optional ddlRequired As Boolean) As Variant

        ' This function requires a reference to DAO library.
        ' This function will both return and set the value of startup properties
        ' in your database. It can also be used for other database properties
        ' with some slight modification.

        Dim db As DAO.Database, prp As DAO.Property, varPropType As Variant
        Const conPropNotFoundError = 3270

        If IsMissing(ddlRequired) Then
            ddlRequired = False
        End If

        ' Because this code is specific to the startup properties, we assume that the
        ' data type of the property is Boolean unless stated otherwise.

        varPropType = dbBoolean
        Select Case strPropName
        Case "frmLogin"
        varPropType = dbText
        End Select
        Set db = CurrentDb

        ' This function will either set the value of the property or try to
        ' return it. It knows which mode it is in by the existence of the
        ' property value in the procedure that called the function.

        If Not IsMissing(varPropValue) Then

            ' As we change the value of the startup property, we will first try to
            ' assign that value. If the property does not exist, it will be
            ' added to the database object by using the following error handling code.
            On Error GoTo AddProps_Err
            db.Properties(strPropName) = varPropValue
            StartUpProps = True
        Else
            ' If we find out the value of the startup property, we first see if
            ' that value exists. If the property does not exist, we will return a null string.
            On Error GoTo NotFound_Err
            StartUpProps = db.Properties(strPropName)
        End If

    Exit_Handler:
        On Error Resume Next
        Set db = Nothing
        Set prp = Nothing
        Exit Function

        'When a property doesn't exist in the database, you must use the CreateProperty method to add the property
        'to the database. The error handling section of the sub-routine handles this method as follows:

    AddProps_Err:
        If Err = conPropNotFoundError Then
            ' Property not found when adding a property value.
            Set prp = db.CreateProperty(strPropName, varPropType, varPropValue, ddlRequired)
            db.Properties.Append prp
            Resume Next
        Else
            ' Unknown error.
            StartUpProps = False
            'create error message & log
            strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
            PopulateErrorLog
            Resume Exit_Handler
        End If

    NotFound_Err:
        If Err = conPropNotFoundError Then
            ' Property not found when returning a property value.
            StartUpProps = Null
            Resume Next
        Else
            MsgBox "Error " & Err.Number & " in StartUpProps procedure : " & Err.description, vbOKOnly + vbCritical
            Resume Exit_Handler
        End If

    End Function

    '======================================================

    Function DeleteStartupProps(strPropName As String) As Boolean
    ' Function requires a reference to DAO library.

    Dim db As DAO.Database, prp As DAO.Property
    Const conPropNotFoundError = 3270

    DeleteStartupProps = False

    On Error GoTo Err_Handler

    CurrentDb.Properties.Delete (strPropName)
    DeleteStartupProps = True

    Exit_Handler:
       On Error Resume Next
       Set db = Nothing
       Set prp = Nothing
       Exit Function

    Err_Handler:
       If Err = conPropNotFoundError Then
          ' Property not found.
          DeleteStartupProps = False
          Resume Next
       Else
          MsgBox "Error " & Err.Number & " in DeleteStartUpProps procedure : " & Err.description, vbOKOnly + vbCritical
          Resume Exit_Handler
       End If

    End Function

                                                       

    In the startup form itself, I use the following procedure to disable features for standard users but enable them for the developer (these could also be enabled for the app administrator):

    Sub ModifyStartUpProps() On Error GoTo Err_Handler 'First clear all existing start up properties (needed if previous user was an admin user) DeleteStartupProps "AllowFullMenus" DeleteStartupProps "StartUpShowStatusBar" DeleteStartupProps "AllowBuiltInToolbars" DeleteStartupProps "AllowShortcutMenus" DeleteStartupProps "AllowToolbarChanges" DeleteStartupProps "AllowSpecialKeys" DeleteStartupProps "StartUpShowDBWindow" DeleteStartupProps "AllowBypassKey" 'By default, set all start up properties to False StartUpProps "AllowBypassKey", False, True StartUpProps "AllowFullMenus", False, True StartUpProps "StartUpShowStatusBar", False, True StartUpProps "AllowBuiltInToolbars", False, True StartUpProps "AllowShortcutMenus", False, True StartUpProps "AllowToolbarChanges", False, True StartUpProps "AllowSpecialKeys", False, True StartUpProps "StartUpShowDBWindow", False, True 'for developer mode with ACCDB file, enable props

    If Environ("ComputerName") = "ISLADOGS52-PC" And CurrentDBFileType = "accdb" Then

    'or could also do this for program administrator

    'If GetAdminStatus=True StartUpProps "AllowBypassKey", True, True StartUpProps "AllowFullMenus", True, True StartUpProps "StartUpShowStatusBar", True, True StartUpProps "AllowBuiltInToolbars", True, True StartUpProps "AllowShortcutMenus", True, True StartUpProps "AllowToolbarChanges", True, True StartUpProps "AllowSpecialKeys", True, True StartUpProps "StartUpShowDBWindow", True, True End If Exit_Handler: Exit Sub Err_Handler: MsgBox "Error " & Err.Number & " in ModifyStartUpProps procedure : " & Err.description, vbOKOnly + vbCritical Resume Exit_Handler End Sub

    In the startup Form_Load event, just use (with error handling added):

    Private Sub Form_Load()
       ModifyStartupProps
    End Sub
    
    

    Obviously there are numerous other properties you can set. For example:

    db.Properties("AppIcon").Value = CurrentProject.Path & "\MyApp.ico" db.Properties("AppTitle").Value = strAppTitle 'string specified elsewhere e,g, in setting table db.Properties("UseAppIconForFrmRpt").Value = True

    In one app, aimed at Access developers only, I also set all tables & queries to show alternate rows in green

    In that app, I use Allen Browne's SetPropertyDAO code (see link in my first reply

    For Each tdf In CurrentDb.TableDefs
                '================================
                Call SetPropertyDAO(tdf, "DatasheetAlternateBackColor", dbLong, RGB(198, 217, 241))
                Call SetPropertyDAO(tdf, "DatasheetBackColor", dbLong, RGB(255, 255, 255))
                Call SetPropertyDAO(tdf, "AlternateBackThemeColorIndex", dbInteger, -1) '6
                Call SetPropertyDAO(tdf, "AlternateBackTint", dbInteger, 100) '60
                Call SetPropertyDAO(tdf, "AlternateBackShade", dbInteger, 100)
                Call SetPropertyDAO(tdf, "DatasheetBackColor12", dbLong, -2147483643)
                
                Call SetPropertyDAO(tdf, "BackThemeColorIndex", dbInteger, -1)
                Call SetPropertyDAO(tdf, "BackTint", dbInteger, 100)
                Call SetPropertyDAO(tdf, "BackShade", dbInteger, 100)
                Call SetPropertyDAO(tdf, "ThemeFontIndex", dbInteger, -1)
                Call SetPropertyDAO(tdf, "DatasheetGridlinesThemeColorIndex", dbInteger, -1)
                Call SetPropertyDAO(tdf, "DatasheetForeThemeColorIndex", dbInteger, -1)
                
                'get the field values for GUID & NameMap from the system table
               ' strName = tdf.Name
               ' strSelection = "{guid " & Nz(DLookup("GUID", "qryTablesList", "ObjectName = '" & strName & "'"), "") & "}"
              '  Call SetPropertyDAO(tdf, "GUID", dbGUID, strSelection)
                
              ' strSelection = Nz(DLookup("NameMap", "qryTablesList", "ObjectName = '" & strName & "'"), "")
              '  Call SetPropertyDAO(tdf, "NameMap", dbLongBinary, strSelection)
    
    Next tdf

    ...etc, etc. Hope that helps/makes sense



    • Marked as answer by ForssPeterNova Saturday, October 10, 2020 2:28 PM
    Saturday, October 10, 2020 1:28 PM

All replies

  • If possible I want some code to run as a part of the AutoExec macro.

    Hi Peter,

    Instead of an AutoExec macro you can also use a form, e.g. Startup_form. In this form you have the complete VBA at your disposal.

    You can define it in (Dutch menuitems):  Extra > Opstarten.

    Imb.

    • Marked as answer by ForssPeterNova Saturday, October 10, 2020 11:10 AM
    Saturday, October 10, 2020 8:05 AM
  • I would advise you to put any such code in a standard module procedure

    You can then use that whether you choose to use a startup form or an autoexec macro.

    If needed, I can provide some example code later but Allen Browne has something ready made which will do most of what you may need e.g. switch subdatasheets off. See Set Standard Properties.

    It is easy to extend his code for additional properties not covered.

    My example app Control the Application Interface includes code to show/hide/minimise the navigation pane, ribbon & taskbar if you wish to do so. Going one (big) step further, it also demonstrates how to hide the entire application interface


    • Marked as answer by ForssPeterNova Saturday, October 10, 2020 11:10 AM
    Saturday, October 10, 2020 8:26 AM
  • Hi

    Thank both, again.

    I am happy to get samples.

    What I'm aiming for is a module that make the "production app" to run as fast as possible.

    Like:

    <tfoot></tfoot>
    Access Option [Track Name AutoCorrect Info] should be turned off.

    I already have:

        Application.SetOption "Confirm Action Queries", False
        Application.SetOption "Show Status Bar", False

    In the BE all Subdatasheets is off


    Cheers // Peter Forss Stockholm

    Saturday, October 10, 2020 11:22 AM
  • The following is a cut down version of code from one of my apps. Its not the simplest coding approach but it easily allows me to set/clear properties for different types of user

    I use variations on this for most of my commercial apps

    First add these two functions to a standard module:

    Function StartUpProps(strPropName As String, Optional varPropValue As Variant, _
            Optional ddlRequired As Boolean) As Variant

        ' This function requires a reference to DAO library.
        ' This function will both return and set the value of startup properties
        ' in your database. It can also be used for other database properties
        ' with some slight modification.

        Dim db As DAO.Database, prp As DAO.Property, varPropType As Variant
        Const conPropNotFoundError = 3270

        If IsMissing(ddlRequired) Then
            ddlRequired = False
        End If

        ' Because this code is specific to the startup properties, we assume that the
        ' data type of the property is Boolean unless stated otherwise.

        varPropType = dbBoolean
        Select Case strPropName
        Case "frmLogin"
        varPropType = dbText
        End Select
        Set db = CurrentDb

        ' This function will either set the value of the property or try to
        ' return it. It knows which mode it is in by the existence of the
        ' property value in the procedure that called the function.

        If Not IsMissing(varPropValue) Then

            ' As we change the value of the startup property, we will first try to
            ' assign that value. If the property does not exist, it will be
            ' added to the database object by using the following error handling code.
            On Error GoTo AddProps_Err
            db.Properties(strPropName) = varPropValue
            StartUpProps = True
        Else
            ' If we find out the value of the startup property, we first see if
            ' that value exists. If the property does not exist, we will return a null string.
            On Error GoTo NotFound_Err
            StartUpProps = db.Properties(strPropName)
        End If

    Exit_Handler:
        On Error Resume Next
        Set db = Nothing
        Set prp = Nothing
        Exit Function

        'When a property doesn't exist in the database, you must use the CreateProperty method to add the property
        'to the database. The error handling section of the sub-routine handles this method as follows:

    AddProps_Err:
        If Err = conPropNotFoundError Then
            ' Property not found when adding a property value.
            Set prp = db.CreateProperty(strPropName, varPropType, varPropValue, ddlRequired)
            db.Properties.Append prp
            Resume Next
        Else
            ' Unknown error.
            StartUpProps = False
            'create error message & log
            strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
            PopulateErrorLog
            Resume Exit_Handler
        End If

    NotFound_Err:
        If Err = conPropNotFoundError Then
            ' Property not found when returning a property value.
            StartUpProps = Null
            Resume Next
        Else
            MsgBox "Error " & Err.Number & " in StartUpProps procedure : " & Err.description, vbOKOnly + vbCritical
            Resume Exit_Handler
        End If

    End Function

    '======================================================

    Function DeleteStartupProps(strPropName As String) As Boolean
    ' Function requires a reference to DAO library.

    Dim db As DAO.Database, prp As DAO.Property
    Const conPropNotFoundError = 3270

    DeleteStartupProps = False

    On Error GoTo Err_Handler

    CurrentDb.Properties.Delete (strPropName)
    DeleteStartupProps = True

    Exit_Handler:
       On Error Resume Next
       Set db = Nothing
       Set prp = Nothing
       Exit Function

    Err_Handler:
       If Err = conPropNotFoundError Then
          ' Property not found.
          DeleteStartupProps = False
          Resume Next
       Else
          MsgBox "Error " & Err.Number & " in DeleteStartUpProps procedure : " & Err.description, vbOKOnly + vbCritical
          Resume Exit_Handler
       End If

    End Function

                                                       

    In the startup form itself, I use the following procedure to disable features for standard users but enable them for the developer (these could also be enabled for the app administrator):

    Sub ModifyStartUpProps() On Error GoTo Err_Handler 'First clear all existing start up properties (needed if previous user was an admin user) DeleteStartupProps "AllowFullMenus" DeleteStartupProps "StartUpShowStatusBar" DeleteStartupProps "AllowBuiltInToolbars" DeleteStartupProps "AllowShortcutMenus" DeleteStartupProps "AllowToolbarChanges" DeleteStartupProps "AllowSpecialKeys" DeleteStartupProps "StartUpShowDBWindow" DeleteStartupProps "AllowBypassKey" 'By default, set all start up properties to False StartUpProps "AllowBypassKey", False, True StartUpProps "AllowFullMenus", False, True StartUpProps "StartUpShowStatusBar", False, True StartUpProps "AllowBuiltInToolbars", False, True StartUpProps "AllowShortcutMenus", False, True StartUpProps "AllowToolbarChanges", False, True StartUpProps "AllowSpecialKeys", False, True StartUpProps "StartUpShowDBWindow", False, True 'for developer mode with ACCDB file, enable props

    If Environ("ComputerName") = "ISLADOGS52-PC" And CurrentDBFileType = "accdb" Then

    'or could also do this for program administrator

    'If GetAdminStatus=True StartUpProps "AllowBypassKey", True, True StartUpProps "AllowFullMenus", True, True StartUpProps "StartUpShowStatusBar", True, True StartUpProps "AllowBuiltInToolbars", True, True StartUpProps "AllowShortcutMenus", True, True StartUpProps "AllowToolbarChanges", True, True StartUpProps "AllowSpecialKeys", True, True StartUpProps "StartUpShowDBWindow", True, True End If Exit_Handler: Exit Sub Err_Handler: MsgBox "Error " & Err.Number & " in ModifyStartUpProps procedure : " & Err.description, vbOKOnly + vbCritical Resume Exit_Handler End Sub

    In the startup Form_Load event, just use (with error handling added):

    Private Sub Form_Load()
       ModifyStartupProps
    End Sub
    
    

    Obviously there are numerous other properties you can set. For example:

    db.Properties("AppIcon").Value = CurrentProject.Path & "\MyApp.ico" db.Properties("AppTitle").Value = strAppTitle 'string specified elsewhere e,g, in setting table db.Properties("UseAppIconForFrmRpt").Value = True

    In one app, aimed at Access developers only, I also set all tables & queries to show alternate rows in green

    In that app, I use Allen Browne's SetPropertyDAO code (see link in my first reply

    For Each tdf In CurrentDb.TableDefs
                '================================
                Call SetPropertyDAO(tdf, "DatasheetAlternateBackColor", dbLong, RGB(198, 217, 241))
                Call SetPropertyDAO(tdf, "DatasheetBackColor", dbLong, RGB(255, 255, 255))
                Call SetPropertyDAO(tdf, "AlternateBackThemeColorIndex", dbInteger, -1) '6
                Call SetPropertyDAO(tdf, "AlternateBackTint", dbInteger, 100) '60
                Call SetPropertyDAO(tdf, "AlternateBackShade", dbInteger, 100)
                Call SetPropertyDAO(tdf, "DatasheetBackColor12", dbLong, -2147483643)
                
                Call SetPropertyDAO(tdf, "BackThemeColorIndex", dbInteger, -1)
                Call SetPropertyDAO(tdf, "BackTint", dbInteger, 100)
                Call SetPropertyDAO(tdf, "BackShade", dbInteger, 100)
                Call SetPropertyDAO(tdf, "ThemeFontIndex", dbInteger, -1)
                Call SetPropertyDAO(tdf, "DatasheetGridlinesThemeColorIndex", dbInteger, -1)
                Call SetPropertyDAO(tdf, "DatasheetForeThemeColorIndex", dbInteger, -1)
                
                'get the field values for GUID & NameMap from the system table
               ' strName = tdf.Name
               ' strSelection = "{guid " & Nz(DLookup("GUID", "qryTablesList", "ObjectName = '" & strName & "'"), "") & "}"
              '  Call SetPropertyDAO(tdf, "GUID", dbGUID, strSelection)
                
              ' strSelection = Nz(DLookup("NameMap", "qryTablesList", "ObjectName = '" & strName & "'"), "")
              '  Call SetPropertyDAO(tdf, "NameMap", dbLongBinary, strSelection)
    
    Next tdf

    ...etc, etc. Hope that helps/makes sense



    • Marked as answer by ForssPeterNova Saturday, October 10, 2020 2:28 PM
    Saturday, October 10, 2020 1:28 PM
  • BTW - if you have both a startup form and an autoexec macro, perhaps counterintuitively, the startup form loads before the macro runs
    Saturday, October 10, 2020 5:37 PM
  • What I'm aiming for is a module that make the "production app" to run as fast as possible.

    Hi Peter,

    Re-thinking about this thread, i was wondering why you need such a tool, since I have not (yet?) had the need for it.

    Perhaps it is the way a new application is started. I go to my Application-database, and add a new record to the DB_tbl, with the new name of the application in the corresponding field. After (checks and) save a template FE is copied to the new FE with the new name, and in the development-BE-map the same is done for the new BE, and the tables are linked.

    On this moment a completely working database is ready, and working, with about 98% of its future functionality. All settings are already done. The FE contains only (empty) metadata-tables, the BE a few standard "setting" tables. It lacks on this moment only the user tables.

    The users never (need to) go in development mode, nor do any changes to the data definition. When they want something added or changed (fields, tables, "forms", or some other functionality), they get a new updated FE with all the changes. This new FE contains the recipe how to modify the production-BE, if necessary.

    So, until now, no need to change the options, or do I overlook something?

    Imb.

    Saturday, October 10, 2020 6:23 PM
  • Hi Imb

    This is how I think about it.

    When you create a new database, Access gives you, as the developer, a lot of built in support. Full menues, a Status bar, "Use special keys" = F11 display the navigation pane, Perform name AutoCorrect ... and more.
    They are all wanted as long as I develop the application.
    But when the app is ready for the users, you may want to turn off some of that bulit in support. For safty and for performance reasons.
    You can do it manually in File and Options.


    Cheers // Peter Forss Stockholm

    Sunday, October 11, 2020 6:59 AM
  • Hi Peter

    That's basically my approach as well plus the need to allow different conditions e.g. for program admins compared to standard users or for ACCDE compared to ACCDB.

    That's why my ModifyStartUpProps routine above includes a section like this:

     'By default, set all start up properties to False
        StartUpProps "AllowBypassKey", False, True
        StartUpProps "AllowFullMenus", False, True
        StartUpProps "StartUpShowStatusBar", False, True
        StartUpProps "AllowBuiltInToolbars", False, True
        StartUpProps "AllowShortcutMenus", False, True
        StartUpProps "AllowToolbarChanges", False, True
        StartUpProps "AllowSpecialKeys", False, True
        StartUpProps "StartUpShowDBWindow", False, True
        
        'for developer mode with ACCDB file, enable props
        If Environ("ComputerName") = "ISLADOGS52-PC" And CurrentDBFileType = "accdb" Then
    
        'or could also do this for program administrator 
        'If GetAdminStatus=True
                StartUpProps "AllowBypassKey", True, True
                StartUpProps "AllowFullMenus", True, True
                StartUpProps "StartUpShowStatusBar", True, True
                StartUpProps "AllowBuiltInToolbars", True, True
                StartUpProps "AllowShortcutMenus", True, True
                StartUpProps "AllowToolbarChanges", True, True
                StartUpProps "AllowSpecialKeys", True, True
                StartUpProps "StartUpShowDBWindow", True, True
        End If


    • Edited by isladogs52 Sunday, October 11, 2020 11:31 AM
    Sunday, October 11, 2020 11:30 AM