CODEWITHSUNDEEP
vbams-accessms-access-2010
user2576682
user2576682

Reputation: 123

MS Access recordcount is always 1 even with no record

I am using MS Access 2010. I am trying to search a table and determine if the record exists based on First and Last name, If the record exists then update the record, and if it does not exist, then insert the new record. I am not getting any errors but I always get a recordcount of 1 even if I enter a name that I know does not exist in the table.

    Private Sub txtSearchFirstName_Exit(Cancel As Integer)
    Dim strSQL As String
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim recordCount As Long

    Set db = CurrentDb
    Set rs = Nothing

    Stop

    ''Check if a keyword entered or not
    If IsNull(Me.txtSearchlastName) = "" Then
    MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"

Else

    strSQL = "SELECT COUNT(*) " & _
    "FROM tblBobbettesMarketBulletin_CustNum " & _
    "WHERE tblBobbettesMarketBulletin_CustNum.Last = " & Chr(34) & txtSearchlastName & Chr(34) & _
    " AND tblBobbettesMarketBulletin_CustNum.First = " & Chr(34) & txtSearchFirstName & Chr(34)
    Debug.Print strSQL
   

    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    If (rs.BOF And rs.EOF) Then
            recordCount = 0
        Else
            rs.MoveLast
            recordCount = rs.recordCount
    
    End If
    If recordCount > 0 Then MsgBox ("Record exists")

    If recordCount = 0 Then MsgBox ("Record does not exist")
    
     rs.Close

    Set rs = Nothing
    

    End If
    End Sub

Upvotes: 0

Views: 703

Answers (1)

dbmitch
dbmitch

Reputation: 5386

@HansUp describes your problem perfectly.

If you want to simplify the code and use this feature, assign a field name to your Count(*) and then query and use its value in one step

strSQL = "SELECT COUNT(*) AS NumFound " & _
"FROM tblBobbettesMarketBulletin_CustNum " & _
"WHERE tblBobbettesMarketBulletin_CustNum.Last = " & Chr(34) & txtSearchlastName & Chr(34) & _
" AND tblBobbettesMarketBulletin_CustNum.First = " & Chr(34) & txtSearchFirstName & Chr(34)
Debug.Print strSQL
   

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

MsgBox ("Found " & rs!NumFound & " Record(s)")

Upvotes: 1

Related Questions