Pass Report Input Parameters & Record Source in vba

[dodyryda]

Hi

I'm trying to pass a reports record source and input parameters via vba as
I have a problem with my reports in my adp project that require parameters from forms.

It works fine under my user account but for my users from what I've read adp projects do not append the correct record source qualifier of dbo and instead append the username.queryname (basically it works for the sysadmin but not for any of my users which is hence useless, as it kicks an error 'the record source for this object dbo.query name does not exists'

so I have my form all setup and on the buttons on click event I have the following. But keep getting error 'the report name "RPT Jobs by Customer" you entered is either misspelled or refers to a report that isn't open or doesn't exist'

.. my question is can anyone point me in the right direction to solve this one???

thanks Nathan

Private Sub Command23_Click()
On Error GoTo Err_Command23_Click

Dim rpt As Report
Set rpt = Access.Application.Reports("RPT Jobs by Customer")

DoCmd.OpenReport rpt, acViewDesign

rpt.RecordSource = "EXEC dbo.[RPT Jobs by Customer]"
rpt.InputParameters = _
" @Company datetime=" & Forms![RPT Customer Criteria]!Company & " , " & _
" @StartDate datetime=" & Forms![RPT Customer Criteria]!SD & " , " & _
" @EndDate datetime=" & Forms![RPT Customer Criteria]!ED

DoCmd.OpenReport rpt, acViewPreview

Me.Visible = False

Exit_Command23_Click:
Exit Sub

Err_Command23_Click:
MsgBox Err.Description
Resume Exit_Command23_Click

End Sub

 

[LPurvis]

Hi

I have to say, ADP or not, it's a big decision to implement your application such that the design view is exposed. i.e. you're shipping an ADP rather than an ADE.
Obviously, that's a requirement for you to be able to alter the recordsource of the report.

Anyway - let's look at it.
You have the report already loaded at the start of this procedure?
If not then surely the lines:

Set rpt = Access.Application.Reports("RPT Jobs by Customer")
DoCmd.OpenReport rpt, acViewDesign

are the wrong way around. (Open the report, set an object variable).
As far as the use of InputParameters - I know they're a relatively big positive in an ADP, but I've never loved them.
I'd rather assign the source directly.

rpt.RecordSource = "EXEC dbo.[RPT Jobs by Customer] '" & Forms![RPT Customer Criteria]!Company & "', " & _
'" & Forms![RPT Customer Criteria]!SD & "', " & _
'" & Forms![RPT Customer Criteria]!ED & "'"

AFAIK you shouldn't have to pass the data type in the InputParameter property - but if you do, then it seems to me the Company parameter isn't going to be a DateTime?

And I'd set this from the report itself. In its open event - not externally. Then there's no need to use design view and more options present themsevles.

(P.S. Usual qualifier: I'm not a big user of ADPs at all!)

Cheers.