runtime 3021

[Kevin]

i am trying to delete an item from a list box by clicking on a
button. It's not working, obviously but the problem is that I dont
know why. I have this same code for list box 1 and it works just
fine. this one errors out on rst.delete with runtime 3021 : no
current record. What am i doing wrong??

Private Sub Command3_Click()
'delete from list box2
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM account WHERE
'account type' = '" & Me.list2 & "'")
rst.Delete
Me.list2.Requery


End Sub

 

[Dirk Goldgar]

i am trying to delete an item from a list box by clicking on a
button. It's not working, obviously but the problem is that I dont
know why. I have this same code for list box 1 and it works just
fine. this one errors out on rst.delete with runtime 3021 : no
current record. What am i doing wrong??

Private Sub Command3_Click()
'delete from list box2
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM account WHERE
'account type' = '" & Me.list2 & "'")
rst.Delete
Me.list2.Requery


End Sub

Your SQL statement incorrectly puts quotes around the [account type] field.
If you are going to do it via a recordset like that, this would be better:

'------ start of amended code #1 ------
Private Sub Command3_Click()

Dim rst As DAO.Recordset

With Me.list2

If Not IsNull(.Value) Then

Set rst = CurrentDb.OpenRecordset( _
"SELECT * FROM account WHERE [account type] = '" & _
.Value & "'")

rst.Delete
rst.Close

End If

.Requery

End With

End Sub
'------ end of amended code #1 ------

However, that is not the best way to do it. This would be better:

'------ start of amended code #2 ------
Private Sub Command3_Click()


With Me.list2

If Not IsNull(.Value) Then

CurrentDb.Execute _
"DELETE FROM account WHERE [account type] = '" & _
.Value & "'", _
dbFailOnError

End If

.Requery

End With

End Sub
'------ end of amended code #2 ------

 

[Tom van Stiphout]

On Tue, 3 Nov 2009 22:55:57 -0500, "Dirk Goldgar"

I agree the [ ] are better than what was there before, but the real
issue with the Recordset approach is that it does not test for EOF:
set rst = CurrentDB.OpenRecordset(...)
if rst.EOF then
Msgbox "Alarm: No record found. Unable to delete unfound record!"
else
rst.Delete ...


Your suggested Delete statement is OK, but better would be to run a
Delete query. I consider that better because it eliminates
hard-to-maintain inline SQL.
dim qd as dao.querydef
set qd = currentdb.querydefs("myDeleteQuery")
qd!myFirstParameter = ...
qd.Execute dbFailOnError

-Tom.
Microsoft Access MVP

i am trying to delete an item from a list box by clicking on a
button. It's not working, obviously but the problem is that I dont
know why. I have this same code for list box 1 and it works just
fine. this one errors out on rst.delete with runtime 3021 : no
current record. What am i doing wrong??

Private Sub Command3_Click()
'delete from list box2
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM account WHERE
'account type' = '" & Me.list2 & "'")
rst.Delete
Me.list2.Requery


End Sub

Your SQL statement incorrectly puts quotes around the [account type] field.
If you are going to do it via a recordset like that, this would be better:

'------ start of amended code #1 ------
Private Sub Command3_Click()

Dim rst As DAO.Recordset

With Me.list2

If Not IsNull(.Value) Then

Set rst = CurrentDb.OpenRecordset( _
"SELECT * FROM account WHERE [account type] = '" & _
.Value & "'")

rst.Delete
rst.Close

End If

.Requery

End With

End Sub
'------ end of amended code #1 ------

However, that is not the best way to do it. This would be better:

'------ start of amended code #2 ------
Private Sub Command3_Click()


With Me.list2

If Not IsNull(.Value) Then

CurrentDb.Execute _
"DELETE FROM account WHERE [account type] = '" & _
.Value & "'", _
dbFailOnError

End If

.Requery

End With

End Sub
'------ end of amended code #2 ------

 

[Dirk Goldgar]

On Tue, 3 Nov 2009 22:55:57 -0500, "Dirk Goldgar"

I agree the [ ] are better than what was there before, but the real
issue with the Recordset approach is that it does not test for EOF:
set rst = CurrentDB.OpenRecordset(...)
if rst.EOF then
Msgbox "Alarm: No record found. Unable to delete unfound record!"
else
rst.Delete ...

You're right that the code ought to check for EOF, just in case, so as to
give a less obscure error message. However, I concluded from the problem
description that the record *must* exist in the table, since it is displayed
in the list box. Therefore, the primary problem is that the recordset isn't
finding a record when the record exists.