Delete Query Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

drwip - 20 Mar 2006 03:45 GMT

The line of code shown below consistantly deletes one fewer records than it
should, i.e if there are three records that meet the delete criteria, ony two
are deleted.

Docmd.RunSQL("Delete From Output WHERE [Cntrl] = True")

Also, in debug the line below,
? Dcount("[LastName],"Output",[Cntrl] = True")
when typed in the immediate box also returns a number that is one less than
the correct number.

Tnx,

Don W.

Signature

Drwip

Reply to this Message

Allen Browne - 20 Mar 2006 04:22 GMT

Any chance it is the current record that has not been saved yet, and so does
not meet the criteria?

Additionally, if you turn off SetWarnings, RunSQL gives you no indication if
the query failed to execute. You can leave SetWarnings alone, and use the
Execute method with the dbFailOnError so you do get a message if the query
fails, and you can see what is going on.

Try:
If Me.Dirty Then Me.Dirty = False
dbEngine(0)(0).Execute "Delete From Output WHERE [Cntrl] = True;",
dbFailOnError

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> The line of code shown below consistantly deletes one fewer records than
> it
[quoted text clipped - 13 lines]
>
> Don W.

Reply to this Message

drwip - 20 Mar 2006 23:25 GMT

Allen - thanks so much - once again you have helped me.

You were correct in suggesting that the problem was an unsaved record before
the delete query was called. The fix was simple --

If(Me.dirty) then
Me.refresh
End if

inserted before the call to the delete query.

Again, Thanks!

Don W.

Signature

Drwip

> Any chance it is the current record that has not been saved yet, and so does
> not meet the criteria?
[quoted text clipped - 26 lines]
> >
> > Don W.

Reply to this Message