Onload - Dlookup? (1 Viewer)

[evictme]

Hello All,

I am working on the On_Load event of a "Logged In" Form i have. It checks to make sure that they are not already logged-in. However, at times, if the dbase crashes it will not remove them from the table of logged in users. When this happens, the user has to contact me to request I "let them back in."

What would be the best way to address this automatically? I was thinking about a Dlookup for the table which keeps track of button clicks (also catches errors) that checks to see if a specific error exists for that user for today.

How would the syntax look if I want it to check the [ButtonClicks] Table for Currentuser() in the [User] Field and "Error 2501" in [ButtonClick] Field and make sure that the error is from today's date. [ClickDate] Field

I've tried dlookup and dlast and dcount but Im not sure I have the syntax right on any of them. Any advice?

Thank you

 

[Minty]

There is no easy way around this short of clearing down the fact they are logged in automatically.
What is the perceived issue with them logging in twice?

 

[evictme]

There is no easy way around this short of clearing down the fact they are logged in automatically.
What is the perceived issue with them logging in twice?

Its not a perceived issue, it is an actual, historical issue with users opening multiple instances of the database at once. It is also an additional way for me to catch and look into any errors that have caused crashing, if any.

 

[Isaac]

How about this:

When the database starts up, run some code that sees whether or not they are already still marked as "logged in" from a previous run. (I'm assuming you're using their logged-in username to prompt all of this which would make sense and is highly recommended IMO, rather than rolling your own username/password structure)

If it is true that, at the time they open the database, your logged in users table is still showing them as logged in, then you can assume this is an error, due to someone Ending Task on Access, crashing, etc - and your code can automatically log them out at that point--before any further code continues.
Just separate the two. Create code that checks if they are logged in and clears that, paving the way for the next code which lets them log in, however you might be doing that.

This seems easy enough or have I missed something

 

[evictme]

How about this:

When the database starts up, run some code that sees whether or not they are already still marked as "logged in" from a previous run. (I'm assuming you're using their logged-in username to prompt all of this which would make sense and is highly recommended IMO, rather than rolling your own username/password structure)

If it is true that, at the time they open the database, your logged in users table is still showing them as logged in, then you can assume this is an error, due to someone Ending Task on Access, crashing, etc - and your code can automatically log them out at that point--before any further code continues.
Just separate the two. Create code that checks if they are logged in and clears that, paving the way for the next code which lets them log in, however you might be doing that.

This seems easy enough or have I missed something

Thanks Isaac. Yes, all of the above. Currently, if they are logged-in they will receive a prompt to "Log-out from their current instance of the database" The multiple instances issue is for various reasons but above all because we have users who use several terminals, sometimes at once.

Lately, i have been able to fix errors in coding down to practically non-existent but I am noticing that if the DB crashes it will produce Error 2501. This has been consistent for that past week or so...I want to include in the current code a dlookup (or whatever fits) to check for this error, on top of everything else.



This is what I have so far ....I know I am missing something but not sure what or where

DLookup("User", "ButtonClicks", "User = '" & Chr(34) & CurrentUser() & Chr(34) "'" _
             & " AND ButtonClick = '" & "Error 2501" & "'" _
            & " AND ClickDate =#" & Date() & "#")

This is the full code I have in logged-in on_load:

On Error GoTo Err_Form_Load

DoCmd.SetWarnings False
If CurrentUser() <> "Admin" And DCount("*", "tblCurrentlyLoggedIn", "empCurrentlyLoggedIn=" & Chr(34) & CurrentUser() & Chr(34)) > 0 And DCount("*", "tblCurrentlyLoggedIn", "empEnviron=" & Chr(34) & Environ("computername") & Chr(34)) > 0 Then

DoCmd.Close acForm, "Splash", acSaveNo

DoCmd.RunMacro "mcrHide"
DoCmd.OpenForm "uhoh", acNormal, , , , acWindowNormal
DoCmd.OpenForm "frmWarning", acNormal, , , , acHidden

DoCmd.SetWarnings True

Else
CurrentDb.Execute "Insert Into tblCurrentlyLoggedin (empCurrentlyLoggedIn,empEnviron,popup) " _
                & " values ('" & CurrentUser() & "','" & Environ("computername") & "','" & -1 & "')", dbFailOnError


Set rs = CurrentDb.OpenRecordset("SELECT * FROM ButtonClicks")

rs.AddNew

rs![ButtonClick] = "Logged-In"
rs![ClickTime] = Time()
rs![ClickDate] = Date
rs![User] = CurrentUser()
rs![SearchInput] = Environ("Computername") & " " & DLast("Version", "qryVersion")

rs.Update
rs.Close

Set rs = Nothing
Else
If CurrentUser() = "Admin" Then
CurrentDb.Execute "Insert Into tblCurrentlyLoggedin (empCurrentlyLoggedIn,empEnviron) " _
                & " values ('" & CurrentUser() & "','" & Environ("computername") & "')", dbFailOnError



Set rs = CurrentDb.OpenRecordset("SELECT * FROM ButtonClicks")

rs.AddNew

rs![ButtonClick] = "Logged-In"
rs![ClickTime] = Time()
rs![ClickDate] = Date
rs![User] = CurrentUser()
rs![SearchInput] = Environ("Computername") & " " & DLast("Version", "qryVersion")

rs.Update
rs.Close

Set rs = Nothing



End If
End If

