UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Record source - 'No fields available to be added to the current view"    
 
   
dbtech1
post Dec 11 2009, 01:37 PM
Post #1

UtterAccess Guru
Posts: 698
From: Michigan, USA
Hello,

I put together a test SQL stored procedure, and set it as the Record Source for a new form in Access, but when I click on the "Add Existing Fields" button, I am presented with a message stating:

No fields available to be added to the current view.
The current recordsource may be invalid (for example, it may contain an invalid join expression), or there was some other problem gathering recordsource information for this object.

Below is a copy of the code, which runs fine and produces correct results in Query Analyer. Can anyone see what is wrong? I'm stumped...

CODE
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
    
---
    
ALTER proc pTest
as
set nocount on
    
---
    
create table #tblTest (xField1 varchar(10), xField2 int)
    
insert into #tblTest (xField1, xField2) values ('abc', '1')
insert into #tblTest (xField1, xField2) values ('def', '2')
insert into #tblTest (xField1, xField2) values ('ghi', '3')
    
select xField1, xField2 from #tblTest
    
drop table #tblTest
    
---
    
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Go to the top of the page
 
+
niesz
post Dec 14 2009, 11:35 AM
Post #2

Utter A-fishin'-ado
Posts: 17,723
From: Cincinnati, Ohio, USA . . . ><((((°>
I believe you have to set NOCOUNT back to OFF before the final SELECT.
Go to the top of the page
 
+
dbtech1
post Dec 14 2009, 12:52 PM
Post #3

UtterAccess Guru
Posts: 698
From: Michigan, USA
Hi Walter,

That didn't seem to do it... Any other ideas...?

I'm about to pull some hair our over here...
Go to the top of the page
 
+
niesz
post Dec 14 2009, 01:23 PM
Post #4

Utter A-fishin'-ado
Posts: 17,723
From: Cincinnati, Ohio, USA . . . ><((((°>
So, what do you have now in your SP?
Go to the top of the page
 
+
dbtech1
post Dec 14 2009, 01:30 PM
Post #5

UtterAccess Guru
Posts: 698
From: Michigan, USA
CODE
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
    
---
    
ALTER proc pLiveMetrics
as
set nocount on
    
---
    
create table #tblTest (xField1 varchar(10), xField2 int)
    
insert into #tblTest (xField1, xField2) values ('aaa','1')
insert into #tblTest (xField1, xField2) values ('bbb','2')
insert into #tblTest (xField1, xField2) values ('ccc','3')
    
set nocount off
    
select xField1, xField2 from #tblTest
    
drop table #tblTest
    
---
    
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Go to the top of the page
 
+
dbtech1
post Dec 14 2009, 01:55 PM
Post #6

UtterAccess Guru
Posts: 698
From: Michigan, USA
Hello,

I think I found the answer... (totally makes sense now)

So I set the Record Source for the form, and then added some text boxes and set their Control Source to the name of the field that I know is being returned from the SP... When doing this in Design Mode, the text boxes showed that little green arrow in the corner saying that this field didn't exist... But once I saved the form and opened in Form View, the data displayed correctly...

So after figuring it out, the answer seems so easy... Access can't show me the "Existing Fields" becasue there are no existing fields because the table doesn't exist yet... I should have seen that a while ago...

Thanks for your help though Walter...!
Go to the top of the page
 
+
niesz
post Dec 14 2009, 02:08 PM
Post #7

Utter A-fishin'-ado
Posts: 17,723
From: Cincinnati, Ohio, USA . . . ><((((°>
NP. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)
Go to the top of the page
 
+
« Next Oldest · Access Data Projects - ADP · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 25th May 2013 - 09:00 PM