Results 1 to 2 of 2
Thread: store discrete data (2000)
2004-05-30, 16:26 #1
- Join Date
- Mar 2004
- Thanked 0 Times in 0 Posts
store discrete data (2000)
I would like to be able to save some "variables" (for want of a better way of describing them) which I can use in a calculated text box. For example on a form used to enter job information, the client prints off a report summarising various jobs, which includes a calculation based on hours and on a labour rate. The labour rate may need to change from time to time and I want to allow the user to click a button to open a form enabling them to change that rate. The report will then use this value each time it is printed.
I thought of saving this data in a table, but there would only ever be one "record" for labour rate. Extrapolating this idea further, I may want to save another "variable" in a table, with there only ever being one "record" for that too in exactly the same way, with the user being able to modify that value by clicking a button.
Can I use a table in this manner, and if so, how do I go about setting this up, because this seems to be a "non-standard" use of a table?
Thanks for any help you can give.
2004-05-30, 17:35 #2
- Join Date
- Dec 2000
- Sacramento, California, USA
- Thanked 1 Time in 1 Post
Re: store discrete data (2000)
Certainly this is a valid use of a table. The table would probably be local to the front end, assuming the database is split and the users have a copy of the front end on their individual machines. You can create a table with a field for the "variable" name and a field for the value. Make both of them text fields and use the variable name field as the primary key. If the users share the table, you would also need another field to allow them to store their individual settings, a UserName field, for instance. You would need a set of public functions to return the current value of the "variable" by looking it up in the table or in a static variable within the function. Something like this would work, assuming you have a table called USysVars, with fields called SysVarName and SysVarValue:
<pre>Public Function DATA_PATH(Optional NewValue As Variant) As String
Static strValue As String
Dim strTemp As String
Dim strSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
On Error Resume Next
If strValue = "" Then
strValue = DLookup("[SysVarValue]", "USysVars", _
' If a NewValue was passed, update the variable and the record
If Not IsMissing(NewValue) And Not IsNull(NewValue) Then
If strValue <> CStr(NewValue) Then
strValue = CStr(NewValue)
' Now change the value in the table
strSQL = "SELECT * FROM USysVars WHERE SysVarName = 'DATA_PATH'"
Set dbs = CurrentDb
' Open a dynaset. That way we can add or edit a record
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.EOF Then
' No record already exists, so we add one
!SysVarName = "DATA_PATH"
!SysVarValue = strValue
Set rst = Nothing
DATA_PATH = strValue