Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

"No Current Record" Error on Access Append Query

Posted on 2009-07-07
5
Medium Priority
?
718 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

609 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