Solved

"No Current Record" Error on Access Append Query

Posted on 2009-07-07
5
663 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:Lawrence Barnes
  • 3
  • 2
5 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 24795197
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
 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 24795357
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
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 24798275
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
 
LVL 5

Author Closing Comment

by:Lawrence Barnes
ID: 31600590
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24817174
Thanks, glad to help
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now