Delete Query Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.


--
Drwip


Allen Browne said:
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

drwip said:
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.
Click to expand...
Click to expand...
 
Back
Top