Access VBA

css navigation by Css3Menu.com

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")

© 2009-2017

Updated:  09/30/2017 19:48
This page added:  12 February 2009