Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Opening External Access Report inside Active Database

Last week we have learned how to print a Report from another Ms-Access Database. With the help of a VBA Program we have created a separate MS-Access Application Window, opened a database within that Application Window and printed a Report from it. We have used the Application.Docmd.OpenReport command to open the Report in Print Preview. The same code we have used in an Excel Application (in a Macro enabled Workbook) to Print Preview the same MS-Access Report.

The procedure was somewhat complicated and difficult to understand, if you are not that familiar with VBA. If you need to print a Report or open a Form from an external MS-Access database, inside the current database then we can do it in a much simpler way.

Once we go through the following simple procedure the external database's Report can be opened within the current database window in Print Preview or Print mode, depending on how you set up the procedure. With the same ease of operation we can open Forms as well.

The procedure goes something like the following:

First let us define some names of databases, Report and Form involved in this procedure, for references.

  • Current Database Name: DatabaseA.mdb
  • Second Database Name: DatabaseB.mdb
  • Report to Print Preview from DatabaseB.mdb: myReport.
  • Form to open from DatabaseB.mdb: myForm.
  1. Open DatabaseB.mdb (you may select any database you have, with at least one Report and One Form).
  2. Open the VBA Editing Window (ALT+F11).
  3. Insert a new Standard Module (Insert - - > Module).
  4. Copy and paste the following VBA Code into the Module:
     
    Public Function myReportOpen()
    'Replace myReport with your own Report name in quotes.
       DoCmd.OpenReport "myReport", acViewPreview
    End Function

    Calling the above VBA Function from DatabaseA.mdb will open myReport from DatabaseB.mdb and will appear in DatabaseA Application Window, in Print Preview.

  5. Copy and Paste the following VBA Code below the earlier Function myReportOpen():
     
    Public Function myFormOpen()
     'Replace myForm with your own Form Name
       DoCmd.OpenForm "myForm", acViewNormal
    End Function

    We have created two simple functions in DatabaseB and we have to ensure that DatabaseA is not having any function with the same name.

  6. Save the VBA Module with the functions and close DatabaseB.mdb.
  7. Open DatabaseA (any database you would like to see the Report/Form open from DatabaseB.mdb).
  8. Open VBA Editing Window (ALT+F11).
  9. Select Tools - - > References.

    You will see the VBA References Dialog Box as shown below, with a lengthy list of available Library Files and the files already selected for the current project, with check marks, on top of the list.  You must browse and find DatabaseB.mdb from it’s parent folder, select it and click Open to attach it to the current database as a Library File.

  10. Use Browse... button on the References Dialog Box, find DatabaseB.mdb from it's folder and open it. See that you have selected 'Microsoft Access Databases' in the Files of Type control, otherwise Database file names will not appear.

    The selected Database's Project Name (can be different from your database name) will appear at the bottom of the Library Files list.

  11. Click OK to close the Dialog Box.

    Now, the functions myReportOpen() and myFormOpen() of DatabaseB are visible in DatabaseA. It means that you can call those functions from DatabaseA.mdb to open myReport or myForm from DatabaseB.mdb and to display them in DatabaseA Window.

    Note: When Function myReportOpen() is called from DatabaseA.mdb, first the function will look for myReport in DatabaseB (the function's parent database), if found it opens from there and displays in the current database, otherwise it will search for the Report, with the same name, in DatabaseA.mdb and opens from there. 

    Keep this particular aspect in mind about library functions and it's ability of searching for objects within the library file first, if reference of objects appears in the library functions, when you design Custom Wizards or some common Form useful in several Databases.

    At this point you can test the programs by running it from the Debug Window.

  12. Press CTRL+G to display the Debug window (Immediate Window), if it is not already visible.
  13. Type the following Command in the Debug Window and press Enter Key:

    myReportOpen

    The Report is open in Print Preview mode behind the VBA Window, minimize the VBA Window to see the Report. You may run myFormOpen() function also to see that it works too. You may create two Command Buttons on a Form so that you can run the programs from the Command Button Click Event Procedure.

  14. Open a Form and create two Command Buttons on it.
  15. Select the first command button and display it's Property Sheet (F4).
  16. Change the Name Property value to cmdReport.
  17. Find the On Click Event property and select [Event Procedure] from the drop-down list.
  18. Click on the build button (. . .) at the right end of the property to open the VBA Window.

    You will find the opening and closing lines of a Sub-routine, similar to the program lines given below, except the middle line.

    Private Sub cmdReport_Click()
       myReportOpen
    End Sub
  19. Copy the center line of the above procedure and paste it into the middle of the VBA subroutine.
  20. Similarly, name the second Command Button as cmdForm and follow the same steps (16 to 19) to create the following Sub-Routine in the VBA Window.
    Private Sub cmdForm_Click()
       myFormOpen
    End Sub
  21. Save the Form.
  22. Open the Form in normal view, click on the Command Button(s) to open myReport/myForm from DatabaseB.mdb.  The user of your database will not know whether the Report is opening from the library database or from active database.
Share:

2 comments:

  1. A very interesting approach to external library.
    Assuming that I want to open the external form in a subform of my active database, how could I do this? a direct sourceobject instruction would not work.
    Any idea?

    ReplyDelete
  2. I am not sure about this aspect. But, I would like to try this out.

    I am on a short visit to USA now and will be returning soon. I will check this out.

    Regards,

    ReplyDelete

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Accesstips Array Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Class Module Graph Charts msaccessQuery Combo Boxes List Boxes Objects Property VBA Command Buttons Data Emails and Alerts Query Calculation Collection Object Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Dictionary Object Object Reference msaccessprocess security advanced Access Security Command Button Custom Functions Field Type Form Macros Menus Report Top Values Variables msaccess email msaccess menus progressmeter Access2007 Add Auto-Number Controls Copy Expression Fields Form Instances Item Join Key Methods Microsoft Numbering System Records Recordset Security Split SubForm Table Time Difference Utility Workgroup Wrapper Classes database msaccess wizards tutorial Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code

Featured Post

Add Class Objects as Dictionary Items

We have learned the fundamentals of Dictionary Object, done trial runs for sorting simple items and displayed Access Table records through D...

Labels

Blog Archive

Recent Posts