Store Data

**Eranka** · 05-29-2018, 10:52 PM

Hi Guys

Code:

Dim User As StringDim UserLevel As Integer
Dim TempPass As String
Dim ID As Integer
Dim UserName As String
Dim TempID As String




If IsNull(Me.txtUserName) Then
 MsgBox "Please Enter User Name", vbInformation, "Username required"
 Me.txtUserName.SetFocus
ElseIf IsNull(Me.txtPassword) Then
 MsgBox "Please Enter Password", vbInformation, "Password required"
 Me.txtPassword.SetFocus
Else
 If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPassword = '" & Me.txtPassword.Value & "'"))) Then
 MsgBox "Invalid Username or Password!"
 Else
 TempID = Me.txtUserName.Value
 UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 UserLevel = DLookup("[UserSecurity]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 ID = DLookup("[ID]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 DoCmd.Close
 If (TempPass = "password") Then
 MsgBox "Please change Password", vbInformation, "New password required"
 DoCmd.OpenForm "frmUserinfo", , , "[ID] = " & ID
 Else
 'open different form according to user level
 If UserLevel = 1 Then ' for admin
 DoCmd.OpenForm "Navigation Form"
 ElseIf UserLevel = 2 Then ' for academic
 DoCmd.OpenForm "Navigation Form"
 ElseIf UserLevel = 3 Then ' for exam
 DoCmd.OpenForm "Navigation Form"
 Else
 DoCmd.OpenForm "Main_Menu_2"
 Forms![Main_Menu_2]!Command19.Enabled = False
 End If


 End If
 End If
End If

I have created a login form for my system using the above. Is there is a way to store details of every login in a table (username,logindate, logintime)?
Eg: If Admin login to the system then i want his login details to be saved in a table.

Is this possible? if so how to do this part?

Thanks in Advance.

**AccessToGo** · 05-30-2018, 01:32 AM

Hi,

dim rs as recordset
set rs = currentdb.openrecordset("the name of your table", dbopendynaset)
rs.addnew
rs![the name of a field] = (some value)
... repeat for each field
rs.update
rs.close
set rs = nothing

Regards
Chris

**Eranka** · 05-30-2018, 01:33 AM

Hi Chris

Code:

Private Sub Command12_Click()Dim User As String
Dim UserLevel As Integer
Dim TempPass As String
Dim ID As Integer
Dim UserName As String
Dim TempID As String
Dim rs As Recordset




If IsNull(Me.txtUserName) Then
 MsgBox "Please Enter User Name", vbInformation, "Username required"
 Me.txtUserName.SetFocus
ElseIf IsNull(Me.txtPassword) Then
 MsgBox "Please Enter Password", vbInformation, "Password required"
 Me.txtPassword.SetFocus
Else
 If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPassword = '" & Me.txtPassword.Value & "'"))) Then
 MsgBox "Invalid Username or Password!"
 Else
 TempID = Me.txtUserName.Value
 UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 UserLevel = DLookup("[UserSecurity]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 ID = DLookup("[ID]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 DoCmd.Close
 If (TempPass = "password") Then
 MsgBox "Please change Password", vbInformation, "New password required"
 DoCmd.OpenForm "frmUserinfo", , , "[ID] = " & ID
 Else
 'open different form according to user level
 If UserLevel = 1 Then ' for admin
 DoCmd.OpenForm "Navigation Form"
 Forms![Navigation Form]![txtUser] = UserName
 ElseIf UserLevel = 2 Then ' for academic
 DoCmd.OpenForm "Navigation Form"
 Forms![Navigation Form]![txtUser] = UserName
 ElseIf UserLevel = 3 Then ' for exam
 DoCmd.OpenForm "Navigation Form"
 Forms![Navigation Form]![txtUser] = UserName
 Else
 DoCmd.OpenForm "Main_Menu_2"
 Forms![Main_Menu_2]!Command19.Enabled = False
 End If


 End If
 End If
End If


Set rs = CurrentDb.OpenRecordset("UserLog", dbOpenDynaset)
rs.AddNew
rs![UserName] = txtUserName
rs![Log_Time] = txtTime
rs![Log_Date] = txtDate


rs.Update
rs.Close
Set rs = Nothing


End Sub

Is this correct?

**isladogs** · 05-30-2018, 01:37 AM

Yes. Use an append query or sql equivalent to store the loginID and use Now() for the logindatetime which should be saved as one field.
I would also suggest looking the datetime users logout later.

Your user level If...else..end if could also be reduced to 2 items by combining the first three (user level<=3)
Or use select case to do the same thing

**Eranka** · 05-30-2018, 01:38 AM

Hi Ridders

appreciate if you could show me the code

**isladogs** · 05-30-2018, 02:45 AM

1. Use an append query or sql equivalent to store the loginID and use Now() for the logindatetime which should be saved as one field.

Code:

CurrentDB.Execute "INSERT INTO tblWhoseOn (UserID, DateTimeOn)" & VALUES ('" & Me.txtUserName & "','" & Now() & "')""

Or use a recordset as suggested by Chris
I also save the workstationID using Environ("ComputerName") together with other info such as Access version & screen resolution but that's 'icing on the cake'

2. I would also suggest looking the datetime users logout later.
Similar idea but using an update query just before the app closes.
For this to work you need to reference the autonumber LoginID created when the login record was created and store this as a variable or tempvar whilst logged in

3. Your user level If...else..end if could also be reduced to 2 items by combining the first three (user level<=3)

Code:

 'open different form according to user level 
If UserLevel <= 3 Then
     DoCmd.OpenForm "Navigation Form"
     Forms![Navigation Form]![txtUser] = UserName
 Else
     DoCmd.OpenForm "Main_Menu_2"
     Forms![Main_Menu_2]!Command19.Enabled = False 
 End If

Or use select case to do the same thing

Code:

 'open different form according to user level 
Select Case UserLevel

Case Is <= 3 
     DoCmd.OpenForm "Navigation Form"
     Forms![Navigation Form]![txtUser] = UserName
Case Else
     DoCmd.OpenForm "Main_Menu_2"
     Forms![Main_Menu_2]!Command19.Enabled = False 

End Select

P.S Beware use of the built in navigation form. They work fine at a basic level
However if you want to modify them in any significant way e.g. add subforms they become difficult to work with

**Eranka** · 05-30-2018, 06:23 AM

Hi Ridders

When i try to use Chris code its gives me an error. "Run Time Error 2467"

Why would it be?

**isladogs** · 05-30-2018, 06:38 AM

Error 2467 - The expression you entered refers to an object that is closed or doesn't exist. For example, you may have assigned a form to a Form object variable, closed the form, and then referred to the object variable

It should tell you what the problem item is

For an explanation, I suggest you ask Chris (AccessToGo)!

Did the code I uploaded help at all?
Also did you see my response a few days ago to your encryption thread?

**Eranka** · 05-30-2018, 06:46 AM

HI Ridders

I tried your code as well

Code:

CurrentDb.Execute "INSERT INTO UserLog ([User_name], [Log_Time])" & "VALUES ('" & Me.txtUserName & "','" & Now() & "')"

it give me the same error 2467.

Do i have to put the above code inside the If else statement?

"yes encryption thread, i tried that but it seems bit complex so went with password format.

"

**isladogs** · 05-30-2018, 07:31 AM

Originally Posted by Eranka

HI Ridders

I tried your code as well

Code:

CurrentDb.Execute "INSERT INTO UserLog ([User_name], [Log_Time])" & "VALUES ('" & Me.txtUserName & "','" & Now() & "')"

it give me the same error 2467.

Do i have to put the above code inside the If else statement?

"yes encryption thread, i tried that but it seems bit complex so went with password format.

"

The fact that you get the same error with both my code & that by Chris, suggests the issue is with your code
Unfortunately your code is incomplete & the first line is incorrect

I've indented it to try & make sense of the nested If statements
Are you sure your logic is correct?
Select Case would be easier to follow

Suggest placement in BLUE but that may not be correct

Code:

Sub Eranka()

Dim User As String, UserLevel As Integer, TempPass As String
Dim ID As Integer
Dim UserName As String
Dim TempID As String


If IsNull(Me.txtUserName) Then
        MsgBox "Please Enter User Name", vbInformation, "Username required"
        Me.txtUserName.SetFocus
ElseIf IsNull(Me.txtPassword) Then
        MsgBox "Please Enter Password", vbInformation, "Password required"
        Me.txtPassword.SetFocus
Else
        If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPassword = '" & Me.txtPassword.Value & "'"))) Then
               MsgBox "Invalid Username or Password!"
        Else
            TempID = Me.txtUserName.Value
            UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
            UserLevel = DLookup("[UserSecurity]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
            TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
            UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
            ID = DLookup("[ID]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")

    CurrentDb.Execute "INSERT INTO UserLog ([User_name], [Log_Time])" & "VALUES ('" & Me.txtUserName & "','" & Now() & "')"
            DoCmd.Close 'move this later?


            If (TempPass = "password") Then
                MsgBox "Please change Password", vbInformation, "New password required"
                DoCmd.OpenForm "frmUserinfo", , , "[ID] = " & ID
            Else
                'open different form according to user level
                If UserLevel = 1 Then ' for admin
                    DoCmd.OpenForm "Navigation Form"
                ElseIf UserLevel = 2 Then ' for academic
                    DoCmd.OpenForm "Navigation Form"
                ElseIf UserLevel = 3 Then ' for exam
                    DoCmd.OpenForm "Navigation Form"
                Else
                    DoCmd.OpenForm "Main_Menu_2"
                    Forms![Main_Menu_2]!Command19.Enabled = False
                End If
            End If
        End If
 End If


End Sub

As a general rule, open the other form BEFORE closing the current one.
This helps ensure any variables are carried through to the new form

As for encryption, it isn't simple but if you are storing passwords in your app without encrypting them you are asking for trouble BIG TIME
You will render yourself liable for big fines under the new GDPR law
The password format is EASY to break. Users just need to remove / replace the format

**Minty** · 05-30-2018, 07:35 AM

Minor Correction to the code - the dtae time value should be escaped with # # - as per;

Code:

CurrentDb.Execute "INSERT INTO UserLog ([User_name], [Log_Time]) " & " VALUES ('" & Me.txtUserName & "',#" & format(Now(),"yyyy/mm/dd") & "# ) "

Assuming that your time stamp is actually a DatelTime field. (Which it should be)

**Eranka** · 05-30-2018, 07:50 AM

Hi Guys

Found the reason for Error 2467 - The expression you entered refers to an object that is closed or doesn't exist .
It because after user enters user name and password on login screen the form close.

So the easiest way to place the code as below. Under form close event.

Code:

Private Sub Form_Close()
CurrentDb.Execute "INSERT INTO UserLog ([User_name], [Log_Time])" & "VALUES ('" & Me.txtUserName & "','" & Now() & "')"
End Sub

Thank you all for you time.

**isladogs** · 05-30-2018, 07:59 AM

Originally Posted by Minty

Minor Correction to the code - the dtae time value should be escaped with # # - as per;

Code:

CurrentDb.Execute "INSERT INTO UserLog ([User_name], [Log_Time]) " & " VALUES ('" & Me.txtUserName & "',#" & format(Now(),"yyyy/mm/dd") & "# ) "

Assuming that your time stamp is actually a DatelTime field. (Which it should be)

OOPS! How did I forget that!

Eranka
Yes I agree about the form close issue though personally I wouldn't use that event..
In my last post, I wrote:

As a general rule, open the other form BEFORE closing the current one.
This helps ensure any variables are carried through to the new form

**ssanfu** · 05-30-2018, 05:15 PM

Minor point....

Using "format(Now(),"yyyy/mm/dd")" converts the date to a string. And if you are going to format to date only, just use Date().

If the field "Log_Time" is a Date/Time type field, I would just use Now() for date and time or Date() for the date.
No formatting is necessary since the "Date" is a Number - Double (so formatting is unimportant/irrelevant).

Code:

CurrentDb.Execute "INSERT INTO UserLog ([User_name], [Log_Time]) " & " VALUES ('" & Me.txtUserName & "',#" & Now() & "# ) "

or

Code:

CurrentDb.Execute "INSERT INTO UserLog ([User_name], [Log_Time]) "  & " VALUES ('" & Me.txtUserName & "',#" & Date() &  "# ) "

My $0.02.............

Store Data

Thread Tools

Store Data

Similar Threads

Estimates vs Actuals - best way to store data?

Can I use a query to store data?

Separate store's managers to only see their store's data

How to store data many row in one table

Store data in Excel automatically is that possible

Posting Permissions