Application.Quit (A2K)

Topic

New Reply

Good Afternoon,

I know there is very nice code to apply to an Access db that can log ALL users out after a specified amount of time. What I’m searching for is to an adaptation of the code that would log off A SPECIFIC user.

There are times when A user has a problem, the IT department needs to be contacted to bump this user off. However, I’m hoping to find alternative to this. Currently I’m working in this direction:

Pull ONLY specific records rather than using the entire rst.[/b]
SQL statement to look a table where a field (yes/no) is True and include the field that has the users login name (2 fields).

Using the SQL statement set the variable (value of the field for user’s LoginName)

Using the Variable in a FOR EACH NEXT statement to bump each user off.
application.quit

However, I haven’t had any success. With my entire code, I don’t get an error, but the user isn’t logged out either. I suspect this has something to do with the .ldb file.

Does anyone know of any such code or ability.

Reply | Quote

Replies

It would help if you posted the code that you tried but doesn’t work.

Reply | Quote

Ok…I will provide 2 that I have and neither work.

Function fGetOut() As Integer
Dim RetVal As Integer
Dim db As DAO.Database
Dim rst As Recordset
On Error GoTo Err_fGGO
Set db = DBEngine.Workspaces(0).Databases(0)
Set rst = db.OpenRecordset("qLogEmOff", dbOpenSnapshot)
If rst.EOF And rst.BOF Then
 RetVal = True
 GoTo Exit_fGGO
Else
 If rst!Log = True Then
    For Each v In rst
        
 
  'This is where the forms are closed and quit the database

   Application.Quit
   Next v
 Else
  RetVal = True
 End If
End If
Exit_fGGO:
 fGetOut = RetVal
 Exit Function
Err_fGGO:
 'Note lack of message box on error
 Resume Next
End Function

AND

Private Sub cmdUpdate_Click()
Dim db As Database
Dim rs As Recordset
Dim mySQL As String
Dim myATable As String
Dim qd As DAO.QueryDef
Dim v As Variant
Dim sUser As String
Dim SQL_STM As String

Set db = CurrentDb
'''********************************************************
myATable = "Case Managers Int"

    mySQL = mySQL & " SELECT " & myATable & ".LoginID, "
    mySQL = mySQL & " FROM Case Managers Int"
    mySQL = mySQL & " WHERE (((" & myATable & ".Log)=True));"
'''************************************************************
sUser = GetUser()

SQL_STM = "UPDATE [Case Managers Int] SET Log = TRUE WHERE LoginID = '" + sUser + "'"
Set qd = db.CreateQueryDef("", SQL_STM)
qd.Execute

Set rs = db.OpenRecordset("Case Managers Int", dbOpenDynaset)

rs.Requery
''''''Everything above this line works as expected'''''''''
For Each v In rs
        v.Application.Quit
Next v

Me.Requery

End Sub

Thanks

Reply | Quote

A loop such as

For Each v In rst
Application.Quit
Next v

makes no sense.

Where is the function fGetOut and when is it called? Where is the command button cmdUpdate?

Reply | Quote

Good Afternoon Hans,

Sorry for the delay in my response. Below is the outline of my attempts and the answers to your questions.

fGetOut is a function which is called from an OnClick event on cmdLogUserOff which is in sfUserLog.

Private Sub cmdLogUserOff_Click()
Call fGetOut
End Sub

cmdUpdate code is called from an OnClick Event on (cmdUpdate) which is also in sfUserLog and is used to update a field (LOG–this is a flag field) in the table that stores user information. The top part of code (cmdUpdate) works exactly as expected.

sfUserLog is a sub form to fUserActivity. fUserActivity provides me with the information of every user logged into the db. The user information is captured when a user opens the Main Menu. It captures the login name and time the user logged in and count of users.

As the db administrator/developer, I’m the only one who can open fUserActivity. It’s also here where I have the ability to set the feature to log EVERYONE out.

sfUserLog is the form I’m attempting to set the code to select ONLY A SPECIFIC user or multiple USERS to log off.

My attempt so far is to modify cmdUpdate to apply

application.quit

for each user that I selected. The loop (as you stated doesn’t make sense) is my failed attempts at getting
the modification to work as I had hoped. I’ve also attempted to modify fGETOUT for the same purpose, however, neither do what I wanted, hence my post.

I’ve also attempted the following:

Private Sub cmdUpdate_Click()
Dim db As Database
Dim rs As Recordset
Dim mySQL As String
Dim myATable As String
Dim fld As Field
Dim qd As DAO.QueryDef
Dim v As Variant
Dim sLoginID As String
Dim sLID As String
Dim sUser As String
Dim iLog As Integer
Dim sLogin As String
Dim SQL_STM As String

Set db = CurrentDb
'Set rs = db.OpenRecordset("qLogEmOff", dbOpenDynaset)
'''********************************************************
'myATable = "Case Managers Int"

