Field list is blank

[gsnidow]

Greetings eveyone. I am using Access 2003 and SQL Server 2000. I am trying
to make a procedure the record source of a new form, but in design view the
field list is empty. The procedure has several variables, all of which may
be null, and the where clause is constructed dynamically. I have many other
forms with the *exact* same set up. I use an unbound form with text boxes to
pass the parameters to the procedure and open the main form. If I execute
the procedure manully, all the records are returned as expected. I tried
using the form wizard to make the form, but when I choose the procedure as
the record source, the field list is blank there also. Even without being
able to add fields to the form, when I open the form I can see the correct
record count at the bottom. I am completely flumoxed. I seem to remember
having this problem maybe a year ago, but I can not remember what I did to
fix it. Below is the procedure if it matters. Thank you.

Greg
************************************************************************************
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE UPDATE_tblspoc
@Region VARCHAR(50),@Name VARCHAR(100),@Org VARCHAR(50),@LOB VARCHAR(50),
@IsRbSpoc CHAR(1),@IsRtrnSpoc CHAR(1)
AS
DECLARE @SQLString VARCHAR(8000)
DECLARE @SELECT VARCHAR(8000)
DECLARE @ORDER VARCHAR(8000)
SET NOCOUNT ON
SELECT @SELECT = '
SELECT *
FROM tblspoc
'
IF @Region IS NOT NULL
BEGIN
SELECT @SQLString = ' WHERE region = ''' + @Region + ''''
END
IF @Name IS NOT NULL
BEGIN
IF @SQLString IS NOT NULL
BEGIN
SELECT @SQLString = @SQLString + ' AND name = ''' + @Name + ''''
END
ELSE
BEGIN
SELECT @SQLString = ' WHERE name = ''' + @Name + ''''
END
END
IF @Org IS NOT NULL
BEGIN
IF @SQLString IS NOT NULL
BEGIN
SELECT @SQLString = @SQLString + ' AND org = ''' + @Org + ''''
END
ELSE
BEGIN
SELECT @SQLString = ' WHERE org = ''' + @Org + ''''
END
END
IF @Lob IS NOT NULL
BEGIN
IF @SQLString IS NOT NULL
BEGIN
SELECT @SQLString = @SQLString + ' AND lob = ''' + @Lob + ''''
END
ELSE
BEGIN
SELECT @SQLString = ' WHERE lob = ''' + @Lob + ''''
END
END
IF @IsRbSpoc IS NOT NULL
BEGIN
IF @SQLString IS NOT NULL
BEGIN
SELECT @SQLString = @SQLString + ' AND IsRbSpoc = ''' + @IsRbSpoc + ''''
END
ELSE
BEGIN
SELECT @SQLString = ' WHERE IsRbSpoc = ''' + @IsRbSpoc + ''''
END
END
IF @IsRtrnSpoc IS NOT NULL
BEGIN
IF @SQLString IS NOT NULL
BEGIN
SELECT @SQLString = @SQLString + ' AND IsRtrnSpoc = ''' + @IsRtrnSpoc +
''''
END
ELSE
BEGIN
SELECT @SQLString = ' WHERE IsRtrnSpoc = ''' + @IsRtrnSpoc + ''''
END
END
SELECT @ORDER = ' ORDER BY region,name,lob'
IF @SQLString IS NULL
BEGIN
SELECT @SELECT = @SELECT + ' ' + @ORDER
END
ELSE
BEGIN
SELECT @SELECT = @SELECT + ' ' + @SQLString + ' ' + @ORDER
END
EXECUTE (@SELECT)

 

[Sylvain Lafontaine]

What are you expecting? The metadata is not available because the select
query is dynamically created. Build the form, add the controls (text box,
combobox, etc.) and either their control source manually.

 

[gsnidow via AccessMonster.com]

Thanks for the reply. What is the most important part of my post, is that I
have many other forms set up the *exact* same way. In fact, since there were
so many variables used to construct the where clause here, I copied another
procedure I had written, with the same number of variables, and simply
changed the variable and table and field names. If I open the form wizard,
and choose the procedure i copied, then all the fields show up in the field
list. If I choose this new procedure, however, no fields appear.

What are you expecting? The metadata is not available because the select
query is dynamically created. Build the form, add the controls (text box,
combobox, etc.) and either their control source manually.

Greetings eveyone. I am using Access 2003 and SQL Server 2000. I am
trying

[quoted text clipped - 105 lines]

END
EXECUTE (@SELECT)

 

[Sylvain Lafontaine]

What version of Access are you using?

It's quite possible that once the SP has been executed at least one time,
Access remember the metadata that has been used this last time and reuse it.
However, if anything changes, then you will be using some metadata that
possibly won't be available the next time the procedure is run.

I've just made a test with one of my one SP based on a dynamically build
query string and with it, Access 2003 didn't remember the metadata from
execution to execution. Later, I will repeat the test with a simpler SP but
I don't see the point to it: if the fields list viewer is empty, then all
you have to do is to fill manually the control source.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thanks for the reply. What is the most important part of my post, is that
I
have many other forms set up the *exact* same way. In fact, since there
were
so many variables used to construct the where clause here, I copied
another
procedure I had written, with the same number of variables, and simply
changed the variable and table and field names. If I open the form
wizard,
and choose the procedure i copied, then all the fields show up in the
field
list. If I choose this new procedure, however, no fields appear.

What are you expecting? The metadata is not available because the select
query is dynamically created. Build the form, add the controls (text box,
combobox, etc.) and either their control source manually.

Greetings eveyone. I am using Access 2003 and SQL Server 2000. I am
trying

[quoted text clipped - 105 lines]

END
EXECUTE (@SELECT)

 

[gsnidow via AccessMonster.com]

Thank you again Sylvain. Ok, I created a new form with my sp as the record
source. Then I added a text box, and tried to manually type in the control
source. I tried both the field name only, and also three part name. I get
the error "no such field in the field list". Is there another way I need to
manually set the control source? I am using Access 2003.

Greg

What version of Access are you using?

It's quite possible that once the SP has been executed at least one time,
Access remember the metadata that has been used this last time and reuse it.
However, if anything changes, then you will be using some metadata that
possibly won't be available the next time the procedure is run.

I've just made a test with one of my one SP based on a dynamically build
query string and with it, Access 2003 didn't remember the metadata from
execution to execution. Later, I will repeat the test with a simpler SP but
I don't see the point to it: if the fields list viewer is empty, then all
you have to do is to fill manually the control source.

Thanks for the reply. What is the most important part of my post, is that
I

[quoted text clipped - 18 lines]

 

[gsnidow via AccessMonster.com]

Well, I got it to work. For some reason it did not like the @Order variable.
I took it out
and the fields appeared in the field list. There are only 20 or so records
in the table, so I guess
I can live without the order by.

Greg

Thank you again Sylvain. Ok, I created a new form with my sp as the record
source. Then I added a text box, and tried to manually type in the control
source. I tried both the field name only, and also three part name. I get
the error "no such field in the field list". Is there another way I need to
manually set the control source? I am using Access 2003.

Greg

What version of Access are you using?

[quoted text clipped - 14 lines]

 

[Sylvain Lafontaine]

Why can't you use Order instead of @Order?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Well, I got it to work. For some reason it did not like the @Order
variable.
I took it out
and the fields appeared in the field list. There are only 20 or so
records
in the table, so I guess
I can live without the order by.

Greg

Thank you again Sylvain. Ok, I created a new form with my sp as the
record
source. Then I added a text box, and tried to manually type in the
control
source. I tried both the field name only, and also three part name. I
get
the error "no such field in the field list". Is there another way I need
to
manually set the control source? I am using Access 2003.

Greg

What version of Access are you using?

[quoted text clipped - 14 lines]

END
EXECUTE (@SELECT)

 

[gsnidow via AccessMonster.com]

I tried this

SELECT @SELECT = @SELECT + ' ORDER BY region,[name]'

in the line before EXECUTE, but as soon as I introduce the ORDER BY, either
by hard coding it or
by using a variable, the fields would not show up in the field list. My
understanding of what is going on
behind the scenes is not great enough to figure out why this would happen.
All I know is that when
I took out the ORDER BY, the fields appeared.

Greg

Why can't you use Order instead of @Order?

Well, I got it to work. For some reason it did not like the @Order
variable.

[quoted text clipped - 23 lines]