Intermediate

Set the Access Application Title Bar to the Full Path of the File at Startup

Today's tip helps you keep track of which Access file is which when you have several similar Access files open at the same time.

Mike Wolfe

Jan 26, 2024 • 5 min read

The topic for today's post comes from reader Greg Taillon (shared with permission):

Often I copy objects between a development and an operational front end. It would be nice to have the filename of the database you are editing appear in the top banner of the Access window. Any work arounds to do this?

Great question, Greg! This is two (or maybe three) questions in one:

How do you set the title bar text?
How do you find the file name of the current database?
How do you set the title bar text to the current database file automatically at startup?

Setting the Title Bar Text

There are several ways to set the title bar text:

Manually
Via the "AppTitle" property in VBA
Using the "TitleBar" property my clsApp class module

I cover all three of these methods in greater detail in my article, "3 Ways to Get and Set the TitleBar Text for an MS Access Application."

For reference, I'll recap each option briefly below.

Manually via the Options Dialog

File > Options > Current Database > Application Title

Via VBA

To set a custom Application Title in code, you can assign a new value to the current database's "AppTitle" property directly. You will also need to call the RefreshTitleBar method of the Access.Application object to force the change to take effect:

CurrentDB.Properties("AppTitle") = "My App - Dev"
RefreshTitleBar

However, the top line will fail if the "AppTitle" has not been set before either via code or the Options dialog. In that case, you will need to create the property.

CurrentDB.Properties.Append _
    CurrentDB.CreateProperty("AppTitle", dbText, "My App - Dev")

Unfortunately, you can't just blindly create the property, because if it already exists then that will raise an error. Which brings us to our third option...

Via App.TitleBar

I wrote a class module that I include in every application named clsApp. Among other features, it includes a TitleBar property, which allows you to safely read and write the contents of the application title, whether it's already been set or not.

Dim App As New clsApp

App.Title = "My App - Dev"

Finding the FileName of the Current Database

This one is easy. The info can be found in these two properties:

CurrentProject.FullName: contains the full path to the database
CurrentProject.Name: contains the file name only

?CurrentProject.FullName
C:\Path\To\My App - Dev.accdb

?CurrentProject.Name
My App - Dev.accdb

Automatically Setting the Title Bar at Startup

I'm imagining a scenario where the same file is being copied back and forth among different folders or backups are renamed with the current date as part of a poor man's version control. In that case, I'd strongly recommend considering proper version control of your Access applications, but I don't need to tilt at those windmills today.

Instead, let's provide you with the tools to make it easy to identify the full path to the currently opened database file as soon as it starts up, without requiring any manual intervention at all.

To do this, we will use the special AutoExec macro.

I loathe macros in Microsoft Access. However, there are two special macros that are acceptable to use because they provide unique functionality:

There is no special setting to enable the functionality of these macros. The name of the macro itself is what provides the functionality.

So, while I approve of using an AutoExec macro in Access, the macro itself should only do one of two things:

Open a hidden form (with Form_Load / Form_Open code) OR
Run a Startup function

We'll use the second option for this demonstration.

Create the AutoExec Macro

The following macro will run a function named Startup whenever the database is opened (unless you hold down the [Shift] key to bypass all startup code):

Create > Macro
Add New Action > Run Code
Function Name: =Startup()
[Ctrl] + [S] to save, Macro Name: AutoExec > [OK]
Close the Macro window

Create the Startup Function

Next, we need to create a public function named Startup in a standard module in VBA. Note that it must be a Function and not a Sub even though it won't return any values.

Public Function Startup()
    On Error Resume Next
    'Attempt to create the AppTitle property in case it has never been set
    CurrentDb.Properties.Append _
        CurrentDb.CreateProperty("AppTitle", dbText, "")    
    On Error Goto 0
    
    CurrentDb.Properties("AppTitle") = CurrentProject.FullName
    RefreshTitleBar
End Function

In my own projects, I always include a public instance of my clsApp module named App, so I would write the above code like this instead:

Public Function Startup()
    Const DevComputerName As String = "MyDevComputer"
    
    If App.ComputerName = DevComputerName Then
        App.TitleBar = CurrentProject.FullName
    Else
        App.TitleBar = App.PgmName
    End If
End Function

This version is more readable. It also has a condition where it only sets the title bar to the full path of the file if the code is running on the developer's computer. This provides you the benefit of seeing the full path in the title bar during development, but showing the end users a friendlier title bar.

Incidentally, App.PgmName defaults to the current file name minus the file extension, but it can be manually set to anything you want. The name is stored in a custom database property so it survives closing and reopening the database.

Of course, the second version would require you to import a copy of my clsApp class module and also define a public App object like so, Public App As New clsApp.

I think the enhanced readability is worth it, but I'll leave that up to you to decide.