MsgBox and SetFocus if no not working

[ITguy1981]

I have a field for SSN that I would like a prompt to let the user know that hey haven't entered a SSN. This field isn't required, but can be very important. If I use validation text or requirements some users may get annoyed especially if they don't have the information. So, if the field is blank I like the box to let he user know it is blank and to click yes to continue or click no to enter a SSN. The prompt seems to be working, but if a user clicks "no" to go back and enter the SSN the cursor isn't going back to the field using set focus. I'm pretty sure the issue is in my statement.

Private Sub SSN_Exit(Cancel As Integer)
Dim strmsg As String
strmsg = "No SSN has been Entered. "
strmsg = strmsg & "Do you want to continue without entering a SSN? "
strmsg = strmsg & "Click Yes to Continue or No to enter a SSN. "
If IsNull(Me.SSN) Then
If MsgBox(strmsg, vgQuestion + vbYesNo, "Continue") = vbYes Then
'continue
Else
'DoNothing
End If
Else
Me.SSN.SetFocus
End If
End Sub

 

[MarkK]

Notice that this event allows you to set a Cancel parameter. If you set cancel to true in the exit event, I bet the exit will be cancelled and focus will stay on the control without any need for a setfocus. Consider...

Private Sub SSN_Exit(Cancel As Integer)
  Dim strmsg As String
  strmsg = _
    "No SSN has been Entered." & vbcrlf & _
    "Do you want to continue without entering a SSN?" & vbcrlf & _
    "Click <Yes> to Continue or <No> to enter a SSN. "
  If IsNull(Me.SSN) Then
    If MsgBox(strmsg, vgQuestion + vbYesNo, "Continue") = vbYes Then
[COLOR="Green"]      'cancel the exit, probably has the effect of not allowing focus to move away[/COLOR]
      Cancel = true
    End If
  End If
End Sub

But I think this will annoy the user too. I'd want to have the freedom to tab through this field if I didn't have the data.
Cheers,
Mark

 

[ITguy1981]

Thank you for the quick reply. At this point I think I may contact the main user of the database to see if they are okay with having the SSN field blank. Any search options I have that could include the SSN use IsNull or Like in the search so it won't matter if the field is blank. They may be okay with forced entry as well. Personally, I would rather have the field always filled, but I'm not the one using the database. Thanks again for your help.

 

[ITguy1981]

I tried you code to see if it would work. The cancel still allows the cursor to move to the next field. I think i'm just going to put the code in the save button.