Re: Switching a Form's Recordsource at RunTime to a SP w/ a Parameter

I know you don't want to hear this but if it is not too late, go back to
using the MDB but link it to SQL Server if you need to support more users or
larger tables. Development on ADPs is no longer being done although they
will still be supported in the next version of Access.

"Ecomorph" <davidlessin@xxxxxxxxxxx> wrote in message
news:1134104659.203179.190710@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Hi - I have been upsizing a complex application to an ADP app (Access
> 2003 and SQL Server 2K) for the past few months, and have reached an
> impasse that I can't just seem to get past, and am hoping for some
> help. I have a form based on a view with several listboxes and subforms
> based on SPs with parameters. I want to limit the default recordset to
> records created in the past 60 days but need to allow the users to
> expand all the way to 100% of records. Writing SQL via VBA is an
> option, but in order to retain the functionality of the form,
> performance over a WAN, and ease of maintanence, I'd prefer to use SPs
> with parameters. I also don' want to clutter up the Form or change UI,
> so I have added a command button that opens a 2nd form with a single
> text control and Option frame where the user can specify the type of
> search (e.g. Location, Account#, etc..). The users enter a text string,
> hits the Find Records button, which verifies valid search criteria, and
> then opens the target form. It passes the name of a stored procedure to
> the Open Args via Select Case...and now I am stuck.
> Private Sub Form_Open(Cancel As Integer)
> ' This works
> If IsNull(Me.OpenArgs) Then
> Me.InputParameters = ""
> Me.RecordSource = "vw_NetworkFootprint"
> Else
> ' Problems start here
> Me.RecordSource = Me.OpenArgs
> Me.InputParameters = "@txt_SearchBox nvarchar(25) =
> Forms!frm_SMang.txt_SearchBox"
>
> Here is where I am stuck.Can't pass the Param. I have tried defining
> the InputParams via VBA several different ways, Executing a SP via the
> Command object with Parameters, Creating a Function and attempting to
> Call the Function as the row source, but nothing seems to do both (1)
> Pass the Parameter or (2) Assign the Stored Procedure to the Form's
> rowsource. Help! Have gone page after page of books and Google Groups,
> written a # of iterations of code, but nothing I have come up seems to
> cut it. Thanks for the help!
>
> Private Function CallStoredProc()
> Dim cnn As ADODB.Connection
> Dim cmd As ADODB.Command
>
>
> Set cnn = CurrentProject.Connection
> Set cmd = New ADODB.Command
> With cmd
> .ActiveConnection = cnn
> .CommandText = Me.OpenArgs
> .CommandType = adCmdStoredProc
> .Parameters.Refresh
> .Parameters("@txt_SearchBox") = Forms!frm_SMang.txt_SearchBox
> .Execute
> End With
> End Function
>


.