?
Solved

"No Current Record" Error on Access Append Query

Posted on 2009-07-07
5
Medium Priority
?
707 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

777 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