LostFocus on Current Record

[brett429]

How do I get the following code to run when I lose focus on the active record? This is on a continuous form. If the user tries to click off the form or click to another record on the form, I want this to happen:

If Me.AGENCY > 0 And Me.ASSIGNEDTO = "" Then
MsgBox "Please assign an employee to this record."
End If

Also, let's say I wanted to change the color of the field to RED to show them which field needed to be completed. How do I have that color change only apply to that record? Usually when I have it change a field color, it changes it on ALL of that field for each record.

Thanks!

 

[missinglinq]

The only way you can do the background color change for a control in the current record only, in a continuous or datasheet form, is to use Conditional Formatting from the menu; you can't do it from code like you could if it were a single view form. You could use Conditional Formatting to make the background red until a value was filled in, but it would be red from the minutethe record was started, not only if they tried to leave record with it empty. This code will do what you desire and return focus to the AssignedTo textbox until a value is entered, so the user shouldn't need any other hint to as to what's needed:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Me.Agency > 0 And IsNull(Me.AssignedTo) Then
    MsgBox "Please assign an employee to this record."
    Cancel = True
    AssignedTo.SetFocus
  End If
End Sub

 

[brett429]

The only way you can do the background color change for a control in the current record only, in a continuous or datasheet form, is to use Conditional Formatting from the menu; you can't do it from code like you could if it were a single view form. You could use Conditional Formatting to make the background red until a value was filled in, but it would be red from the minutethe record was started, not only if they tried to leave record with it empty. This code will do what you desire and return focus to the AssignedTo textbox until a value is entered, so the user shouldn't need any other hint to as to what's needed:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Me.Agency > 0 And IsNull(Me.AssignedTo) Then
    MsgBox "Please assign an employee to this record."
    Cancel = True
    AssignedTo.SetFocus
  End If
End Sub

Perfect! Thank you!

 

[missinglinq]

You're quite welcome!