Insert Username in table

Question

Hi Expert,

I have a table called MACHINE having a field named "updatedby". Whenever, users open the database, a Login Form appears and prompts for Username & Password. These Username and Password are stored in a table called "tblUser". 

What i want to achieve is, when a user uses his/her Username & Password to open a form called MachineNo to insert records, their username should automatically store in the "updatedby" field in table MACHINE. The "MachineNo" form has a save "button". It will be easy if i can know which user had keyed in the transaction.

Please advise if this can be achieved. 

Thanks

Rakesh

Answer 1

Hi Tom,

Is this correct?

======================

Option Explicit

Public g_strUserName As String

Option Compare Database

Private Sub Command1_Click()

If IsNull(Me.txtLoginID) Then

    MsgBox "Please Enter LoginID", vbInformation, "LoginID Required"

    Me.txtLoginID.SetFocus

ElseIf IsNull(Me.txtPassword) Then

    MsgBox "Please Enter password", vbInformation, "Password Required"

    Me.txtPassword.SetFocus

Else

    'process the job

    'If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin ='" & Me.txtLoginID.Value & "'"))) Or _

    '(IsNull(DLookup("Password", "tblUser", "Password ='" & Me.txtPassword.Value & "'"))) Then

    'If (IsNull(DLookup("[UserLogin]", "tblUser", "[UserLogin] = "" & me.txtLoginID.Value & "" And password = "" & me.txtPassword.Value & """))) Then

    'If (IsNull(DLookup("[UserLogin]", "tblUser", "[Userlogin] = "" & Me.txtLoginID.Value & "" And password = "" & Me.txtPassword.Value & """))) Then

    'If (IsNull(DLookup("Userlogin", "tblUser", "UserLogin='" & Me.txtusername.Value & "' And password='" & Me.txtPassword.Text & "'"))) Then

     '   MsgBox "Incorrect LoginID or Password"

    If (IsNull(DLookup("userlogin", "tbluser", "userlogin = '" & Me.txtLoginID.Value & " '"))) Or _

    (IsNull(DLookup("password", "tbluser", "password  = '" & Me.txtPassword.Value & " '"))) Then

      MsgBox "incorect loginid or password"

Else

        DoCmd.close

        DoCmd.OpenForm "switchboard"

        End If

End If

End Sub

Private Sub Detail_Click()

g_strUserName = Me.txtLoginID

End Sub

Private Sub Form_Click()

g_strUserName = Me.txtLoginID

End Sub

Answer 2

You would set g_strUserName as soon as you know the u/pw is valid:

Else

     g_strUserName = Me.txtLoginID 

     DoCmd.close

     DoCmd.OpenForm "switchboard"

Not sure where "g_struserlogin" comes from. Confusing.

Also make sure you have Option Explicit at the top of EVERY module in your application.

Answer 3

Dear Tom,

It was not updating as expected. Below is the code for form "LOGIN". Not sure where did i get it wrong...pls advise.

====================================================

Private Sub Command1_Click()

If IsNull(Me.txtLoginID) Then

    MsgBox "Please Enter LoginID", vbInformation, "LoginID Required"

    Me.txtLoginID.SetFocus

ElseIf IsNull(Me.txtPassword) Then

    MsgBox "Please Enter password", vbInformation, "Password Required"

    Me.txtPassword.SetFocus

Else

    'process the job

    If (IsNull(DLookup("userlogin", "tbluser", "userlogin = '" & Me.txtLoginID.Value & " '"))) Or _

    (IsNull(DLookup("password", "tbluser", "password  = '" & Me.txtPassword.Value & " '"))) Then

      MsgBox "incorect loginid or password"

Else

        DoCmd.close

        DoCmd.OpenForm "switchboard"

        End If

End If

End Sub

Private Sub Detail_Click()

g_strUserName = Me.txtLoginID

End Sub

Private Sub Form_Click()

g_struserlogin = Me.txtLoginID

End Sub

Answer 4

You can have a global variable that gets set when the user logs in:

(of course I'm guessing at some of your object names)

(in a Standard Module)

Public g_strUserName as string

(in the login button's click event)

g_strUserName = Me.txtUserName

(in MachineNo's Form_BeforeInsert event)

Me.UpdatedBy = g_strUserName

Note that Form_BeforeInsert is the one and only correct event to set such field values.

Answer 5

As part of the login, when successful, pass the username to a TempVar.  Then in your form, probably using the Current event, simply pass the TempVar to the appropriate control so the record is updated accordingly.