Disable Close Button

[Jgr4ng3]

Hello

I have a Time Management database which the users keep closing without running a specific query by mistake. In an effort to work around this I added a the query to the 'On-close' event of the main form, however this cannot be used as some times they need to close the db without running this query.

I have therefore instead opted to disabling the close button. I followed this guide (support.microsoft.com/kb/300688) using a test database however when I try to run the code I am presented with the following error...

Compile error:

The code in this project must be updated for use on 64-bit systems.
Please review and update Declare statements and then mark them with the PtrSafe attribute.

Now I'm not an expert with VBA, but I know you all are so though I'd request your assistance .

Cheers!

 

[spikepl]

So sometimes, before the db is closed, the users indicate that the query is not to be run, and some times they don't. How ? Specify how, and then get help to tackle the two logical paths, instad of adding complexity and weirdness to your db by disabling the close button.

 

[Jgr4ng3]

So sometimes, before the db is closed, the users indicate that the query is not to be run, and some times they don't. How ? Specify how, and then get help to tackle the two logical paths, instad of adding complexity and weirdness to your db by disabling the close button.

Basically I have a button they can click to close it without running the query, and a button they can click to run the query and close. When they are supposed to be click the button to run query and close, they are just pressing the close X.

I don't see what else I can do but any advise or suggestions are appreciated.

 

[pr2-eugin]

Hello Jgr4ng3, by the looks of the Error, you are using the 64-bit version of Access.. So you need to make sure that they are declared as PtrSafe Functions.. The cvode you copied, calls for the library functions GetSysteMenu and EnableMenuItem, they just needs to be declared as PtrSafe and returned LongLong.. So in the place of the Declaring functions paste this..

[COLOR=Green]'Declare PtrSafe Function FunctionName Lib "kernel32" () As LongLong[/COLOR]
Private Declare [COLOR=Red][B]PtrSafe[/B][/COLOR] Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, _
ByVal bRevert As Long) As [COLOR=Red][B]LongLong[/B][/COLOR]

Private Declare [COLOR=Red][B]PtrSafe[/B][/COLOR] Function EnableMenuItem Lib "user32" (ByVal hMenu As _
Long, ByVal wIDEnableItem As Long, ByVal wEnable As Long) As [COLOR=Red][B]LongLong[/B][/COLOR]

That should fix the problem you are facing..

 

[Jgr4ng3]

Hello Jgr4ng3, by the looks of the Error, you are using the 64-bit version of Access.. So you need to make sure that they are declared as PtrSafe Functions.. The cvode you copied, calls for the library functions GetSysteMenu and EnableMenuItem, they just needs to be declared as PtrSafe and returned LongLong.. So in the place of the Declaring functions paste this..

[COLOR=green]'Declare PtrSafe Function FunctionName Lib "kernel32" () As LongLong[/COLOR]
Private Declare [COLOR=red][B]PtrSafe[/B][/COLOR] Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, _
ByVal bRevert As Long) As [COLOR=red][B]LongLong[/B][/COLOR]
 
Private Declare [COLOR=red][B]PtrSafe[/B][/COLOR] Function EnableMenuItem Lib "user32" (ByVal hMenu As _
Long, ByVal wIDEnableItem As Long, ByVal wEnable As Long) As [COLOR=red][B]LongLong[/B][/COLOR]

That should fix the problem you are facing..

So just tried this, now I'm getting a Type Mismatch Error at

Sub CloseButtonState(boolClose As Boolean)

Thanks Microsoft...!

 

[pr2-eugin]

What is the code you are trying to Pass to the procedure? At what point are you getting this error?

Make sure it is either True or False not "True" or "False".. It requires a Boolean.. Not String..

 

[Jgr4ng3]

What is the code you are trying to Pass to the procedure? At what point are you getting this error?

Make sure it is either True or False not "True" or "False".. It requires a Boolean.. Not String..

This part... Sorry, I'm not great with VB...

'Disable the Close Button Option
Sub CloseButtonState(boolClose As Boolean)
Dim hWnd As Long
Dim wFlags As Long
Dim hMenu As Long
Dim result As Long

hWnd = Application.hWndAccessApp
hMenu = GetSystemMenu(hWnd, 0)
If Not boolClose Then
wFlags = MF_BYCOMMAND Or MF_GRAYED
Else
wFlags = MF_BYCOMMAND And Not MF_GRAYED
End If

result = EnableMenuItem(hMenu, SC_CLOSE, wFlags)
End Sub

 

[pr2-eugin]

No, that is the code from the website, I am asking the code inside the button click.. Also are you getting the Type mismatch error when you compile or click the button?

 

[spikepl]

I am sorry but I still do not see any need to disable the application's close button.

Basically I have a button they can click to close it without running the query, and a button they can click to run the query and close. When they are supposed to be click the button to run query and close, they are just pressing the close X.

So? "They are supposed to" presumably means that there is some condition in the data that says that the query should run. So it can be run from the CLose-event of some main form which runs if the user clicks on the X-close button.

The proper way to do such things is to internally test for outstanding issues, and if there are any, then ask the user what to do.

 

