Office automation - open a report in MS aAccess with VB6

Posted on 2007-08-03
Last Modified: 2008-01-09
I am using a vb6 front end form tied to a back end MSAccess 11 database. I want to programatically open the database, and open a report. The report will print an inovice, so it will need to be one specific record from the Orders table in the database. The OrderID is supplied at runtime from the vb6 form, and is used as the filter to open the report to the one specific record that I wish to see on the report.

Here is my code:

Dim appAccess As New Access.Application
Dim OrderID as Integer

appAccess.OpenCurrentDatabase C:\MyDB.mdb, True, "password"
appAccess.Visible = True
appAccess.DoCmd.OpenReport "rptInvoice", acViewPreview

rptInvoice is the report in Access. In the last line, after "acViewPreview", the next two parameters are "filter" and "WhereCondition". I need to know what to put in for the "WereCondition" that will convey to the Access database theOrderID on the VB6 form that is to match the "OrderID" in the Orders table of the database.

After I create a new instance of Access, I should have the ability to see all the objects and properties of the database. What I think I need to do is to find out how to reference the field 'OrderID' in the ' tblOrders'
of the database and put the value found in the txtOrderID text box on the vb6 form into the Where Condition of the DoCmd OpenReport statement.
Question by:M_Epstein
    LVL 18

    Accepted Solution


    Author Comment

    The first link refers to using snapshots, but that is not what I want to do.
    the second link was much better. The solution was easier than I thought. I thought that I would have to access the table in my database as an object, and then set a criteria for the OrderID field within the table, but the problem was as simple as "OrderID = 12345" as the WhereCondition string. I actually found this post a week ago, but somehow missed the solution.

    Thank you very much fo helping me solve my first question on EE!
    LVL 69

    Assisted Solution

    by:Éric Moreau

    Author Comment

    This is essentially the same as the other solutions. My problem was that I was not using proper syntz for the where clause. Thank you all!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    730 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

    17 Experts available now in Live!

    Get 1:1 Help Now