Solved After Login to Display Logged in Username (1 Viewer)

[justin0312]

Hi, I am very new to MS Access. I follow this login code from a website.

Private Sub login_Click()

  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
 
  If Trim(Me.Username.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Username is empty.", buttons:=vbInformation, title:="Username Required"
    Me.Username.SetFocus
    Exit Sub
  End If
 
  If Trim(Me.Password.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Password is empty.", buttons:=vbInformation, title:="Password Required"
    Me.Password.SetFocus
    Exit Sub
  End If
 
  'query to check if login details are correct
  strSQL = "SELECT Username FROM tbl_user WHERE Username = """ & Me.Username.Value & """ AND Password = """ & Me.Password.Value & """"
 
  Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL)
  If rst.EOF Then
    MsgBox prompt:="Incorrect username or password. Try again.", buttons:=vbCritical, title:="Login Error"
    Me.Username.SetFocus
  Else
    MsgBox prompt:="Hello, " & rst.Fields(0).Value & ".", buttons:=vbOKOnly, title:="Login Successful"
    
    DoCmd.OpenForm "Splash"
  
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblLoginSession (UserName, Login) VALUES ('" & Me.Username.Value & "','" & Now() & "')"
    DoCmd.SetWarnings True
    
   DoCmd.Close acForm, "Login", acSaveYes
  End If
 
 Set db = Nothing
 Set rst = Nothing

End Sub

After user successful login, it will open to another form. In the open form, I want to display the username that is used to login (not the system name). I have a table to store the login details

tblLoginSession
-ID (AutoNumber)
-username (short text)
-LoginTime (Date/Time)
-LogoutTime (Date/Time)

I have tried to use currentUser() but it is not working.

Anyone can guide me on this. Thanks in advance

 

[arnelgp]

save username to Tempvar collection:

Private Sub login_Click()

  Dim db As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
 
  If Trim(Me.Username.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Username is empty.", buttons:=vbInformation, title:="Username Required"
    Me.Username.SetFocus
    Exit Sub
  End If
 
  If Trim(Me.Password.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Password is empty.", buttons:=vbInformation, title:="Password Required"
    Me.Password.SetFocus
    Exit Sub
  End If
 
  'query to check if login details are correct
  strSQL = "SELECT Username FROM tbl_user WHERE Username = """ & Me.Username.Value & """ AND Password = """ & Me.Password.Value & """"
 
  Set db = CurrentDb
  Set rst = db.OpenRecordset(strSQL)
  If rst.EOF Then
    MsgBox prompt:="Incorrect username or password. Try again.", buttons:=vbCritical, title:="Login Error"
    Me.Username.SetFocus
  Else
    MsgBox prompt:="Hello, " & rst.Fields(0).Value & ".", buttons:=vbOKOnly, title:="Login Successful"
    If IsNull(Tempvars!tvarUserName) Then
        Tempvars.Add "tvarUserName", ""
    End If
    
    Tempvars!tvarUserName.Value =  rst.Fields(0).Value
    DoCmd.OpenForm "Splash"
 
    DoCmd.SetWarnings False
    DoCmd.RunSQL "INSERT INTO tblLoginSession (UserName, Login) VALUES ('" & Me.Username.Value & "','" & Now() & "')"
    DoCmd.SetWarnings True
    
   DoCmd.Close acForm, "Login", acSaveYes
  End If
 
 Set db = Nothing
 Set rst = Nothing

End Sub

you then use Tempvars!tvarUserName.Value to retrieve the username.
example:

Me!Label1.Caption = Tempvars!tvarUserName.Value

 

[justin0312]

Sorry, for this
Me!Label1.Caption = Tempvars!tvarUserName.Value

I placed = Tempvars!tvarUserName.Value into the unbound textbox (default value). Not sure if it is correct, but the username is as per login

 

[arnelgp]

but the username is as per login

of course it is.

can another user login on same db (front end), on same computer at the same time?

 

[justin0312]

Ok thanks. Only 1 user can login at one time