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:

Printing Ms-Access Report from Excel

Printing Ms-Access Report from Excel or printing Report of another Database from Active Database.

We often talk about the Back-End, Front-End design of Microsoft Access Applications. All the Tables are maintained in the back-end database and kept linked to the front-End. We keep them linked because we always retrieve information from them or update with latest data.

When the external database tables are linked to the front-end they are as good as the native tables of the front-end and you will not find any difference in working with them.

NB:  When you are linking Tables from a Network location see that you provide full network address of the database  \\ServerName\FolderName\Subfoldername\DatabaseName.mdb format, rather than server mapped address T:\FolderName\Subfoldername\DatabaseName.mdb. If the Network Location mapping to you machine (T:\) changes (to K:\ or Z:\ etc.) at a later stage you don't need to do anything to refresh the linked Tables.

When you need to retrieve (or add/update) information in a table that is not linked to the front-end database we need a VBA program to do that.

A sample procedure is shown below:

Public Function CategoryTable()
Dim wsp As Workspace, db As Database
Dim rst As Recordset

'Set a reference to the active Workspace
Set wsp = DBEngine.Workspaces(0)

'DBEngine.Workspaces(0).Databases(0) is CurrentDB 
'Open a second database in Workspace(0).Databases(1) position 
Set db = wsp.OpenDatabase("C:\mdbs\NWSample.mdb")
 
'Open Categories recordset from Database(1) 
Set rst = db.OpenRecordset("Categories")
 
'Display the CategoryName field value 
MsgBox rst![CategoryName]
 
rst.Close 
db.Close 

'remove objects and release memory
Set rst = Nothing 
Set doc = Nothing 
Set db = Nothing 
Set wsp = Nothing 
End Function

When an Access Database is open in the Application Window, it is open within a Workspace in the Workspaces Collection, under Application.DBEngine Object. The default Workspace is Workspace(0) and the first open database is addressable at Workspaces(0).Databases(0). We can open more than one Database within the same Workspace and work with it's Table or Query recordsets. This approach is good if we are not working with those tables/queries on a day-to-day basis, rather than keeping them linked permanently to the front-end database.

But, we cannot open Forms or Report with this approach.

Application.DBEngine.Workspaces(0).Databases(0) Object is CurrentDB. Several databases can be opened in Workspaces(0) but the Current Database only will be visible in the Application Window and other databases, if open, will remain in memory till we close them. Now you know that we can work with tables, of databases loaded into memory, which are not linked to the Front-end, whenever we want to retrieve or update information in them.  Besides that we can create Tables or Queries in them too.

What we will do if we want to create a Query on an external Table that is not linked to the Font-end database? Believe it or not you can create Queries in Microsoft Access without linking external Tables to the current database. Want to find out how? You can learn the trick from here.

So far our discussion was on external Tables/Queries. But, the procedures explained above will not help to open a Form or Report from another database. This can be done only within a separate Access Application Window.   This statement is not 100% true, we will see why it is not next week.

Before going with the Excel based procedure let us see how it is done from the Active Database.

The simple procedure steps are given below:

  1. Create a separate Access Application Object.
  2. Set it’s visible property value to Yes, so that we can see the Application Window.
  3. Open the required Access Database within that Application Window.
  4. Open the required Report in Print-mode (acViewNormal) to send the Report directly to the default printer, or in Preview-mode (acViewPreview) to view the Report before sending it to the printer manually.
  5. Close the Database first and Quit the Application.

Sample VBA Code is given below:

' Include the following in Declarations section of module.
Dim appAccess As Access.Application

Public Function PrintReport()
'---------------------------------------------------------
'Original Code Source: Microsoft Access Help Document
'---------------------------------------------------------

    Dim strDB As String

' Initialize string to database path.
    Const strConPathToSamples = "C:\Program Files\Microsoft Office\Office11\Samples\"
    strDB = strConPathToSamples & "Northwind.mdb"

' Create new instance of Microsoft Access Application.
    Set appAccess = CreateObject("Access.Application")
' Make Application Window Visible
    appAccess.Visible = True

' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strDB

' Open Catalog Report in Print Preview
    appAccess.DoCmd.OpenReport "Catalog", acViewPreview
    
' Enable next line of code to Print the Report
    'appAccess.DoCmd.OpenReport "Catalog", acNormal

    'appAccess.DoCmd.Close acReport, "Catalog", acSaveNo
    'appAccess.CloseCurrentDatabase
    'appAccess.Quit
    
End Function
  1. Copy and Paste the above Code into a new Standard Module of your Database.
  2. Make changes to the Path of the Database and Report name, if needed.
  3. Click somewhere in the middle of the Code and press F5 to run the Program.

If you were able to run the code successfully and Print/Preview your Report in a separate Access Application Window, then you may proceed to do the same thing from Microsoft Excel.

  1. Open Microsoft Excel.
  2. Display VBA Window (Developer - - >Visual Basic).
  3. Insert a Standard Module (Insert - - > Module) in the VBA Window.
  4. Copy and Paste the above Code into the Module and Save it.

Before running the Code you must add the Microsoft Access 12.0 Object Library to the Excel Project.

  1. Select References.. from Tools Menu.
  2. Find Microsoft Access 12.0 Object Library (or whatever version available in your machine) and put a check mark to select it.
  3. Click OK Command Button to close the Control.
  4. Click in the middle of the Code and press F5 to run.

You will see the same result you saw when you run the Code in Microsoft Access.

Share:

Search for Record Macro Action Access2007

Searching for a record on the Form is normally done with the help of Find (Ctrl+F) method of Microsoft Access. For the search operations on the Last Name field of employees record, on the Employees Form, it is absolutely necessary that the Last Name data field is present on the Form. 

This is where the SearchForRecord Macro Action mainly makes the difference, besides other flexible features.  You can search for the Last Name of an employee even when this field is not present on the Form.  But the data field must be available on the Record Source (Table/Query) of the Form.  The SearchForRecord Macro Action can accept logical comparisons like <, >, AND, OR and BETWEEN to search and find the required record.  But, the Find method accepts only one of the three options, viz. Whole Field, Any Part of Field & Start of Field to search for a record on any of the available field on the Form.

Let us try out SearchForRecord Macro Action with the help of data from Employees Table of Northwind.accdb sample database.

  1. Import the Employees Table from Northwind.accdb sample database.
  2. Design the Form frmEmployees in Columnar Format (see the sample image given below). You can do this quickly with the help of Form Wizard option from the Forms Group under Create Menu.
  3. Select the Last Name Textbox and Delete it.

    We will try to search and find records using this field, without placing the field on the Form. We will also try the search method with the First Name field on the Form combined with Last Name field (not on the form) and learn the usage of AND, OR Logical Operations in the search criterion in the Macro we are going to create.

  4. Create two Text Boxes and a Command Button at the Footer of the Form, as shown in the image above.
  5. Change the Child Label Caption value of the first Text Box to Last Name and the Textbox Name Property Value to lstName.
  6. Similarly, change the second Child Label Caption of the second Text Box to First Name and the Textbox Name Property Value to fstName.

    Before making changes to the Command Button Properties we must create a Macro with the SearchForRecord Action.

  7. Save the Form with the name frmEmployees and close it.
  8. Select Macro from Create Menu to open a new Macro design window.

    Sample Macro image is given below:

  9. Select SearchForRecord from the drop-down list in Action Column of the Macro.
  10. Set Form in the Object Type control, in the property sheet below.
  11. Select frmEmployees from the drop-down list of Object Name.
  12. Select First in the Record control.
  13. Type [Last Name] = "Kotasa" in the Where Condition control. 

    NB: You may open the Employees Table to view and select any record value from the Last Name field, preferably after few records from the beginning of the records. Note down the first name of the employee also so that we can cross check the correctness of the record found by the search operation, when the record changes on the form.  Remember we have deleted the Last Name Field  from the frmEmployees Form.

    Note: Initially, we will try this method with a simple constant criteria (easier to understand it’s usage) in the Where Condition control and search for last name of an employee Kotasa in the Last Name field not on the Form.  After that we will modify the macro to use the values typed on the Text Boxes, which we have created on the Footer of the frmEmployees, as search criteria.  This way it will give us much needed flexibility on search operations on the Form, by simply changing the search values on the text boxes.

  14. Save the Macro with the name macSearch and close it.
  15. Open frmEmployees in Design View.
  16. Click on the Command Button at the Footer of the Form to select it.
  17. Display the Property Sheet (F4), if it is not visible.
  18. Change the Name Property value to cmdRun and change the Caption value to Search For Record.
  19. Select On Click Event on the Event tab of the Property Sheet and type the macro name macSearch, or select it from the drop-down list.
  20. Save the Form and open it in Normal View.  You will see the first record on the form is active now.
  21. Click on the Command Button to search the Last Name Kotasa (or whatever last name you have inserted in the criteria) on the Form.

    You will see the record changes on the Form. Check and confirm that the First Name on the form matches with the name you have noted down earlier.  We will modify the Macro to make it more flexible.

  22. Close the frmEmployees for now.

Now, we will modify the Where Condition control settings on the Macro to take the values we type on the Text Boxes (with the names: lstName and fstName) on the frmEmployees Form, rather than using constant values in the search criteria, as we did in the earlier example. We must create an expression to take the lstName and fstName Text Box values joined with the AND Logical operator to conduct search operation on Last Name and First Name fields on the Form.  For this reason we need to take some extra effort to build the expression correctly so that it works every time.  We must join the Data Field names (Last Name, First Name) and Text Box (lstName, fstName) contents combined with the Logical Operator AND  in the expression.

  1. Open the macSearch Macro in Design View.
  2. Copy and paste the following expression into the Where Condition control, replacing existing one.

    ="[Last Name] = '" & [lstName] & "' AND [First Name] = '" & [fstName] & "'"

    The expression starts with an = sign, the field name Last Name have a space in the middle and is placed in square brackets followed by an equal sign for an exact match of value and the whole segment of the expression is placed in double quotes.  Before closing the second double-quote a open single quote is placed because we have joined the text data from the lstName text box on the form.

    The next segment of the expression is opening with a double-quote, and a closing single quote for the first text data, followed by a space and the AND logical operator followed by the First Name field name in square brackets followed by a space, = sign, opening single quote for the fstName text value followed by the closing double-quote of the third segment of the expression.  The fstName text box reference from the form is joined with an ampersand followed by an ampersand to join the closing single quote within double quotes.

  3. Save and close the macro.

    Since, we have used the AND Logical operator both Last Name (doesn’t exists on the and Form) and First Name field values should match to find a record on the Form.

  4. Open the Employees Table and note down last name and first name of few records on paper and close the Table.
  5. Open the Form frmEmployees.
  6. Type last name and first name from the first record, you have noted down earlier, into their respective text boxes on the footer of the Form.
  7. Click the Command Button to run the macSearch to find the record on the form that matches both last name and first name.  Remember, the last name field is not there on the form.  You may repeat this method with the other record values you have noted down earlier, if any.
  8. You may modify the macro to change the AND Logical Operator to OR.  You may try the macro after entering search value in any one of the text boxes (lstName or fstName) or values in both text boxes.  If any one of the two or both values matches with the record then it will be returned.

The modified expression is given below for reference:

="[Last Name] = '" & [lstName] & "' OR [First Name] = '" & [fstName] & "'"

Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

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 Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference 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 tutorial updating upload vba code

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts