Listbox parameterized query. HELP!!

[mpalmer78]

Using: Access 2000 SP3 and SQL Server 2000



I have a form with a textbox and a listbox. I want the listbox's
RowSource set to a parameterized stored proc. The parameter will be
based on the textbox's value. If the textbox is null, I want ALL the
records returned. Here's what I've done. I've simplified it a lot for
this posting. I actually need about 6 parameters each tied to textboxes
on a form each of which may or may not be null. This is easy with an MDB
by using "Forms!form!textbox or Forms!form!texbox is null" in the column
criteria from the query assigned to the rowsource.





Stored Procedure:



Create Procedure proc_test



(@parm1 INT)



As

SELECT tblTransactions.ID

FROM tblTransactions

WHERE tblTransactions.ID = COALESCE(@parm1, tblTransactions.ID)

return





Form's code:

OnLoad or on Textbox's AfterUpdate event...



me.list1.RowSource = "EXEC proc_test @parm1=" & me.textbox

me.list1.Requery (or I've also done... me.list1.RowSource =
me.list1.RowSource, to make sure it's actually requerying.)





This works great when the textbox is not null, but when it is null no
rows are returned to the Listbox. To twist things up a little more, when
I run the sp either by directly clicking it in the database window or
through ADO while passing NULL to the parameter, all the rows are
returned!! They're just not showing up in the listbox.



Any ideas? TIA

 

[Howard Brody]

I don't use stored procs for this. Instead I build the
SQL string in code:

Form_Load()
Dim strSQL As String
strSQL = "SELECT tblTransactions.ID " _
& "FROM tblTransactions;"
List1.RowSource = sqrSQL
List1.Requery
End Sub

Private Sub Text1_AfterUpdate()
Dim strSQL As String
If IsNull([Text1]) Or [Text1]="" Then
strSQL = "SELECT tblTransactions.ID " _
& "FROM tblTransactions;"
Else
strSQL = "SELECT tblTransactions.ID " _
& "FROM tblTransactions WHERE " _
& "tblTransactions.ID='" & [Text1] & "';"
End If
List1.RowSource = sqrSQL
List1.Requery
End Sub

Hope this helps!

Howard Brody