SetFocus VBA Not Working

[Irene83]

Hello,
I am new to VBA. Could you, please, tell me what's wrong with the code

Private Sub HOSPITAL_Change()

    If HOSPITAL.Value <> "Other" Then
        OTHHOSP_label.Visible = False
        Me.OTHHOSP.Visible = False
        Me.OTHHOSP.Value = Null
    End If
    
    If HOSPITAL.Value = "Other" Then
        OTHHOSP_label.Visible = True
        Me.OTHHOSP.Visible = True
        Me.OTHHOSP.SetFocus
        Me.OTHHOSP.ValidationRule = "<> Null"
        Me.OTHHOSP.ValidationText = "Please, name other hospital"
        Me.Refresh
    End If

End Sub

Private Sub OTHHOSP_LostFocus()
    'If OTHHOSP.Value = "" Then
     '     MsgBox "Please, name other hospital"
      '    OTHHOSP.SetFocus
    'End If
    If IsNull(Me.OTHHOSP) Then
        Me.OTHHOSP.SetFocus
        MsgBox "Please, name other hospital"
    End If
End Sub

Validation rule for some reason does not start working until I enter something in the field and then delete it to make it Null. That's why I added LostFocus event. When I am loosing focus from the field, I get the intended message, however the SetFocus command does not work.

Thank you.

Regards,
Irene

 

[Micron]

What you haven't said is what you're trying to do, but you are probably trying to do it with the wrong event. The Change event fires every time you add/delete one character from a textbox. As for validation rules on controls or their bound fields, it will not become evident unless you break the rule, and the odd thing about your choice of events is that the first time you enter the control and type something, it is NULL until the control loses focus.

You can either create a validation rule at the table level or control, but it will not be evaluated until the control updates. Suggest you try the Before_Update event if you want to enact data entry constraints before they become visible or actually update a record. The After_Update event is another possibility, but it cannot be cancelled whereas the other can. Whether or not that is of concern depends on what you're trying to do.

Suggest you research the "order of events for.." the control type you want to use.

 

[Irene83]

Thank you for your reply.
I understand that Change event fires on changing the field and nothing should happen until I actually type something. But in my code it sets validation rule for the field OTHHOSP after changing the field HOSPITAL, so the validation rule should be already working before I try to change anything.
And I still cannot understand why

Private Sub OTHHOSP_LostFocus()
    If IsNull(Me.OTHHOSP) Then
        MsgBox "Please, name other hospital"
        Me.OTHHOSP.SetFocus
    End If
End Sub

produces a message, but does not return back to the field, because LostFocus precedes GotFocus in order chain.

Thank you.
Regards,
Irene

 

[Micron]

Without benefit of knowing exactly what controls are what and the process, I see the first part this way:
With a new form open, I go to "Hospital" textbox or combo box (whichever it is) and start to type the word "Other". As soon as I type O, the change event fires and the control is Null. I keep going until I type the r in Other and the change event fires for the last time (if I don't backspace or whatever). After this last fire of the change event, the control value is still Null because I haven't moved the focus, so it will NEVER be true.

As for the focus problem, I guess you can't set focus to a control within the lost focus event for that control. I've never tried, because the proper method would be to put your validation code into the Before_Update event because it makes more sense to not move off the control if validation fails. So the correct method would be

Private Sub OTHHOSP_BeforeUpdate(Cancel as Integer)
    If IsNull(Me.OTHHOSP) Then
        MsgBox "Please, name other hospital"
        Cancel = True
    End If
End Sub

However, doing this for a Null value doesn't make sense to me. If I fill out this control, then change my mind about another one and delete the contents of this control before going back to correct a previous one, the validation will not allow me to leave this control and the message will continually pop up each time I try to make my corrections elsewhere.

 

[Irene83]

Thank you, you have described exacxtly what the code should do.
I have a combo box for the HOSPITAL, and on choosing "Other", a textbox appears, where one should enter the hospital name.
Using your code here

Private Sub HOSPITAL_Change()

    If HOSPITAL.Value <> "Other" Then
        OTHHOSP_label.Visible = False
        Me.OTHHOSP.Visible = False
        Me.OTHHOSP.Value = Null
    End If
    
    If HOSPITAL.Value = "Other" Then
        OTHHOSP_label.Visible = True
        Me.OTHHOSP.Visible = True
        Me.OTHHOSP.ValidationRule = "<> Null"
        Me.OTHHOSP.ValidationText = "Please, name other hospital"
        Me.Refresh
        Me.OTHHOSP.SetFocus
    End If
End Sub
Private Sub OTHHOSP_BeforeUpdate(Cancel as Integer)
    If IsNull(Me.OTHHOSP) Then
        MsgBox "Please, name other hospital"
        Cancel = True
    End If
End Sub

produces the wollowing error: The expression On Change you entered as the event propery setting produced the following error: Ambiguous name detected: OTHHOSP_BeforeUpdate.
I agree, that I probably should not set strict validation rules, and a simple reminder message would be enough.
Thank you.

Regards,
Irene

 

[Micron]

Well, it doesn't do that for me. Aside from that, I should repeat that checking for null makes no sense, especially since I've tried it. It is always null until the focus moves off of the control. I'd suggest the following approach (you should not copy the entire code block; rather, create your subs in the normal fashion, then copy/paste the text between the Sub/End Sub parts. Creating a procedure by dumping the whole thing in the editor can result in it being dis-connected from the form or control):

Private Sub HOSPITAL_AfterUpdate()
If HOSPITAL <> "Other" Then
   OTHHOSP_label.Visible = False
     With Me.OTHHOSP
      .Visible = False
      .Value = ""
     End With
ElseIf HOSPITAL = "Other" Then
   OTHHOSP_label.Visible = True
   Me.OTHHOSP.Visible = True
   Me.OTHHOSP.SetFocus
End If
End Sub

Private Sub OTHHOSP_LostFocus()
If IsNull(Me.OTHHOSP) Or Me.OTHHOSP = "" Then
  MsgBox "Please, name other hospital"
End If
End Sub