current recordset does not support scrolling backward

[mheiy]

im going to delete a record but i encounter an error of current recordset does not support scrolling backward..
here's my code

Private Sub cmdDelete_Click()
With tbDepartment
ans = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Wait")
        If ans = vbYes Then
           .Find "DepartmentName = '" & txtDepTitle.Text & "'"
            If .EOF = True Then
                MsgBox "Huh?"
            Else
                .Delete
                .Requery
                txtDepTitle = ""
            End If
        Else
            Refresh
        End If
End With
End Sub

Private Sub cmdGetDepartment_Click()
PickDepartment.Show
PickDepartment.cmdSelect.Visible = False
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub Form_Load()
tbdepartment.open "Select * from tbdepartment", cn. adopendynamic, adloackoptimistic
end sub

[TysonLPrice]

Is this "adloackoptimistic" really in your code or just a pasting error?

[zynder]

I think it's adLockOptimistic.

[westconn1]

also i believe should be comma after cn, not period

[mheiy]

Private Sub cmdDelete_Click()
With tbDepartment
ans = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Wait")
If ans = vbYes Then
.Find "DepartmentName = '" & txtDepTitle.Text & "'"
If .EOF = True Then
MsgBox "Huh?"
Else
.Delete
.Requery
txtDepTitle = ""
End If
Else
Refresh
End If
End With
End Sub

Private Sub cmdGetDepartment_Click()
PickDepartment.Show
PickDepartment.cmdSelect.Visible = False
End Sub

Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub Form_Load()
Set tbDepartment = Nothing
dept = "Select * from tbdepartment where DepartmentName like '" & txtDepTitle.Text & "'"
tbDepartment.Open dept, cn, adOpenDynamic, adLockOptimistic

End Sub

[danasegarane]

Try this

rivate Sub cmdDelete_Click()
With tbDepartment
ans = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Wait")
        If ans = vbYes Then
           .Find "DepartmentName = '" & txtDepTitle.Text & "'"
            If .EOF = True Then
                MsgBox "Huh?"
            Else
                .Delete
                  [B] ' .Requery[/B] 'Comment this line and run
                txtDepTitle = ""
            End If
        Else
            Refresh
        End If
End With
End Sub

[mheiy]

it was running now but it always show the msgbox. and the .EOF is always true

Private Sub cmdDelete_Click()
'Set tbDepartment = Nothing
'dept = "Select * from tbdepartment where DepartmentName like '" & txtDepTitle.Text & "'"
'tbDepartment.Open dept, cn, adOpenDynamic, adLockOptimistic
 
tbDepartment.Open "Select * from tbdepartment", cn, adOpenDynamic, adLockOptimistic
 
With tbDepartment
ans = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Wait")
        If ans = vbYes Then
           .Find "DepartmentName = '" & txtDepTitle.Text & "'"
            If .EOF = True Then
                MsgBox "Huh?"
            Else
                .Delete
              '  .Requery
                txtDepTitle = ""
            End If
        Else
            Refresh
        End If
End With
End Sub

[danasegarane]

I think you can try this method

Private Sub cmdDelete_Click()
'Set tbDepartment = Nothing
'Dim tbDepartment As New ADODB.Recordset
'dept = "Select * from tbdepartment where DepartmentName like '" & txtDepTitle.Text & "'"
'tbDepartment.Open dept, cn, adOpenDynamic, adLockOptimistic
 
 
 
 
    If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Wait") = vbYes Then
            tbDepartment.Open "Select * from tbdepartment where DepartmentName='" & txtDepTitle.Text & "'", cn, adOpenDynamic, adLockOptimistic
            If tbDepartment.EOF = False Then
                 tbDepartment.Delete adAffectCurrent
             Else
                MsgBox "Huh?"
                 txtDepTitle = ""
             End If
            
    End If
End Sub

[mheiy]

whats the use of adaffectcurrent

[mheiy]

still not working and have the same error

[mheiy]

it was running now thank you

[danasegarane]

Try this also

Private Sub cmdDelete_Click()
'Set tbDepartment = Nothing
'Dim tbDepartment As New ADODB.Recordset
'dept = "Select * from tbdepartment where DepartmentName like '" & txtDepTitle.Text & "'"
'tbDepartment.Open dept, cn, adOpenDynamic, adLockOptimistic
 
 
 
 
    If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Wait") = vbYes Then
            'tbDepartment.Open "Select * from tbdepartment where DepartmentName='" & txtDepTitle.Text & "'", cn, adOpenDynamic, adLockOptimistic
            cn.Execute ("Delete * from tbdepartment where DepartmentName='" & txtDepTitle.Text & "'")
'            If tbDepartment.EOF = False Then
'                 tbDepartment.Delete adAffectCurrent
'             Else
'                MsgBox "Huh?"
'                 txtDepTitle = ""
'             End If
            
    End If
End Sub

[danasegarane]

Running Now ?

[mheiy]

how about this one.... when i'm adding a new data and my table is empty i always encounter the .eof or .bof is closed or deleted. how can i avoid this even if my table is empty

[danasegarane]

tbDepartment.Open "Select * from tbdepartment where 1<>1", cn, adOpenDynamic, adLockOptimistic
tbDepartment.addnew
'Do your additions
tbDepartment.update

[mheiy]

in all my tables when i' going to add i have to this code same with other tables

tbDepartment.Open "Select * from tbdepartment where 1<>1", cn, adOpenDynamic, adLockOptimistic
tbDepartment.addnew
'Do your additions
tbDepartment.update

[mheiy]

i still have the same error either .eof or .bof is true, or current recordset has been deleted. requested operation requires a record... this the error i always got when that table is empty

[RobDog888]

The Find method requires you to initialize the recordset with a .Movexxxxx call. Either MoveFirst or MoveLast depending on the search direction you will be performing.

You can pass the argument of adSearchForward or adSearchBackwards. Depending on the direction of the search, if no matching record is found then the rs will be positioned at either the BOF or EOF.

I personally dont like using Find as its just creates more work/code to use when you can just call the delete action in a sql statement call. The call will still be made to the db with the Find plus you will have to execute the delete too so by passing the "DELETE Table1 FROM Table1 WHERE SomeField = 'Something'" is preferred.