Solved Form_Unload Event (1 Viewer)

evictme

Registered User.
Local time
Today, 06:03
Joined
May 18, 2011
Messages
168
Hello All,

Back at it with a little syntax situation. I've been trying to prevent folks from closing the database by simply clicking the "X" in order to avoid any issues with events and VBA coding.

I have a form that loads hidden whenever they log in and have place this code (see below) to trigger on the Unload Event. Initially, I wanted it to cancel the unload process, [ cancel = true] but I wanted to give a message that will tell folks whats going on. It will do nothing, which is fine, but knowing my users theres going to be a bit of chaos. So, I thought to add a message whenever the event happens. and Of course it works but It also gives me the message when I close the database correctly (using the button on the main form).

I've gotten the code to work correctly, to an extent: Click the X, get prompt, do nothing. Use close button, get prompt, close database correctly.

I want it to avoid the prompt if the close button has been used/pressed but for some reason it is NOT catching that button click. it will give me the prompt and then close the database. Ive tried using the EXIT sub and the call Close before the else.

Please take a look, any suggestions or corrections are much appreciated. THank you.

Private Sub Form_Unload(Cancel As Integer)

If CurrentUser() <> "whoever" And DCount("*", "tblCurrentlyLoggedIn", "empCurrentlyLoggedIn=" & Chr(34) & CurrentUser() & Chr(34)) > 0 And DCount("*", "tblCurrentlyLoggedIn", "empEnviron=" & Chr(34) & Environ("computername") & Chr(34)) > 0 And CurrentProject.AllForms("EAA-Database-Payroll").IsLoaded = True And [Forms]![EAA-Database-Payroll].[Command195].OnClick = True Then
Exit Sub
Else
Cancel = True
MsgBox CurrentUser() & _
vbCrLf & " " & _
vbCrLf & "Please close the database using the Log-Off button.", vbExclamation, "Attention."
End If

End Sub
 

bob fitz

AWF VIP
Local time
Today, 11:03
Joined
May 23, 2011
Messages
4,717
Create a boolean variable.

set its value when the correct Quit button is pressed.

Check its value in the forms unload event
 

evictme

Registered User.
Local time
Today, 06:03
Joined
May 18, 2011
Messages
168
Create a boolean variable.

set its value when the correct Quit button is pressed.

Check its value in the forms unload event

I am not too familiar with boolean variables. Any chance you know where I can look for samples or if you got an example as to where I can start?
 

Isaac

Lifelong Learner
Local time
Today, 04:03
Joined
Mar 14, 2017
Messages
8,738
@evictme
Why not just disable the close (red x) entirely, so users have no choice but to use your programmed button? I think that would be a common approach
 

evictme

Registered User.
Local time
Today, 06:03
Joined
May 18, 2011
Messages
168
I've done that for the switchboard form but not too sure how I would do that for the Access program itself. Thats the reason I went with the cancel=true route. Im working on coding the boolean but might just leave it as is.
 

Isaac

Lifelong Learner
Local time
Today, 04:03
Joined
Mar 14, 2017
Messages
8,738
I've done that for the switchboard form but not too sure how I would do that for the Access program itself. Thats the reason I went with the cancel=true route. Im working on coding the boolean but might just leave it as is.
You're letting your Access end users access to the Access Shell window? I wouldn't do that. You should have one underlying Main form, with the close button disabled, and a close button programmed to both close any open forms and quit the application.
 

evictme

Registered User.
Local time
Today, 06:03
Joined
May 18, 2011
Messages
168
You're letting your Access end users access to the Access Shell window? I wouldn't do that. You should have one underlying Main form, with the close button disabled, and a close button programmed to both close any open forms and quit the application.
I do have a main form and it has a close button that is programmed to quit, saveall. This is the main thing they see once they log in. However, it is within the Access App (Im guessing this is what you mean by Access Shell Window?) they dont have access to anything other than the Main Form (unless they use the shift bypass, which they dont). Is there a way to have the Main form outside of the Access Shell window?
 

Isaac

Lifelong Learner
Local time
Today, 04:03
Joined
Mar 14, 2017
Messages
8,738
I do have a main form and it has a close button that is programmed to quit, saveall. This is the main thing they see once they log in. However, it is within the Access App (Im guessing this is what you mean by Access Shell Window?) they dont have access to anything other than the Main Form (unless they use the shift bypass, which they dont). Is there a way to have the Main form outside of the Access Shell window?
There are ways to just get rid of the shell completely.
Another option is to have your main form be modally displayed. This way no one can click the background Access/shell window.
 