Exit_Form_Load:
     Exit Sub
Err_Form_Load:
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM ButtonClicks")

    rs.AddNew

    rs![ButtonClick] = "Error " & Err.Number
    rs![ClickTime] = Time()
    rs![ClickDate] = Date
    rs![User] = CurrentUser()
    rs![SearchInput] = Err.Description

    rs.Update
    rs.Close

    Set rs = Nothing
Resume Exit_Form_Load

 

[Isaac]

Lately, i have been able to fix errors in coding down to practically non-existent but I am noticing that if the DB crashes it will produce Error 2501.

What is the error description and when precisely do you mean that this happens? At the time of the crash? When someone says Crash, in this context, I imagine a total system crashed, like "this application cannot continue and will be shut down", or, the app simply disappearing, or, someone clicking End Task or End Process in Windows. Is that what you are talking about? If yes, how do you log any errors that occur at this time - and how can you even know such an error exists, if not within the context of a regular runtime?

 

[evictme]

What is the error description and when precisely do you mean that this happens? At the time of the crash? When someone says Crash, in this context, I imagine a total system crashed, like "this application cannot continue and will be shut down", or, the app simply disappearing, or, someone clicking End Task or End Process in Windows. Is that what you are talking about? If yes, how do you log any errors that occur at this time - and how can you even know such an error exists, if not within the context of a regular runtime?

By "Crash" i mean the application crashes and disappears completely. Just as if we were to End the task with task manager. I may be correlating two things that are not connected but everytime the Access App crashes for the user, requiring them to log-in again, an error will be captured just a moment before it closes for the user (i am assuming before or at the time of) - "Error 2501 -The Close Action was canceled".

Regardless of where the error comes from, I want to use this as a criteria and if the criteria exists THEN allow them to log-in unimpeded.

 

[Isaac]

I see, thanks for the explanation. So I am guessing that the 2501 is probably superfluous to the crash, and only occurring because some code can't run because it's crashing, rather than 2501 having caused the crash (just my guess).

I would lean towards using the logic "if they log in, and they're already logged in, then it must have crashed" (rather than checking to see if you have recently captured a 2501 error) - but are you saying you can't do that because you allow concurrent logins of the same person on different PC's?

 

[evictme]

I see, thanks for the explanation. So I am guessing that the 2501 is probably superfluous to the crash, and only occurring because some code can't run because it's crashing, rather than 2501 having caused the crash (just my guess).

I would lean towards using the logic "if they log in, and they're already logged in, then it must have crashed" (rather than checking to see if you have recently captured a 2501 error) - but are you saying you can't do that because you allow concurrent logins of the same person on different PC's?

Yes. Im pretty sure that error is not the cause of the crash but it is a part of the events when it does crash.

And Yes, they use multiple terminals at once. This doesnt happen very often. Im more worried about the situations when they are already logged-in with the database running correctly, they just have it minimized or hidden and attempt to open an additional instance of the database. This situation happened often before and I know without the log-in checks they would have many instances of the dbase open.

 

[Minty]

You can log the name of the windows machine they are using, would that help.
You can also check for another instance running on the same machine, which might also assist with determining if they are already "in" the system?

 

[evictme]

You can log the name of the windows machine they are using, would that help.
You can also check for another instance running on the same machine, which might also assist with determining if they are already "in" the system?

I do check the windows machine name on login with a dcount (Code is available in earlier reply).
I havent tried checking of another instance is running on the same machine, I tried it before but was never able to find the syntax for that.

 

[Minty]

This code will check that a database is open or not, what I haven't checked is if it works for the database you are in.

Function IsDatabaseRunning(strDBName As String) As Boolean
    '   Function to check if a database is already running
    '   Accepts:
    '       The path and name of an Access database
    '   Returns:
    '       True if the database can't be opened (because it is already open)
    '       False if the database can be opened (because it is not already open)
    On Error GoTo E_Handle
    IsDatabaseRunning = True
    Dim db      As Database
    Set db = DBEngine(0).OpenDatabase(strDBName, True)
    IsDatabaseRunning = False
fExit:
    On Error Resume Next
    db.Close
    Set db = Nothing
    Exit Function
E_Handle:
    Select Case Err.Number
        Case 3704                                ' Database already opened
        Case 3045                                ' Database already open as well
        Case Else
            MsgBox "E_Handle  " & Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    End Select
    Resume fExit
End Function

Maybe the answer is to use a "Launcher" db that can do all the checks you need then opens the real database?

 

[Gasman]

I do check the windows machine name on login with a dcount (Code is available in earlier reply).
I havent tried checking of another instance is running on the same machine, I tried it before but was never able to find the syntax for that.

I would have thought that you could cycle through all the open windows and see if more than one exists for the DB?

You do need to get the window caption correct though.?

Here is a link to where I was trying to help someone find a window, and a link to code that I used for a brief test. You would just need to count the occurrences of your DB, or save the window handle of the DB you are in and see if another exists?

https://www.access-programmers.co.u...ocus-on-current-database.315711/#post-1742648

Will not help, if they use another computer at the same time though.

 

[arnelgp]

you can also Check if there is an Instance of the db Already running:

    With GetObject(CurrentProject.FullName)
        If .hWndAccessApp <> Application.hWndAccessApp Then
            MsgBox "Another instance of this db is already running"
            'close this db?
            'DoCmd.Quit
        End If
    End With