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 & "'")
MsgBox "Error in UTV():" & Err.Number & ":" & Err.Description
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"
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
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.