Validation before closing form (1 Viewer)

Danick

Registered User
Joined
Sep 23, 2008
Messages
266
I have a form that doesn't have a Save button, but saves the record or any modifications to the record when the user hits a close button that dimply fires a DoCmd.Close on click event.

The problem is that one of the fields is needed in order to save the record. And if the user doesn't enter anything in that field, the form simply closes and doesn't save the record or any of the data that was input.

I would like to validate the form before it closes and then pop a message box about the field that needs to be filed.

I tried making the field mandatory in the table, but that isn't doing anything.

Then I tried the Before update of the form with this code, but that didn't do anything either:

If Nz(Me.Combo31, "") = "" Then
MsgBox "Please Enter something...!"
Cancel = True
Combo31.SetFocus
End If


Any ideas?
 

pr2-eugin

Super Moderator
Joined
Nov 30, 2011
Messages
8,498
Is that Mandatory field a ComboBox? if so.. try the following..

If Len(Combo31.Value & "") = 0 Then
MsgBox "Please Enter something...!"
Combo31.SetFocus
Exit Sub 'So that it exits the Procedure and stays on the Form.
End If
 

Danick

Registered User
Joined
Sep 23, 2008
Messages
266
Yes it's a combo box and thanks for your input, but the form still closes without any message box.

Can't figure out why the before update event isn't stopping the form from closing.
 

VilaRestal

';drop database master;--
Joined
Jun 8, 2011
Messages
1,046
Are you sure that's the name of the combobox? Normally a bound combobox would have the same name as the field. (Might be an idea to change it to that anyway so it's more meaningful than Combo31.) Do you have On Error Resume Next in the sub? Then it wouldn't say that it didn't recognise the object.

Finally are you sure that combobox is bound to the field?
And does the field have a default value set in the table?
When you say the field is mandatory, does it allow zero-length strings?

Check the Form's properties: that it has Event Procedure on its Before Update event.

Everything you tried should work hence my asking these dumb questions ;)
 

missinglinq

AWF VIP
Joined
Jun 20, 2003
Messages
6,329
This is caused by a long recognized bug that the Boys of Redmond have refused to correct! When using DoCmd.Close, if validation rules have been violated or required fields have been left empty, Access will, as you've discovered, not give any warnings and simply dump the Record! To prevent this you need to force a Save first, then Close the Form. So replace

DoCmd.Close

with

If Me.Dirty Then Me.Dirty = False
DoCmd.Close


This will force Access to try to Save the Record, and your validation code in the Form_BeforeUpdate event will execute.

Linq ;0)>
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,528
The edit code needs to go into the FORM's BeforeUpdate event. Many edits should go into the BeforeUpdate events of controls but not all. If you need to prevent "empty" fields or if the edit involves multiple fields, you can't effectively perform the edit in the event of a single control. In the case of "empty", you'll never recognize the problem if the control was never changed and therefore the BeforeUpdate event was never triggered. However, if anything was changed on the form, the FORM's BeforeUpdate event will fire and you can check it there. You have a similar problem with multi-field edits. For example, if date1 must be greater than date2, which event would you use? Neither BeforeUpdate events would prevent bad data plus you would need additional code to allow for nulls because the user simply hasn't gotten to the other field yet. Here again, the FORM's BeforeUpdate event solves the problem because by the time that runs, the user has presumably filled both fields because the record is about to be saved so if you give him an error message that a is not greater than b, it will make sense.
 

missinglinq

AWF VIP
Joined
Jun 20, 2003
Messages
6,329
What does 'edit' code have to do with this thread? Perhaps you meant 'validation' code? Which, as you said, frequently belongs in the Form_BeforeUpdate event, for the reasons you gave.

But the OP tried his validation code in the Form_BeforeUpdate event and it failed, for the reason that I gave, a long standing bug in Access where using DoCmd.Close without first explicitly saving the Record, causes Access to dump the Record without warning.

Linq ;0)>
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,528
Close only dumps modifications if there is an error. Edit/Validation code in the form's BeforeUpdate event should at least warn about it. I forgot to mention that it still doesn't stop the form from closing but at least you get an error message on the way. This is what I put in all my close buttons and it does stop the form from closing.

I mis-read the OP's comment regarding the validation he put in. He said it didn't do anything. What he maent I'm sure was, it still didn't prevent the close.

Code:
Private Sub cmdClose_Click()
On Error GoTo ErrProc
    If Me.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    DoCmd.Close
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 2501   ' save failed due to an error
            MsgBox "Please fix error or cancel update", vbOKOnly
            Resume ExitProc
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
End Sub
PS - you need to check for error 2101 rather than 2501 if you use Me.Dirty = False
 

Danick

Registered User
Joined
Sep 23, 2008
Messages
266
This is caused by a long recognized bug that the Boys of Redmond have refused to correct! When using DoCmd.Close, if validation rules have been violated or required fields have been left empty, Access will, as you've discovered, not give any warnings and simply dump the Record! To prevent this you need to force a Save first, then Close the Form. So replace

