login : Automaticcaly update, Date and Time (1 Viewer)

[Mat1994]

Hi All,

I've created a simple login form that pops up when the Access database opens. I've followed the instruction of a tutorial on youtube.


The login is a simple form with a User Name and a Password. The code compares the name and password with the data in a specific table.

The Vb code I copied is :

Private Sub ButtonLogin_Click()
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("tbl_Administrator", dbOpenSnapshot, dbReadOnly)

rs.FindFirst "UserName='" & Me.TXTUserName & "'"

If rs.NoMatch = True Then
Me.lblIncorrectUserName.Visible = True
Me.TXTUserName.SetFocus
Exit Sub
End If
Me.lblIncorrectUserName.Visible = False

If rs!Password <> Me.txtPassword Then
Me.lblIncorrectPassword.Visible = True
Me.txtPassword.SetFocus
Exit Sub
End If
Me.lblIncorrectPassword.Visible = False
DoCmd.OpenForm "OPT database"
DoCmd.Close acForm, Me.Name


End Sub

I copied and pasted this code in the "on click" event of a login button. This code works perfectly.

My problem : I would like to automatically update the date and time the user logs in.

I've created a table (tbl_Administrator) withh 4 columns (Username, Password, Update, UpTime).

I tried adding rs.fileds("Update")= Date and rs.Fields("UpTime")=Time just before the "End Sub".
It doesn't work. Does anyone have an idea how to finish the VB code, to automatically update the Date and time the user logs in?

Thank you for your help.
Mat

 

[isladogs]

If you want to record the login datetime, do it as one field Login=Now()

However, rather than reinvent the wheel, have a look at this example database https://www.access-programmers.co.uk/forums/showthread.php?t=193131

 

[Mat1994]

Hi isladogs,

I found this quote is the database you linked to me.
' CurrentDb.Execute "UPDATE tblUsers SET PWD = EncryptKey(strPassword), PWDDATE = Date ()" & "WHERE UserName ='" & GetUserName() & "';"

Has i'm not using an encrypted key to tried to adpate this example to my database by adding :
CurrentDb.Execute "UPDATE tbl_Administrator SET UpDate = Date()" & "WHERE UserName='" & Me.TXTUserName & "';"

By doing so, I get "Run-Time error '2467' which is of no help. I'm guessing the syntax is wrong.
Can you help?

Thank you,
Mat

 

[isladogs]

Several things here
1. Ideally, passwords should not be stored in Access at all. Much better to use Active Directory.
But if you must do so, you should NEVER EVER save passwords in Access without encrypting them.
Its far too easy for knowledgeable users of Access to break whatever security you may have.
If user passwords are discovered and misused, you will be liable for any damage caused. That could cost you your job and potentially much more.
2. What database security do you have?
3. You copied and modified code from another part of my application inaccurately. A crucial space was omitted before WHERE
4. Update is a reserved word in Access so should never be used as a field name. Rename as e.g. PwdUpdate

Try this

CurrentDb.Execute "UPDATE tbl_Administrator SET PwdUpDate = Date()" & _
" WHERE UserName = '" & Me.TXTUserName & "';"

But this is better

CurrentDb.Execute "UPDATE tbl_Administrator SET PwdUpDate = Now()" & _
" WHERE UserName = '" & Me.TXTUserName & "';"

 

[Mat1994]

- The database I'm creating doesn't contain any confidential information. For the moment, I wanted to limit the access to myself and then the rest of the team. When the time comes, I'll talk to my manager and the team to know what level of security they want.

Thank for the "update" information. I've changed my fields name to ModifiedDate.

-I would like to keep the date and time in two separete field, so I tried your first code. I still get the error 2467.
Could the problem come from where I tape this code? I entered your code just before the End Sub. is that where it should be?

 

[isladogs]

Yes it does contain confidential info-user passwords.
If they aren't confidential there is no point having them whatever other data you may have.

Error 2467 is application defined or object defined error....which doesn't help much. If you have added the code to that in post #1 just before End Sub, the error is because you've closed the form already so Me.txtUserName has no meaning. Move that code before the OpenForm line

Why do you want separate fields? There are very good reasons for combining them and you will probably regret ignoring this advice later.
You can easily split them again for display purposes.

 

[Mat1994]

I'm creating the username/password to only allow my team to use the database. My team is part of an airport company. The information in it doesn't contain any confidential information but I just want to limit the number of people to have access to the database.

In the future, in the form, I'll create two export buttons were I'll need to associate a specific name to a specific field. And I'll need separete date and time and I'm guessing it will be easier if there in separete fields already. I'm far from having the knowledge and skill to do that. But it's planned for the future of the project.

By moving the code before the OpenForm line did the trick. Thank you very much.

Mat

 

[isladogs]

Glad that part's working.

My previous comments still apply regarding both encryption and combining date and time into one field.
Much less code will be needed.
Easy to separate the components as and when needed using DatePart or Format etc
For example Format(DateField, "mm/dd/yyyy") ; Format(DateField,"hh:nn:ss")

Anyway, good luck with your project

 

[Mat1994]

Thank you a lot
After I've managed to export correctly the data I want. I'll go trough my work to optimize everything I can. I'll keep in mind want you have taught me.
Thank you
Mat

 

[isladogs]

You're welcome. I'm sure you will be back with more questions and I expect I'll make the same points again at that time