Now, Where Did THAT Come From?

I recently started thinking about setting application properties a little differently than I had been. Well, actually, it's been a while since I really gave it much thought at all, to be honest. A long time ago, I created a module with default constants like "MBTitle" and "AppTitle", and started importing it into new accdbs or mdbs. Then I'd edit the values for the new database. It works, but it never really made me completely happy. Something about hard-coding values like the support phone number seemed less than appropriate. But I couldn't really think of a better approach, and just stopped thinking about it.

Well, for some reason, it recently occurred to me that it might be better to make this a table-driven function. Actually, I'd thought of that once before, but my implementation wasn't all that creative at the time and I had let it slide again.

This time, though, it dawned on me that I could create a Hidden User System Table for my application parameters and retrieve them as needed with a simple little function. If I distributed the app as an mde it would be easier to update values, such as Application Version, when necessary, with having to redo a module. There is still no guarantee a knowledgeable user won't figure out how to get to my hidden table, of course. But if you’re going to work with Access-built applications, you have to learn to live with that.

So, here’s a screen shot of my USysValueList table with some typical properties and values.

Here's the function that retrieves values from the Hidden User System Table:

Public Function UTV(strField As String) As String
'Retrieves values for various database properties from a User System Table
On Error GoTo errHandler
UTV = DLookup("UsysValue", "USysValueList", "USysValueList.UsysField ='" & strField & "'")
ExitProc:
Exit Function
errHandler:
MsgBox "Error in UTV():" & Err.Number & ":" & Err.Description
Resume ExitProc
End Function

Here's an example of a VBA procedure that would use properties from the table to populate controls on the Main Menu of the application when it opens:

Private Sub Form_Open()
...
Me.Caption = UTV("AppTitle") & " - [Main Menu]"
Me![lblTitle] = UTV("AppTitle")
Me![lblVersion].Caption = UTV("AppVersion") & " D" 'D for "demo"
.....
End Sub

The next step is to create a new function so that I can start saving user preferences into this table. I'm thinking of things like storing preferred date formats ("mm/dd/yyyy" or "dd/mm/yyyy"), for example.

Here's a query that displays a list of dates in two formats, using a stored date format preference value:

SELECT tblWork.WorkDate, Format([workdate],utv("DateFormat")) AS YYYYMMDDFormatted
FROM tblWork;

To support it, we added a new row to USysValueList:




I'm starting now to develop a start up form in which we'll be able to save one or more user specified values for properties that the database will then call up as needed to personalize the database.

Fortune Cookie Blues

Family, Good Friends, Old Friends, New Friends . The cookie knows. All you have to do is find the right cookie. I think maybe we've got it this time. Thanks, YoYo. It was a very good Thanksgiving, all in all.