By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

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

P: 68
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.

Expand|Select|Wrap|Line Numbers
  1.     'Create Recordset
  2.     Dim rs As ADODB.Recordset
  3.     Dim sSQL As String
  4.     Dim rs1 As ADODB.Recordset
  5.     Dim sSQL1 As String
  6.     Dim sPID As String
  7.     Dim sStat As String
  8.     Dim stErr As String
  9.     Dim lngRcdCnt As Long
  10.  
  11.     sPID = [Forms]![frm_Employee]![PersonID] ' this is pulling correct value 
  12.     sStat = [Forms]![frm_Employee]![StatusID] ' this is pulling Correct Value
  13.     stErr = ""
  14.  
  15.     Set rs1 = New ADODB.Recordset
  16.     sSQL1 = "SELECT Max(t_AllP_Position.PosnEfftDate) AS MaxPosnEfftDate " _
  17.         & "FROM t_AllP_Position " _
  18.         & "HAVING t_AllP_Position.PersonID= " & sPID & " AND " _
  19.         & "t_AllP_Position.StatusID= " & sStat
  20.     Debug.Print sSQL1
  21.     rs1.Open sSQL1, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
  22.     rs1.MoveLast
  23.     Debug.Print rs1.RecordCount
  24.     Set rs = New ADODB.Recordset
  25.     sSQL = "SELECT t_AllP_StatusHistory!PersonID, t_AllP_StatusHistory!StatusID, " _
  26.         & "t_AllP_StatusHistory!RoleStatus, t_AllP_StatusHistory!Status_End, " _
  27.         & "t_AllP_Position!PosnEfftDate, t_AllP_Position!PosnCurrent " _
  28.         & "FROM t_AllP_StatusHistory INNER JOIN t_AllP_Position ON t_AllP_StatusHistory.StatusID = t_AllP_Position.StatusID " _
  29.         & "AND t_AllP_StatusHistory.PersonID = t_AllP_Position.PersonID " _
  30.         & "WHERE t_AllP_StatusHistory.PersonID = " & sPID _
  31.         & " AND t_AllP_StatusHistory.StatusID= " & sStat & " AND t_AllP_Position.PosnCurrent= Yes"
  32.  
  33.     Debug.Print sSQL
  34.     rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
  35.     rs.MoveLast
  36.     Debug.Print rs.RecordCount
  37.     'Verify the Recordset loaded with 1 and only 1 record
  38.  
  39.  
  40.     If rs.BOF And re.EOF Then
  41.         MsgBox "Problem with Recordset used to verify data prior to loading qualifications. " & vbCrLf _
  42.         & "Document the Person's Name, PersonID, and StausID and let the System Administrator know you received this message." _
  43.         , vbCritical + vbOKOnly, "Recordset Record Count = 0"
  44.         Exit Sub
  45.     End If
Mar 25 '12 #1

✓ answered by 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

Share this Question
Share on Google+
18 Replies


ADezii
Expert 5K+
P: 8,619
There is no Recordset Object re, Error would occur on the first Line of the If...End If Statement.
Expand|Select|Wrap|Line Numbers
  1. If rs.BOF And re.EOF Then       're does not exist
  2.   MsgBox "Problem with Recordset used to verify data prior to loading qualifications. " & vbCrLf _
  3.           & "Document the Person's Name, PersonID, and StausID and let the System Administrator know you received this message." _
  4.           , vbCritical + vbOKOnly, "Recordset Record Count = 0"
  5.      Exit Sub
  6. End If
  7.  
Mar 25 '12 #2

P: 68
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.
Mar 25 '12 #3

Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. 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
Mar 25 '12 #4

P: 68
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.

Expand|Select|Wrap|Line Numbers
  1. 'Test for Employee Status = PCUR
  2. If rs!RoleStatus <> "PCUR" Then
  3.     stErr = stErr & vbCrLf & "This is not the Person's Current Role "
  4. End If
  5. 'Test for the Status End Field being Null
  6. If Not IsNull(rs!StatusEnd) Then
  7.     stErr = stErr & vbCrLf & "This Role is recorded as having ended on " & rs!StatusEnd
  8. End If
Mar 25 '12 #5

NeoPa
Expert Mod 15k+
P: 31,409
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.
Mar 25 '12 #6

P: 68
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.

Expand|Select|Wrap|Line Numbers
  1. rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
  2.     rs.MoveLast
  3.     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.

Expand|Select|Wrap|Line Numbers
  1.     'Test for Multiple Current Positions
  2.     If rs.RecordCount > 1 Then
  3.         stErr = stErr & vbCrLf & "Person has multiple Current Positions "
  4.     End If
Thank you so much for you help!
Attached Files
File Type: docx Code1.docx (15.3 KB, 330 views)
Mar 25 '12 #7

NeoPa
Expert Mod 15k+
P: 31,409
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.
Mar 26 '12 #8

P: 68
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.
Mar 26 '12 #9

NeoPa
Expert Mod 15k+
P: 31,409
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.
Mar 26 '12 #10

ADezii
Expert 5K+
P: 8,619
Sometiimes you need to explicitly 'Traverse' the Recordset in order to attain a valid Count of Records, as in:
Expand|Select|Wrap|Line Numbers
  1. rs.moveLast:rs.MoveFirst
  2. Msgbox "Number of Records in rs: " & rs.RecordCount 
Mar 26 '12 #11

NeoPa
Expert Mod 15k+
P: 31,409
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.
Mar 27 '12 #12

Expert 100+
P: 634
Hi

My three penny worth

As I understand it, it is all to do with the cusor type, for instance in this code
Expand|Select|Wrap|Line Numbers
  1. Dim rs As ADODB.Recordset
  2.  
  3.     Set rs = New ADODB.Recordset
  4.     rs.Open "SELECT * FROM tblLocations", CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
  5.  
  6.     MsgBox rs.RecordCount
  7.  
  8.     rs.Close
  9.     rs.Open "SELECT * FROM tblLocations", CurrentProject.Connection, adOpenStatic, adLockBatchOptimistic
  10.  
  11.     MsgBox rs.RecordCount
  12.  
  13.     rs.Close
  14.     Set rs = Nothing
the first message is -1 and the second is 9

!!

MTB
Mar 27 '12 #13

NeoPa
Expert Mod 15k+
P: 31,409
:Confused: Are you saying that using adOpenDynamic precludes the system from ever returning a value for .RecordCount (as you appear to be).
Mar 27 '12 #14

Expert 100+
P: 634
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
Mar 27 '12 #15

NeoPa
Expert Mod 15k+
P: 31,409
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).
Mar 27 '12 #16

ADezii
Expert 5K+
P: 8,619
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).
Mar 27 '12 #17

ADezii
Expert 5K+
P: 8,619
Here is another little Trick that you can use using GetRows():
Expand|Select|Wrap|Line Numbers
  1. Dim rs As ADODB.Recordset
  2. Dim strSQL As String
  3. Dim varRecCount As Variant
  4.  
  5. strSQL = "SELECT * FROM [Order Details];"
  6.  
  7. Set rs = New ADODB.Recordset
  8.  
  9. rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockBatchOptimistic
  10.  
  11. varRecCount = rs.GetRows()
  12.  
  13. MsgBox "Record Count: " & FormatNumber((UBound(varRecCount, 2) + 1), 0)
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. 2,156
Mar 28 '12 #18

P: 68
Thank you all for troublshooting this.
Apr 7 '12 #19

Post your reply

Sign in to post your reply or Sign up for a free account.