Using a field value in a public error handling code

[Fred Boer]

Hello!

I am trying to set up a table of application settings which would allow the
user to set certain values. At the moment I am working on setting up the
application name. I know that the user can set an application name using the
standard Access Options, but in addition, I want the user's choice of an
application name to be reflected in my generic error handling. So... I
thought about creating a table "tblSettings"; (SettingsID, SettingText,
SettingDescription), ("1", "My Little Library","ApplicationName"). I use a
generic error handling process in all subs. When the errorhandling code
runs, I want to be able to pull the value of the application name from the
table, and insert it into the messagebox that pops up in response to the
error. I'm not sure of the best way to "pull the value from the table",
however. I've done something that appears to work, but I wonder: is this the
best way to do it, using a recordset? Is there a better approach?

(Note: I am aware that this code will pull information from the first record
in the table.)

Public Function fncErrorMessage(errNumber As Long, errDescription As String)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblSettings")

Dim strAppName As String
strAppName = rs("SettingText")

Dim Msg As String

Msg = "An error has occurred." & vbCr & vbCr & "Error Type: " &
errDescription & vbCr & vbCr & "Error Number: " & errNumber & vbCr & vbCr &
"Please contact the Database Administrator immediately."
msgbox Msg, vbExclamation, strAppName ' *** Here is where I want to
put the application name....

End Function

 

[Fred Boer]

Continuing...

Of course, just after I posted this, and having spent a significant amount
of free time on this, I started thinking... (why wait 'til then to start
thinking, you might ask! <g>)

I know about GetOption/SetOption. Flailing around in the help system, I
stumbled across a reference to the "Application Icon", which led me to
"RefreshTitleBar", which had an example of updating the title bar, which
contained the following:

strAppName = db.Properties!AppTitle

AHA! Well, that works just fine for what I want. But, (if you have had the
strength to stay with me to this point!), I have two questions:

1. Is *this* the better way?
2. Where the *&$! in the help system (Access 2003), can I get a list of the
database properties that lists, for example, "AppTitle"?!

Happily puttering...
Fred

 

[david epsom dot com dot au]

2. Where can I get a list of the database properties

In the A97 help, searching for AppTitle leads me to the "Properties
Reference". In A2000, using the help system just makes me frustrated.

So, in the debug window:

for i=0 to codedb.properties.count-1: ?codedb.properties(i).name: next i

This uses the default container (databases) and document (MSysDB). The
complete address would be

CodeDB.Containers("databases").Documents("MSysDb").properties.

The startup properties do not exist if they are not in use. There is no
Apptitle property unless you have defined an AppTitle

1. Is *this* the better way?

This is a way. For AppTitle it is a good way. For custom properties, I would
use

CodeDB.Containers("databases").Documents("UserDefined").properties

because you can look at those by going to
File, Database Properties, Custom

Both properties and records get stored in the database: I don't think that
matters much. I think it is more a matter of coding practice. I expect to
find data in a table, so that is always where I first think of putting it.
When I want to show the values at runtime, I just put up a form, bound to
the table. So there are very few cases where the convenience of using custom
database properties is greater than the convenience of using a table. And I
hate to do any kind of lookup in the middle of an error handler, so I always
use Module Level variables there, initialised at startup.

Of course, you can get the app name on the message box automatically after
you set the AppTitle, like this:

eval("msgbox(" & Msg & ", vbExclamation)")

Using Eval forces VBA to use the Access MsgBox (a feature partially broken
in A2K+), instead of the VBA MsgBox. Still, hardly worth it unless you are
already using the other features of the Access MsgBox (bold sections of the
msgbox text).

(david)

 

[Fred Boer]

Dear David:

Thank you for your help! With the help you provided, I was able to get a
list of the properties. I doubt if I will be ever be creating custom
properties, but thanks for the additional information. I had no idea that
there was an Access Messagebox as opposed to the VBA Messagebox, so I think
I'll just let that lie as well!

You say:

"And I hate to do any kind of lookup in the middle of an error handler, so I
always use Module Level variables there, initialised at startup."

I think I can understand why it would be bad practice to try to do a lookup
while Access is in the process of dealing with an error, but, if you don't
mind, could you expand on the "module level variables" and how you
initialize them at startup? Do you mean that I could create a public
variable (for example, in code behind my splash form), which I could then
refer to in the error handling code?

Thanks!
Fred