What if the SQL statement does not find any 'matches' in the table?

Using MS Access, I tried to run an SQL statement but I can't make it work in the case that the table has no records yet. Will appreciate the help! Thanks!

Public Function GetReferenceID(RefCode As String) As Integer
    Dim RefID As Integer
    Dim rec As Recordset

    Call connectDB
    sSQL = "select RefID from Exceptions where RefCode = '" & RefCode & "'"
    Set rec = CurrentDb.OpenRecordset(sSQL)

    If (Not rec.EOF And Not rec.BOF) Then
        RefID = rec.RecordCount + 1
    Else
        RefID = 1
    End If
    GetReferenceID = RefID 
End Function

Private Sub RefCode_Change()
    Dim tr As Transactions, rID As Integer
    Set tr = New Transactions
    tr.GetReferenceID (RefCode.Value)
end sub

---

UPDATE! There's an err开发者_开发技巧or on this line (Run-time error 3464, "Data type mismatch in criteria expression"):

Set rec = CurrentDb.OpenRecordset(sSQL)

...in this code:

Private Sub RefCode_Change()
        Dim rec As Recordset, RefID As Integer
        sSQL = "select RefID from Exceptions where RefCode = '" & RefCode.Value & "'"
        Set rec = CurrentDb.OpenRecordset(sSQL)

        If (rec.EOF And rec.BOF) Then
            RefID = 1
        Else
            rec.MoveFirst
            RefID = rec.RecordCount + 1
        End If
End Sub

---

I think you want to do this...

If (rec.EOF And rec.BOF) Then
    'empty recordset
    RefID = 1
Else
    'at least one row
     rec.movefirst
     RefID = rec.RecordCount + 1
End If

You could also simplify all of that to

RefID = Dcount("*","Exceptions","refCode='" & RefCode & "'") +1

---

I'm not entirely sure what the problem is, but I see two potential issues:

1. you haven't specified your recordset declaration, so you could be running into the ambiguity between the DAO and ADO recordset types. Instead, declare your recordset variable as Dim rs As DAO.Recordset or Dim rs As ADOX.Recordset (I think that latter is correct -- I never use ADO, so never have to specify it!). The fact that Set rec = CurrentDb.OpenRecordset(sSQL) returns a data type mismatch strongly suggests to me that you have declared an ADO recordset, because that would cause a mismatch with the DAO recordset returned by CurrentDB.OpenRecordset().

2. DAO recordsets cannot be guaranteed to return an accurate Recordcount until you've traversed the entire recordset. Because of Jet/ACE's Rushmore technology, the beginning of the recordset is delivered while the end of it is still being retrieved. You could wait around for a while and hope the Recordcount is accurate, or you can explicitly make an rs.MoveLast, in which case the Recordcount will be guaranteed to be accurate.