bastanu

AWF VIP
Local time
Today, 04:03
Joined
Apr 13, 2010
Messages
1,401
In a standard module declare a Public variable:

Public boOKToClose as boolean 'the default value is False

In the click event of the Close button set it to True before attempting to call the Quit method:

boOKToClose =True
Application.Quit

Finally in the Unload event check the value before displaying the promt:

If boOKToCLose = False Then
Msgbox....'your existing prompt
End If

I think this is what Bob was suggesting, just giving you a bit more info.

Cheers,
 

evictme

Registered User.
Local time
Today, 06:03
Joined
May 18, 2011
Messages
168
Thank you all! I've incorporated that boolean code and have actually started working on shifting my project to outside the Access Shell. This has been a fun project .
 

evictme

Registered User.
Local time
Today, 06:03
Joined
May 18, 2011
Messages
168
In a standard module declare a Public variable:

Public boOKToClose as boolean 'the default value is False

In the click event of the Close button set it to True before attempting to call the Quit method:

boOKToClose =True
Application.Quit

Finally in the Unload event check the value before displaying the promt:

If boOKToCLose = False Then
Msgbox....'your existing prompt
End If

I think this is what Bob was suggesting, just giving you a bit more info.

Cheers,
Thank you for this! What do you mean by "In a standard module?" I have placed it at the top of all the subs for the Form but I am still getting the msgbox firing whether the user clicks the log off button or not. Any ideas?

Log off button:
Private Sub Command195_Click()

ExitButtonClick = True

CurrentDb.Execute "delete * from tblCurrentlyLoggedIn where empCurrentlyLoggedIn=CurrentUser() and empEnviron='" & Environ("computername") & "'", dbFailOnError

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

rs.AddNew

rs![ButtonClick] = "Logged-Off (Button)"
rs![ClickTime] = Time()
rs![ClickDate] = Date
rs![User] = CurrentUser()
rs![SearchInput] = Environ("Computername")

rs.Update
rs.Close

Set rs = Nothing
boOKToClose = True
Application.Quit

End Sub

Form Unload:

Private Sub Form_Unload(Cancel As Integer)

If CurrentUser() = "usery" Then
DoCmd.OpenForm "EAA-Database-Payroll"
End If


If boOKToClose = False Then
If CurrentUser() = "userx" Then

Cancel = True

MsgBox CurrentUser() & _
vbCrLf & " " & _
vbCrLf & " Use Log Off Button to close. " & _
vbCrLf & " " & _
vbCrLf & "Please use the log off button on the Main Database page to log off or close.", vbExclamation, "Stop, you cannot close the database this way."

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

rs.AddNew

rs![ButtonClick] = "Bad Close Attempted"
rs![ClickTime] = Time()
rs![ClickDate] = Date
rs![User] = CurrentUser()

rs.Update
rs.Close

Set rs = Nothing

DoCmd.RunMacro "mcrHide"

DoCmd.OpenForm "EAA - Database", acNormal

End If
End If

End Sub
 

Isaac

Lifelong Learner
Local time
Today, 04:03
Joined
Mar 14, 2017
Messages
8,738
Thank you for this! What do you mean by "In a standard module?" I have placed it at the top of all the subs for the Form
What Vlad meant was the opposite of what you are doing.
A "standard module" means one that was created by Insert > Module.
Sounds like you have put it in a Form's class module, which is not what was suggested.
 

evictme

Registered User.
Local time
Today, 06:03
Joined
May 18, 2011
Messages
168
What Vlad meant was the opposite of what you are doing.
A "standard module" means one that was created by Insert > Module.
Sounds like you have put it in a Form's class module, which is not what was suggested.

I have put it in one of my standard modules. Do I need to reference/call it on the Form code somewhere aside from using the boolean name when checking in the unload event?

I keep getting the same error
 

bob fitz

AWF VIP
Local time
Today, 11:03
Joined
May 23, 2011
Messages
4,717
I think it would be best for you to post a copy of your db
 

evictme

Registered User.
Local time
Today, 06:03
Joined
May 18, 2011
Messages
168
Found a workaround for the issue -

Created a blank form that opens as Hidden when the log off button is clicked. The unload event then checks for this form. Works like a charm.

Thanks for the help! I am in the middle of cleaning up a lot of the code in this database and all the help I get from here is greatly appreciated and applied diligently.

Thanks all.
 

Users who are viewing this thread

Top Bottom