Querydef execute returning a runtime error

Question

I am creating a querydef that grabs everything from another query, applies a parameter and exports to excel. This worked fine for months until yesterday, when it started returning runtime error 3021 - no current record.

The error occurs on qdf.execute.

I have another database that is basically the exact same thing with the exact same code, this works fine.

I have tried to put the sql string into a new query manually, this also works fine.


    Dim Dstring As Date

    Dstring = asofdate

    Set qdf = CurrentDb.CreateQueryDef("")

    qdf.SQL = "SELECT * INTO WIP_Report FROM Qry_WIP_Percentage"
    qdf.Parameters("AsofDate") = Dstring
    qdf.Execute

There are about 900 records in the query, and the parameter is being applied.

this is the SQL from the query -

SELECT Left([Qry_WIP].[rollup],5) AS Parent, Qry_WIP.rollup AS [Shared Parent], " " AS [Previous Wip], Qry_WIP.WIP AS [Current WIP], Qry_WIP.[PO Price Summed] AS [PO Value], "" AS [Anticipated PO], " " AS [Previous Percent Complete], Qry_Percentage.Percentage AS [Current Percent Complete], Qry_WIP.[prod code], Qry_WIP.[cust-id], " " AS [Customer / Project], " " AS Notes, " " AS [Previous Amt to Recognize], " " AS [Previous Status], "" AS [Current Amt to Recognize], Qry_WIP.status AS [Current Status], Qry_WIP.[enter date]
FROM Qry_WIP LEFT JOIN Qry_Percentage ON Qry_WIP.rollup = Qry_Percentage.rollup
GROUP BY Left([Qry_WIP].[rollup],5), Qry_WIP.rollup, Qry_WIP.WIP, Qry_WIP.[PO Price Summed], Qry_Percentage.Percentage, Qry_WIP.[prod code], Qry_WIP.[cust-id], Qry_WIP.status, Qry_WIP.[enter date]

No clue what is happening here as it worked perfectly the other day.

I see there are a bunch of questions about no current record, however, those all appear to be for recordsets not a querydef.

Based on what you've said - its hard to know what is happening. Especially since you've basically re-traced it manually without issue. I would try stepping through the code line by line and check out the value in your `Dstring`. — ArcherBird, Jun 13 '19 at 15:55
If it worked before today - its likely something to do with file/table access. Is WIP_Report a table local to this access file? Are you able to open and look at this table manually? — ArcherBird, Jun 13 '19 at 16:02
Possibly you need to [compact & repair](https://support.office.com/en-us/article/compact-and-repair-a-database-6ee60f16-aed0-40ac-bf22-85fa9f4005b2), [decompile and recompile](https://stackoverflow.com/questions/3266542/how-does-one-decompile-and-recompile-a-database-application) the Access app. At times the VBA and objects need a clean out or resetting due to usage. And if you have corrupt objects, create a new database and import all objects. — Parfait, Jun 13 '19 at 16:22
Dstring is the proper value, it is a date taken from the form that executes the code. WIP_Report is created and deleted in the code, just a temporary. — Warcupine, Jun 13 '19 at 17:03
Usually this happens when your QueryDef returns no records. If that is the case, add some error handling, and if the error is 3021, ignore it — kismert, Jun 13 '19 at 17:09
[enter date] is just a field in the query, AsOfDate is a parameter that gets applied to [close-date] which isn't displayed in qry_wip_percentage, it's in one of the queries that it is built on top of. — Warcupine, Jun 14 '19 at 13:02
I'm rather surprised that `qdf.Parameters("AsofDate")` would ever work at all, since it's not a parameter of that query. I would suggest to explicitely declare the parameter in this query and all queries down the chain that use it or pass it on. — Andre, Jun 14 '19 at 13:24
They should flow up, at least every time I've done something like this. I just tried to declare parameter all the way down and it still gave the error, I also tried to just remove the parameter all together and put a date into the criteria of each query using it. That still returned the error, so I don't think the parameter is the issue. — Warcupine, Jun 14 '19 at 13:54

Gustav · Answer 1 · 2019-06-15T10:09:24.207

0

First, double-check that the parameter in the query is explicitly declared as DateTime.

Second, convert to date value:

Dstring = DateValue(asofdate)

Edit:

Try to move the parameter forward:

qdf.SQL = "PARAMETERS Asofdate DateTime; SELECT * INTO WIP_Report FROM Qry_WIP_Percentage"
qdf.Parameters("AsofDate").Value = asofdate

edited Jun 15 '19 at 10:09

answered Jun 13 '19 at 20:18

Gustav

53,498
7
29
55

I tried this, I also tried to just hardcode a date to remove the variable all together. Still get no current record. – Warcupine Jun 14 '19 at 11:51
Then there is no record having that date. – Gustav Jun 14 '19 at 12:15
There are hundreds of records that should show up and do when i open qry_wip_percentage. It is only when i use this piece of code to export a subset of the query that they don't show up. – Warcupine Jun 14 '19 at 12:18
But how do you know? I still don't see where the parameter comes into play. – Gustav Jun 14 '19 at 13:38
Some of the queries that this final query is built off of use the parameter. I tried to just completely remove the parameter, from code and queries and put a date into the criteria of the query. It still will produce an error on qdf.execute. I can open the query in table view and see the records that should be produced by the code. – Warcupine Jun 14 '19 at 13:58
Without the other queries it is not easy. However, see edited answer, please. – Gustav Jun 15 '19 at 10:10

score 0 · Accepted Answer · answered Jun 14 '19 at 13:17

I was able to get this working again, though it's not very satisfying.

I had a server side backup from about 6 months ago which gave the same error. I tried to create a new database and import everything over which still gave the same error. I tried to create the thing over from scratch, but it continued to have the error.

The only thing that worked was to go into the file properties and restore a previous version. Why this worked I don't know because the restore point I used was more recent than the server backup I had, and there have been no changes between server, restored, and current files.

I have a working database now, though I can't even guess at what happened.

Querydef execute returning a runtime error

2 Answers2