Changing and Resetting the Caption Bar in Access 2007

  • Article

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn a simple way to retrieve, modify, and reset the text that is in the Microsoft Office Access 2007 title bar. (7 printed pages)

Office Visual How To

**Applies to:**2007 Microsoft Office system, Microsoft Office Access 2007

Adapted from Access Cookbook, 2nd Edition by Ken Getz, Paul Litwin, and Andy Baron. Copyright © 2004, O'Reilly Media, Inc. All rights reserved. Used with permission.

Ken Getz, MCW Technologies, LLC

June 2009

Overview

If you want to change the text in the title bar of the main Microsoft Office Access 2007 window and then reset it to its original value when you are done, you can do so by using the AppTitle property along with a bit of VBA code.

NoteNote
This article and its accompanying video sometimes use the term “caption bar” to refer to the Access 2007 title bar, and “caption” to refer to the text in that title bar.
See It Changing and Resetting the Caption Bar in Access

Watch the Video

Length: 8:59 | Size: 9.12 MB | Type: WMV file

Code It | Explore It

Code It

Download the sample database

Access 2007 databases have several built-in properties that define the behavior of the database. To learn more about those properties in a given database, you can use the Properties property. For example, open the sample database, ChangeAccessCaption.accdb, and load the module basProperties in the VBA editor. Click in the ListProperties procedure, which contains the following code.

Sub ListProperties()
    On Error Resume Next
    Dim prp As DAO.Property
    For Each prp In CurrentDb.Properties
        Debug.Print prp.Name,
        Debug.Print prp.Value,
        Debug.Print
    Next prp
End Sub

If you click in the procedure and press F5 to run it, it generates output to the Immediate window that lists all of the properties of the current database.

Figure 1. ListProperties outputs a list of properties in the database

Code outputs a list of properties in the database

You can copy this code into any database and then run the code to review the properties for that database. The code loops through the complete set of database properties and displays a name and value for each property.

Note

Some database properties have values that cannot be printed this easily. Those values generate an error when the code attempts to print them. In those cases, the code disregards the error and prints the property name without printing the property value. In other words, you end up with a complete list of property names, but not necessarily a complete list of property values.

To set the text in the title bar of the main Access window, you must set the AppTitle property for the database. However, Access 2007 does not include the AppTitle property by default when you create a new database, so you must first create the property.

That work has already been done in the sample form, frmSetAccessCaption, which is included in the sample database, ChangeAccessCaption.accdb. The form displays the current Access caption; that is, the text in the title bar of the main Access window. To change that text, type a new value in the New Access Caption text box and then click the Set New Caption button. To revert to the original text in the title bar, click the Reset Caption button when you are done.

Figure 2. Clicking Set New Caption modifies the main Access title bar

Set New Caption modifies the main Access title bar

To include similar functionality in your applications, follow these steps:

  1. Import the module basCaption from the sample database, ChangeAccessCaption.accdb; that module supplies the necessary procedures.

  2. To retrieve the current Access caption, call the GetTitleBar function that is in basCaption. If GetTitleBar is unable to retrieve the AppTitle property of the database, then it assumes that the current caption is "Microsoft Access". Call the function by using code that is similar to the following example.

    Dim caption As String
caption = GetTitleBar()

  3. To set a new Access caption, call the SetTitleBar method and pass it a string that holds your new caption, similar to the following code. (You cannot set the title to be empty; if you pass an empty string you will receive an error message.)

    SetTitleBar "New Title"

If you examine the contents of the basCaption module, you find two public procedures, SetTitleBar and GetTitleBar, and two private procedures, SetDBProperty and CreateDBProperty. The SetTitleBar procedure calls SetDBProperty, passing in the property name, type, and value, and it lets SetDBProperty do the work (you can use SetDBProperty to set any database property). It then calls the Application.RefreshTitleBar method to refresh the Access title bar.

