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 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.

Simple Preparations.

The procedure goes something like the following:

First, let us define some names of databases, reports, and Forms 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.

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 we have to ensure that DatabaseA is not having any function 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 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 (which can 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 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 its ability to search for objects within the library file first, if references of objects appear 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 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 is open in Print Preview mode behind the VBA Window, minimize the VBA Window to see the Report. You may run the 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.

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 Sub-routine, 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 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
```
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 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 them with the 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 the 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 your machine (T:\) changes (to K:\ or Z:\ etc.) at a later stage you don't need to do anything to refresh the linked Tables.

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.

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 the Table or Query record set. This approach is better 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, after loading the database into memory, which is not linked to the Front-end. Besides that, we can create Tables or Queries in them too.

Creating Queries on a non-linked External Table.

What we will do if we want to create a Query, with external Table data, 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:

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 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.

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 the 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 run 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 on 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.
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:
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] & "'"
```