Recordset Recordcount = -1; SQL Statement opens with 2 records in Query

[time2hike]

Big Picture: I am trying to open 2 recordsets and test the results against a set of rules prior to loading data into my database. I have created this as a Sub attached to a command button on a form in my Access 2007 database.

Problem: My Recordsets are not opening. I am receiving a recordcount of -1 and a "Object Required" error when trying to verify the recordset loaded correctly. When I copy to SQL Statement to a query the query loads 1 and 2 records (which is the correct results) so, I know I am missing something but I am not sure what.

    'Create Recordset
    Dim rs As ADODB.Recordset
    Dim sSQL As String
    Dim rs1 As ADODB.Recordset
    Dim sSQL1 As String
    Dim sPID As String
    Dim sStat As String
    Dim stErr As String
    Dim lngRcdCnt As Long
 
    sPID = [Forms]![frm_Employee]![PersonID] ' this is pulling correct value 
    sStat = [Forms]![frm_Employee]![StatusID] ' this is pulling Correct Value
    stErr = ""
 
    Set rs1 = New ADODB.Recordset
    sSQL1 = "SELECT Max(t_AllP_Position.PosnEfftDate) AS MaxPosnEfftDate " _
        & "FROM t_AllP_Position " _
        & "HAVING t_AllP_Position.PersonID= " & sPID & " AND " _
        & "t_AllP_Position.StatusID= " & sStat
    Debug.Print sSQL1
    rs1.Open sSQL1, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
    rs1.MoveLast
    Debug.Print rs1.RecordCount
    Set rs = New ADODB.Recordset
    sSQL = "SELECT t_AllP_StatusHistory!PersonID, t_AllP_StatusHistory!StatusID, " _
        & "t_AllP_StatusHistory!RoleStatus, t_AllP_StatusHistory!Status_End, " _
        & "t_AllP_Position!PosnEfftDate, t_AllP_Position!PosnCurrent " _
        & "FROM t_AllP_StatusHistory INNER JOIN t_AllP_Position ON t_AllP_StatusHistory.StatusID = t_AllP_Position.StatusID " _
        & "AND t_AllP_StatusHistory.PersonID = t_AllP_Position.PersonID " _
        & "WHERE t_AllP_StatusHistory.PersonID = " & sPID _
        & " AND t_AllP_StatusHistory.StatusID= " & sStat & " AND t_AllP_Position.PosnCurrent= Yes"
 
    Debug.Print sSQL
    rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
    rs.MoveLast
    Debug.Print rs.RecordCount
    'Verify the Recordset loaded with 1 and only 1 record
 
 
    If rs.BOF And re.EOF Then
        MsgBox "Problem with Recordset used to verify data prior to loading qualifications. " & vbCrLf _
        & "Document the Person's Name, PersonID, and StausID and let the System Administrator know you received this message." _
        , vbCritical + vbOKOnly, "Recordset Record Count = 0"
        Exit Sub
    End If

[ADezii]

There is no Recordset Object re, Error would occur on the first Line of the If...End If Statement.

If rs.BOF And re.EOF Then       're does not exist
  MsgBox "Problem with Recordset used to verify data prior to loading qualifications. " & vbCrLf _
          & "Document the Person's Name, PersonID, and StausID and let the System Administrator know you received this message." _
          , vbCritical + vbOKOnly, "Recordset Record Count = 0"
     Exit Sub
End If
 

[time2hike]

True, this is where I am getting the error. So there is something wrong before this, can you see it? I can't find it. When I step through everything works up to the 1st line of the if and the record counts all returning -1.

[Stewart Ross]

Surely this is a transcription error? The IF at line 40 looks like it is guarding against an empty recordset, in which case it should just be

IF rs.BOF AND rs.EOF THEN

(BOF and EOF are both true if the recordset is empty).

If you used an OPTION EXPLICIT statement at the top of your code module the compiler would have failed to compile this module as recordset variable re has not been defined.

-Stewart

[time2hike]

Thanks! that solved the Object Required issue.

Do you know why the record count is -1? the next part of the code takes Fields in the recordset and tests validity. See below. Now I am getting Item cannot be found in the collection corresponding to the requested name or ordinal. Option Explicit did not catch any issues on this.

'Test for Employee Status = PCUR
If rs!RoleStatus <> "PCUR" Then
    stErr = stErr & vbCrLf & "This is not the Person's Current Role "
End If
'Test for the Status End Field being Null
If Not IsNull(rs!StatusEnd) Then
    stErr = stErr & vbCrLf & "This Role is recorded as having ended on " & rs!StatusEnd
End If

[NeoPa]

Please check out Before Posting (VBA or SQL) Code carefully. Too much time wasted on the basics which are not the question you need help with.

The problem you're currently reporting is unclear because you don't specify which line you're even talking about, but you can be sure that the name of one of the fields doesn't match the code that you've used. Your code looks considered (not just thrown together haphazardly as much code seems to be), so I'm surprised you'd suffer from this, but I suppose we all slip up sometimes. Anyway, check out your exact field names.

PS. You appear to have thrown two extra questions in that last post, although one is without any supporting information and I didn't realise it was separate. Sometimes a .RecordCount (I'm assuming that's what you're referring to but I'm frankly guessing what you mean) can be requested before it's been determined. If you check in the Help system you'll see recordsets are not fully processed immediately for larger amounts of data so there can be a delay before this property is reliably set. If you'd noticed, Stewart already pointed you towards determining if a recordset is empty using .BOF & .EOF. This is the recommended way for that very reason.

