Delete query doesn't delete records

[Paul Fenton]

In an application we run, to produce a certain report of daily
activities in a certain date range, we create a temporary table
containing all the records that meet the user's criteria. Some of
those records have start dates or end dates outside the user's
criteria, so we need to delete those records.

The temp table looks like this:

Name Task DayOfWeek Start End
Jones Paint 1 2/2/09 2/27/09
Jones Paint 2 2/2/09 2/27/09
Jones Pain 3 2/2/09 2/27/09
etc.

The table in this case would have 26 records for Jones. If the user
asks to see only the week of 2/16, he only wants 7 records on his
report, Monday thru Saturday, so we need to delete all the other
records.

Here is the code that eliminates the records from the temporary table
prior to running the report:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qDeleteContractorsReportRecords"
DoCmd.SetWarnings True

And here is the query "qDeleteContractorsReportRecords"

DELETE ContractorsScheduleTemp.Start
FROM ContractorsScheduleTemp
WHERE
(((ContractorsScheduleTemp.Start)<[forms]![contractorsschedule].[txtStart]
Or
(ContractorsScheduleTemp.Start)>[forms]![contractorsschedule].[txtend]));

Where the form "contractorsschedule" has the start and end dates the
user wants, 2/16 thru 2/21 in this case.

The problem is that the records don't always delete.

If I stop the code at the "SetWarnings False" line however, and then
run the query manually from the database window, it works.

I'm stumped. What's wrong with this solution?

Paul Fenton

 

[Douglas J. Steele]

What exactly is being put in txtStart and txtEnd? If it's only 2/16 and
2/21, I'm surprised you're not getting an error (as opposed to just a
warning).

First, try changing your SQL statement to

PARAMETERS [forms]![contractorsschedule].[txtStart] DateTime,
[forms]![contractorsschedule].[txtend] DateTime;
DELETE ContractorsScheduleTemp.Start
FROM ContractorsScheduleTemp
WHERE
ContractorsScheduleTemp.Start<[forms]![contractorsschedule].[txtStart]
Or
ContractorsScheduleTemp.Start)>[forms]![contractorsschedule].[txtend]

(don't forget the semi-colon between DateTime and DELETE)

Then, instead of

DoCmd.SetWarnings False
DoCmd.OpenQuery "qDeleteContractorsReportRecords"
DoCmd.SetWarnings True

try using

CurrentDb.QueryDefs("qDeleteContractorsReportRecords").Execute,
dbFailOnError

That will prevent the "Access is about to delete..." message, plus raise a
trappable error should something go wrong with the query.

 

[Douglas J. Steele]

What exactly is being put in txtStart and txtEnd? If it's only 2/16 and
2/21, I'm surprised you're not getting an error (as opposed to just a
warning).

First, try changing your SQL statement to

PARAMETERS [forms]![contractorsschedule].[txtStart] DateTime,
[forms]![contractorsschedule].[txtend] DateTime;
DELETE ContractorsScheduleTemp.Start
FROM ContractorsScheduleTemp
WHERE
ContractorsScheduleTemp.Start<[forms]![contractorsschedule].[txtStart]
Or
ContractorsScheduleTemp.Start)>[forms]![contractorsschedule].[txtend]

(don't forget the semi-colon between DateTime and DELETE)

Then, instead of

DoCmd.SetWarnings False
DoCmd.OpenQuery "qDeleteContractorsReportRecords"
DoCmd.SetWarnings True

try using

CurrentDb.QueryDefs("qDeleteContractorsReportRecords").Execute,
dbFailOnError

That will prevent the "Access is about to delete..." message, plus raise a
trappable error should something go wrong with the query.

 

[Paul Fenton]

Thank you, Doug. I'm getting a compile error on the word "EXECUTE" in
the line

CurrentDb.QueryDefs("qDeleteContractorsReportRecords").Execute,

"Wrong number of arguments or invalid property assignment"

In my code, I put the phrase "dbFailOnError" right after the .Execute,

Paul Fenton

 

[Paul Fenton]

Sorry...

I'm also getting a "Too Few Parameters. Expected 2" error on the
Currentdb.QueryDefs... line.


Paul Fenton

 

[Douglas J. Steele]

Sorry, my fault. I knew you're working with parameters, and I completely
overlooked that.

Dim qdfCurr As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set qdfCurr = CurrentDb.QueryDefs("qDeleteContractorsReportRecords").
For Each prmCurr In qdfCurr.Parameters
prmCurr.Value = Eval(prmCurr.Name)
Next prmCurr
qdfCurr.Execute, dbFailOnExecute

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you, Doug. I'm getting a compile error on the word "EXECUTE" in
the line

CurrentDb.QueryDefs("qDeleteContractorsReportRecords").Execute,

"Wrong number of arguments or invalid property assignment"

In my code, I put the phrase "dbFailOnError" right after the .Execute,

Paul Fenton

What exactly is being put in txtStart and txtEnd? If it's only 2/16 and
2/21, I'm surprised you're not getting an error (as opposed to just a
warning).

First, try changing your SQL statement to

PARAMETERS [forms]![contractorsschedule].[txtStart] DateTime,
[forms]![contractorsschedule].[txtend] DateTime;
DELETE ContractorsScheduleTemp.Start
FROM ContractorsScheduleTemp
WHERE
ContractorsScheduleTemp.Start<[forms]![contractorsschedule].[txtStart]
Or
ContractorsScheduleTemp.Start)>[forms]![contractorsschedule].[txtend]

(don't forget the semi-colon between DateTime and DELETE)

Then, instead of

DoCmd.SetWarnings False
DoCmd.OpenQuery "qDeleteContractorsReportRecords"
DoCmd.SetWarnings True

try using

CurrentDb.QueryDefs("qDeleteContractorsReportRecords").Execute,
dbFailOnError

That will prevent the "Access is about to delete..." message, plus raise a
trappable error should something go wrong with the query.

 

[Paul Fenton]

Thanks again, Doug. Here's what's happening.

I substituted the code below and when I compiled, I got a "variable
not defined" on 'dbFailOnExecute' word. When I remove it, the compile
succeeds.

When I ran it, the records that were before and after the start and
end dates were still there, so I put a STOP right before your code and
noticed something very strange.

If I sep through the code (F8), the unwanted records get deleted from
the temp table. However, if I "F5" at the STOP line, they are not.
Any ideas?


Paul Fenton

 

[Douglas J. Steele]

Sorry, that should be

qdfCurr.Execute dbFailOnExecute

(no comma between them either...)

Afraid I have no idea why you're seeing what you're seeing. Perhaps using
the dbFailOnError will raise a meaningful error message.

 

[Paul Fenton]

Doug, I changed "dbFailOnExecute" to "dbFailOnError" and it compiled
fine. The other problem was just some weird quirk probably because
I'm testing this on a wireless network, which is a no-no with an
Access database. In production, they're wired and don't see that
behavior.

It was the Parameters thing that solved the original problem, so once
again, thank you so much for your help.


Paul Fenton