How can I add username when user makes an update?

[alexandervj]

I have a simple multiuser login script seen below that works alright -

Private Sub Validate_Click()
Username.SetFocus
If Username = "Tom W" And Password = "tw123!" Then
MsgBox "Access Granted - Welcome Tom", vbInformation, "Welcome"
DoCmd.Close
DoCmd.OpenForm "frm_Main_Menu"
ElseIf Username = "Rich C" And Password = "rc123!" Then
MsgBox "Access Granted - Welcome Rich", vbInformation, "Welcome"
DoCmd.Close
DoCmd.OpenForm "frm_Main_Menu"
ElseIf Username = "Alex J" And Password = "Gal.2:20" Then
MsgBox "Access Granted - Welcome Alex", vbInformation, "Welcome"
DoCmd.Close
DoCmd.OpenForm "frm_Main_Menu"
ElseIf Username = "manager1" And Password = "manager1" Then
MsgBox "Please Exercise Caution When Altering Back End", vbInformation, "Manager Area"
DoCmd.Close
DoCmd.OpenForm "frm_Main_Menu"
Else
MsgBox "Login Error - Please re-enter your information", vbInformation, "Login Error"
End If
End Sub

but what I want is to have the username go in a field in the table whenever a user makes a change to a record in that table. Can I do this with this script? How would I do this? Thanks

[June7]

I save the user ID to a textbox on a form that never closes (Main Menu) so it is always available for reference by any procedure.

The trick is figuring out what event to put code in.

Maybe in form BeforeUpdate event, something like:

Me.fieldname = Forms!Main.tbxUser

[alexandervj]

Awesome idea! I will try it. Thanks

[alexandervj]

Having trouble passing the username from the login form to the Main Menu so far I've tried putting =[Forms]![frm_login].[Username] in the default value of the text box (Username_main) on the main menu.

Also tried putting Me.Username = Forms!frm_Main_Menu.Username_main in the BeforeUpdate event on the login form. What am I doing wrong? Thanks

[June7]

Correction, what I really do now is bind MainMenu to Users table and open MainMenu filtered to user record. So from the login form:

DoCmd.OpenForm "Main", , , "UserID=" & Me.UserID

DefaultValue is only useful for new record on a bound form.

[alexandervj]

I see. My script doesn't use a users table though, is there a way I can adapt it to use a users table? Thanks

[June7]

Why would you set textbox on Login form to value from Main form? Isn't that backwards?

Table not required - you have the validation hard coded. Not as flexible as table but functional. Populate a textbox on Login form with username. Close the Login form after opening Main form.

[alexandervj]

Yeah I thought it was backwards, I must have misunderstood what you told me

[alexandervj]

I used the onLoad event for the Main Menu form, it all seems to work well. Now just have to figure out how to close the login form after the main menu form is loaded. Just DoCmd.Close closes the main menu form that just loaded

[alexandervj]

Figured it out - DoCmd.Close acForm, "frm_login", acSaveYes. Thank you June!

[alexandervj]

So to recap for reference -

I added

DoCmd.OpenForm "frm_Main_Menu", , , "Username=" & Me.Username
DoCmd.Close acForm, "frm_login", acSaveYes

to my onclick event for the login button of my login form after the user puts in the correct username/password. Then added

Private Sub Form_Load()
Me.Username = Forms!frm_login.Username
End Sub

to the onLoad event of my main menu form. Works great. Thanks again June

[June7]

The acSaveYes is to save design changes, has nothing to do with saving data.

Data is committed to table when form (or table) is closed or move to another record or run code (DoCmd.RunCommand acCmdSaveRecord).

[alexandervj]

Yep I know

[June7]

If using code to modify properties (Filter is one commonly treated with VBA), probably don't want to close the form and save the changes. I always use acSaveNo.

[alexandervj]

Oh ok, thanks!