'    mySQL = mySQL & " SELECT " & myATable & ".LoginID, "
'    mySQL = mySQL & " FROM Case Managers Int"
'    mySQL = mySQL & " WHERE (((" & myATable & ".Log)=True));"
'''************************************************************
sUser = GetUser()

SQL_STM = "UPDATE [Case Managers Int] SET Log = TRUE WHERE LoginID = '" + sUser + "'"
Set qd = db.CreateQueryDef("", SQL_STM)
qd.Execute
Set rs = db.OpenRecordset("Case Managers Int", dbOpenDynaset)

rs.Requery


'For Each fld In rs
'    With rs
       ' v.Application.Quit
'       fld = 1
'    End With
'Next fld

'Debug.Print fld
'''*************************************************************************
'With rs
'    v = DLookup("[LoginID]", "Case Managers Int", "[Log]='" & True & "'")
           
'        For Each v In rs
'            Application.Quit
'        Next
'End With
'''************************************************************************
Me.Requery
End Sub[

But again, nothing. I don’t get an error, but none of my coding attempts work.

Reply | Quote

In which database does the form sfUserlog reside? I get the impression that it is in some kind of “manager” database, not the end user database. But you cannot use Application.Quit in the manager database to log out users from their instance of the end user database. Application.Quit will act on the database *you* are working in!

The idea behind schemes to log off end users is that the end user database contains a form that is kept open (although it may be hidden) with code in the On Timer event. This code checks periodically whether a value in a table in the manager database has changed. If so, the database is closed (perhaps after showing a message first). The value in the table is set by the manager, but the code runs in the end user database.

See LogUsersOff on Roger Carlson’s site – it is a freely downloadable example containing all necessary code.

Reply | Quote

Hans,

I’m incorporating this code in the End Users db with ability by developer to log someone off in the very same manner in which you’ve described. As a matter of fact I am already using that feature (which by the way does use application.quit) What I was hoping to do was to see who was in the db (as an IT manager does) and if for some reason (as an IT manager does) knock that user out and I thought the application.quit would work on ONE person as it does on multiple people as it does in the current LOGEMOFF I’m using.

However, from your response, apparently there isn’t a way to log ONE person out.

As always Hans, thanks for the advice.

Reply | Quote

It is possible to log off one or more users selectively, but I don’t understand how your code works – it looks like a strange mix of “manager” and “end-user” code to me – so I have no idea how I could help. Sorry. Perhaps you could post stripped down and zipped copies of the “manager” and “end-user” databases.

Reply | Quote

The “End User’s db” as you refer to is a fe and be structure and although I disagree, the powers that be insist the fe and be must reside on a shared network drive.

With that said, there isn’t a “Manager” version. Should I need to enter the db for maintenance (Manager) or whatever work I might need to do, I open the front end use the LOGEMOFF.

I have 3 forms that are of course in the FE. The structure of this uses an OPEN but HIDDEN form with a timer event on it. The timer event is triggered if I open fUserActivity and select the check box. Once I select the check box several things happen, a flag is set to TRUE in a table that has 1 field in it. The OnTimer event of the HIDDEN but OPEN form is triggered based on the TRUE value in the table.

Another form is used for a msg that is sent to all users informing them they have 3 minutes to complete their work which has a timer label on it and provides a visual countdown of the 3 minutes they have to complete their work. The check box can also set a flag to true in the tbUserInformation table from the subform “sfUserLog”.

sfUserLog is a subform to fUserActivity. It’s here where I’m making a feable attempt to LOG ONE or MORE USERs off.

Currently at this time, this entire process works as it should in the working db. The code I published in my post is a combination of the code that works as it should in the working db and my feable attempts to achieve LOG ONE USER off.

What I wanted to do was take the existing code a step further. It’s this peice that I don’t know how to code. If it looks like a strange mix of THIS and THAT, that’s because that’s what it is because I don’t know how to adapt the “LOG ONE USER OFF”, which is why I made the post. The step I want to add to the code is as follows:

Using sfUserLog is bound to a table (tbUserLog) that is updated by this process:

Public Sub UserLogin()
    Dim userName As String
    userName = Environ("Username")
    
  
    Dim objCommand As ADODB.Command
    Set objCommand = New ADODB.Command
    Dim strSQL As String
    
    strSQL = "INSERT INTO tbUSERLOG (userID,dateEntered) VALUES" & _
             "('" & Environ("Username") & "',#" & Now() & "#)"
             
    'save userEntered so userExit can be updated to the right record
    'this will be used to query the right record when the user exits
    DateEntered = Now()
        
    Set objCommand.ActiveConnection = CurrentProject.Connection
    objCommand.CommandText = strSQL
    objCommand.Execute
    
    Set objCommand = Nothing

End Sub

which updates tbUserLog with who is logged in, what time they logged in and a YES/NO field. Another table, tbUserInformation has the user’s loginID and a YES/NO field. Once the YES/NO field from sfUserLog is set to true the code cmdUpdate runs and sets the YES/NO field in tbUserInformation to true.

The YES/NO field from tbUserInformation is what I was hoping to use to log a specific user out . Hoping it would work along this line.

If the flag in tbUserInformation is TRUE, lookup that persons loginID and log that person out.

Everything works up to the point of “If the flag in tbUserInformation is TRUE, lookup that persons loginID and log that person out.”

Timers are set as they should, flags are set to TRUE as they should, msg forms appear as they should, countdown of 3 minutes works as it should, and after the 3 minutes, everyone is logged out, as they should be. What doesn’t work is “If the flag in tbUserInformation is TRUE, lookup that persons loginID and log that person out.” which is what appears to be a Mix of this and that. Which is what it is because that’s where I began having problems and sent a post.

I would love to provide you with a stripped down version, but this db is far to big to strip down to the size limits of this site. I truely don’t believe I can get it that small.

As always, your valued input is greatly appreciated.

Reply | Quote

Could you please post the code without the modifications that you tried to apply?
I’d need the code that YOU use to set the logoff into motion, and the code in the On Timer event of the hidden form. Both in their original, working versions without any later additions.
Thanks in advance.

Reply | Quote

Step 1:]
From a command button on Main Menu which opens fUserActivity

On Error Resume Next
        Set objPopup = CommandBars("Maintenance")
        objPopup.ShowPopup
        Set objPopup = Nothing
    If IsLoaded("Main Menu") Then Forms![Main Menu].Visible = False

Step 2:
From fUserActivity

Option Compare Database
Option Explicit

Private Sub cmdClose_Click()
On Error GoTo Err_Handler
If IsLoaded("Main Menu") Then Forms![Main Menu].Visible = True
   DoCmd.Restore
   DoCmd.Close acForm, Me.Name
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error"
   Resume Exit_Here
End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Handler

   DoCmd.OpenForm "frmLogoutTimer", , , , , acHidden
   
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error"
   Resume Exit_Here
End Sub

Private Sub chkLogOutAllUsers_AfterUpdate()
On Error GoTo Err_Handler
   DoCmd.RunCommand acCmdSaveRecord
   Call LogEmOff
   Me.cmdClose.SetFocus
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error"
   Resume Exit_Here
End Sub

Call LogEmOff[indent]

---

Public Sub LogEmOff()
Dim db As Database
Dim rs As Recordset

Dim stDocName As String
Dim stLinkCriteria As String

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

rs.MoveFirst
If rs.Fields(1).Value = -1 Then
    stDocName = "frmLogoutStatus"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
End If
End Sub

---

[/indent]
Step 3–HIDDEN FORM:
From frmLogoutTimer

Option Compare Database
Option Explicit

Private Sub cmdHide_Click()
   On Error GoTo Err_Handler
   Me.Visible = False
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error"
   Resume Exit_Here
End Sub

Private Sub cmdRunNow_Click()
   On Error GoTo Err_Handler
   Me.TimerInterval = 9000
   DoCmd.OpenForm "frmLogoutStatus"
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error"
   Resume Exit_Here
End Sub

Private Sub Form_Timer()
   On Error GoTo Err_Handler
   
   Dim fLogout As Boolean
   
   fLogout = DLookup("[LogOutAllUsers]", "[tblVersionServer]")
   If fLogout = True Then
      Me.TimerInterval = 9000
      DoCmd.OpenForm "frmLogoutStatus"
   Else
      Me.TimerInterval = 30000
      If IsLoaded("frmLogoutStatus") Then
         DoCmd.Close acForm, "frmLogoutStatus"
      End If
   End If
   
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error"
   Resume Exit_Here
End Sub

Public Function IsLoaded(ByVal sFormName As String) As Boolean
   On Error GoTo Err_Handler
   
   Const conObjStateClosed = 0
   Const conDesignView = 0
   
   If SysCmd(acSysCmdGetObjectState, acForm, sFormName)  conObjStateClosed Then
      If Forms(sFormName).CurrentView  conDesignView Then
         IsLoaded = True
      End If
   End If
   
Exit_Here:
   Exit Function
Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error"
   Resume Exit_Here
End Function

Step 4:
Using the MSG FORM (frmLogoutStatus)

Option Compare Database
Option Explicit

Private mdat_StartCountdownTime As Date

Private Sub cmdOK_Click()
   On Error GoTo Err_Handler
   Me.Visible = False
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error"
   Resume Exit_Here
End Sub

Private Sub Form_Open(iCancel As Integer)
   On Error GoTo Err_Handler
   mdat_StartCountdownTime = DateAdd("n", 3, Now())
   Me!txtMinsSecs = " 3  minutes and  0  seconds "
   
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error"
   Resume Exit_Here
End Sub

Private Sub Form_Timer()
   On Error GoTo Err_Handler
   
   Dim intIMins As Integer
   Dim intISecs As Integer
   Dim fLogout As Boolean
   
   fLogout = DLookup("[LogOutAllUsers]", "[tblVersionServer]")
   
   If fLogout = False Then DoCmd.Close acForm, Me.Name
   
   intIMins = DateDiff("s", Now(), mdat_StartCountdownTime)  60
   intISecs = DateDiff("s", Now(), DateAdd("n", (intIMins * -1), mdat_StartCountdownTime))
   
   If intIMins = 2 And intISecs = 0 Then
      Me.Visible = True
   End If
   If intIMins = 1 And intISecs = 0 Then
      Me.Visible = True
   End If
   If intIMins = 0 And intISecs = 20 Then
      Me.Visible = True
   End If
   
   If intIMins <= 0 And intISecs <= 0 Then
      Application.Quit
   Else
      On Error Resume Next
      Me.txtMinsSecs = " " & intIMins & "  minutes and  " & intISecs & "  seconds "
   End If
Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error"
   Resume Exit_Here
End Sub

Step 5
Using the subform sfUserLog
This is where I’m I want to incorporate code that I’ve outlined in my prior posts (but not including in this post) to log a user off .
Call fLOGOUT and the Public Sub “cmdUpdate”

Reply | Quote

Ok, let’s try to see how it works.

You have to keep in mind that this solution uses two entirely separate sets of forms:

• You, as “admin” or “manager” or whatever you want to call it, use fUserActivity to indicate that you want to log off users. You do this by toggling a check box which is bound to a Yes/No field LogOutAllUsers in the table tblVersionServer.
  Note that the instruction Application.Quit does not occur anywhere in the code behind fUserActivity.

• Each end user has the form frmLogoutTimer open (but hidden). The On Timer event procedure of this form checks periodically whether the field LogOutAllUsers in the table tblVersionServer has become Yes, and if so, it displays another form: frmLogoutStatus. This form also has an On Timer event procedure, and it is this procedure that eventually executes Application.Quit.
  The On Timer event procedures use DLookup to check the value of LogOutAllUsers in tblVersionServer.
  [/list]It is important to note that Application.Quit is executed by code running in the end user’s instance of the database, not in your instance.

  As noted, the On Timer event procedures use DLookup to check the value of LogOutAllUsers in tblVersionServer. This is a single Yes/No value – if it is Yes, users will be logged off, if it is No nothing happens.
  We have to change this so that the DLookup returns a value that applies to the current user instead of all users. We will look at that in the next reply.

Reply | Quote

Now let’s try to make it possible to log off individual users.

If I understand correctly, you have a subform sfUserLog bound to the table Case Managers Int. This table has a text field LoginID containing the user name and a Yes/No field Log.
All you need to do as database admin is to tick the check box bound to Log for each user you want to kick out. Leave the check box clear for all other users. As soon as you move to another record in the subform, the value of Log will automatically be saved, and the code in the On Timer event of the form in the end user’s database will pick up the change and act accordingly.

We must modify the On Timer event procedures to take the value of Log for the individual user into account. The lookup is currently

Dim fLogout As Boolean
fLogout = DLookup("[LogOutAllUsers]", "[tblVersionServer]")
If fLogout = True Then
...

These lines occur in both On Timer event procedures. We’re going to extend this code:

Dim fLogout As Boolean
Dim fLogout1 As Boolean
' Log out all users?
fLogout = DLookup("[LogOutAllUsers]", "[tblVersionServer]")
' Log out this user?
fLogout1 = DLookup("[Log]", "[Case Managers Int]", "[LoginID] = " & Chr(34) & Environ("Username") & Chr(34))
' If either is true, show message form
If fLogout = True Or fLogout1 = True Then
...

Don’t forget to apply this change in both On Timer event procedures.
This code will allow you to log off all users at once, using the check box on the main form, or to log off individual users, using the Log field in the subform.

Reply | Quote

Hans,

Thanks very much for your patience and help, it works brilliantly.

FYI: Your final analysis of the db structure was almost 100%. Slight difference was that I DON’T have a separate db.

The db I use is the FE that everyone else uses (end user). However, it was that thinking that sent me in another direction, which was to have a different db than the end users. By linking in the tables to MY db, I can do almost everything I need to do. Your insight has been most helpful.

Thanks,

Reply | Quote