Prev: Migrate from Access 97?
Next: You do not have exclusive access
From: Lauren Quantrell on 21 Feb 2010 09:45
Working with an ADP in all previous versions of Access prior to Access
2007, this worked fine when populating a subform using VBA:

Me.Child.Form.InputParameters = "@SomeParameter = Forms!
FormName.ControlName, @ID = FunctionName()"
Me.Child.Form.RecordSource = "dbo.SprocName"

Now it does not work in Access 2007. Instead the user is prompted to
enter the parameter values for each parameter as soon as the
recordsource is called.What in the world is going on with Access 2007
and how do I fix this?

Any help is appreciated,
lq
From: Rich P on 24 Feb 2010 17:22
See if this works in the code behind like say a button click:

Private Sub btn1_Click()

Me.yourSubformName.Form.RecordSource = "Exec yourProc " & txt0 & ", " &
txt1 & ", " & txt2

End Sub

and here are the details: txt0 & ", & ", " & txt1 & ", " & tx2 are the
parameter values that you would be passing to your stored procedure
which you would enter in textboxes, txt0, txt1, txt2


Also, in the subform you have to select the controlsource for each
field on the form from your Procedure. If you set the Proc as the
recordsource in the property sheet of your subform then it will populate
the List Menu on the toolbar of the designview of your subform. From
here you select/drag the desired fields - and SAVE.

And lastly, instead of Me.Child... you need to use the subform's name:
Me.subX.Form.RecordSource

Rich

*** Sent via Developersdex http://www.developersdex.com ***
From: Rich P on 24 Feb 2010 17:44
A few more notes: once you have selected the controlsource fields from
the fieldList in the subform's design view -- you need to remove/delete
the recordsource reference to the proc in the subform's propertysheet.

Also, if you params are varchar - you may have to delimit them with
single quotes:

Me.subX.Form.RecordSource = "Exec yourProc '" & txt0 & "', '" & txt1 &
"', '" & txt2 & "'"

Rich

*** Sent via Developersdex http://www.developersdex.com ***
From: Lauren Quantrell on 25 Feb 2010 08:59
On Feb 24, 5:44 pm, Rich P <rpng...(a)aol.com> wrote:
> A few more notes:  once you have selected the controlsource fields from
> the fieldList in the subform's design view -- you need to remove/delete
> the recordsource reference to the proc in the subform's propertysheet.  
>
> Also, if you params are varchar - you may have to delimit them with
> single quotes:
>
> Me.subX.Form.RecordSource = "Exec yourProc '" & txt0 & "', '" & txt1 &
> "', '" & txt2 & "'"
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

Thanks.
'Child' is the name. Used here for illustrasting the methodology.
Specifying the parameters in the recordsource
(Me.subX.Form.RecordSource = "Exec yourProc '" & txt0 & "', '" & txt1)
will not allow backwards compatability with older versions of Access
especially Access 2000, so that option is out.
From: Lauren Quantrell on 25 Feb 2010 09:08
What I have discovered is that Access 2007 handles parameters
differently than in previous versions of Access if the form is loaded
without a recordsource, or even an empty recordsource.

In previous versions, if parameters are specified in VBA:
Me.ChildName.Form.InputParameters = "@SomeParameter = Forms!
FormName.ControlName"

Then the recordsource is specified:
Me.ChildName.Form.RecordSource = "dbo.SprocName"

Everything worked fine in older versions of Access. Change the value
of Forms! FormName.ControlName and the subform updates with a requery
of the recordsource.

Now in Access 2007, it seems necessary not only to enter
"dbo.SprocName" in the form properties for the subform, it also seems
necessary to resend the parameters in VBA every time the value of
Forms! FormName.ControlName changes.

In other words, instead of requerying the recordsource, resending the
parameters, but only if the recordsource was specified on the form's
properties.

This is creating coding havoc for a number of my pre-Access 2007
databases.




 | 
Pages: 1
Prev: Migrate from Access 97?
Next: You do not have exclusive access