Save Changing Parameters
Although most of the tables in my database reside on MS SQL Server, one table stays in Access. There is a “RegisterFile” that is moved for holding local parameters.
'Get value from RegisterFile table
Public Function KeyVal(KeyName As String) As String
On Error GoTo KV_Error
If IsNull(DLookup("[keynumber]", "RegisterFile", "[keyname]= '" + KeyName + "'")) Then
KeyVal = ""
Else
KeyVal = DLookup("[keyvalue]", "RegisterFile", "[keyname]='" + KeyName + "'")
End If
Exit Function
KV_Error:
KeyVal = ""
End Function
'Set value in RegisterFile table
Public Sub SetKeyValue(KeyName As String, Kval As String)
Dim MyTable As Recordset
Set MyTable = CurrentDb.OpenRecordset("RegisterFile", dbOpenDynaset)
MyTable.MoveFirst
MyTable.FindFirst ("[keyname] = '" + KeyName + "'")
If MyTable.NoMatch = False Then
With MyTable
.Edit
![KeyValue] = Kval
![KeyUpd] = Now()
.Update
End With
End If
MyTable.Close
End Sub
To put something into the file, call it from VBA like
Call SetKeyValue("CurrentID", Me.ID.Value) and pull it out to use it in your query with =KeyVal("CurrentID")
|