[time2hike]

NeoPa, thanks for your patience and assistance. I usually plagerize code and this is the most complex code I have written from beginning to end. I have taken care of all of the misspellings and naming issues and am left with my original question. I know that the recordset has 2 records in it. Everything is testing out correctly but I can not get the record count to equal anything but -1. My code opens the recordset then moves last and then asks for the record count.

rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
    rs.MoveLast
    Debug.Print rs.RecordCount 'See below for where the actual count is used.

I need to test for multiple current postitions. Which would be a count > 1. Attached is the entire code as it is written, but I have included the problem lines here. The code works except the record count is -1 which does not allow for the testing multiple current positions.

    'Test for Multiple Current Positions
    If rs.RecordCount > 1 Then
        stErr = stErr & vbCrLf & "Person has multiple Current Positions "
    End If

Thank you so much for you help!

[NeoPa]

I'd like to help, but clearly you're not paying the slightest attention to anything I say. Feel free to waste as much more of your time as you like, but I doubt I'll respond to anything here until there are some indications that you're paying some attention.

I consider myself a patient man, but I have little for people who give no indication of even trying.

[time2hike]

NeoPa, I am sorry you don't feel I am paying attention. I have gone back and re-read all of the posts. I have used the .BOF & .EOF method of determining if a recordset is empty. And the record set is populating. I know that the record set has to fully populate before the record count is accurate. I thought the rs.movelast took care of that. I have use this method in other databases successfully, and that is why I don't understand the count I am getting. At this point I will give up on this and try to figure out another workaround.

Thank you everyone for all your assistance.

[NeoPa]

time2hike:
NeoPa, I am sorry you don't feel I am paying attention. I have gone back and re-read all of the posts.

And yet you failed to notice, in the linked thread, that you need to post any code in the tags and also need to report the line number of any errors reported. Frankly, I'm only looking for any signs of effort though. If you looked but didn't see, then that may say other things about you, but not that you couldn't even be bothered to look (which is what I was reacting to). Long story short, please pay close attention to those instructions, but I'm happy that you did at least try.

Personally, I very rarely use ADODB as most of my work is within Access. I did some checking though, and it appears that what you have is sound code (I haven't used a fine-tooth comb mind you). The Open() method call uses the right arguments properly; The SQL is syntactically correct; The code around it makes sense. I can only say then, that I would see a value of -1 indicating that the recordset either failed to open at all, or opened but incorrectly (not as you intend). I cannot see more than this with the data available.

[ADezii]

Sometiimes you need to explicitly 'Traverse' the Recordset in order to attain a valid Count of Records, as in:

rs.moveLast:rs.MoveFirst
Msgbox "Number of Records in rs: " & rs.RecordCount 

[NeoPa]

Are you sure ADezii?

I've seen code that does that, but never, ever, seen anything to support the need for it. Certainly going to the end can be required, but returning to the start again is only required for practicality of processing the data. The count is available as soon as the end is first reached.

NB. The OP indicates in post #7 that they're already using this method.

[MikeTheBike]

Hi

My three penny worth

As I understand it, it is all to do with the cusor type, for instance in this code

Dim rs As ADODB.Recordset
 
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM tblLocations", CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
 
    MsgBox rs.RecordCount
 
    rs.Close
    rs.Open "SELECT * FROM tblLocations", CurrentProject.Connection, adOpenStatic, adLockBatchOptimistic
 
    MsgBox rs.RecordCount
 
    rs.Close
    Set rs = Nothing

the first message is -1 and the second is 9

!!

MTB

[NeoPa]

:Confused: Are you saying that using adOpenDynamic precludes the system from ever returning a value for .RecordCount (as you appear to be).

[MikeTheBike]

Hi NeoPa

Having finally found the relevant page in Help (what have Microsoft done to it - everything was so much easier in 2k3). The relevant sentance seems to be this

The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.

I have no idea what "depending on the data source" means!

Maybe the data source is not an Access database when it returns a value with a dynamic cursor!?

Don't know if this helps.


MTB

[NeoPa]

MikeTheBike:
Don't know if this helps.

Oh yes. You can be sure of that.

I would suspect that a dynamic cursor should work for any SQL engine that supports it. I would also expect Jet to be firmly within that list, but as you say, the documentation leaves much to be desired :-(

@time2hike
Over to you. This (MTB's post) gives you some explanation for why you may be seeing this behaviour. You may want to share where the data is coming from ([t_AllP_...] tables).

[ADezii]

MikeTheBike hit the nail on the head, and it should have been more obvious to me. You cannot expect a Dynamic Cursor to accurately reflect the number of Records in a Recordset, since this number may vary at any time given the nature of the Dynamic Cursor. Replace adOpenDynamic with either adOpenStatic or adOpenKeyset, and you should achieve the desired results. It is my understanding also that if you use a Client Side Cursor, RecordCount will always be correct (Server Side is the Default).

[ADezii]

Here is another little Trick that you can use using GetRows():

Dim rs As ADODB.Recordset
Dim strSQL As String
Dim varRecCount As Variant
 
strSQL = "SELECT * FROM [Order Details];"
 
Set rs = New ADODB.Recordset
 
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
 
varRecCount = rs.GetRows()
 
MsgBox "Record Count: " & FormatNumber((UBound(varRecCount, 2) + 1), 0)

OUTPUT:

2,156

[time2hike]

Thank you all for troublshooting this.