creating login session (1 Viewer)

[SeanATech]

Hey Im curious where I am going wrong with this. I currently have it in a model labeled sessions and it is not saving anything. Am I supposed to call the session with cmd_Login_Click? I am not sure how to do this if I do have to. Where else might I be going wrong?


Option Compare Database
Public LngLoginId As Long


Function LogMeIn(sUser As Long)
'/Go to the users table and record that the user has logged in

Dim Rs As DAO.Recordset

Set Rs = CurrentDb.OpenRecordset("SELECT * FROM [Logins] WHERE AgentLoginID =" & sUser)
If Not Rs.EOF Then
Rs.Edit
Rs.Fields("LoggedIn").Value = 1
Rs.Update
End If
Rs.Close
Set Rs = Nothing

End Function

Function LogMeOff(sUser As Long)
'/Go to the users table and record that the user has logged out

Dim Rs As DAO.Recordset

Set Rs = CurrentDb.OpenRecordset("SELECT * FROM [Logins] WHERE AgentLoginID =" & sUser)
If Not Rs.EOF Then
Rs.Edit
Rs.Fields("LoggedIn").Value = 0
Rs.Update
End If
Rs.Close
Set Rs = Nothing


End Function
Function CreateSession(WhoAmi As Long)
'/This function records the details regarding the login details of the person
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("StoredLogins")
Rs.AddNew
Rs.Fields("AgentLoginID").Value = StrLoginName
Rs.Fields("DateTImeLogin").Value = Now()
Rs.Update
'/Next get the autonumber and store it to a public variable that will be used to
'/identify this session.
'/This id is used when user logs off to close this session.
Rs.MoveFirst
DoEvents
Rs.MoveLast
LngLoginId = Rs(0)

Rs.Close
Set Rs = Nothing

End Function

Function CloseSession()
'This closes the open session
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("SELECT * FROM StoredLogins WHERE SessionID =" & LngLoginId)

If Not Rs.EOF And Not Rs.BOF Then
Rs.Edit
Rs.Fields("DateTimeLogout").Value = Now()
Rs.Update
Rs.Close
End If

Set Rs = CurrentDb.OpenRecordset("SELECT * FROM [Logins] WHERE AgentLoginID =" & LngUserID)

'Flag user as being logged out
If Not Rs.EOF And Not Rs.BOF Then
Rs.Edit
Rs.Fields("LoggedIn").Value = 0
Rs.Update
Rs.Close
End If
Set Rs = Nothing

End Function

 

[DJkarl]

One thing I see is the code

Rs.Fields("AgentLoginID").Value = StrLoginName

This seems to indicate that AgentLoginID is a string, but your look up is using a long against that field. Unless StrLoginName is actually a number and just has a misleading name this would be a problem.

Function LogMeIn(sUser As Long)
'/Go to the users table and record that the user has logged in

Dim Rs As DAO.Recordset

Set Rs = CurrentDb.OpenRecordset("SELECT * FROM [Logins] WHERE AgentLoginID =" & sUser)

 

[SeanATech]

Ok this is where I was getting confused and is starting to make sense now that you mention that. My AgentLoginID can either be a length of numbers if you are a student agent and if you are an instructor agent more than likely your loginID is going to be letters. I have AgentLoginID saved in tables as a text so would I then be changing sUsers to a string and loginID as a string?

 

[DJkarl]

Ok this is where I was getting confused and is starting to make sense now that you mention that. My AgentLoginID can either be a length of numbers if you are a student agent and if you are an instructor agent more than likely your loginID is going to be letters. I have AgentLoginID saved in tables as a text so would I then be changing sUsers to a string and loginID as a string?

Basically yes.

Set Rs = CurrentDb.OpenRecordset("SELECT * FROM [Logins] WHERE AgentLoginID =[COLOR=red]'[/COLOR]" & sUser [COLOR=red]&"'"[/COLOR])

You also need to enclose text in quotes in your queries.

 

[SeanATech]

