"No Current Record" Error on Access Append Query

Hello EE:

When I run the query below with totals off, I get results.  When I turn totals on I get a "No Current Record" error.  I have already tried removing the column with a refernce to a form.  This is my first time seeing this error.

Suggestions?
LVBarnes
INSERT INTO Z_201_InvoiceData ( [Inv Code], SoldTo, CustomerName, [Inv Date], InvNbr, Suffx, Material, [Inv Cs], [Inv$], [S-Ofc], EU_Name, EU, Date2, HasMultipleGFSItems, [Price Date], NetPrice )
SELECT IRAdesso.[Inv Code], GFS_Xref33_GFS_CustList.SoldTo, GFS_Xref33_GFS_CustList.CustomerName, IRAdesso.[Inv Date], IRAdesso.InvNbr, IRAdesso.Suffx, IRAdesso.Material, IRAdesso.[Inv Cs], IRAdesso.[Inv$], IRAdesso.SalesOffice AS [S-Ofc], Xref_EU_List.EU_Name, IRAdesso.EU, IRAdesso.Date2, GFS31_NetPriceTable.HasMultipleGFSItems, [00_ControlTable].PriceTableDate AS [Price Date], 0.0001 AS NetPrice
FROM (00_ControlTable INNER JOIN ((GFS_Xref33_GFS_CustList INNER JOIN IRAdesso ON GFS_Xref33_GFS_CustList.SoldTo = IRAdesso.SoldTo) LEFT JOIN Xref_EU_List ON IRAdesso.EU = Xref_EU_List.EU) ON [00_ControlTable].InvoiceNbr = IRAdesso.InvNbr) LEFT JOIN GFS31_NetPriceTable ON IRAdesso.Material = GFS31_NetPriceTable.Solo_Item
GROUP BY IRAdesso.[Inv Code], GFS_Xref33_GFS_CustList.SoldTo, GFS_Xref33_GFS_CustList.CustomerName, IRAdesso.[Inv Date], IRAdesso.InvNbr, IRAdesso.Suffx, IRAdesso.Material, IRAdesso.[Inv Cs], IRAdesso.[Inv$], IRAdesso.SalesOffice, Xref_EU_List.EU_Name, IRAdesso.EU, IRAdesso.Date2, GFS31_NetPriceTable.GFS_Item, GFS31_NetPriceTable.HasMultipleGFSItems, Forms!Form1_MainMenu.Check37_UsePriceTable, [00_ControlTable].PriceTableDate, 0.0001, IRAdesso.Prefx
HAVING (((IRAdesso.Prefx)="SD"))
ORDER BY IRAdesso.InvNbr, IRAdesso.Suffx;

Open in new window

LVL 5
Lawrence BarnesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GRayLCommented:
If you remove the INSERT INTO clause and just run everything after the SELECT, does it run?  What do you mean by 'with totals off' and 'When I turn totals on' ??
0
Lawrence BarnesAuthor Commented:
Hi Gray,
After I remove the Insert query and just run it as a select:

1)  It runs fine as a select query that is non grouped.
2)  When I add the group by (by selecting totals in the Access QBE grid...what's shown in the code above) ... THEN I get the No Current Record error.

Note: This is someone else's database.  I've been looking at it more and noticed that
* Forms!Form1_MainMenu.Check37_UsePriceTable
* GFS31_NetPriceTable.HasMultipleGFSItems
referred to in the query are check boxes.  I've never seen that in a group by query before.

LVBarnes

0
GRayLCommented:
Also, the form has to be open for the query to run without any prompts.  You do have the form?  Can you post the SQL without the INSERT clause - the part that runs fine.  Then post the SQL of the query when you add the GROUP BY.  Remember that a checkbox contains the values 0 or -1 and that makes it eligible for inclusion in a GROUP BY clause.  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lawrence BarnesAuthor Commented:
Hi Gray,
You pointed me in the right direction with the form.  The form was open.  But...there was a module behind the form with a bunch of IF and Exit Sub statements.  In some cases the data the query needed was not available based on user selections.  I rearranged the module and I no longer get that error.  Regarding your last post, the code was basically the same except for the group by and having statements.  Thanks for pointing me.

LVBarnes
0
GRayLCommented:
Thanks, glad to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.