July 2013 ~ LEARN MS-ACCESS TIPS AND TRICKS

Opening External Access Report inside Active Database

a.p.r. pillaiJuly 31, 2013Custom Wizards 3 comments

Introduction.

Last week, we explored how to print a Report from another MS Access database. Using a VBA program, we created a separate MS Access Application Window, opened the external database within that window, and printed a report from it. To accomplish this, we used the Application.DoCmd.OpenReport command to open the report in Print Preview. The same code was also used within Excel (in a macro-enabled workbook) to preview the same MS Access report.

However, the procedure was somewhat complicated and not easy to follow—especially if you are not very familiar with VBA.

Fortunately, if you only need to print a report or open a Form from an external MS Access database within your current database, there is a much simpler way.

By following the procedure explained below, you can open a report from another database directly in your current database window, either in Print Preview or Print mode, depending on how you configured it. With the same approach, you can also open forms just as easily.

Simple Preparations.

The procedure goes something like the following:

First, let us define the names of databases, reports, and Forms involved in this procedure, for reference.

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.

Open DatabaseB.mdb (you may select any database you have, with at least one Report and one Form).
Open the VBA Editing Window (ALT+F11).
Insert a new Standard Module (Insert -> Module).
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.
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 ensured that DatabaseA does not have functions with the same name.
Save the VBA Module with the functions and close DatabaseB.mdb.
Open DatabaseA (any database you would like to see the Report/Form open from DatabaseB.mdb).
Open VBA Editing Window (ALT+F11).

The Reference Library Usage.
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 checkmarks, on top of the list. You must browse and find DatabaseB.mdb from its parent folder, select it, and click Open to attach it to the current database as a Library File.
Use the Browse... button on the References Dialog Box, find DatabaseB.mdb from its 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 (must be different from your database name) will appear at the bottom of the Library Files list.
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 display them in DatabaseA Window.

How It Works.
Note: When the Function myReportOpen() is called from DatabaseA.mdb, it will first check for myReport in DatabaseB (the parent database of the function). If the report exists there, it will open from 'DatabaseB' and display in the current database. If not, the function will then search for the report with the same name in DatabaseA.mdb and open it from there.
Keep this behavior in mind when working with library functions. By design, they look for referenced objects in the library file first. This feature can be useful when you are creating custom wizards or designing shared forms that need to work seamlessly across multiple databases.
At this point, you can test the programs by running them from the Debug Window.
Press CTRL+G to display the Debug Window (Immediate Window) if it is not already visible.
Type the following command in the Debug Window and press Enter Key:
```
myReportOpen
```
The report will open in Print Preview mode behind the VBA window. To view it, simply minimize the VBA window. You can also test the myFormOpen() function to confirm that it works in the same way. For convenience, consider creating two command buttons on a form and assigning these functions to their Click event procedures, so you can run them directly from the form without opening the VBA editor each time.

Opening Form/Report of DatabaseB from DatabaseA
Open a form and create two Command Buttons on it.
Select the first command button and display its Property Sheet (F4).
Change the Name Property value to cmdReport.
Find the On Click Event property and select [Event Procedure] from the drop-down list.
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 Subroutine, similar to the program lines given below, except for the middle line.
```
Private Sub cmdReport_Click()
   myReportOpen
End Sub
```
Copy the centerline of the above procedure and paste it into the middle of the VBA subroutine.
Similarly, name the second Command Button to 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
```
Save the Form.
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 the active database.

Earlier Post Link References:

Printing Ms-Access Report from Excel

a.p.r. pillaiJuly 21, 2013External Links No comments

Introduction.

Printing an MS Access Report from Excel or from Another Database

In Microsoft Access applications, we often use a Front-End/Back-End design. All the tables are maintained in the Back-End database and linked to the Front-End. We link them because the Front-End frequently needs to retrieve or update data from those tables.

Once linked, external database tables behave just like native tables in the Front-End. You can work with them seamlessly, without noticing any difference.

Note: When linking tables from a network location, always use the full UNC path of the database file, for example:

\\ServerName\FolderName\SubfolderName\DatabaseName.mdb

Instead of a server-mapped drive path, such as:

T:\FolderName\SubfolderName\DatabaseName.mdb

This is important because if the mapped drive letter (e.g., T:\) changes later to K:\, Z:\, or another letter, the links will break. Using the full network address ensures the linked tables remain intact regardless of drive mappings.

Updating a Table not Linked with the Current Database.

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

The Databases Workspace.

Working with Databases in the DBEngine Workspaces Collection

When an Access database is opened in the Application Window, it is actually opened within a Workspace in the Workspaces collection, under the Application.DBEngine object.

The default Workspace is Workspace(0).

The first open database inside that workspace is addressable as Workspaces(0).Databases(0).

You can open more than one database within the same workspace and work with its tables or query recordsets. This approach is often better than permanently linking those tables to the Front-End database, especially if you do not need to use them on a day-to-day basis.

⚠️ However, there is an important limitation: you cannot open Forms or Reports from databases opened this way.

In fact, the object reference:

Application.DBEngine.Workspaces(0).Databases(0)

is equivalent to the CurrentDb object. While several databases can be loaded into Workspaces(0), only the current database will be visible in the Access Application Window. Other databases, if opened, will remain in memory until you explicitly close them.

This means you can:

Read and update tables from an external database without linking them together.

Create new tables or queries in those external databases as needed.

But you cannot:

Open forms or reports stored in those databases through this method.

Creating Queries on a non-linked External Table.

What if we want to create a query using data from an external table that is not linked to the front-end database? Surprisingly, Microsoft Access allows you to create queries without permanently linking external tables to the current database. Curious about how this works? You can learn the trick [here].

Up to this point, our discussion has focused on working with external tables and queries. However, the methods we covered so far will not allow you to open a form or report from another database. Normally, this requires opening the database in a separate Access Application Window.

That said, this statement is not entirely accurate—we’ll explore why in next week’s session.

But before diving into the Excel-based procedure, let’s first see how the same task can be handled from the active database itself.

The simple procedure steps are given below:

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

The 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

Copy and paste the above code into a new Standard Module of your Database.
Make changes to the Path of the Database and Report name, if needed.
Click somewhere in the middle of the Code and press F5 to run the Program.

In Microsoft Excel.

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.

Open Microsoft Excel.
Display VBA Window (Developer ->Visual Basic).
Insert a Standard Module (Insert -> Module) in the VBA Window.
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.

Right-click to open Large Image in New Tab/Window.

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

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

Search for Record Macro Action Access2007

a.p.r. pillaiJuly 10, 2013Macros No comments

Introduction.

Searching for a record on the Form is normally done with the help of the Find (Ctrl+F) method of Microsoft Access. For the search operations in the Last Name field of the employee's record, on the Employees Form, it is absolutely necessary that the Last Name data field is present in 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 in the Form. But the data field must be available on the Record Source (Table/Query) on 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 fields on the Form.

Prepare for a Test Run.

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

Import the Employees Table from the Northwind sample database.
Design the Form frmEmployees in Columnar Format (see the sample image given below). You can do this quickly with the help of the Form Wizard option from the Forms Group under Create Menu.

Right-click to open Large Image in New Tab/Window.
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 the 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.
Create two TextBoxes and a Command Button at the Footer of the Form, as shown in the image above.
Change the Child Label Caption value of the first Text Box to Last Name and the Textbox Name Property Value to lstName.
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.
Save the Form with the name frmEmployees and close it.

Creating a Macro.

Select Macro from the Create Menu to open a new Macro design window.

The Sample Macro image is given below:
Right-click to open Large Image in New Tab/Window.
Select SearchForRecord from the drop-down list in the Action Column of the Macro.
Set Form in the Object Type control, in the property sheet below.
Select frmEmployees from the drop-down list of Object Name.
Select First in the Record control.
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 a 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 simple constant criteria (easier to understand its usage) in the Where Condition control and search for the 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 TextBoxes, which we have created on the Footer of the frmEmployees, as search criteria. This way it will give us much-needed flexibility in search operations on the Form, by simply changing the search values in the text boxes.
Save the Macro with the macro name - macSearch and close.

The Form Design Change.

Open frmEmployees in Design View.
Click on the Command Button at the Footer of the Form to select it.
Display the Property Sheet (F4), if it is not visible.
Change the Name Property value to cmdRun and change the Caption value to Search For the Record.
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.
Save the Form and open it in Normal View. You will see the first record on the form is active now.
Click on the Command Button to search the Last Name Kotasa (or whatever the 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 the name you noted down earlier. We will modify the Macro to make it more flexible.
Close the frmEmployees for now.

The Condition Control Settings
Now, we will modify the Where Condition control settings on the Macro to take the values we type on the TextBoxes (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 TextBox values joined with the AND Logical operator to conduct a search operation in the 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.

Open the macSearch Macro in Design View.
Copy and paste the following expression into the Where Condition control, replacing the existing one.
```
="[Last Name] = '" & [lstName] & "' AND [First Name] = '" & [fstName] & "'"
```
The Search Criteria Expressions.
The expression starts with an = sign, the field name Last Name has 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 an 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.
Save and close the macro.

Since we have used the AND Logical operator both the Last Name and First Name field values should match to find a record on the Form.

Open the Employees Table and note down the last name and first name of a few records on paper and close the Table.
Open the Form frmEmployees.
Type the last name and first name of the first record, you have noted down earlier, into their respective text boxes on the footer of the Form.
Click the Command Button to run the macSearch and find the record on the form that matches both the 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 noted down earlier if any.
You may modify the macro to change the AND Logical Operator to OR. You may try the macro after entering the 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 the record, then it will be returned.

The modified expression is given below for reference:
```
="[Last Name] = '" & [lstName] & "' OR [First Name] = '" & [fstName] & "'"
```