July 2013 ~ LEARN MS-ACCESS TIPS AND TRICKS

Opening External Access Report inside Active Database

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

Opening an External Access Report inside Active Database.

Last week, we explored how to print a Report from another MS Access database. Using VBA, 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 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 having 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 the 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 and close DatabaseB.mdb.
Open DatabaseA (any database you would like to see the Report/Form open from DatabaseB.mdb).
Open the 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, check-marked and appearing at the top of the list. You must browse and locate DatabaseB.mdb, 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 you can call those functions from DatabaseA.mdb to open myReport or myForm from DatabaseB.mdb and display them in the 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 window. If not, the function will then search for the report with the same name in DatabaseA.mdb and open it.
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 the 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 call these functions from 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 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

Printing MS Access Report from Excel.

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, you 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 directly in the active database.

The simple procedure steps are given below:

Create a separate Access Application Object.
Set its Visible property to Yes so 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 it 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 code into a new Standard Module of your Database.
Make changes to the Path of the Database and Report name, if needed.
Click in 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 the VBA Window (Developer ->Visual Basic).
Insert a Standard Module (Insert -> Module) in the VBA Window.
Copy and paste the 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 the available version on your machine) and select it.
Click the OK Command Button to close the Control.
Click in 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

Search for Record Macro Action in Access 2007.

Searching for a record in the Form is normally done using the Find (Ctrl+F) method in Microsoft Access. To search an employee's last name in the Employees Form, the Last Name data field must be present in the Form.

This is where the SearchForRecord Macro Action makes the difference, besides other flexible features. You can search for the Employee's Last Name 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 <, >, AND, OR, and BETWEEN for searching and finding 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 the SearchForRecord Macro Action using 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 Form Wizard option.

Right-click to open Large Image in New Tab/Window.
Select the Last Name Textbox and delete it.
We will search and find Employee records using this field without the Last Name 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 logical operators AND and OR, with the search criteria we create in the Macro.
Create two TextBoxes and a Command Button in the Form Footer 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 to fstName.
Before making changes to the Command Button Properties, we must create a Macro with the SearchForRecord Action.
Save the Form as 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 Macro Action Column.
Set Form in the Object Type control, in the property sheet below.
Select frmEmployees from the Object Name drop-down list.
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 from the Last Name field, preferably after skipping a few records at the beginning. Note down the employee first name so that we can cross-check the accuracy of the search operation. 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 value input in the TextBoxes we created on the Footer of the frmEmployees as search criteria. 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 as 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 the 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 the Command Button to search for the Last Name Kotasa (or whatever the last name you have inserted in the criteria) on the Form.
You will see the record change 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 in the Macro to use the values from the lstName and fstName TextBoxes 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 that combines the lstName and fstName TextBox values with the AND logical operator to perform the search on the Last Name and First Name fields on the Form. For this reason, we need to take extra care when constructing the expression to ensure that it works correctly every time. The expression must combine the data field names (Last Name and First Name), the corresponding TextBox values (lstName and fstName), and the AND logical operator.

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 begins with an = sign. The field name Last Name, which contains a space, is enclosed in square brackets followed by the '=' sign to specify an exact value match. The entire segment of the expression is enclosed in double quotation marks. Before the closing double quotation mark, an opening single quotation mark is included because the text value from the lstName text box on the form is concatenated with the expression.

The next segment begins with a double quotation mark, and contains the closing single quotation mark for the first text value, followed by a space and the AND logical operator. This is followed by the First Name field name enclosed in square brackets, a space, an equal sign (=), and an opening single quotation mark for the fstName text value. The segment ends with the closing double quotation mark. The fstName text box reference from the form is concatenated with an ampersand (&), followed by another ampersand to join the closing single quotation mark enclosed within double quotation marks.
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 Unbound TextBoxes in the Footer of the Form.
Click the Command Button to run the macSearch Macro 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 only one of the text boxes (lstName or fstName) or values in both text boxes. If any or both values match the record, then it will be returned.

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