Ok so the red is where I am running into issues now. I am trying to take the agentID from the cboAgentID and have the module store it in the my storedlogins tbl.
First thing: I am not sure where I declare that LngLoginID = Me.cboAgentLoginID
2nd: It keeps telling me that I cannot add or change the record because a related record is stored in Logins. Is this because I have a relationship between the two tables?
This is the last part of my project and so far the hardest part.

This is the Login in form

Private Sub cboAgentLoginID_AfterUpdate()
Me.txtPassword.SetFocus
LngLoginID = Me.cboAgentLoginID
End Sub

Private Sub cmd_Login_Click()
'Create a Login session
     Call CreateSession(LngLoginID)
     Call LogMeIn(LngLoginID)
    Exit Sub
    
'Check to see if data is entered into the UserName combo box

    If IsNull(Me.cboAgentLoginID) Or Me.cboAgentLoginID = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.cboAgentLoginID.SetFocus
        Exit Sub
    End If

    'Check to see if data is entered into the password box

    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
    End If

    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box

    If Me.txtPassword.Value = Nz(DLookup("Password", "Logins", _
            "[AgentLoginID]=" & Chr(34) & Me.cboAgentLoginID.Value & Chr(34)), vbNullString) Then

        AgentLoginID = Me.cboAgentLoginID.Value

        'Close logon form and open splash screen

        DoCmd.Close acForm, "frm_Logins", acSaveNo
        DoCmd.OpenForm "frm_Home"

    Else
      MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.txtPassword.SetFocus
    End If

    'If User Enters incorrect password 3 times database will shutdown

    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
      MsgBox "You do not have access to this database.Please contact admin.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If
End Sub

This is the module.

Option Compare Database
Public LngLoginID As String

Function LogMeIn(sUser As String)
'/Go to the users table and record that the user has logged in
'/and which computer they have logged in from

Dim Rs As DAO.Recordset

    Set Rs = CurrentDb.OpenRecordset("SELECT * FROM [Logins] WHERE AgentLoginID ='" & sUser & "'")
    If Not Rs.EOF Then
        Rs.Edit
        Rs.Fields("LoggedIn").Value = True
        Rs.Update
    End If
    Rs.Close
    Set Rs = Nothing
    
End Function

Function LogMeOff(sUser As String)
'/Go to the users table and record that the user has logged out

Dim Rs As DAO.Recordset

    Set Rs = CurrentDb.OpenRecordset("SELECT * FROM [Logins] WHERE AgentLoginID ='" & sUser & "'")
    If Not Rs.EOF Then
        Rs.Edit
        Rs.Fields("LoggedIn").Value = False
        Rs.Update
    End If
    Rs.Close
    Set Rs = Nothing
    

End Function
[COLOR=Red]Function CreateSession(WhoAmi As String)
'/This function records the details regarding the login details of the person
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("StoredLogins")
    Rs.AddNew
    Rs.Fields("AgentLoginID").Value = LngLoginID
    Rs.Fields("DateTImeLogin").Value = Now()
    Rs.Update
    '/Next get the autonumber and store it to a public variable that will be used to
    '/identify this session.
    '/This id is used when user logs off to close this session.
    Rs.MoveFirst
    DoEvents
    Rs.MoveLast
    LngLoginID = Rs(0)
    Rs.Close
Set Rs = Nothing

End Function[/COLOR]

Function CloseSession()
'This closes the open session
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("SELECT * FROM StoredLogins WHERE SessionID ='" & LngLoginID & "'")

    If Not Rs.EOF And Not Rs.BOF Then
        Rs.Edit
        Rs.Fields("DateTimeLogout").Value = Now()
        Rs.Update
        Rs.Close
    End If
    
    Set Rs = CurrentDb.OpenRecordset("SELECT * FROM [Logins] WHERE AgentLoginID ='" & LngLoginID & "'")
    
    'Flag user as being logged out
        If Not Rs.EOF And Not Rs.BOF Then
            Rs.Edit
            Rs.Fields("LoggedIn").Value = 0
            Rs.Update
            Rs.Close
        End If
    Set Rs = Nothing

