Public Function? (2000)

[RNCIENG]

Afternoon All,

How can I pass a value from a form to a Public Function where this value will stay in memory until the db is closed?

Thanks.

[HansV]

Declare a variable as public in a standard module (not a form or report module). In the example, I use a string variable, but you can adapt it to your needs.

Public strMyVar As String

You can assign a value to this variable while the form is open:

strMyVar = Forms!frmMyForm!txtMyTextBox

You can refer to the public variable in all code in all modules in this database.

[RNCIENG]

Good Morning,
Thanks for the suggestions, I went with this:

Public Type UserInfo
ViewID As Integer
AccessID As Integer
Active As Boolean
Password As String
UserID As String
SecurityID As String
End Type

Public User As UserInfo

Works great.

[charlotte]

A method that hasn't been mentioned is the use of a static variable within your public function. The static variable will hold the value assigned to it until the end of the Access session. Then all you need to do is call the function to retrieve the static value. Something like this will work:

Public Function StaticValue(optional NewValue as Variant) As Variant
Static varValue As Variant

If Not IsMissing(NewValue) Then
varValue = NewValue
End If

StaticValue = varValue
End Function

You would set it by passing in a value to the function and retrieve it by calling the function without an argument.