Full Version: Data validation problem.
UtterAccess Forums > Microsoft® Access > Access Forms
HairyBob
Could someone please try this and let me know whether there's any solution (example file attached)...
et up a table with two text fields ('Name' and 'Address'), both with 'Required' set to 'Yes' and 'Allow Zero Length' set to 'No'.
Create a Form (using wizard or otherwise) for data entry, then enter the following in Form_Error():
If DataErr = 3314 Then
If IsNull(Me!Name) Then
MsgBox "You must enter a name."
txtName.SetFocus 'Or whatever you've named the Textbox.
End If

If IsNull(Me!Address) Then
MsgBox "You must enter an address."
txtAddress.SetFocus 'Or whatever you've named the Textbox.
End If
End If
Response = acDataErrContinue
Enter data one field only and click the record navigation button for next record to attempt to save; the relevant message will be displayed. Next, enter data in the textbox originally left blank and delete the entry from the other textbox - you will find that when you attempt to save the record, although Form_Error() fires, BOTH fields (me!Name and Me!Address) have retained the previously entered data and therefore, no message is displayed!
Although I realise that no user is likely to do this, I'm intrigued to know whether or not it's possible to detect which control has the missing entry in the Form_Error event so as to display the relevant message after the second edit.
Any ideas appreciated.
KeesB
L.S.
irst of all, I would not test after the error but before wink.gif
So I would set a test in the "Before Update" event of the form looking like
CODE
Sub Before_Update(Cancel As Integer)
     On Error Goto Err_Before_Update
     If IsNull(Me.txtName then
           Cancel = True
           MsgBox "You MUST enter a name."
           Me.txtName.SetFocus
           Goto Exit_Before_Update
     ElseIf IsNull(Me.txtAddress) then
           Cancel = True
           MsgBox "You MUST enter an address"
           Me.txtAddress.SetFocus
           Goto Exit_Before_Update
     End If
Exit_Before_Update:
     Exit Sub
Err_Before_Update:
     <Error code>
     Resume Exit_Before_Update
End Sub

In your code you are testing the field in the table(Me.Name) which of course has the old value and not the control on the form(Me.txtName) which is empty.
HairyBob
Kees,
Thank you for your reply, however;
Using your code does not solve the problem for the series of actions I specified - when you perform the second stage (entering the missing data and deleting the data entered in the first edit, you get a data error as follows:
The field 'Table1.Address' cannot contain a Null value because the Required property for this field is set to True.
Enter a value in this field.
Osubstituted testing of me!Name and me!Address for null with me.txtName and me.txtAddress respectively and found that this yields the same result as my code. I think that by using me.txtName and me.txtAddress, the Value property of the text boxes is being referenced which is the same value as the underlying field (you can set a breakpoint on the first IsNull test line to check this).
The Text property of the control is indeed an empty string, however you have to make sure that the control to be tested has the focus first and if you use txtName.SetFocus and txtAddress.SetFocus before the relevant tests, on the 2nd edit I specified, you get Run-time error 2108 stating that you must save the field first - you can't save the field first of course, as this is what has caused the Form_Error() event to fire in the first place.
I've since found that the following code solves my problem...
Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3314 Then
If IsNull(Me!Name) Then
MsgBox "You must enter a name."
On Error Resume Next
txtName.SetFocus
On Error GoTo 0
End If

If IsNull(Me!Address) Then
MsgBox "You must enter an address."
On Error Resume Next
txtAddress.SetFocus
On Error GoTo 0
End If
End If
Response = acDataErrContinue
End Sub
Private Sub txtAddress_BeforeUpdate(Cancel As Integer)
If txtAddress.Value = "" Then txtAddress.Value = Null
End Sub
Private Sub txtName_BeforeUpdate(Cancel As Integer)
If txtName.Value = "" Then txtName.Value = Null
End Sub
Where I'm forcing Null if a Textbox entry has been deleted, so that when Form_Error() fires, the relevant IsNull test is True and the Message Box is displayed. In addition, I've used error trapping around txtName.SetFocus and txtAddress.SetFocus so that a Run-time error does not occur if the SetFocus method fails. This does not explain however, why you cannot detect the reason for the data error - there must surely be some way of finding out which text box has the deleted entry in the Form_Error() event.
KeesB
Hi Bob
irst of I made a typo in the first if line there is a ) missing after Me.txtName.
You are correct for Me.TxtName/Me.txtAddress testing the controls on the form(if the names are correct).
However the controls (on the form) and the fields (in the table) are not always the same value. When you open a form or go to another record Access takes the values of the fields and put these into the controls of the form.
Now when you change or delete these values in the controls nothing changes in the fields.
Access writes the values of the controls to the fields when one of the following happens:
  • Move to another record
  • Close the form
  • Go to a tab page on the form
  • Click the pencil symbol on the vertical bar on the left of the form
  • ...

So we need to test the control before Access tries to write it to the field.
I did you put the code at the forms Before Update event or at the controls?
It should be the forms.
Oops I just saw that I missed a little more code "Before_Update" should be Form_Before_Update. blush.gif
Now this should work. I'm using this myself quit regularly.
If it doesn't then put a breakpoint in at the sub line and step through the code with F8.
It is possible that the control isn't Null but a zero length string. In that case the code will jump over the if. But this can be trapped by changing the if code from
- If IsNull(Me.txtName) then - into - If IsNull(Me.txtName) Or Me.txtName = "" then -
HTH
HairyBob
Hi Kees,
any thanks for the reply, however...
If you investigate the procedure I originally specified, you'll find that if coding in Form_BeforeUpdate(), on the SECOND edit (entering data in the textbox which data was originally omitted from and deleting the data in the other textbox, then attempting to save the record) the Form_BeforeUpdate() event doesn't fire ! - instead, you'll get an Access data error message. Try setting a breakpoint on the line; 'If IsNull(me.txtName) Then', then do the following:
(1) Run the form and perform the 1st edit (enter some text in the Address textbox).
(2) Attempt to save the record by clicking on the next record button of the navigation control.
(3) Press <F5> to continue.
(4) Close the message box stating the you MUST enter an address.
(5) Enter some text in the Name textbox.
(6) Attempt to save the record by clicking on the next record button of the navigation control - We know that the Form_BeforeUpdate() event doesn't fire beacuse we never hit the breakpoint in Form_BeforeUpdate().
The Access data error message can be prevented by setting the Response parameter of Form_Error() to acDataErrContinue and a custom error message can be displayed by coding in Form_Error() however, As I've previously explained, I see no way of determining which textbox has the missing entry on the save attempt after the SECOND edit. Try coding in Form_Error(), set a breakpoint, perform steps (1) to (6) above, then when code stops on the breakpoint during the attempt to save after the SECOND edit, investigate the textbox properties and field values - you'll see what I mean.
The only soultion to this specific problem that I can see at present is provided by the code I previously specified (setting the textbox value property to null in the textbox BeforeUpdate() event if its value is an empty string) that way, when Form_Error() fires, the empty textbox is detected by the IsNull() test.
Any further ideas from anyone would be appreciated.
KeesB
Hi Bob
I've left the office for today. Unfortunately I've got no Access on this box.
I'll give it a test tomorrow
KeesB
Hi Bob,
Is promised I've been doing a bit of testing today.
I've been playing with the required property of the table and the code.
So in no particular order, here are the results yayhandclap.gif
****
With required Yes
  • New record - the form before update events fires - the form before update code works
  • Existing record - make a change to the data, NOT delete - the form before update events fires
  • Existing record - delete data - the form before update events does not fire, but the table gives an error.

****
With required No
  • New record - the form before update events fires - the form before update code works
  • Existing record - make a change to the data, NOT delete - the form before update events fires
  • Existing record - delete data - the form before update events fires

This is what I've been using. As it is good practice NOT to modify the data direct in the table I am perfectly happy with that.
****
With required Yes, Using the form on error event.
The event fires but does not test correctly for NULL.
However, If you trap the controls before update event(apparently just trapping seems to be enough, no code required), the test for NULL in the forms on error event works correctly.
I've attached the Db I've tested with, including some more comment. It would be nice to now if you can reproduce my results.
I'm a bit shrug.gif giveup.gif about it. I've got not good explanation for this behaviour.
P.S. "Name" is a reserved word in Access. This produced some strange results during testing.
Odid a debug of Me.Name and the result was the name of the form.
So I changed Name into NamePerson.
HairyBob
Hi Kees,
Thanks for your reply...
HAs you've commented in your code, it's really bizarre that if you include an empty BeforeUpdate() handler for the TextBox control and you've deleted the text from the control before attempting to save after the second edit, you end up with NULL in the value property for that control in Form_Error(), whereas if you don't include the empty BeforeUpdate() handler, the value property in Form_Error() is as it was entered in the first edit !!
Odon't understand why inclusion of an empty event handler should have an effect on the second edit - I guess it's just an Access peculiarity.
Thanks again for taking the trouble to respond.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.