End Function

 

[boblarson]

Can you post a copy of your database (with bogus data of course)? I believe it is how you have defined your relationships and perhaps used lookups at table level which is causing your problem.

 

[SeanATech]

Thank you for taking time to have a look at this. Much appreciated. None of the information in here is valid. All bogus data to make sure things were working correct most of the passwords for the logins are passw0rd with a zero.

 

[boblarson]

Okay, well I am seeing what you are doing but unfortunately I don't have time to do anything about it at this point. I will try to put together a better sample for you which will work much better than what you have. I'll also try to have my netbook with me so I can make more comments about this database tomorrow as well.

 

[SeanATech]

Thanks a lot I appreciate you taking your time to have a look at.

 

[SeanATech]

Bob,
Great news I can store my login right now, however I cannot logout. Its telling me a data mismatch

Function CloseSession()
'This closes the open session
Dim Rs As DAO.Recordset
[COLOR=Red]Set Rs = CurrentDb.OpenRecordset("SELECT * FROM [StoredLogins] WHERE SessionID ='" & LngLoginId & "'")
[/COLOR]
    If Not Rs.EOF And Not Rs.BOF Then
        Rs.Edit
        Rs.Fields("DateTimeLogout").Value = Now()
        Rs.Update
        Rs.Close
    End If
    
    Set Rs = CurrentDb.OpenRecordset("SELECT * FROM [Logins] WHERE AgentLoginID ='" & LngLoginId & "'")
    
    'Flag user as being logged out
        If Not Rs.EOF And Not Rs.BOF Then
            Rs.Edit
            Rs.Fields("LoggedIn").Value = 0
            Rs.Update
            Rs.Close
        End If
    Set Rs = Nothing

End Function

 

[boblarson]

So, before we go further, what are you trying to gain by doing all of this? The reason I ask is that I have used a logging method in the past to track who has been in the database and for how long, etc. by doing it all without them having to be involved.

How I do that is I have a form that opens up with an autoexec macro and it opens up hidden. It gets the user's network ID automatically put in as well as the date/time that they opened the database and then the record is saved but it stays on that record. So then when the database is closed, the form's UNLOAD event writes the date/time of exit and then it keeps track of things that way. It also will keep the time if the user just closes the app by using the X at the top. The only time it won't log the exit is if the task manager is used to kill the app. But it won't hurt anything because when they open it up again, a new record is started which stays on that form until it closes again.

 

[SeanATech]

The reason I am tracking the agents login and logout times is because every week the agent will be required to volunteer two hours of their time working on various help desk issues. It is part of the requirement for the class. Its the equivalent of when a person is logging in and out for work. I dont plan on tracking the computers theyre logged into because there will be two computers that the agents will have access to so I dont need to track that. All I need to track and store are their ID and the amount of time that they have put in each day.

 

[boblarson]

The reason I am tracking the agents login and logout times is because every week the agent will be required to volunteer two hours of their time working on various help desk issues. It is part of the requirement for the class. Its the equivalent of when a person is logging in and out for work. I dont plan on tracking the computers theyre logged into because there will be two computers that the agents will have access to so I dont need to track that. All I need to track and store are their ID and the amount of time that they have put in each day.

So they won't be logged into the computer as themselves? In other words, they might be logged into the computer but the other agent might be able to go to that computer and even though someone else is logged in, they can bring up the database and work? So just logging things in the background wouldn't work for you? And I didn't say that I logged the computers that they were logged in to, but that if someone logs into Windows, then you can capture that automatically without user intervention. The less they have to remember to do (log in and log out) will make it a little more accurate as it is human nature to forget occasionally. So, if they forget to log out - how would you deal with that?

 

[SeanATech]

Yes, multiply people will be able log on to the same computer as one user and start working on trouble tickets. So running things in the background would not work unfortunately. If a user forgets to log out for the day then when the DB closes to logout all users, and then an administrator would be able to go in and manually adjust the times. This DB is kind of an open DB with limited securities.