Check a table for an existing SSN

[Stingfish]

Im having a litle difficulty with the last few lines in the code. maily the last Mgsbox and VbYesNo



Private Sub PrpSSN_AfterUpdate()
Dim strRecordOwner As String
Dim vntWhoGotIt
vntWhoGotIt = DLookup("[ClientID]", "tblreferrals", "[ClientSSN]='" & Me.PrpSSN & "'")

If Not IsNull(vntWhoGotIt) Then
strRecordOwner = DLookup("[Surname]", "tblreferrals", "[ClientSSN]='" & Me.PrpSSN & "'")

MsgBox "This SSN has already is registered to: " & vbNewLine & _
vbNewLine & _
MsgBox "ClientID:" & vntWhoGotIt & "(" & strRecordOwner & ")"

If Yes
Me.txtAddress.SetFocus
else

If No??
intcancel = true
Me.undo

End If

End Sub


I need to add vbYesNo to the message box. If it yes then the focus moves to txtAddress on the form. If its is No I need the PrpSSN to be cleared so that the user can re-enter the number.

 

[pbaldy]

Here's a sample of a yes/no message box with different actions as appropriate:

 Dim msg, button, title, response
 msg = "Vehicle is Shopped - Do you want to dispatch anyway?"
 button = vbYesNo + vbDefaultButton2
 title = "Vehicle Shopped!"

    response = MsgBox(msg, button, title)
    If response = vbYes Then
        'what to do if yes
    Else  
        'what to do if not
    End If

 

[Stingfish]

Thanks Paul.

I'll have a play around with it.

cheers.