Keep form from closing

[MysticElaine]

I have a form that has some fields with validation rules, which are Not Is Null. I have a private function to validate records (here is a snipet)

Private Function ValidateRecord() As Boolean
ValidateRecord = False
Select Case Outcome
    Case "Interpretation"
        If IsNull(Me.[Date Provided]) Then
            MsgBox [Date Provided].ValidationText
        Exit Function
        End If
End Select
ValidateRecord = True
End Function

and a form_before update sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If ValidateRecord = False Then
Cancel = True
End If
End Sub

The form has a save and close button. I was wondering if there was a way to keep the form from closing if those fields are null. Right now, you click the button, the validation text pops up (it pops up twice too), and then the form closes. Can we keep the form open so they can fix the problem?

I thought about maybe having the (If...Then, Inputbox)code in the Form_BeforeUpdate instead. If I do that, is there a way to do that for date textboxes and comboboxes where you choose the value instead of typing it?

Thanks,

[jimatqsi]

There is an OnClose event that you can use here. Just check those objects in the OnClose event and cancel the event (cancel=true) if conditions are not right.

Jim

[MysticElaine]

The OnClose event for the form doesn't have (Cancel As Integer). So do I just do

If IsNull(Me.Providers) Then
MsgBox "Enter a Provider!", vbOKOnly, "Error"
DoCmd.CancelEvent
End If

Edit: that didn't work

[twinnyfo]

Elaine,

I think you have a 95% solution with your first post. Just add caode to the button that closes the Form.

Private Sub cmdClose_Click() 
    If ValidateRecord Then
        'ValidateRecord is True
        'Someone clicked the Close button without adding
        'All the Required Data--Put your validatino code here
 
    Else
        'If it is False, then we can close the Form
        Docmd.Close acForm, Me.Form.Name
    End If
End Sub

This prevents you from even beginning the "Close Form" processes until you are absolutely sure that the required data is present.

[jimatqsi]

Yes, but the users can click the X on the window frame, which will not get routed through the Close button on her form.

Jim

[jimatqsi]

Sorry, Elaine, you can use the Unload event for that purpose. I don't know if your CancelEvent code will work, but I think so.

[twinnyfo]

Jim, @ your Post Number 5, that's another good reason to keep that "x" close button off the form. I like to control what happens on my forms....

[MysticElaine]

Thanks for the response, the Unload event worked.

[jimatqsi]

Yes, twinny, but it's not your form, it's Elaine's form :)

[twinnyfo]

It's always a good time to share my tips and tricks!

:-)

[NeoPa]

Twinny, there are ways of closing a form that don't require clicking on the red X that would confound your approach (Ctrl-F4 for instance) :-(

It is only necessary to ensure that the Form_Unload() event doesn't complete successfully unless it finds a setting that indicates whatever validation is required has already completed successfully. To do this consider the structure of the following code :

Private blnAllowClose As Boolean
 
Private Sub cmdClose_Click()
    With Me
        If {Validation failed} Then Exit Sub
        blnAllowClose = True
        Call DoCmd.Close(ObjectType:=acForm, ObjectName:=.Name, Save:=acSaveNo)
    End With
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
    Cancel = Not blnAllowClose
    'Optional code
    Call MsgBox(Prompt:="Please use the Command Button to close the form" _
                Buttons:=vbOKOnly Or vbInformation _
                Title:=Me.Name)
End Sub

[twinnyfo]

NeoPa,

So there is a keyboard shortcut to close forms?!?!?!?! ARGH! Well, I learned something new today, but now I have to make sure my folks don't learn this trick and add code to prevent it!

Thanks!

Twinnyfo

[NeoPa]

You are certainly not the first member (or even expert) to have been caught out by this one Twinny ;-)

[burrina]

Here is the Code to deal with this issue.

If Shift = acAltMask And KeyCode = vbKeyF4 Then        'KeyDown Procedure
  KeyCode = 0    'Set Form KeyPreview Property to YES
 End If 
 

HTH

[twinnyfo]

Thanks, burrina!

Another tool for my kit!

[burrina]

Your Very Welcome. FYI, for 2010 it is different.

'Disable Ctrl-F4 Hot Key in Access 2010.
Select Case KeyCode
 
        Case vbKeyF4
            'MsgBox "The F2 key was Pressed"
            KeyCode = 0
        Case Else
            'MsgBox "No match!" 'testing
    End Select

[NeoPa]

Hi Burrina.

While your code may be useful as illustrating how keys can be captured (When the properties of the form are set up correctly), I would remind readers that the point is not so much to handle individual exceptions, but more to handle the concept as a whole. This is done perfectly well using the Form_Unload() event procedure as already suggested by Jim. Even if you were to capture all available ways of closing a form (And handling Ctrl-F4 is by no means that.) then you could never guarantee that new ones couldn't be introduced in future.

Thank you for your offerings anyway. As I say, it illustrates what code can do. It's certainly worth including how a form should be configured in order to allow keystrokes to be captured though.