I've seen a lot of people here describe the use of a hidden form with information stored on it that can be accessed by other forms. While it's functional, it's clumsy and referencing it from various other areas can become a syntactical test for the best of us.
In that light, I'm offering what I think is a better solution.
Create a table called t_SystemSettings (or something similar). In this table, you are going to store whatever data elements you will need throughout the life of the DB. This can be a user ID, start date, stop date, version number, or whatever else you need to carry. This is an especially useful method for passing values to other forms or reports. There's only going to be one record in here, so make your field names specific to their function.
To get information from this table, make a simple function like this:
Now, depending on what you've stored there, you can easily identify this code in your program because it will be obvious things like GetSettings("UserID") or GetSettings("Start_Date"). In the long run, this makes maintenance very easy, and it's much cleaner than something like UserID = Forms!Form("your_hidden_form_name").FieldName.
To write to the table, make another one line subroutine like this:
Note that using CurrentDb.Execute automatically disables and then reenables the warning messages (I.e., you won't get the "You are about to update 1 row in t_SystemSettings. Are you sure?"). It's the equivalent of SetWarnings Off, run query, SetWarnings On.
Now, to write a setting, just code it like SetSettings("UserID", environ("username")).
I believe you'll find that a method like this is cleaner and simpler to use than the hidden form method. I am, of course, open to suggestions or revisions to this method, so feel free to tear it apart or utilize it or whatever else you'd like to do.
In that light, I'm offering what I think is a better solution.
Create a table called t_SystemSettings (or something similar). In this table, you are going to store whatever data elements you will need throughout the life of the DB. This can be a user ID, start date, stop date, version number, or whatever else you need to carry. This is an especially useful method for passing values to other forms or reports. There's only going to be one record in here, so make your field names specific to their function.
To get information from this table, make a simple function like this:
Code:
Function GetSettings (settingname As String) As String
GetSettings = DLookUp(settingname,"t_SystemSettings")
End FunctionNow, depending on what you've stored there, you can easily identify this code in your program because it will be obvious things like GetSettings("UserID") or GetSettings("Start_Date"). In the long run, this makes maintenance very easy, and it's much cleaner than something like UserID = Forms!Form("your_hidden_form_name").FieldName.
To write to the table, make another one line subroutine like this:
Code:
Sub SetSettings(settingname as String, settingvalue as String)
CurrentDb.Execute "UPDATE t_SystemSettings SET " & settingname & "='" & settingvalue & "';"
End SubNote that using CurrentDb.Execute automatically disables and then reenables the warning messages (I.e., you won't get the "You are about to update 1 row in t_SystemSettings. Are you sure?"). It's the equivalent of SetWarnings Off, run query, SetWarnings On.
Now, to write a setting, just code it like SetSettings("UserID", environ("username")).
I believe you'll find that a method like this is cleaner and simpler to use than the hidden form method. I am, of course, open to suggestions or revisions to this method, so feel free to tear it apart or utilize it or whatever else you'd like to do.
Last edited:
