G
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)
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)