Access ADP Form and Stored Procedure as RecordSource Options

[chrise]

This may be common knowledge but I could not find any posts. My ADP
form allows the user to input search criteria into textboxes,
checkboxes, and listboxes in the Form Header and the results are
displayed in the detail section of the form. First, in the form
properties, I set the form RecordSource to one Stored Procedure that
would return the last 50 records created when the form is first
opened
with no Input Parameters. That worked. I would change the
Me.RecordSource property, to a second Stored Procedure, in code when
the Search button was clicked and include all the Input Parameters
from the search criteria the user would input. This failed every
time.
What I realized is you can't change the RecordSource from one Stored
Procedure to a second Stored Procedure if the Input Parameters do not
match exactly. My solution was to Remove the RecordSource and Input
Parameters from the Form Properties and assign these in the Form Open
event. I use the same Stored Procedure to open the form and the
search
button function. When the form opens instead of displaying the last
50
records, I display all records created in the last 60 days.

When you assign a Stored Procedure with Input Parameters as the
RecordSource in code, you must set up the Me.InputParameters first
then assign the Me.RecordSource second. I also tried concatenating a
string to assign as the InputParameter but that didn’t work. This is
how I got it to work


Me.InputParameters = “@Param1=’” & strParam1 & _
“,@Param2=’” & strParam2 & “’,@Param3=’” & _
strParam3 & “’”

 

[Bob]

One option is to have your SP look at a param to tell it what query
to use... Then just change the Form.Inputparameters in the combo.OnChange
Event.

something like
Create
Proc YourStoredProc
@whatQuery int
, @param1 ...
, @param2
as

if @whatQuery = 1
Begin
Select ..
From..
--no params
End
if @whatQuery = 2
Begin
Select ..
From..
where something = @param1 and somethingelse = @param2
End

hth,
...bob

This may be common knowledge but I could not find any posts. My ADP
form allows the user to input search criteria into textboxes,
checkboxes, and listboxes in the Form Header and the results are
displayed in the detail section of the form. First, in the form
properties, I set the form RecordSource to one Stored Procedure that
would return the last 50 records created when the form is first
opened
with no Input Parameters. That worked. I would change the
Me.RecordSource property, to a second Stored Procedure, in code when
the Search button was clicked and include all the Input Parameters
from the search criteria the user would input. This failed every
time.
What I realized is you can't change the RecordSource from one Stored
Procedure to a second Stored Procedure if the Input Parameters do not
match exactly. My solution was to Remove the RecordSource and Input
Parameters from the Form Properties and assign these in the Form Open
event. I use the same Stored Procedure to open the form and the
search
button function. When the form opens instead of displaying the last
50
records, I display all records created in the last 60 days.

When you assign a Stored Procedure with Input Parameters as the
RecordSource in code, you must set up the Me.InputParameters first
then assign the Me.RecordSource second. I also tried concatenating a
string to assign as the InputParameter but that didn’t work. This is
how I got it to work


Me.InputParameters = “@Param1=’” & strParam1 & _
“,@Param2=’” & strParam2 & “’,@Param3=’” & _
strParam3 & “’”

 

[Sylvain Lafontaine]

A second possibility would be to build a sql string beginning with "EXEC "
then followed with the name of the procedure and the required parameters and
use this string a the record source. I've always used EXEC at the beginning
to make an explicit call in T-SQL to the stored procedure but maybe it's not
really necessary.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)