Link to home
Start Free TrialLog in
Avatar of Laurence Martin
Laurence MartinFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Why is my recordset returning a recordcount of -1?

I am trying to generate an quote number using a query like:

stsql = "SELECT Max(Val(Mid([Quote],4))) AS [Number] From [Quote Numbers] GROUP BY Left([Quote],3) HAVING (((Left([Quote],3))='HTR'));"

The code that runs this is:
rstProject.Open stSQL, CurrentProject.Connection, adOpenForwardOnly

With rstProject
    If .RecordCount = -1 Then
        fnNewProjectNumber = 1
        fnNewProjectNumber = !Number + 1
    End If
End With

When I first ran it, I thought the RecordCount came back as -1 because there were no records, but now the query does return a record, but the RecordCount is still -1.

If an aggregate query always returns -1, how can you handle the case when there are no records?

Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

because you are using a ForwardOnly recordset, the number of rows can only be evaluated after the recordset has been fully loaded (which is only after you looped through it fully).

you either need to use a different type of recordset or use a client cursor as shown in RecordCount returns -1 - 
Just to add to what Eric said:

0 is returned for no records,  so to indicate that there are some records, but it doesn't know how many, it uses -1.

Sometimes you will see a positive number right off depending on the recordset type, which depends on the type and size of the recordset.  

Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
First of all: your table violates the first normal form.

The simple solution: Use Nz(). Cause in conjunction with an aggregate you'll get always at least a row.

Const SQL_SELECT As String = _
  "SELECT Nz(Max(Val(Mid([Quote], 4))), 0) AS [Number] " & _ 
  "FROM [Quote Numbers] " & _
  "WHERE Left([Quote], 3) = 'HTR';"

rstProject.Open SQL_SELECT, CurrentProject.Connection, adOpenSnapshot
fnNewProjectNumber = rstProject![Number] + 1

Open in new window

that aside, this can fail in a multi-user situation.

The table needs to be locked.   Opening as a snapshot is not enough.

@Jim: Maybe.

The current code is incomplete. My assumption is: this is a function to return the next free number. This means, no exclusive lock is needed in most scenarios.
Avatar of Laurence Martin


Thanks everyone for lots of good advice.  
It was returning a -1 even though the table has no records.  The test for .EOF works great, but I realise there are other improvements too.