"This action will reset the current code in break mode" error 1

[ulteriormotif]

Going a little nuts here (nothing new in that). I'm getting the following error message after I try to run one of my queries.

"This action will reset the current code in break mode

Do you want to stop running the code?

To halt the execution of the program so the Module window can be closed, select Yes.
To leave the code in the current state, select No. "

It's just a garden variety query just used to tell me whether a production target has been entered for a particular block of land.

SELECT [U - blocks subblocks currentseason].[Property ID], [U - blocks subblocks currentseason].[Block ID], [U - blocks subblocks currentseason].[SubBlock ID], [U - blocks subblocks currentseason].Currentseason, [DAT - production targets].blockid, IIf([dat - production targets]![blockid] Is Null,"NO","yes") AS [Target set]
FROM [U - blocks subblocks currentseason] LEFT JOIN [DAT - production targets] ON ([U - blocks subblocks currentseason].[SubBlock ID] = [DAT - production targets].blockid) AND ([U - blocks subblocks currentseason].Currentseason = [DAT - production targets].Season);


Here's the rub. The query itself won't run (although it was fine yesterday when I built it). I can open it in design view, but trying to run it does nothing at all. BUT, after trying to run it, I get the message above on any other queries that I run when I try to close them, and the same message on forms when I try to close them.

I've compacted and repaired. I've rebooted. No luck.

What am I missing here?

 

[ulteriormotif]

ok... so I just pasted the exact same sql into a new query, and it works fine.

What sparked the first one to go *poof* though?

 

[ulteriormotif]

Alrighty. So this morning the new version of the same query is causing the same problem, even though it was fine last night.

Help?

 

[ulteriormotif]

Ok. a new development. Copied the SQL to a new query again, and it ran fine. Made a report off it no problem, saved the report fine.

But try to run the report and I get the message "multi-level grupo by clause is not allowed in a sub-query", and of course the report doesn't run.

 

[ulteriormotif]

Ok, so tracking down the error message above, I get back to the query [U - blocks subblocks currentseason] which is used to bring my blocks and sub-blocks together, and define the season I want to look at.

The Currentyear table only has one record in it ever - it's a single record holding table that's used to control pretty much the whole database - the user can change the current year at various points and that limits all queries, input screens, etc, appropriately. So am I missing something with that subquery? There's only a single record there, so there's no 'where' criteria to add.

SELECT [DAT - Blocks].[Property ID], [DAT - Blocks].[Block ID], [DAT - Block Sub-blocks].[SubBlock ID], (select [currentyear] from [BAS - current year]) AS Currentseason
FROM [DAT - Blocks] LEFT JOIN [DAT - Block Sub-blocks] ON [DAT - Blocks].[Block ID] = [DAT - Block Sub-blocks].tBlockID;

 

[cdagenais]

Hi,
I'm having a similar problem in Access 2000. A simple select query will run, then not run.
Did you find a solution?

 

[ulteriormotif]

Hi cdagenais

Yep, actually we did find the solution. It was originating from a simple subquery in one of the queries this query was based on. The answer in the end was to simply make the subquery a saved query in its own right, and then include that in the next query up. Here's the thread:

http://www.tek-tips.com/viewthread.cfm?SQID=865407&SPID=701&newpid=701&page=1