ADODB RecordCount

[tfurnivall]

I have a code snippet that I am expecting to return a valid recordcount from an ADODB.Recordset:

Private Sub cmdGoToPrevious_Click()

Dim FindCriteria As String
Dim SQLCommand As String
Dim SegmentedBrowse As Boolean

#If TraceLevel > 1 Then
    Debug.Print Time(), "STUDYDESIGN", "GoToPrev"
#End If
'   Test SegmentedBrowse
SegmentedBrowse = False

'   Build a record set and go to the first record
If RecordSetLoaded Then
   rs.Close
   Set rs = Nothing
End If
   
RecordSetLoaded = False
Set rs = New ADODB.Recordset
rs.Open "SELECT * from STDY_StudyControl", _
        CurrentProject.Connection, _
        adOpenDynamic, _
        adLockOptimistic
If rs.EOF Or rs.BOF Then
'  We have no surveys to look at, skip!
   Exit Sub
Else
   rs.MoveFirst
End If
RecordSetLoaded = True

'   Check for errors
rs.MoveFirst
Me.txtStudyID.SetFocus
If Me.txtStudyID.Text = "" Then
'  If there is no existing record, then use the first one!
Else
   FindCriteria = "STDY_StudyID= #" + Me.txtStudyID + "#"
   rs.Find FindCriteria
   rs.MovePrevious
End If
'   Create a new study and load the recordset fields

If Study Is Nothing Then
Else
   Set Study = Nothing
End If

If rs.BOF Then
   Me.cmdGoToPrevious.Enabled = False
Else
   Set Study = New STDY_Study
   Study.LoadDatabaseFields rs
   LoadObjectFields
   Me.cmdCreateSurvey.Visible = True
   Me.cmdCancelStudy.Caption = "Clear"
   Me.cmdStudyAction.Caption = "Update Study"
   Me.cmdGoToPrevious.Enabled = True
End If
Me.cmdGoToNext.Enabled = True
Me.txtStudyName.SetFocus
Me.Repaint

#If TraceLevel > 1 Then
    Debug.Print Time(), "STUDYDESIGN", "GoToPrev", "RecordCount="; rs.RecordCount, "BOF="; rs.BOF
    Debug.Print Time(), "STUDYDESIGN", "GoToPrev", Study.StudyID, Study.StudyDescription
#End If

My understanding is that rs.Open will allow me to specify (and get) a dynamic cursor (adCursorDynamic), which will, in turn, support the recordcount property.
However, I am still getting the -1 value, so obviously the Cursor type is not what I specified [aside] How silly to expect MS to do what I ask![/aside]

Where can I get a clear, comprehensible description of what cursortype to use when. The environment here is obviously a browse environment, but I need to be able to update a record that I find!

Also [aside]Why am I not surprised that there seems to be no way to do this![/aside] How can one test the success or otherwise of an ADODB call? If the cursor type is being changed on me I would expect to be able to get at some value, somewhere that said "Hey turkey! We've changed the cursortype because it is not compatible with what you seem to be asking for" . That at least would allow me to determine right away that I've made a mistake. I may very well, indeed probably have made a mistake, but it's only after things seem to work OK that you realize they're not.

Aaaagh!

Any help/hope?

Tony

 

[Galaxiom]

Use CurrentProject.AccessConnection as the connection object.

 

[tfurnivall]

Thanks, Galaxiom!

It worked like a charm. Of course it's only documented if you know to look for it, and most of the examples I've see use simple .Connection.

Tony