.RecordCount is always -1

[mheiy]

my .recordcount is always -1 what will i do... im using an mysql database.. here's my code pls help me

Option Explicit
Dim AY As String
Dim ls As ListItem
 
 
Private Sub cmdAdd_Click()
frmAcademicYear.addstate = True
frmAcademicYear.Show
 
End Sub
 
Private Sub cmdClose_Click()
Unload Me
End Sub
 
Private Sub cmdDelete_Click()
On Error GoTo Err:
With tbAcademicYear
    '---------------------------------
    'Check if there is no record
    '---------------------------------
'    If .RecordCount < 1 Then MsgBox "No school year in the list.Please check it!", vbExclamation, "CSRS version 1": Exit Sub
    '---------------------------------
    'Confirm deletion of record
    '---------------------------------
    Dim ans As Integer
    Dim pos As Integer
    ans = MsgBox("Are you sure you want to delete the selected record?", vbCritical + vbYesNo, "Confirm Record Delete")
    If ans = vbYes Then
        '-----------------------------
        'Delete the record
        '-----------------------------
        pos = Val(LV1.SelectedItem)
       '  tbAcademicYear.Open "Select * from tbacademicyear where AcademicYearTitle='" & LV1.SelectedItem.Text & "'", cn, adOpenDynamic, adLockOptimistic
             If .EOF = False Then
                .Delete adAffectCurrent
                '.Requery
             End If
        
        If .RecordCount > 0 Then
            .AbsolutePosition = pos
            If .EOF Then .MoveFirst
            '---------------------------------
            'Fill listview
            '---------------------------------
            pos = .AbsolutePosition
            load_rec
            LV1.ListItems.Item(pos).EnsureVisible
            LV1.ListItems.Item(pos).Selected = True
            .AbsolutePosition = LV1.SelectedItem
            '---------------------------------
            'End-fill listview
            '---------------------------------
        'Else
           ' LV1.ListItems.Clear
        End If
                MsgBox "Record has been successfully deleted.", vbInformation, "Confirm"
    End If
    ans = 0
    pos = 0
    Me.MousePointer = vbDefault
End With
Exit Sub
Err:
    MsgBox Err.Description, vbCritical, "Academic Year"
 
End Sub
 
Private Sub cmdEdit_Click()
 
With tbAcademicYear
'If .RecordCount < 1 Then MsgBox "No school year in the list.Please check it!", vbExclamation, "Titus School of Multimedia": Exit Sub
 
If Not LV1.SelectedItem = "" And Not tbAcademicYear.RecordCount < 1 Then .AbsolutePosition = LV1.SelectedItem
frmAcademicYear.addstate = False
frmAcademicYear.Show
Me.Enabled = False
'End If
End With
End Sub
 
Private Sub Form_Load()
tbAcademicYear.Open "Select * from tbacademicyear", cn, adOpenDynamic, adLockOptimistic
CallFields
End Sub
 
Private Sub CallFields()
With tbAcademicYear
    LV1.ListItems.Clear
    While .EOF = False
        Set ls = LV1.ListItems.Add(, , .Fields(0))
        .MoveNext
    Wend
End With
End Sub
 
Sub load_rec()
Screen.MousePointer = vbHourglass
 
CallFields
Screen.MousePointer = vbDefault
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
tbAcademicYear.Close
End Sub

[Hack]

Use either adOpenKeyset or adOpenStatic as the CursorType for server side cursors or use a client side cursor. Client side cursors use only adOpenStatic for CursorTypes regardless of which CursorType you select.

[mheiy]

still not working

[ggettings]

.recordcount can be problematic. If you are only using it though to see if there is a record then just use:

If .EOF = False Then
      msgbox "There Is at least 1 Record"
End If

Edit: Not problematic any more after reading this thread.

[mheiy]

whta will i do if my .absoluteposition is always = -3

[mheiy]

my .absoluteposition is always = -3

[Besoup]

rs.CursorType = adOpenStatic

should fix the problem.

[Hack]

rs.CursorType = adOpenStatic

should fix the problem.

This WILL fix the problem. I use it all the time when I need recordcounts.

[mheiy]

then its running now but my .absoluteposition is always = -3

[szlamany]

.RecordCount equal to -1 means that the client side does not yet know the number of rows in the recordset.

This always means there are some rows - you will never get -1 when the true recordcount is 0.

In order to get the recordcount when it's showing as -1 you need to "read" all the rows - MOVELAST or something to that affect.

but - in my opinion - you never need to know the record count specifically...

[mheiy]

its running now but my .absoulteposition is always = -3

[szlamany]

Immediately after you get the recordset do a MOVELAST followed by a MOVEFIRST - this should resolve the RECORDCOUNT immediately...

[techgnome]

Hehehe.... that's what we call "Jiggling the Recordset" around here....

-tg