Field Validation using Form_BeforeUpdate (1 Viewer)

I’m hoping someone can help with this problem which I have been struggling with for a few days now. So much so that I have created a small database to specifically try to resole the problem!
My problem concerns the use of validation for all fields on a form before I save it. Over the last few days I have read extensively on various forums including this one on how best to do validation for all fields on a form and have concluded that it is best done on the Form_BeforeUpdate event, so this is what I have done. Originally I preferred the use of my own Close button, rather than the ‘X’ button, but I seem to get different errors for both methods.
For the purposes of trying to resolve the problem, I have created a small relational database with a few small tables, and then created a form for each table for data entry. I have used one of these forms for this experiment:
I have created a Products table with a few fields and a Products form with all of the fields on it. I have added the word “Required” (without the quotes) to the Tag field of each control on the form. I have added the following code to the Form_BeforeUpdate event and my Close button (most of the code has been created from code on this forum, including the use of ‘If Me.Dirty Then Me.Dirty = False’ just before my’ DoCmd.Close’ code):

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control
nl = vbNewLine & vbNewLine
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
If ctl.Tag = "Required" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
"You can't save this record until this data is provided!" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
ctl.BackColor = 14680063
Cancel = True
Exit For
End If
End If
Next
Exit_BeforeUpdate:
Exit Sub
Err_BeforeUpdate:
MsgBox Err.Number & " " & Err.Description
Resume Exit_BeforeUpdate
End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
If Me.Dirty Then Me.Dirty = False
DoCmd.Close
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End Sub

If I make no changes to any existing record, or do not add a new record, the form closes correctly, irrespective of whether I have pressed the ‘X’ button or my Close button.
If, after creating a new record or editing an existing record and leave any of the fields blank, I press the ‘X’ button on the form, the code in the Form_BeforeUpdate event fires and displays the message informing me of the missing data, which is as it should be. When I click OK on the message box I get an Access message informing me that ‘You can’t save this record at this time’ and giving me a choice of either closing the form (which discards any changes) or leaving the form open (which puts the cursor into the field in error – which is as it should be).
If, after creating a new record or editing an existing record and leave any of the fields blank, I press my Close button on the form, the code in the Form_BeforeUpdate event fires and displays the message informing me of the missing data, which is as it should be. When I click OK on the message box I get an Access message (number 2101) informing me that (The setting you have entered isn’t valid for this property’. When I click OK to this message, the cursor is positioned in the field in error – which is as it should be.
My question is this – why do I get these Access messages and how do I get rid of them?
Incidentally, I would prefer to list all the fields in error in one message, but have realised that if the user does not correct them all they will get the message repeated for the remaining controls anyway, so I am not too bothered as long as I can get through this one!
Many thanks for any help in advance.

The error handling in code handles visual basic error codes, but as I recall data errors have to be handled separately by trapping the data error event.

If you are just validating that required fields are filled in, it may be easier to set the required property of the table fields to yes.

Stephen thanks for the suggestion, but I have already set the required fields to Yes in the table design. All I am trying to do is to ensure that before a form is closed all required data is present and its integrity is maintained! (Note that I have developed a handful of Access databases for my own use over the years, but now I have been asked to produce one for use by my company. Whilst the ones for my use have not included this level of sophisticated data checking (I know what fields need data and how it should be entered and can cope with the annoying Access messages when they arise), my users do not and will not understand them. In any event, I should be able to easily produce an application that looks and works in a professional manner and can maintain the integrity of its data!
In an attempt to get this to work and to simplify and streamline, and given that most of my users are used to using the ‘X’ button to close forms, I decided to use this instead of trying to use my own Close button. So initially I did not have the Close button or its associated code. But when I made changes and left a required field blank, I not only got my error message (as expected) but also the Access message informing me that ‘You can’t save this record at this time’ and giving me a choice of either closing the form (which discards any changes) or leaving the form open (which puts the cursor into the field in error – which is as it should be). I can understand Access wanting to help me to ensure the integrity of my data, but this message is not necessary when I have already told the user what is wrong - if I could suppress it that would be great.
So I then added my own Close button using the Access wizard, which simply added the code: DoCmd.Close. But when I ran this code I got my error message and then the form closed without saving the changes or giving me the opportunity to correct anything! I eventually found out from this site that due to an Access bug I had to add the code ‘If Me.Dirty Then Me.Dirty = False’ just before my ’DoCmd.Close’ code. This then did what I wanted (i.e. displayed my error message), but also gives me the Access message (number 2101) informing me that ‘The setting you have entered isn’t valid for this property’. When I click OK to this message, the cursor is positioned in the field in error – which is as it should be. But I don’t want this message – why is it appearing and how do I suppress it?

It sounds to me like you have doubled up on your redundancies.

By setting the required to true in the table and having the code trap to ensure the fields are filled you are getting two errors when something goes wrong. They are basically fighting each other.
I much prefer having the validation set to "is not null" rather than having the required set to true. Then I can put a custom instruction on the message and it is the same for all the forms I use that field in.

if I have the wrong end of this please let me know I will reconsider the options.

I tried your suggestion but I get the same results.

It doesn't seem to matter what I try I still end up getting the annoying Access messages! I've got to the point where I don't care how I do it, I just want to be able to tell the users in a controlled and friendly way which fields are wrong, and allow them to correct them before they exit the form.

Surely this cannot be difficult and must be something we all do all the time?

I normally perform validation by handling each control individually. This way you can define a custom message for each control should it fail validation and set the focus to the offending control.

I also remove the X button and force the user to close the form using my own custom close button

One more suggestion. Try trapping the error and ignore the redundant ones. Example:
On error goto Err_FormBeforeUpdate
...
Exit_FormBeforeUpdate:
Exit sub
Err_FormBeforeUpdate:
Select case err.number
case xxxx
resume next
...
End Select
Exit Sub

Thanks to everyone for your assistance. I finally solved the problem by trapping the Access error I did not want and then resuming the code. I also disabled the 'X' control on the form so that users can only close the form using my own button.

there's 2 basic ways of doing this

either - use forms before update event, and test all the likely errors there or

test for errors in the before update event for each control

the problem with the former is that it is Sooooo irritating to get to the end of a complex form, and THEN find that the input isnt valid.

before update may not catch everything anyway - you may still find you get duplicate key errors etc, whenn the record is committed.

managing user input to give them a friendly experience is worth spending time on