error 3167: Record is deleted

[Max Moor]

Hi All,
I have a form with a listbox (and other controls) on it. The listbox
has it's own query for its row source. The form's record source is a query
that refers to the selection in the listbox. This way, the controls on the
rest of the form reflect detail data about the highlighted item.

The problem is that when I delete a record (in VB), then call
Me.Requery, I get a 3167 error saying the record is deleted. Sure enough, if
I look in the listbox, "#Deleted" is there. The odd thing is that if I click
on Debug when I get the error, I can immediately press F5 to run the code,
and it completes without repeating the error.

I've tried requerying the listbox before I call Me.Requery to requery
the form, but it doesn't help. The only solution I've found online was that
the database might be corrupted. I doubt this, but did a compact and repair
just to be sure. It didn't help.

I'm developing in Access 2002, by the way. Can anyone point me to a
solution?

Thanks, Max

 

[Dirk Goldgar]

Hi All,
I have a form with a listbox (and other controls) on it. The
listbox
has it's own query for its row source. The form's record source is a
query that refers to the selection in the listbox. This way, the
controls on the rest of the form reflect detail data about the
highlighted item.

The problem is that when I delete a record (in VB), then call
Me.Requery, I get a 3167 error saying the record is deleted. Sure
enough, if I look in the listbox, "#Deleted" is there. The odd thing
is that if I click on Debug when I get the error, I can immediately
press F5 to run the code, and it completes without repeating the
error.

I've tried requerying the listbox before I call Me.Requery to
requery
the form, but it doesn't help. The only solution I've found online
was that the database might be corrupted. I doubt this, but did a
compact and repair just to be sure. It didn't help.

I'm developing in Access 2002, by the way. Can anyone point me to
a solution?

Thanks, Max

What is the code you're using to delete the record? Am I right in
understanding that you're deleting the record from the form's
recordsource, not the list box's rowsource? Are these derived from the
same table?

 

[Max Moor]

What is the code you're using to delete the record? Am I right in
understanding that you're deleting the record from the form's
recordsource, not the list box's rowsource? Are these derived from the
same table?

Hi Dirk,
Thanks for the response. Sorry for not being more complete.

tblGroups is the underlying table that both the listbox and form
query. The code that deletes the group record is:

Dim ADOCon As ADODB.Connection
Dim strSQL As String

Set ADOCon = CurrentProject.Connection
strSQL = "DELETE FROM tblGroups WHERE (tblGroups.idxGroup = " & lIdxGroup
& ")"
ADOCon.Execute strSQL

lIdxGroup is the record's unique index. I do the 'Me.Requery' right
after this code runs. My assumption had been that with the record gone in
the table, I could requery the list first, then the form, and be okay, but
even requerying the listbox first, I still get the error.


- Max

 

[Max Moor]

Hello Again Dirk,

I found that if I selected a different record in the list and requeried
the form before the deletion, then I could do the deletion and subsequent
requery without error.

I know from stepping through the code that the listbox was getting
updated before the requery of the form, so the error had something to do with
the form's recordset still pointing to the deleted record, regardless of the
listbox's contents. I still don't quite understand the mechanism behind the
error, but at least it's gone.

If you (or anyone) can explain it to me, I'd still like to understand.

Thanks, Max

 

[Dirk Goldgar]

Hello Again Dirk,

I found that if I selected a different record in the list and
requeried the form before the deletion, then I could do the deletion
and subsequent requery without error.

I know from stepping through the code that the listbox was getting
updated before the requery of the form, so the error had something to
do with the form's recordset still pointing to the deleted record,
regardless of the listbox's contents. I still don't quite understand
the mechanism behind the error, but at least it's gone.

If you (or anyone) can explain it to me, I'd still like to
understand.

I'm not sure. It could be that it's a timing problem, and that using
some combination of

DoEvents

and

Application.DBEngine.Idle dbRefreshCache

after your delete would force Access to synchronize with the updated
database. I don't know if that would work in an ADP, if that's what
you're using, but it might work in an MDB.

Or it may just be that the value of the list box remains, even after you
requery it so that its current value is no longer in its list. In that
case, you might just set its value to Null before requerying things.
I'd certainly try that before fiddling around with the methods I
mentioned above.