Maintaining USERID from form to form after INITIAL capture of ID

[msaccesspat]

I have a main form and 2 sub forms loaded for Edit to 3 linked tables. On the main form load a module is run 'captureuserid'. The user id is successfully displayed on the main form. When I update THE FIRST SET OF tables on the form, I successfully update the sql column name-updt_oper_ID, on the sql table with the 'userID' that was captured on load.

Without closing the application, I click on a search button(command button) that queries the sql tables to bring in 3 new tables that are TO BE updated. I update all three tables successfully but have LOST THE USERID (add watch says its null) because the module is not rerun . I don't want to rerun the module to capture the windows login ID again, I want to keep,save,maintain it during the entire update , or insert session etc, no matter how many changes I make.

How do I maintain that windows userid in a global manner, during the application session ?


thanks, please keep it simple, Im new to access
Pat

[ranman256]

userID can be called anytime. put it in a form, if you open another form refill it.
txtUser = Environ("Username")

for a query
select forms!frmMain!txtUser, * from table

[msaccesspat]

you say. "userid can be called anytime" Yes I hoped so and is what i want. You say "put it in a form" yes, i do that on main form successfuly, and the text property name is txtuserid. You say "if you open another form refill it", Yes I do need to open a subform, I do open the subform and on first update I write it to the sql table successfully.
Your post isn't helpful because on a "REQUERY" i have lost the userID because the captureID module is only run on LOAD of main form.
i appreciate your time but either you don't understand my dilemma or I don't understand your post reply.
thanks

[RuralGuy]

Is there some reason instead of closing the 1st form you couldn't just set it invisible? You could then reference it from any additional form.

[msaccesspat]

Yes, the reason is . the application still needs all other information. BUT, how is it I can reference when it's invisible, as your suggesting, when I can't reference it IS VISIBLE.?

[msaccesspat]

I believe I misread your post , RuralGuy. I DO not close the form, and don't understand why it has lost the USERID information.

[msaccesspat]

RuralGuy, to be clear , as I said in original post , i do a VBA "REQUERY" after the application does a new 'search' on a key field. This is when the UserId is lost i believe. See original post.

[RuralGuy]

I'm sorry. I did not read your 1st post correctly. How are you displaying the UserID on the Main Form and in what type of control?

[msaccesspat]

No problem , i appreciate your time. The userid is an unbound text box, called txtuserid.
The module(public) to capture ID is run, here's some code Option Explicit

Public strUserID As String, strEnv As String

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" ( _
ByVal lpBuffer As String, _
nSize As Long _
) As Long . . .

On the open of App, form is loaded and the unbound text box, txtuserid value is supplied via this code>
Private Sub Form_Load()

DoCmd.Maximize
'*** set Userid
strUserID = UCase(UserID)
txtUserID = strUserID . . .

ALL that works only for the first set of records searched, and the value of txtuserid is updated in the sql server table. UPon the second search, i lose the value even though i never close the form.

hope this helps
thanks

[RuralGuy]

How about creating a local variable and store the UserID in it as well? Then use the Current Event to restore the Text Box each time?

[msaccesspat]

I'll try it to see if it works. Can you direct me to an article that tells me how to create a local variable ? and then use current event , which would be the before update. (I really thought I wanted a GLOBAL variable so it was kept no matter what form i was on )>> but i don't know !

[RuralGuy]

A Global variable would simply be created in a stand alone module rather than in your form's code module. At the top of your module outside of any code procedure put:

Option Compare Database
Option Explicit
Public Dim YourVariableName As Text

[RuralGuy]

The Current Event and the Before Update Event are two separate event.

[msaccesspat]

I believe I am doing that already in the module, see 844am post for today.

Option Explicit

Public strUserID As String, strEnv As String

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" ( _
ByVal lpBuffer As String, _
nSize As Long _
) As Long . . .

I reread ranman256 post. Not sure I fully understand but I'll check to see if I haven't coded that already. In every subform, i'll put that 'substitution' statement . txtuserid = struserid
where txtuserid is the textbox control name, struserid is the global variable from the module.

[Perceptus]

Possible to use the TempVars object? You could store your custom UserID in TempVars when it is loaded. and then reference the tempvars object as needed. It clears out when access unloads.