.RecordCount is always -1

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

VB Code:

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

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.

still not working

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

VB Code:

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

Edit: Not problematic any more after reading this thread. :blush:

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

my .absoluteposition is always = -3

rs.CursorType = adOpenStatic

should fix the problem.

Quote:

---

Originally Posted by Besoup

rs.CursorType = adOpenStatic

should fix the problem.

---

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

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

.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...

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

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

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

-tg