DoCmd.Close

with

If Me.Dirty Then Me.Dirty = False
DoCmd.Close


This will force Access to try to Save the Record, and your validation code in the Form_BeforeUpdate event will execute.

Linq ;0)>

Thanks for info.

After replacing my DoCmd.Close with the code you provided, the form no longer just closes. But I can't get my Before Update error message to fire either.
I receive a generic Access error message that states,

"The Microsoft Jet database engine cannot find a record in the table 'tblContacts' with the key matching field(s) 'Contact'. "

I also tried the code provided by Pat with the case errors, but that didn't work either. Any idea how I can get my message box to pop up instead of this generic message?

Thanks
 

Danick

Registered User
Joined
Sep 23, 2008
Messages
266
OK - I got part of the code to work.
Basically, I got rid of the Before Update event and just entered this code in the close button.
---------------
Private Sub Command44_Click()
On Error GoTo Err_Command44_Click

On Error GoTo ErrProc
If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
End If
DoCmd.Close
ExitProc:
Exit Sub
ErrProc:

MsgBox "Please enter a Contact name or cancel this update", vbOKOnly
Me.Combo31.SetFocus
Resume ExitProc

Exit_Command44_Click:
Exit Sub

Err_Command44_Click:
MsgBox Err.Description
Resume Exit_Command44_Click
End Sub
------------------

This works if the user creates a new record and tries to close the form without first entering a contact name in the combo box.

However, if the record already exists and then just deletes the contact name from the combo box, I get a "You Tried to assign a Null value.." Error message as soon as the combo box loses focus.

How can I fix that one?
Thanks
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,528
I gave you the code to use in the close button. It is generic so you can just pop it in your button's click event and it has been tested. I even told you to change the error number if you use Me.Dirty = False because that raises a different error if it is cancelled.

You have an On Error GoTo followed immediately by another. What do you think will happen? Which error event will get executed if an error arises?

You never posted the code from your BeforeUpdate event so we have no idea why it wasn't working.
 

Danick

Registered User
Joined
Sep 23, 2008
Messages
266
I gave you the code to use in the close button. It is generic so you can just pop it in your button's click event and it has been tested. I even told you to change the error number if you use Me.Dirty = False because that raises a different error if it is cancelled.

You have an On Error GoTo followed immediately by another. What do you think will happen? Which error event will get executed if an error arises?

You never posted the code from your BeforeUpdate event so we have no idea why it wasn't working.

OK - I had forgotten to delete the previous error event in the close button. This is what I have for the close button and it's working fine.

Private Sub Command44_Click()
On Error GoTo ErrProc
If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord
End If
DoCmd.Close
ExitProc:
Exit Sub
ErrProc:
MsgBox "Please enter a Contact name or cancel this update", vbOKOnly
Me.Combo31.SetFocus
Resume ExitProc
End Sub

AS for the Form's BeforeUpdate event, I had posted it in the first post of this thread. Which I then changed to this with help from pr2-eugin:

If Len(Combo31.Value & "") = 0 Then
MsgBox "Please Enter something...!"
Combo31.SetFocus
Exit Sub 'So that it exits the Procedure and stays on the Form.
End If

But I don't think that's the problem anymore since the form is working fine by just adding your code to the close button. I just discovered a new problem where the user can delete the contents of the combo box which then gives the null value error message.

Thanks for helping
 

Pat Hartman

Super Moderator
Staff member
Joined
Feb 19, 2002
Messages
28,528
In the Form's BeforeUpdate event, you have to Cancel the update if you find an error:
Code:
If Me.Combo31 & "" = "" Then
    MsgBox "Please enter something ... ",vbOKOnly
    Cancel = True
    Me.Combo31.Setfocus
    Exit Sub
End If
Concatenating a variable with a ZLS allows you to check for null and the ZLS in one If statement. Referring to controls with Me.controlname gives you intellisense. And finally, the .value property is the default for controls and so can be omitted in code and MUST be omitted if you reference form controls from queries.

The code in the BeforeUpdate isn't working because you are not cancelling the update.

A Hint - Before you start creating procedures, give your controls real names. I would suggest changing them now but you'll have to also change the existing code references since you'll get compile errors and the current procedures will be orphaned because the control name changed. You can just cut it from the Command44_Click procedure and paste it into the cmdClose_Click procedure and then delete the unused procedure headers.
 

Danick

Registered User
Joined
Sep 23, 2008
Messages
266
Thanks Pat

I actually solved my problem by changing the relationships a bit.
I was using a one to one relationship with the contacts table. But after I changed it to a one-to-many, the form is now able to allow the user to not include a contact.

This brings another problem that I'll probably be asking in another thread.

Thanks for your help
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom