Form not showing records

[Guest]

Greetings all. Form1 has 3 combos - cboRegion, cboECD1, and cboECD2, all are
unbound. I want them to pass their values to a procedure used as the record
source of Form2. I have recently honed this set up thanks to Sylvain and
Robert, but it will not work this time. The procedure is as follows.

ALTER procedure spODNSchedule
@region varchar(50), @ecd1 char(15), @ecd2 char(15)
As
Declare @SQLString varchar(1000)
Declare @SelectList varchar(1000)
SET NOCOUNT ON
SELECT @SelectList = 'select * from vODNSchedule'
IF @region IS NOT NULL
BEGIN
SELECT @SQLString = 'where region = ''' + @region + '''and ecd '
END
ELSE
BEGIN
SELECT @sqlstring = 'where ecd '
END
IF @ecd1 IS NOT NULL
BEGIN
IF @ecd2 is not null
BEGIN
SELECT @SQLSTRING = @SQLSTRING + 'between ''' + @ecd1 + '''and ''' +
@ecd2 +''''
END
ELSE
IF @ecd2 is null
BEGIN
SELECT @SQLSTRING = @SQLSTRING + '= ''' + @ecd1 + ''''
END
END
IF @SQLSTRING IS NOT NULL
BEGIN
SELECT @SQLSTRING = @SQLSTRING + 'order by region, co, rte, ewo'
END
SELECT @SelectList = @SelectList + ' ' + @SQLString
EXECUTE(@SELECTLIST)

when I run the procedure from query analyzer it works fine, and when I run
it from the database window of my ADP(2000) it runs fine. When I try to open
form2 from a command button on form1 it opens, and down at the bottom I can
see the number of records, but no records are displayed. The input
parameters of form2 are @region=forms!form1!cboRegion, and the same form
ecd1&2. I do not know what else to try. What I want is for @region to be
optional, and @ecd2 to be optional. The pupose of the procedure is to
produce a schedule. If no region is chosen the schedule should reflect the
whole state. If no ecd2 is chosen the schedule will only be for one month,
otherwise it will be for a date range. Thanks for any help.

 

[Sylvain Lafontaine]

If the number of records displayed is OK, I don't see why you see nothing
else. Try storing the values of @SELECTLIST into a table to see if it's
correct (or return it as the value to be displayed in the form). You can
also chose to store or to return the values of the parameters to see if they
are OK too.

Make a test with an easy sql string to see if there is no incompatibility of
this method with ADP 2000.

Finally, you can use the IsNull() function to get rid of some of these IF
statement; for example:

...
if (@region is not null)
set @s = IsNull (@s + ' and ', '') + ' region = ''' + @region + ''''
...
set @s = @SELECTLIST + IsNull (' where ' + @s, '')

Skipping all blank spaces before words like AND, WHERE, BETWEEN and ORDER is
also a good recipe for trouble. Finally, don't forget to use the Replace()
function to replace any embedded single quote in any parameter with two
single quotes.

 

[Guest]

Sylvain, thank you again for your quick response. I am certain the method of
passing parameters through a form works for my ADP. I got it directly from
an article in MS help and I have made several search forms with this method,
so I am not concerned about the compatibility issue. I think it is a problem
with my SQL, so I am very interested in trying your suggestion of eliminating
some of the if statements. I am very new to SQL, however, and I do not fully
understand what you are advising me to do. It seems like you are implying I
should introduce another variable @s, and why do you have () around
if(@region is not null)?

 

[Malcolm Cook]

Greg

@SELECTLIST probably has a syntax error.

My guess is this line...

SELECT @SQLString = 'where region = ''' + @region + '''and ecd '

needs a blank before the word "and".

Find out the value of @SELECTLIST before you execute it.
change EXECUTE(@SELECTLIST ) to
raiserror (@SELECTLIST,16,1)
then call your stored proc from access's immediate window (with form1 open) like this

Access.CurrentProject.AccessConnection.spODNSchedule(forms!form1!cboRegion, ...

and a error window will pop up showing you exactly the value of @SELECTLIST

I bet it is not what you expect.


But why are you getting a number of records displayed in your form>>???

--Malcolm

 

[Guest]

Malcolm, I have never heard of the raiserror command. What a useful tool. I
guess that is why you guys get paid the big bucks. Anyway, you were exactly
right, my select list looks like this:
Select * from vODNSchedule where region = ‘norva’ and ecd between ‘1/1/2006
‘and ‘12/31/2006 ‘order by region, co, rte, ewo

I have tried to lose the spaces after the date values, but have not had any
success. Do you have any suggestions based on the code in my original post?
It seems like it should be something so simple. Thank you so much for your
time and help.

 

[Guest]

Ok I feel dumb now. I changed the @ecd datatypes to varchar rather than char
and the spaces are gone, and my @select list looks as it should. Now for the
tricky part. When I open form2 in design view there are no fields in the
field list, eventhough spODNSchedule shows up in the list of record sources.
I am completely flumoxed.

 

[Sylvain Lafontaine]

In this particular case, there is no need for a blank space between the
ending quote ' and the word AND as it's still a valid T-SQL statement;
nonetheless, it's still a bad idea to be mean with blank spaces when dealing
with dynamic sql.

Thanks for the tip about RaisError, this will be handy in many cases.

 

[Sylvain Lafontaine]

My concern about compatibility was only because you are using an older
version of Access, A2000.

For the variable @s and the parenthesis, there is nothing special about them
here and I've used these simply as a shortcut.

As you are seeing a number of records at the end of the form, I'm not sure
if the problem is with the sql string or with something else.