[Jgr4ng3]

I am sorry but I still do not see any need to disable the application's close button.

So? "They are supposed to" presumably means that there is some condition in the data that says that the query should run. So it can be run from the CLose-event of some main form which runs if the user clicks on the X-close button.

The proper way to do such things is to internally test for outstanding issues, and if there are any, then ask the user what to do.

I had considered this option, however in the instances where they need to close the programme without running the query, it is no longer possible.

 

[pr2-eugin]

The other way I would go around this problem is, create a hidden form (a form that would be opened as acHidden using an AutoExec Macro) that on its open method set a Global flag stopClose as True, and use that flag to prevent closing the application (on the Form_Unload event of the hidden form).. If the user has to run the Query using the button set the flag to False in the OnClick event of the button.. This will, avoid the problem of disabling the close button on Access window.. I have put up a small sample.. Check it out..

 

[Jgr4ng3]

The other way I would go around this problem is, create a hidden form (a form that would be opened as acHidden using an AutoExec Macro) that on its open method set a Global flag stopClose as True, and use that flag to prevent closing the application (on the Form_Unload event of the hidden form).. If the user has to run the Query using the button set the flag to False in the OnClick event of the button.. This will, avoid the problem of disabling the close button on Access window.. I have put up a small sample.. Check it out..

I think this works perfectly... I will dismantle your test DB shortly and see if I can replicate it. Will keep you updated..

 

[shabbaranks]

I too had a simular issue, I had some code to check if users had entered data and not saved properly but they still seemed to close the database without checking what they had done first. So I opted for this

'Here
Private Sub Form_Unload(Cancel As Integer)
'Cancel = Not mblnClose
End Sub

They can only exit using a button I created

 

[Jgr4ng3]

Almost there guys, almost there!

So, it works. But... I get a Runtime Error when running the Macro. Code below...

Private Sub Command2_Click()
    blockClose = False
    DoCmd.OpenQuery "End Shift", acViewNormal, acReadOnly
    DoCmd.OpenQuery "End Client", acViewNormal, acEdit
    DoCmd.RunCommand acCmdCloseDatabase
End Sub

Error:

Execution of this application has stopped due to a run-time error.

The application cant continue and will be shut down.

:banghead:

 

[pr2-eugin]

Hello Jgr4ng3,

Why is that you are shutting down the Access application? The main idea was the prevent closing of the DB..

 

[Jgr4ng3]

Hello Jgr4ng3,

Why is that you are shutting down the Access application? The main idea was the prevent closing of the DB..

Yes, they are successfully prevented from closing the application UNLESS they use this button to do so (as it runs a query that updates one of my tables first).

 

[David R]

I read this thread the other day, and still think it's swatting a fly with a sledgehammer. Why are we not fixing the process instead of tweaking the code cludge?? Read spikepl's replies again.

 

[Jgr4ng3]

I read this thread the other day, and still think it's swatting a fly with a sledgehammer. Why are we not fixing the process instead of tweaking the code cludge?? Read spikepl's replies again.

I really dont know how the process could be tweaked to improve this. There is one button that closes and runs a query, and one button that doesnt. They should always, at the end of their shift, use the button that runs the query and closes the DB. However they are using the close button.

I'm happy with the solution and it is pretty much working perfectly, except for this error.

Any other suggestions on how this issue could be avoided would be greatly appreciated as at present, I'm out of ideas.

 

[David R]

You can tie the running of the query TO the close function of your main form (or hidden form, as pr2-eugin says). Then when the form shuts down, the query runs, and bob's your uncle. Only a force-close will stop that, and you won't be able to dismantle that in any case.

If you've posted the situation by which they will not need to run the query I missed it. Could they run it earlier in the shift maybe? Then you can create a little log table for 'user ran this query at DD:MM:YYYY HH:mm:ss' and check to see if it has an entry for today/the last 30 minutes/whatever, and skip the autorun of the query if true.

 

[Jgr4ng3]

You can tie the running of the query TO the close function of your main form (or hidden form, as pr2-eugin says). Then when the form shuts down, the query runs, and bob's your uncle. Only a force-close will stop that, and you won't be able to dismantle that in any case.

If you've posted the situation by which they will not need to run the query I missed it. Could they run it earlier in the shift maybe? Then you can create a little log table for 'user ran this query at DD:MM:YYYY HH:mm:ss' and check to see if it has an entry for today/the last 30 minutes/whatever, and skip the autorun of the query if true.

Unfortunately this wouldnt work as, like you say, there are situations where they need to close without running the query. There is another button for this also. I'll try to explain in a little more detail.

The db is for Time Management, it logs time spent on each task by logging the time they switch to this task, and it ends that task when they switch to a new one. At the end of their shift, they need to click 'End Shift' which runs a query to update table 'Timesheets' with their shift end time. It also runs a query to end their current task in table 'Time Management'.

If for example the task the agent is moving to requires the use of a different PC, or they need to switch PCs for technical issues, we have a 'Switch PCs' button (that doesnt run the queries).

Let me know if this is makes sense as I can be pretty poor when it comes to explaining things sometimes!