ADP - DoCmd.OpenReport

Posted on 2004-11-27
Last Modified: 2013-12-05
DoCmd.OpenReport "rptShipping", acViewPreview, , "dbo.orders.or_id = " &

"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
Question by:ginoitalo
    LVL 26

    Expert Comment

    by:Alan Warren
    Hi ginoitalo

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

    LVL 26

    Expert Comment

    by:Alan Warren
    or this:

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



    Author Comment

    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 ;)


    Author Comment

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

    LVL 26

    Accepted Solution

    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

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

    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



    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now