Check Table for User Name, prevent log-in or log-off 1st instance (1 Viewer)

[evictme]

Hello All,

I have a hidden form that loads when the database is open that has a few events on the Form_Load event (see below). So far, the code is working great. It does exactly what I need and then some but I would like to have an additional event that checks the username in the "tblCurrentlyLoggedIn" and will log-off the previous instance or prompt the user to log-off the other instance 1st. Any help would be greatly appreciated, thank you in advance!

Private Sub Form_Load()
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblCurrentlyLoggedIn ( empCurrentlyLoggedIn ) " & _
"SELECT CurrentUser() AS Users;"
DoCmd.SetWarnings True
Set rs = CurrentDb.OpenRecordset("SELECT * FROM ButtonClicks")

rs.AddNew

rs![ButtonClick] = "User Log-In"
rs![ClickTime] = Time()
rs![ClickDate] = Date
rs![User] = CurrentUser()
rs![SearchInput] = Environ("Computername")

rs.Update
rs.Close

Set rs = Nothing

If Me.Send = -1 And Me.User = "All Users" Then
Me.Visible = True
ElseIf Me.Send = "-1" And Me.Shutdown = "0" And Me.User = CurrentUser() Then
Me.Visible = True
ElseIf Me.Send = "-1" And Me.Shutdown = "-1" And Me.User = CurrentUser() Or Me.User = "All Users" Then
Me.Visible = True
DoCmd.Quit acQuitSaveAll
Else
Me.Visible = False
End If

End If
End Sub

 

[theDBguy]

Hi. I might try using DCount() for something like that. For example:

If DCount("*", "LogTable", "User=" & ThisUser & " AND LogOff Is Null")>0 Then
    'user has not logged off a previous session, do what you need to
Else
    'all is good here
End If

Hope that helps...

 

[Isaac]

You may want to keep in mind the possibility that an existing or duplicate logged-in entry doesn't really mean a user is logged in; rather, that they simply did a hard shut down (of either their machine or the database). Some users even kind of get in the habit of that.

 

[isladogs]

Have a look at my example app Password Login with Session Login Info. It may be exactly what you need

 

[evictme]

You may want to keep in mind the possibility that an existing or duplicate logged-in entry doesn't really mean a user is logged in; rather, that they simply did a hard shut down (of either their machine or the database). Some users even kind of get in the habit of that.

Yup. I have an On Close event that DELETEs the username from the table.

 

[evictme]

Hi. I might try using DCount() for something like that. For example:

If DCount("*", "LogTable", "User=" & ThisUser & " AND LogOff Is Null")>0 Then
    'user has not logged off a previous session, do what you need to
Else
    'all is good here
End If

Hope that helps...

Thank you. Put together something like this. Works good so far. Testing currently.

 

[Isaac]

Yup. I have an On Close event that DELETEs the username from the table.

Good, just remember it will never fire if someone kills the db from task manager or does a hard shut down - that's what I meant.

 

[evictme]

Have a look at my example app Password Login with Session Login Info. It may be exactly what you need

I made something similar. With a few extra features.

 

[isladogs]

Ditto.

I also have a feature in one of my commercial apps which logs every action done by users.
This was done partly to determine which features were used most in order to prioritise development requests.
In addition, it sent me an email automatically when a user action caused an Access error - this gave full details of the error: who/what/where/when etc and allowed me to fix any bugs promptly

Similarly an automatic kickout feature is used when essential maintenance is required together with a Send Message option.

 

[theDBguy]

Thank you. Put together something like this. Works good so far. Testing currently.

Hi. Glad to hear you're making good progress. Good luck with your project.