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

The easiest solution would be to set the RowSource of your controls and the
RecordSource of your form to a string representing the EXEC command followed
by the name of your stored procedure followed by the parameters:

Me.RecordSource = "exec vw_NetworkFootprint 'text to search' "

Of course, this string can be passed with Me.OpenArgs. You can also set the
RecordSource to the name of the SP, set the RecordSourceQualifier to its
owner (usually « dbo » ) and use the InputParameters property: see the
previous messages in this newsgroup to know how to do this. These past
messages are all on Google:

http://groups.google.ca/group/microsoft.public.access.adp.sqlserver

In your case, your exemple looks good; however, you may try setting the
RecordSourceQualifier to dbo ; it will help if your users doesn't login as
the owner of the database.

Also, the SP will be called when the RecordSource of the form will be
changed, so you must set the InputParameters before setting the
RecordSource. Take a look with the SQL-Server Profiler to be sure.

Don't forget to set the UniqueTable and ResyncCommand properties too!
Otherwise, you may have problems when editing data.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"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
>


.

Loading