Creating a login record (1 Viewer)

kevnaff

Member
Local time
Today, 08:54
Joined
Mar 25, 2021
Messages
141
Hi All.

I have introduced a login screen to my database which requires a username and password to be entered. After logging in successfully, an Intro Page opens which displays the current user that is logged in.

I done this by putting the following code behind the login button:

TempVars!tvarUser = Me!Txt_username.Value

Txt_Username being the textbox that displays the username that is entered on login.

On the Intro Page I want to now add a button that adds a new record to a table which will keep track of all users that have logged in and store the time of the login.

I have been able to add a new record using the following code:

Private Sub Cmd_iaccept_Click()


Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_userrecord")

rs.AddNew
rs.Fields("username") = "newuserid"
rs.Fields("DateTime") = "11/09/21"
rs.Update

End Sub


This code currently adds a new record to the table, but I want to add the username that is displayed in the txt_username box. Currently it just adds a new record with newuserid in the username field. I also want to be able to store the current date and time in the DateTime field.

Would anybody be able to assist?

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:54
Joined
Oct 29, 2018
Messages
21,357
Hi. Try something like this:
Code:
rs.Fields("username")=TempVars!tvarUser 
rs.Fields("DateTime")=Now()
 

kevnaff

Member
Local time
Today, 08:54
Joined
Mar 25, 2021
Messages
141
Hi. Try something like this:
Code:
rs.Fields("username")=TempVars!tvarUser
rs.Fields("DateTime")=Now()

Thanks theDBguy, that works perfectly!

Now what I need to do is add an Exit button that will store the logout date and time, this will follow the same code as above, but I need to try and add it to the same record that the login data is stored in.

Any ideas?

Thanks
 

bastanu

AWF VIP
Local time
Today, 01:54
Joined
Apr 13, 2010
Messages
1,401
When you define your recordset to add the record (would be easier by simply using an append query) update the record where [username]=TempVars!tvarUser and exit date is null (IsNulll([ExitDateTime])=True).

Cheers,
 

kevnaff

Member
Local time
Today, 08:54
Joined
Mar 25, 2021
Messages
141
When you define your recordset to add the record (would be easier by simply using an append query) update the record where [username]=TempVars!tvarUser and exit date is null (IsNulll([ExitDateTime])=True).

Cheers,

Hi Bastanu

Thanks for your help, that makes sense. However I'm not familiar with appened queries. I understand how an update query could do this job, by finding a record where [username]=TempVars!tvarUser and Exit Date is null, and updating the Exit date to Now()

Would this work the same as an append query?

I have implemented the update query using the following code:

DoCmd.OpenQuery "UpdateSessionExit"

I now get a message asking me if I want to run a query that is going to make updates to the table, followed by a message asking me if I want to update the rows. Is there a way to run the query without these confirmation boxes popping up?

Thanks
 

bastanu

AWF VIP
Local time
Today, 01:54
Joined
Apr 13, 2010
Messages
1,401
The easiest way to avoid the warnings is to add Docmd.SetWarnings False just before Docmd.OpenQuery and Docmd.SetWarnings True just after.
 

kevnaff

Member
Local time
Today, 08:54
Joined
Mar 25, 2021
Messages
141
The easiest way to avoid the warnings is to add Docmd.SetWarnings False just before Docmd.OpenQuery and Docmd.SetWarnings True just after.
Fantastic, thanks for all your help.
 

kevnaff

Member
Local time
Today, 08:54
Joined
Mar 25, 2021
Messages
141
The database has an inventory of equipment, which stores a record of who and at what time it was last edited. This record updates whenever somebody enters in to the edit screen of the equipment and makes a change to it. Currently if you enter the edit screen but do no make a change, then the record does not update. You exit out of the inventory simply by clicking the main menu button. This is the code behind the button

Code:
Private Sub CommandMainMenu_Click()
'Reminder to check records
Dim Message, Title, Response
Dim stDocName As String
Dim stLinkCriteria As String
    
Message = "Have you checked all details are correct before leaving this screen?"
Title = "REMINDER CHECK DETAILS"
Response = MsgBox(Message, vbOKCancel, Title)
If Response = vbCancel Then GoTo Exit_CommandMainMenu_Click
 
 
 


    stDocName = "EquipInvRead"
    
    stLinkCriteria = "EquipID =" & Me![EquipID]
  Rem DoCmd.OpenForm stDocName, , , stLinkCriteria


    
    
DoCmd.Close acForm, "EquipInvEdit", acSaveNo


Exit_CommandMainMenu_Click:
Exit Sub


Err_CommandMainMenu_Click:
    MsgBox Err.Description
    Resume Exit_CommandMainMenu_Click
End Sub


I can't see anywhere in the code that would update the record. I could put another update query behind the button, but this would update the record even when a change has not been made by the user. Is there a way to determine whether a change has been made to the record so that the update query can then be applied?
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:54
Joined
Sep 21, 2011
Messages
14,037
Is there a way to determine whether a change has been made to the record so that the update query can then be applied?
Test Me.Dirty?
 

kevnaff

Member
Local time
Today, 08:54
Joined
Mar 25, 2021
Messages
141
Test Me.Dirty?
Thanks Gasman.

On second viewing, I have just noticed that there is a BeforeUpdate event which updates the currentuser and edited date when a change is made.

I've taken over the database recently so wasn't aware of this.
 

Users who are viewing this thread

Top Bottom