Good morning all!

I have a database that is split and the end users use a .mde file to access it. Well, they seem to like to click on the main "X" in the upper right corner of the Access window to close out of the program instead of using the close buttons that I created for them. When they click on these buttons, I have it programed to check and make sure each field is not null. When the click on the "X" it bypasses that check and they don't fill out all the information.
I don't have the required property set to "yes" in the table, because I don't want it to skip numbers in the autonumber field if the network crashes while they are filling out the database.
I know how to disable the "X" of the forms, but is there any way to keep them from closing it with the main "X"?

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

Hi Laura

I think you are approaching the problem the wrong way.
I presume that your validation routine runs when the user clicks your "close" button. Surely all you need to do is only write the record back to the table when the validation is successful. That way, if the application crashes or the user quits the application, the record was not written anyway, therefore not skipping any autonumbers!

Hope this helps!

Joe

I'm not sure exactly how to do this. (Write the record back to the table when the validation is successful.) You are correct in guessing that it checks everything when they click on the button. Could you give me some step by steps on how to accomplish it? I guess you could classify me as a beginner in VBA for Access. I've had one three day class and I knew nothing about VBA prior to the class, so I'm at a little bit of a disadvantage.
I appreciate your help!

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

Are you are using a bound form? That means that in the Record Source property of you form, you have chosen a database table or query.

Yes, It is bound to the table.

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

I have some code here that may solve your problem entirely. This will disable the Access application close button that you don't want people to use. However, the users will still have the ability to use the close functions of ALT + F4 (to close the application) and CTRL + F4 (to close your form. You can also add code to restrict that so you may want to look into it later. But here is your answer....


Option Compare Database
Option Explicit
Private Declare Function apiEnableMenuItem Lib "user32" Alias _
    "EnableMenuItem" (ByVal hMenu As Long, ByVal wIDEnableMenuItem As Long, _
     ByVal wEnable As Long) As Long

Private Declare Function apiGetSystemMenu Lib "user32" Alias _
         "GetSystemMenu" (ByVal hWnd As Long, ByVal Flag As Long) _
         As Long
Function EnableDisableControlBox(bEnable As Boolean, _
    Optional ByVal lhWndTarget As Long = 0) As Long
On Error GoTo ErrorHandling_Err

Const MF_BYCOMMAND = &H0&
Const MF_DISABLED = &H2&
Const MF_ENABLED = &H0&
Const MF_GRAYED = &H1&
Const SC_CLOSE = &HF060&

Dim lhWndMenu As Long
Dim lReturnVal As Long
Dim lAction As Long

lhWndMenu = apiGetSystemMenu(IIf(lhWndTarget = 0, Application.hWndAccessApp, lhWndTarget), False)

If lhWndMenu <> 0 Then
     If bEnable Then
        bEnable = False
        lAction = MF_BYCOMMAND Or MF_ENABLED
     Else
        lAction = MF_BYCOMMAND Or MF_DISABLED Or MF_GRAYED
     End If
     lReturnVal = apiEnableMenuItem(lhWndMenu, SC_CLOSE, lAction)
End If

EnableDisableControlBox = lReturnVal

Exit_ErrorHandling_Err:
    Exit Function

ErrorHandling_Err:
    If Err Then
        MsgBox Err.Description
        Resume Exit_ErrorHandling_Err
    End If
End Function

Paste this code into a module and call it from your autoexec macro - it works GREAT!!!!

Hope this helps you.

Kenny Alligood

Why don't you just have the code that you've got on your close button in the Form Close event? This way it would run the checks you mentioned no matter how the form was closed.

Steven

There are 10 kinds of people in the world - those who understand binary - and those with friends

Hallo Laura,

what you could do is have a form that is opened when the db is opened, then hide the form:

Function F_Open()
    DoCmd.OpenForm "hfrmClose", , , , , acHidden
End Function

On the form ist a chkbox which you set to False (unchecked)

This form can only be closed when the chkbox is set to true

Private Sub Form_Unload(Cancel As Integer)
    If Me!chkErlaubt = False Then Cancel = True
    If Me!chkErlaubt = False Then MsgBox "To close the form please" & vbCrLf & " sie use the close button", vbCritical, "FYI"
End Sub

In your button for closing you call following function (after you’ve done all your checking)
Function LetClose()
    Forms!hfrmClose!chkbox = True
End Function

Then you can do your Application.Close

Remember the hidden form has to open first (or direct after your splash form)
Because M$-Access in the same order they were opened!

BTW this also works by Alt + F4

HTH
alun

Steven's approach would be the easiest assuming there is only one event that is executed when you close the Form. If there are multiple options for closing the form, i.e. [close], [cancel], [move to another form], you would just have to add a series of If/else statements.

-Shay Shepston