ADP - DoCmd.OpenReport

DoCmd.OpenReport "rptShipping", acViewPreview, , "dbo.orders.or_id = " &
CStr(or_id)

Error:
"The column prefix does not match with a
table name  or alias used in the query"

It really does though, Something with this being a ADP for sure

I was trying this, until I found there was no Open() method:
Dim r As New Access.Report
r.Name = "rptShippingReportSuppliment_Receipts"
r.FilterOn = True
r.ServerFilter = "dbo.orders.or_id = " & CStr(or_id)

Access 2003 - ADP - SQL Server 2000 SP3a
ginoitaloAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alan WarrenApplications DeveloperCommented:
 
Hi ginoitalo

try something like this:
   r.ServerFilter = "or_id = '" & CStr(or_id) & "'"

Alan
0
Alan WarrenApplications DeveloperCommented:
or this:

DoCmd.OpenReport "rptShipping", acViewPreview, , "or_id = '" & CStr(or_id) & "'"

Alan

0
ginoitaloAuthor Commented:
The problem with setting r.ServerFilter is that later, there is no r.Open()

I'll try putting the quotes around the or_id field at work tomorrow
or_id is an int, so I'm going to be mad if that's the solution ;)


0
ginoitaloAuthor Commented:
Didn't work either:

DoCmd.OpenReport "rptShipping", acViewPreview, , "or_id = '" & CStr(or_id) & "'"


Column prefix dbo.orders does not match with a table in the query

BUT IT DOES !!!!

0
Alan WarrenApplications DeveloperCommented:
Hi ginoitalo

If your report is powered by a stored procedure, setting a wherecondition for the openReport method of the DoCmd object will have no effect, because stored procedures are executed serverside.

You will have success if you power your report on a View or a Table directly.

I have tried setting a global variable prior to using DoCmd.OpenReport, then checking the global var on report open event, and setting the serverfilter to the global variable, but still no joy.



The following method works with stored procedures:
If you modify your stored proc to expect an input parameter you can set the the reports InputParameters property to get its value from the calling form.

Heres an example using NorthwindSQL catalog

The stored procedure: spEmployees
ALTER  Procedure spEmployees @lname Nvarchar(20)=null

AS
  IF (ISNULL(@lname, '') = '')
    SELECT employees.* FROM employees
  BEGIN
  --Create a where condition
    SELECT employees.* FROM employees  WHERE lastname =@lname
  END
GO


The form button code: Form1
Private Sub Command0_Click()
  Me.txtInputParams = "Callahan"
  DoCmd.OpenReport "rptEmployees", acViewPreview
End Sub


Then in the report: rptEmployees

Set the the recordsource property to: spEmployees
Set the InputParameters property to: @lname nvarchar(20) = Forms!Form1.txtInputParams



Alan

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.