Public Sub SetTitleBar(varTitle As Variant)
    
    SetDBProperty "AppTitle", dbText, varTitle & ""
    
    ' If you want the titlebar to change right now,
    ' refresh it.
    Application.RefreshTitleBar
End Sub

The GetTitleBar method looks similar to the following code.

Public Function GetTitleBar() As String
    Dim strTitle As String
    Dim db As DAO.Database
    
    Set db = CurrentDb()

    ' Retrieve the current databases's AppTitle property.
    On Error Resume Next
    strTitle = db.Properties("AppTitle")
    
    ' Doesn't exist? Return standard title:
    If Err.Number <> 0 Then
        GetTitleBar = "Microsoft Access"
    Else
        GetTitleBar = strTitle
    End If
End Function

This procedure turns off error handling, and then attempts to retrieve the AppTitle property of the current database. If the property does not exist, the attempt to retrieve it returns an error. The code checks for the error, and either returns the value of the AppTitle property or the standard title, "Microsoft Access", if could not retrieve the AppTitle property.

The tricky code occurs in the two private procedures. For example, the SetDBProperty procedure contains the following code.

Private Sub CreateDBProperty( _
  strProperty As String, _
  intType As DataTypeEnum, _
  varSetting As Variant)
  
    On Error GoTo HandleErr
  
    Dim db As Database
    Set db = CurrentDb()
    
    ' Create property, denote type, and set initial value.
    Dim prp As DAO.Property
    Set prp = db.CreateProperty( _
     strProperty, intType, varSetting)
    
    ' Append Property object to Properties collection.
    db.Properties.Append prp
    db.Properties.Refresh
    
ExitHere:
    Exit Sub
        
HandleErr:
    MsgBox Err.Number & ": " & vbCrLf & Err.Description
    Resume ExitHere
End Sub

To do its job, the procedure attempts to set the value of a database property.

db.Properties(strProperty) = varSetting
db.Properties.Refresh

If the attempt succeeds, the procedure returns True. If the attempt fails, the code jumps to the procedure's error handler.

If Err.Number = 3270 Then
    Call CreateDBProperty(strProperty, intType, varSetting)
    SetDBProperty = True
Else
    MsgBox Err.Number & ": " & vbCrLf & Err.Description
    SetDBProperty = False
End If

If the error number indicates that a particular property was not found, the code calls the CreateDBProperty method, passing in the property name, type, and value. If a different error occurred, the procedure fails.

The CreateDBProperty method contains the following code.

Private Sub CreateDBProperty( _
  strProperty As String, _
  intType As DataTypeEnum, _
  varSetting As Variant)
  
    On Error GoTo HandleErr
  
    Dim db As Database
    Set db = CurrentDb()
    
    ' Create property, denote type, and set initial value.
    Dim prp As DAO.Property
    Set prp = db.CreateProperty( _
     strProperty, intType, varSetting)
    
    ' Append Property object to Properties collection.
    db.Properties.Append prp
    db.Properties.Refresh
    
ExitHere:
    Exit Sub
        
HandleErr:
    MsgBox Err.Number & ": " & vbCrLf & Err.Description
    Resume ExitHere
End Sub

Basically, the procedure calls the CreateProperty method of the database, specifying the property name, type, and value. It then appends the new property to the Properties collection, and refreshes the collection.

' Create property, denote type, and set initial value.
Dim prp As DAO.Property
Set prp = db.CreateProperty( _
 strProperty, intType, varSetting)

' Append Property object to Properties collection.
db.Properties.Append prp
db.Properties.Refresh

Although it takes a bit of effort to create the necessary AppTitle property in a new database, once you have the basCaption module in your application, it is simple to let the code do the work for you.

If you want to call the SetDBProperty and CreateDBProperty methods yourself, note that each procedure requires you to supply a property type. When you specify that parameter, use one of the DAO.DataTypeEnum values, such as dbText, dbDate, dbDecimal, and so on. Use the Object Browser in the VBA editor to help you find the appropriate value.

Explore It