Force input in a control

[dgmdvm]

I have a form with multiple controls. I would like to force the user to enter the date in the control named [date] first, before they enter any other data. In testing the form, I have noticed that users bypass the date field for some reason. When the form opens, the focus is passed to the date control first but users jump to a control in the middle of the form. I don't think they see the cursor flashing in the date field.


I thought it would be easy to check to see if the field was Null on the lost focus event and then force the focus back to the date field but I can't get the code right. The code runs, but the focus stays on the field the user inadvertently moves to rather than going back to the date field. I also tried a validation rule of "IS not null" and a date validation rule but that doesn't seem to work either.
Here is my code:

Private Sub date_LostFocus()
'prevents the user from skipping the date when filling in the contribution subform
Do
Dim response As String
If IsNull(Me.date) = True Then
 response = MsgBox("you must enter the date first", vbRetryCancel)
     If response = vbRetry Then
       Me.date.SetFocus
       Exit Do
     Else
       Exit Sub
     End If
 Else
  Exit Do
End If
 Loop While IsNull(Me.date) = True
End Sub

Thanks for any help you can give me.

[June7]

Date is a reserved word. Should not use reserved words as names for anything.

Can try setting the field as Required in the table and let Access nag user. Can also set ValidationRule and ValidationText properties of the control. Is the date supposed to be the current date? Can set DefaultValue property. Are users allowed to abort record?

How are users 'jumping' to a control in middle of form? Shouldn't they progress to control next to Date?

[dgmdvm]

Date is a reserved word. Should not use reserved words as names for anything.

That is easy enough to change.

Can also set ValidationRule and ValidationText properties of the control.

I tried this but couldn't get it to work. I tried #00/00/0000# and also tried 0/0/0 but neither was correct.

Is the date supposed to be the current date?

It can be but doesn't necessarily have to be; that's why I didn't set the defaultvalue property.

Are users allowed to abort record?

Yes they can.

How are users 'jumping' to a control in middle of form?

By mouse click to other controls on the form.

Is there anything wrong with my code? or is the fact that I have [date] as a name of a control that is the problem?

[June7]

On review, ValidationRule will NOT trigger if user does not actually enter data in the control, which means they are still able to move past. Think I've tried to do something like what you want and gave up.

Code behind every control could check for value in [date] and return user to that control - maybe with a popup message.

I validate data before record is committed.

Data validation in VBA is usually done in form BeforeUpdate event or button Click event. I have never used BeforeUpdate. I disable the X close and use button.


The DATE field name is not causing this issue but still advise not to use.

[dgmdvm]

I ended up adding the code listed above to the click event of an open subform command button that users click on after filling out the main form with the date on it. It works perfectly in this situation. I guess I will have to learn about how to use the "lost focus" event on some other application!

Thanks for the help.

[ArviLaanemets]

I myself would set form controls Enabled or Locked property in OnCurrent event of the form depending txtYourDateField being empty or not. And do same in AfterUpdate event of txtYourDateField. And in forms BeforeUpdate event I would check for txtYourDateField being not empty.