Validating the field before closing the form

[sd110404]

Hi
Where am I wrong, When the user clsoe the form, I want to make sure that the Comment field is not empty. If its empty, then force them to enter and shouldnot close the form. else if its entered, then close the form. Why is DoCmd.CancelEvent not working?


Private Sub Form_Close()
Text1.SetFocus
If (Text1.Text = "") Then
MsgBox ("Your comment field cannot be empty, Please enter before close.")
DoCmd.CancelEvent
Exit Sub
Else
MsgBox ("You are ready to close")
End If
End Sub

Thanks.

 

[BobStubbs]

I have two suggestions for you:

First, I'm not sure why you have this test in your Form_Close event. If you are checking that a field contains text before saving a record, your check should be in the BeforeUpdate event.

Second, you need to test for the field being null as well as empty.

Try this code and see if it works for you:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If IsNull(Text1) Or Text1 = "" Then
        MsgBox ("Your comment field cannot be empty, Please enter before close.")
        DoCmd.CancelEvent
        Exit Sub
    Else
        MsgBox ("Record Saved")
    End If
    
End Sub

I hope that this is of some help.

Bob Stubbs

 

[sd110404]

Hi Bob,

I tried the code from your reply. Thanks.
But even for an existing record too i want to check, like when i open up an old record, and try to close, the Form_BeforeUpdate is not called. And hence doesnt check the condition there.

When I close the form (By clicking on the X button on right corner) it saves the record too.

My task is --> when they close the form I want to make sure that they have entered some text in in Textbox (Text1). If the Text1 field is empty, then it should display a message and shouldnot close.

When the Close button is clicked (ie the X on right corner) i belive , it calls Form_Close. thats y i had my code there.

 

[TheAceMan1]

sd110404 . . . . .

Your so close, yet so far away . . .

[blue]BobStubbs[/blue] suggestion of the BeforeUpdate Event is on target! Now I've never had the occasion to use the [blue]DoCmd.CancelEvent[/blue] because [purple]every event that can be cancelled has its own Cancel Arguement![/purple]. Why the [blue]DoCmd.CancelEvent[/blue] doesn't work I can't tell ya, but I can tell ya [blue]with authority and great abundance of use[/blue], the [purple]Cancel Arguement[/purple] does!

[blue]When the [purple]Cancel Arguement[/purple] is set to true (Cancel = True), the BeforeUpdate event [purple]rolls back saving[/purple] and you stay in the edited field. With proper validating code, you can bet [purple]you'll go no where else until its proper![/purple][/blue]

Bear in mind from the time you start using the BeforeUpdate, you prevent future blank fields.

Now realize if you have enough old [blue]saved[/blue] records, another method is required since events won't be triggered for these. For many of us this is called [blue]Clean-Up[/blue], which usually involves [blue]use of a query[/blue] or a form based on that query, which returns [blue]only those records having those specific blank fields[/blue]. Example of a query to return empty fields:

[blue]   SELECT PrimaryKeyName, RequiredFieldName
   FROM YourTableName
   WHERE (RequiredFieldName1 Is Null);[/blue]

Again at the very least you should [purple]permanently prevent future errors[/purple] by having some code in the forms [purple]BeforeUpdate[/purple] event:

[blue]Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim Msg As String, Style As Integer
   Dim Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   
   If Trim(Me![purple][b]Text1[/b][/purple] & "") = "" Then
      Msg = "No data in Comment field!" & DL & _
            "Comments are not allowed to be empty!" & DL & _
            "You must enter data in the comment field!" & DL & _
            "The Database will not let you continue otherwise . . ."
      Style = vbCritical + vbOKOnly
      Title = "No Comment Error! . . ."
      MsgBox Msg, Style, Title
      [purple][b]Cancel = True[/b][/purple] [green]'Roll back saving![/green]
   End If
            
End Sub[/blue]

[purple]Don't forget you cleanup![/purple]

See Ya! . . . . . .

 

[sd110404]

Thanks a lot for taking time and explaning.

I tried and it works. I was trying to avoid the database error (where it asks to close without saving). Ifound another way for that..
So that solves my pbm for now.

Thanks a lot for ur helps.