Solved

"No Current Record" Error on Access Append Query

Posted on 2009-07-07
5
682 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

831 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