Unexpected outcome with SetFocus method

  • Thread starter Thread starter John S. Ford, MD
  • Start date Start date
J

John S. Ford, MD

I've written an event handler to ensure that a user doesn't enter a patient
discharge date the doesn't occur BEFORE the admission date (which is
illogical). I'm using the OnExit event for a TextBox called
txtPatientDischargeDate.

If the user enters a bad date, the MsgBox opens up and the TextBox gets the
value of "". The problem is that when the MsgBox is closed, the focus goes
to the NEXT control in the tab order rather than staying on the original
TextBox. This is confusing to the user and necessitates an extra mouseclick
to get the focus back. What is wrong with this code? Why doesn't the
SetFocus line work?

Am I using the wrong event to trigger my code?

Private Sub txtPatientDischargeDate_Exit(Cancel As Integer)
If txtPatientDischargeDate.Value < txtPatientAdmitDate.Value Then
MsgBox "discharge date must be LATER than admission date."
txtPatientDischargeDate = ""
txtPatientDischargeDate.SetFocus
End If
End Sub

John
 
John,

Use the BeforeUpdate event, and if the dates are wrong, execute the
textbox's Undo method, and set the event's Cancel property = True.

Private Sub txtPatientDischargeDate_BeforeUpdate(Cancel As Integer)
If txtPatientDischargeDate.Text < txtPatientAdmitDate.Value Then
MsgBox "discharge date must be LATER than admission date."
txtPatientDischargeDate.Undo
Cancel = True
End If
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Set the Cancel argument to True to cancel the event (i.e. not let the user
exit the box).

The BeforeUpdate event would be better than Exit: BeforeUpate only fires if
the user entered something.

If txtPatientDischargeDate is a Date/Time field, then attempting to set it
to a zero-length string will fail. Set it to Null instead. (Actually, the
user may prefer to change the value rather than have it wiped out.)

Because you are comparing two field values, and you have no idea which one
the user will enter first, you really need to use the BeforeUpdate event of
the *form*. This event fires after all the boxes are entered, but before the
record is saved to the table, so it is the event to use for comparing
values:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.txtPatientDischargeDate.Value < Me.txtPatientAdmitDate.Value Then
MsgBox "discharge date must be LATER than admission date."
'Me.txtPatientDischargeDate = Null
Me.txtPatientDischargeDate.SetFocus
End If
End Sub


Because it makes no sense at all to have a discharge date prior to an
admission date, it may be better still to enforce this at the engine level
instead of at the form level. To do that:
1. Open your table in design view.

2. Open the Properties box (View menu).

3. Beside the Validation Rule in the Properties box, enter:
(txtPatientDischargeDate Is Null) OR (txtPatientAdmitDate) Is Null) OR
(txtPatientAdmitDate <= txtPatientDischargeDate)

4. Save the table.

Access will now prevent the bad entry, regardless of where the record is
added/updated. Note: Make sure use use the Validation Rule of the table (in
the properties box), not that of a field (in the lower pane of table
design.)
 
Thanks for the help. I've tried putting together what you've all
recommended and came up with this code for the BeforeUpdate event:

If txtPatientDischargeDate < txtPatientAdmitDate Then
MsgBox "discharge date must be LATER than admission date."
txtPatientDischargeDate = Null
txtPatientDischargeDate.Undo
Cancel = True
txtPatientDischargeDate.SetFocus
End If

Unfortunately now I get the following error message:

Run-time error '2115'

The macro or function set to the BeforeUpdate or ValidationRule property for
this field is preventing MS Access from saving the data in the field. The
SetFocus line gets flagged.

Any ideas?
John
 
You likely don't need the setfocus

In fact, you likely don't need:
txtPatientDischargeDate = Null
txtPatientDischargeDate.Undo
Click to expand...

By setting cancel = true, then the focus will stay, and the control will NOT
be updated. The user will still see the "bad" date entered, but that just
gives them a nice chance to fix it...so..try:
 
Back
Top