Access VBA

css navigation by

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 = ""
		KeyVal = DLookup("[keyvalue]", "RegisterFile", "[keyname]='" + KeyName + "'")
	End If
Exit Function
	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.FindFirst ("[keyname] = '" + KeyName + "'")
	If MyTable.NoMatch = False Then
		With MyTable
			![KeyValue] = Kval
			![KeyUpd] = Now()
		End With
	End If
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

© 2009-2016

Updated:  11/16/2016 19:50
This page added:  12 February 2009