System_user value accessible from all forms

[gsnidow via AccessMonster.com]

Greetings folks. I have the following code behind the on open event of
several forms.

Dim cn As ADODB.Connection
Dim rsLOGIN_ID As Recordset
Dim strSQL As String
strSQL = "SELECT system_user AS LOGIN_ID, " & _
"u.first_name + ' ' + u.last_name AS USER_NM " & _
"FROM tblusers u " & _
"WHERE u.login_nm = system_user"

Set rsLOGIN_ID = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rsLOGIN_ID.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic

Me.txtUser = (rsLOGIN_ID("LOGIN_ID"))
Me.txtUserName = (rsLOGIN_ID("USER_NM"))

The purpose is to populate two unbound text boxes, txtUser and txtUserName,
with their nt login and their name from the database, for use by some
security measures I have in place beyond server security. The problem is
that every time I make a new form I must put this code in it. My question is
this: is there a way I could run this code, or something similar, when the
application is starting, and store the two values somewhere globally
accessible, so I could reference them from any form for the duration of the
session until the user quits the application?

Greg

 

[Sylvain Lafontaine]

Easy: create a module and put your code there under the form of a public
function with a static flag to initialise it; something like:

Private UserInitialised as boolean
Private User as string
Private UserName as string

Public Function GetUser ()

if (UserInitialised = False) then
UserInitialised = True
User = ....
UserName = ...
end if

GetUser = User
End Function

Public Function GetUserName ()

if (UserInitialised = False) then
UserInitialised = True
User = ....
UserName = ...
end if

GetUserName = UserName
End Function


Of course, User and UserName should be initialised by a call to a commun
private subroutine. Finally, instead of a module, you could also an object
(or user defined Type) to initialize/contains these values.