BeforeUpdate vs. AfterUpdate

[Eljefegeneo]

I do have a solution to my problem but I do not understand why my first attempt did not work. I want to make sure that a user does not type in a "." all by itself (a habit picked up from a long used DOS based DB). So I tried the following:

Private Sub ADDRESS1_BeforeUpdate(Cancel As Integer)
If ADDRESS1 = "." Then
If MsgBox("Do Not Type a '.' in this field without any other text", vbOKOnly) = vbOK Then
ADDRESS1 = ""
Me.CITY.SetFocus
End If
End If
End Sub

But I got the following error:
Run Time Error '-2147352567 (800200009)';
The macro or function set to the Before Update or Validation rule is preventing MS access from saving the data in the field.

So after trying numerous alternatives on the BeforeUpdate event, I used the following:

Private Sub ADDRESS1_AfterUpdate()
If ADDRESS1 = "." Then
If MsgBox("Do Not Type a '.' in this field without any other text", vbOKOnly) = vbOK Then
ADDRESS1 = ""
End If
End If
End Sub

It does exactly what I wanted it to do. Remind the user not to type in a sole "." and set the focus to the next control, which is [City}.

I have always thought that one tested for data validation in the Before Update event. But I cannot figure out how to correct the code in the Before Update event to (1) remind the user as to the error, (2) clear the field, and then set the focus to the next control in the tab order.

 

[Mihail]

Try this and see if work

Private Sub ADDRESS1_BeforeUpdate(Cancel As Integer)

If ADDRESS1,Text = "." Then
  If MsgBox("Do Not Type a '.' in this field without any other text", vbOKOnly) = vbOK Then
  ADDRESS1.Text = ""
Cancel = True
  Me.CITY.SetFocus
  End If
  End If
  End Sub

 

[bob fitz]

This is untested, so not sure it will work, but perhaps something like:

Private Sub ADDRESS1_BeforeUpdate(Cancel As Integer)
If ADDRESS1 = "." Then
  If MsgBox("Do Not Type a '.' in this field without any other text", vbOKOnly) = vbOK Then
    Cancel = True

    Me.CITY.SetFocus
  End If
End If
End Sub

 

[Eljefegeneo]

thank you both for answering. Neither worked. Bob Fitz, I get run time error 2108. Mihail, I get run time error 2108. both break at the Address1 = "". Is it possible that the form is trying to save the data and it can't? In previous attempts to use the Before Update event, it kept saying that the code wouldn't permit saving of the data. Which, of course, I didn't understand.

 

[Mihail]

Oops
Replace this
If ADDRESS1,Text = "." Then
with this:
If ADDRESS1.Text = "." Then

Not comma but point after ADDRESS1

 

[Brianwarnock]

Bob's does not have Address1=""

From the Microsoft support

A run-time error will occur if you attempt to modify the data contained in the control that fired the BeforeUpdate event in the event's procedure.

Brian

 

[bob fitz]

Try this:
Remove:
Me.CITY.SetFocus
and put it in the After Update event.

 

[Eljefegeneo]

I think that Brian's post is answering my question. You cannot modify data in a control with an BeforeUpdate event, or at least I think that is what he is telling me. I tried removing the me.CitySetfocus, and that did not solve the problem. So the BeforeUpdate event is for checking data and the AfterUpdate event can be used to correct the data.

So you can use the code to cancel the event or in my case, stay on the current field with the "." and then delete it manually. Or you can, in essence, save the data in the control as ".", but then delete it in the AfterUpdate code which checks to see if the data is correct, and if not, correct it. Then go on to the next control in the tab sequence.

As a learning lesson, and please correct me if I am wrong, the BeforeUpdate event is for validating data only and the AfterUpdate event can modify the data.