Invalid Date

[Momma]

Hi there
I'm trying to prevent the user to enter a date in the future. If the date is greater than todays date the field should be cleared and have focus again.
Somehow my code is not working.

Private Sub TreatmentDate_AfterUpdate()
 If Me.TreatmentDate > Date Then
        MsgBox "Treatment Date is in the future. Enter correct Date"
        Cancel = True
        Me.TreatmentDate.SetFocus
  End If
End Sub

 

[GPGeorge]

Hi there
I'm trying to prevent the user to enter a date in the future. If the date is greater than todays date the field should be cleared and have focus again.
Somehow my code is not working.

Private Sub TreatmentDate_AfterUpdate()
If Me.TreatmentDate > Date Then
        MsgBox "Treatment Date is in the future. Enter correct Date"
        Cancel = True
        Me.TreatmentDate.SetFocus
  End If
End Sub

Data validation needs to be done in the BeforeUpdate event. At this point, i.e. in the AfterUpdate event, the invalid date has already been saved and this code has no effect.

 

[Momma]

I tried it with the BeforeUpdate event and I get these error messages.

 

[The_Doc_Man]

With BeforeUpdate, you can return a Cancel=True and prevent the save action. See link

Form.BeforeUpdate event (Access)

Office VBA reference topic

learn.microsoft.com

Inside that event you can pop up a message box complaining about whatever is wrong with the data on the form.

 

[Momma]

I can't seem to get the focus back to the date field.

 

[theDBguy]

I can't seem to get the focus back to the date field.

When you cancel the before update event, the focus stays on that control.

 

[The_Doc_Man]

I would take the approach of popping up messages telling the user that you can't save the record until you fix the date field. Let them navigate to it themselves. As long as you disallow the update, you should be OK.

You should be able to do a Me.controlname.SetFocus just before you exit the BeforeUpdate event code if that is what you needed to do once you decided something was wrong.

When you try to do this, be sure you are working from the Form_BeforeUpdate event. Cancel the BeforeUpdate event by setting the Cancel parameter to 1 (or -1 works as well). Don't attempt to change the state of the control (such as disabling, making it invisible, etc.) because there are restrictions on SetFocus that would be altered if you altered the control state.

 

[Gasman]

Doc, why would you even try that with regard to @theDBguy 's post?
I too thought you would remain in that control if Cancel = True?
Just tested it and that is what happens?

In fact if you go and add the setfocus, you get

Private Sub TxtInput_BeforeUpdate(Cancel As Integer)
If Me.txtInput <> "Test" Then
    MsgBox "Must be test"
    Cancel = True
    Me.txtInput.SetFocus
End If

 

[GPGeorge]

I would take the approach of popping up messages telling the user that you can't save the record until you fix the date field. Let them navigate to it themselves. As long as you disallow the update, you should be OK.

You should be able to do a Me.controlname.SetFocus just before you exit the BeforeUpdate event code if that is what you needed to do once you decided something was wrong.

When you try to do this, be sure you are working from the Form_BeforeUpdate event. Cancel the BeforeUpdate event by setting the Cancel parameter to 1 (or -1 works as well). Don't attempt to change the state of the control (such as disabling, making it invisible, etc.) because there are restrictions on SetFocus that would be altered if you altered the control state.

Cancel = True cancels the update event. It's a "do-over" essentially. The only other thing one could do would be to Undo the attempted change.

Me.ControlName.Undo

At that point, I suspect, after the attempted change is undone, you might be able to set focus elsewhere.

 

[cheekybuddha]

In fact if you go and add the setfocus, you get

Doc also said to use the form's BeforeUpdate event rather than the control's. Does doing so make any difference?

 

[GPGeorge]

Doc also said to use the form's BeforeUpdate event rather than the control's. Does doing so make any difference?

I would think so, but I would also have to verify.

I think the control can be updated independently, and prior to, when the form updates, which would be the record save.

However, I'll defer to those with more insight.

 

[The_Doc_Man]

From the MS Learn site:

BeforeUpdate event

Office VBA reference topic

learn.microsoft.com

The article includes this quote:

"If you set the Cancel argument to True, the focus remains on the control and neither the AfterUpdate event nor the Exit event occurs."

Therefore, though I WAS originally talking about the Form_BeforeUpdate (because I've used that before), there is also justification for using the Control's _BeforeUpdate event.

 

[Gasman]

I would think so, but I would also have to verify.

I think the control can be updated independently, and prior to, when the form updates, which would be the record save.

However, I'll defer to those with more insight.

I would expect it would, as one would be on the last enabled control?
My mistake, I was doing the setfocus from the control's beforeupdate, not the form?
So as long as the control being set is not being in focus, it should work?

 

[GPGeorge]

I assume so.

 

[Pat Hartman]

I can't seem to get the focus back to the date field.

If you use the control's BeforeUpdate event, focus will never leave the control if you cancel the Beforeupdate event so you do NOT need to do anything. Focus will stay in the control.

However, I do not recommend doing this type of validation in the control event. It is better to use the Form's BeforeUpdate event. In that case though, you will need to set focus back to the control with the error.

me.SomeControlName.SetFocus

I only use the control level BeforeUpdate event when I want to prevent the user from moving on because I am not going to allow him to save the record if this particular field is invalid.

Also, you cannot ensure a value is present by using the control's BeforeUpdate event. You must use the form's BeforeUpdate event for this purpose. Why? if a control never gains the focus, then no control level event will ever fire and so you have no way to recognize that the user didn't enter a value.

 

[The_Doc_Man]

I agree with Pat, actually, though I pointed out that the other way COULD work. I have had some success with the method that I mentioned earlier and that Pat just mentioned. The inability to set focus usually means someone has done something to a control that makes it ineligible. The real question is to find out what was done ... and don't do that.

 

[Pat Hartman]

If focus is IN a control, you get an error if you try to set focus to that control.