"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
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;
GRayL
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' ??
ASKER
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.Check
* GFS31_NetPriceTable.HasMul
referred to in the query are check boxes. I've never seen that in a group by query before.
LVBarnes
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.Check
* GFS31_NetPriceTable.HasMul
referred to in the query are check boxes. I've never seen that in a group by query before.
LVBarnes
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Thanks, glad to help