Solved If .NoMatch Then , Doesn't work

[MaryamF]

Appreciate your help in advance,

on this program , I can't get the
If .NoMatch Then
part working...
when there is no match I want to get the message says 'welcome".

-----------------------------------------------------------------------------
Private Sub SSNumber_BeforeUpdate(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM TblePeople WHERE SSN='" & Me.SSNumber & " ' ")

With rst
If .NoMatch Then

MsgBox ("*Welcome*")

End If

If Not .NoMatch Then

MsgBox "SSN: " & Me.SSNumber & " Already Exists and Belongs to:" & vbCrLf & _
!FullName & " " & !DOB & " " & !PhoneNumber, vbExclamation

Me.TenantName = !FullName
Me.TenantName.Enabled = False
Me.FullName.Enabled = False
Me.TenantDOB = !DOB
Me.TenantDOB.Enabled = False
Me.DOB.Enabled = False
Me.TenantPhone = !PhoneNumber
Me.TenantPhone.Enabled = False
Me.PhoneNumber.Enabled = False
.Bookmark = .LastModified

End If

End With

Set rst = Nothing
Set dbs = Nothing

End Sub

 

[CJ_London]

try

if .EOF then

 

[ebs17]

NoMatch should be used in conjunction with FindFirst / FindNext.
The easiest and quickest way to test for an empty recordset is to check for EOF, as already mentioned.

 

[Josef P.]

Recordset.NoMatch property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

Indicates whether a particular record was found by using the Seek method or one of the Find methods (Microsoft Access workspaces only).

 

[Mike Krailo]

Are you certain, this table exists? TblePeople

Or maybe this:

Private Sub SSNumber_BeforeUpdate(Cancel As Integer)
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset

   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("SELECT * FROM TblePeople WHERE SSN='" & Me.SSNumber & " ' ")

   With rst

      If Not .NoMatch Then

         MsgBox "SSN: " & Me.SSNumber & " Already Exists and Belongs to:" & vbCrLf & _
            !FullName & " " & !DOB & " " & !PhoneNumber, vbExclamation

         Me.TenantName = !FullName
         Me.TenantName.Enabled = False
         Me.FullName.Enabled = False
         Me.TenantDOB = !DOB
         Me.TenantDOB.Enabled = False
         Me.DOB.Enabled = False
         Me.TenantPhone = !PhoneNumber
         Me.TenantPhone.Enabled = False
         Me.PhoneNumber.Enabled = False
         .Bookmark = .LastModified

      Else
         MsgBox ("*Welcome*")
      End If

   End With

   Set rst = Nothing
   Set dbs = Nothing

End Sub