Delete a record

[jjinx]

I am trying to delete a recored from a table but when I try to I get error

"Either EOF or BOF is true, or current record
already been deleted"

As far as I can tell none of this is true.

Any ideas why this is happening?

 

[vb5prgrmr]

What kind of database? What technology (ADO, RDO, DAO)? What does your code look like where you get this error?

 

[jjinx]

I am trying to access an Access database with an Adodc control as follows.

MsgBox (Combo1.Text)
data2.Recordset.Find ("UserID = '" & Combo1.Text & "'&quot
data2.Recordset.Delete

the value in the combo box is obtained from the same table and field.

the correct value appears in the msgbox but the data control can not find it.

 

[vb5prgrmr]

Well to help you debug this problem I see you use the find method. Are you sure it finds a record? If it searches from beginning to end and does not find the correct record EOF will be true which means that you will then encounter your error because the record has not been found.

Although another problem may be happening also. If you deleted record 3 it will move to record 4 and if you have not repositioned the cursor to the beginning then the find may miss the value if the value you are searching for is either 1 or 2.

Also after a successful delete do you refresh your recordset? If your combobox is not bound do you remove the value from the combo box once there has been a successful deletion?

Also is userid a number? If it is then you will need to remove the single ticks from around the combo1 in your find statement.

I Hope this helps! I know someone else out there may know exactly what may be wrong. Good Luck

 

[CCLINT]

Ok. As vb5prgrmr has touched upon, take advantage of the EOF marker. Under DAO, there was the NoMatch property and the record wasn't moved to EOF.

So, use the same logic (this was typed on-the-fly so you may need to fine tune it - it is meant to just expose a possible strategy):

Dim bmkCurrentRec
Dim bmkDeleteRec

With data2.Recordset
If Not (.BOF AND .EOF) Then
bmkCurrentRec = .Bookmark
.Find ("UserID = '" & Combo1.Text & "'&quot
If Not .EOF Then
bmkDeleteRec = .Bookmark
.Delete
End If

''Move back to previous record if desired
'If .CompareBookmark(bmkCurrentRec,bmkDeleteRec) = adCompareNotEqual Then
''The previous record, (the one before the Find method was called), is not the deleted record. So reposition back
'.Bookmark = bmkCurrentRec
'Else
''The previous (or last) record has been deleted.
''Move to the previous (or first) record
'If Not .BOF Then .Moveprevious
''If still not .BOF then we are OK, but if BOF and there are more records (Not EOF) then move off of BOF to the first record
'If .BOF And Not .EOF Then
' .MoveNext
'Else
'' No more records
'End If

End With


Or, create an ADO recordset clone and delete the record with that, and afterwards destroying the cloned recordset, or use an Action query with the connection/command Execute method (use the same connection as for the Data control or original recordset) to delete the record, and then run the resync or requery method on the first recordset. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}