Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    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.

    Regards Roger

  2. #2
    Plutonium Lounger
    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", _
    End If

    ' 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
    End If
    With rst
    !SysVarName = "DATA_PATH"
    !SysVarValue = strValue
    End With
    Set rst = Nothing
    End If

    DATA_PATH = strValue
    End Function</pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts