Listox refreshing from stored procedure called from criteria controls 3

[juddm]

This is a question regarding MS Access Project 2000/MS SQL 2000.

I have a stored procedure with two parameters param1 and param2. I have a form with two Input Parameters correctly linking to the two textboxes I have on my form. I also have a listbox on the form which is populated by the stored procedure sp1.

My problem is this - I want to update the results displayed in the listbox (as populated by sp1) which relies on the values of tb1 (textbox 1) and tb2 (textbox 2).

My approach has been to use the change event to force a requery on listbox1 (i.e. listbox1.requery) however this doesn't cause the sp1 to refresh.

Any ideas where I'm going wrong?

 

[EriRobert]

Hi,

Think you need something like:

listbox1.RowSource = "Exec sp1 @param1 = " & tb1 & _
", @param2 = " & tbl2

in the change event or wherever.

If tbl1 & tbl2 are text (not numeric) then you'll need quotes around them:

listbox1.RowSource = "Exec sp1 @param1 = '" & tb1 & _
"', @param2 = '" & tbl2 & "'"

Robert

 

[juddm]

I've got the solution - it was of course a bug in Access 2000 - there's not much literature on the work-around as far sa I could see..... however.....

There no point in usingthe Input Parameters on the form as they don't work. The solution is the following:

strParameters = IIf(IsNull(searchOrg), "NULL", searchOrg) & ", " & IIf(IsNull(searchName), "NULL", searchName) & ", " & AndOr

strExec = "EXEC spSearchEnquirer " + strParameters
NameList.RowSource = strExec


The first line handles the null case, the second line builds the query and the third line executes the query in the correct control. Interestingly, joining lines 2 & 3 cause this to fail, as does specifying Input Parameters...

 

[cmmrfrds]

juddm, good work. I too, tried to use an SP with parameters in a listbox/combobox and discovered like you did that it did not work as one would expect. It was a while back and I didn't save any of my work, so there was not anything useful to response with. I am saving your explanation for future needs.

It deserves a star.

 

[juddm]

thanks! Never had a star before ...