rst.RecordCount returns 1 when no records (1 Viewer)

[PNGBill]

Ms Access 2010 accdb.

What is happening here when 1 is returned as record count when there are no records

Dim dbs As DAO.Database, rst As DAO.Recordset
            Dim lngOldLoanID As Long        'Loan ID to be Refinanced
            Set dbs = CurrentDb
                'populate variables
            lngOldLoanID = Me!cboLoanID
                          Set rst = dbs.OpenRecordset("SELECT TblRefinance.RefinanceID As RefID, TblRefinance.RefinanceAmount " & _
                    "FROM TBLLOAN LEFT JOIN TblRefinance ON TBLLOAN.LDPK = TblRefinance.OldLoanID " & _
                    "WHERE (((TBLLOAN.LDPK)=" & lngOldLoanID & ") AND ((TblRefinance.RefinanceRepayID) Is Null));", dbOpenDynaset)
            If rst.RecordCount > 0 Then             'Record exists. Populate form control
            
            MsgBox rst.RecordCount

I tested the sql and no record exists yet un the vba code the MsgBox returns 1

I want to trap where no record exists but as this returns 1, the code continues and

Any ideas on what is happening here

 

[pbaldy]

I would use this technique to make sure the SQL is coming out the way you expect:

http://www.baldyweb.com/ImmediateWindow.htm

I personally would test for EOF, but I would expect what you have to work if there are no records.

 

[PNGBill]

Thanks Paul, The sql doesn't throw the error. The error occurs later in the code because the direction taken assumes a record exists.

I just checked the sql and it does indicate 1 0f 1 record when in fact no record is shown. What it does display is a new record to be added.

rst.RecordCount could be counting this "pending" new record

 

[pbaldy]

Does changing

If rst.RecordCount > 0 Then

to

If Not rst.EOF Then

work? In a brief test the RecordCount will return 0, even if a new record is there:

  Dim strSQL                  As String
  Dim db                      As DAO.Database
  Dim rs                      As DAO.Recordset

  Set db = CurrentDb()

  strSQL = "SELECT * FROM tblNumbers WHERE 1=0"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
  Debug.Print rs.RecordCount
  Debug.Print rs.EOF
  Set rs = Nothing
  Set db = Nothing

results in

0
True

 

[PNGBill]

This solution works.

    'Check if record exists in TblRefinance for this LoanID and RepayID combination
            Set rst = dbs.OpenRecordset("SELECT TblRefinance.RefinanceID As RefID, TblRefinance.RefinanceAmount " & _
                    "FROM TBLLOAN LEFT JOIN TblRefinance ON TBLLOAN.LDPK = TblRefinance.OldLoanID " & _
                    "WHERE (((TBLLOAN.LDPK)=" & lngOldLoanID & ") AND ((TblRefinance.RefinanceRepayID) Is Null));", dbOpenDynaset)
            If (rst.BOF And rst.EOF) Then               'Record exists. Populate form control

If no record exists then the If fails and "Else" allows for a message and exit.

 

[pbaldy]

I think you have it backwards, but if you're happy I'm happy.

By the way, my daughter just returned home from 2 weeks in NZ. They loved it!

 

[PNGBill]

Yes, text book has

If Not(rst.BOF And rst.EOF) Then

but my "If Then" is setup to expect a record to exist.
Highly unlikely for no record to exist.

We have just had some great weather here. Still sunny in Raglan but wind is very strong.

Where we are is a great surf areaa and we have Kite Surfers at our front door. Not suitable for learners today though