Using the InputParameters Property ????

[don garry]

I'm new to ADPs and trying to get a report to run from a parameterized stored procedure where the values come from a form.

Here's what the Access help files say I can do:

You can use the InputParameters property to specify or determine the input
parameters that are passed to a SQL statement in the RecordSource property
of a form or report or a stored procedure when used as the record source
within a Microsoft Access  (.adp).

My problem is figuring out the appropriate code and correct syntax.

If I have a statement saying Me.RecordSource = "procGetRecords"

How would I go about configuring the InputParameters statement ? Let's say I have two parameters like @txtEmployee and @intYear in the stored procedure.

I'd sure appreciate it if someone could assist me with a small example.
 
 
 
 

[Andy Baron]

Unfortunately, the help file is misleading on the this topic. It
states that you can use Form!ControlName to refer to a control, but
that doesn't work. You need to use:
ParamName Type = Forms!FormName!ControlName
even if the control is on the current form. For multiple parameters,
just separate the data with commas. So if you need to do this in code,
use something like (on one line):
Me.InputParameters = "@txtEmployee varchar(20) Forms!MyForm!MyControl,
@intYear int = Forms!MyForm!MyOtherControl"

Another option, if you are doing this in code rather than using the
design-time properties on the form, would be to evaluate the controls
and contactenate in the values:
Me.InputParameters = "@txtEmployee varchar(20) '" &
Forms!MyForm!MyControl & "', @intYear int = " &
Forms!MyForm!MyOtherControl"

You'll want to change the InputParameters before you change the
RecordSource, so you pick up the new values when the form is
requeried. If you aren't changing the record source, you'll want to
requery the form (me.requery) after updating the controls used in
InputParameters.

-- Andy

Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

[don garry]

Thanks Andy, I've got the design-time option working fine but still can't
seem to get the programmatic option working. Here's the code I'm using in a
little test with one parameter. This code is in the Open Event of the report
with the parameter coming from the frmParameterTest Form

Private Sub Report(Cancel As Integer)

Me.InputParameters = "@AreaCode varchar(6) = '" &
Forms!frmParameterTest!cboArea & "' "

Me.RecordSource = "procParameterTest"

When I try to debug this code it seems to be picking up the value properly
but still wants to ask for the input parameter to be put in manually.

It probably something really simple that I'm not spotting ???

[don garry]

Oops !
Private Sub Report(Cancel As Integer) should read:

Private Sub Report_Open(Cancel As Integer) 'Then the same as I had before.....

Me.InputParameters = "@AreaCode varchar(6) = '" &
Forms!frmParameterTest!cboArea & "' "

Me.RecordSource = "procParameterTest"

[Andy Baron]

It's just busted. You can't successfully set InputParameters in code
in reports the way you can in forms. Sorry, I didn't know you were
asking about code in a report.

What I do is use a form, hidden if necessary, and put the values in
controls in the form, using a static reference to the form controls in
the saved InputParameters on the report. You can even open the form,
if necessary, in the Open event of the report.

-- Andy

[Colin Campbell]

Hi,
I just did what you where enquiring about this morining. Mind you i am using
2002 and a function so i am not sure how differnet it would be but the i got
the code from a book that was written for Access 2000. Can you use a
function?

Private Sub Report_Open(Cancel As Integer)

Dim strSQL As String
Dim varID As Variant

'the variable was in a subform nested three deep
varID = Forms!frmBilling!subfrmClient!subfrmBilliing.Form!requestID

'use the SELECT * FROM for functions

If IsNull(varID) Then
strSQL = "SELECT * FROM fnCreditCardBillingrpt(Null)"
Else
strSQL = "SELECT * FROM fnCreditCardBillingrpt('" & varID & "')"
End If

Me.RecordSource = strSQL

End Sub

"Andy Baron" <Andy_...@msn.com> wrote in message
news:iv7cmt4iu422v0p2l...@4ax.com...