PIE Chart and live data on Form

PIE Chart and live data on the form.

Students' Exam Scores in five different Subjects are recorded in the table: tbl_Students. A sample Table image is given below:

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

The student’s name from the current record on the form is used as a criterion in a query to filter that student’s exam record. These filtered records are then used as the source data for the Pie Chart displayed on the same form. A sample form with the Pie Chart is shown below:

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

Out of the Total five Exam subjects, if any one of the records becomes current on the form, the Pie Chart should display the marks of all five subjects, along with their percentage of the maximum Total marks, on the same form.

This is the tricky part: we cannot directly design a Graph Chart on a form in normal view to filter the query that serves as the data source.

Data Source Queries.

To prepare the data for the chart, we need three simple queries to filter and organize the students’ exam records. In addition, a temporary table is required to store the total of all obtainable maximum marks for each subject (for example, 100 × 5 = 500). This value will be used to calculate the percentage of marks obtained in the chart. The image of the temporary table is shown below:

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

The Three-Part Approach.

We will divide the task into three parts so that the procedure is easy to understand:

1. Part-A: Create tbl_Students and tmp_MaxMarks.

2. Part-B: Design the Form frm_Students for tbl_Students and create three simple Queries to prepare the data for Charts.

3. Part-C: Create a PIE Chart on the frm_Students Form.

Part-A:

1. Create a table with the same structure in the first image at the top, and name it tbl_Students. You may ignore the two empty fields in the table design. As illustrated, the table contains exam scores for three students, each evaluated in five subjects, with a maximum of 100 marks per subject.

2. Key in the sample data into the tbl_Students Table.

3. Create a small table with two fields, as shown in the image above, and save it as tbl_MaxMarks.

4. Create a single record with the sample data, shown in the image above.

Part-B:

1. Design a form in Columnar format based on tbl_Students, leaving enough space on the right side of the form to accommodate the PIE Chart. You can use the Form Wizard to create the Form quickly. Once completed, save the form as frm_Students.

   We will create three Queries before working with the Form.

2. Create the following Queries by copying and pasting the SQL String into the new Query’s SQL Editing Window and saving it with the name as indicated:

   Query-1: StudentsQ

   SELECT [Student] & " " & [Subject] AS [Desc], tbl_Students.Marks
   FROM tbl_Students
   WHERE (((tbl_Students.Student)=[forms]![frm_Students]![student]));
   

   Query-2: MaxMarksQ

   SELECT tmp_MaxMarks.Desc, [MaxMarks]-DSum("Marks","StudentsQ") AS Max_Marks
   FROM tmp_MaxMarks;

   Query-3: UnionPieChart

   SELECT StudentsQ.* FROM StudentsQ
   UNION ALL SELECT MaxMarksQ.* FROM MaxMarksQ;

3. Open frm_Students in Design View.

4. Create two TextBoxes below the existing fields.

5. Click the first TextBox and press F4 to display the Property Sheet.

6. Change the Name Property value to Obtained.

7. Type the following expression into the Control Source Property:

   =DSum("Marks","StudentsQ")

8. Change the Caption Property Value of the Child Label of the Textbox to Total Obtained.

9. Click on the second Textbox to select it.

10. Write the following expression into the Control Source Property:

    =[Obtained]/DLookUp("MaxMarks","tmp_MaxMarks")

11. Change the Format Property Value to Percentage.

12. Change the Caption Property value of the Child Label of the Text Box to % of the Total.

13. Save the Form and open it in Normal View.

Let us review what we did so far.

Created the main Table tbl_Students and designed the Form frm_Students.

While the frm_Students is active, the StudentsQ data is filtered using the current student's name on the Form as a criterion.

Second Query (MaxMarksQ):

This query sums up the total marks obtained by each student from StudentsQ and compares it against the overall maximum marks (500) stored in tmp_MaxMarks. By subtracting the student’s obtained total from the maximum, we calculate the marks lost.

On the PIE Chart, this difference is represented as a percentage of the total. For example, if the chart shows 10% lost, it means the student secured 90% of the aggregate marks out of 500.

Third Query: UnionPieChart combines the StudentsQ source data and MaxMarksQ queries together for the PIE Chart Source Data.

Part-C:

Our PIE Chart needs to be displayed on the frm_Students Form. However, there’s a challenge:

• The form must remain Open in Normal View so that the selected student’s record can filter the data in StudentsQ.

• At the same time, a chart object can be created or embedded on a Form, only when in Design View.

This is why we need a small trick to accomplish the task.

Let’s assume the form frm_Students is open, displaying John’s record (the first student). His exam data is already filtered and available through StudentsQ. With this in place, follow these steps to create and embed the PIE Chart into the form:

1. While keeping the frm_Students in Form View, open a new Form in Design View.

2. Enable Use Control Wizard Button on the Toolbar above, and select Insert Chart Tool from the Controls group.

3. Draw a Chart Object in rectangular shape, large enough to show the formatting (Value Labels, Title, etc.), similar to the sample image above.

4. Select Queries from the View group of the Wizard control and select UnionPieChart from the displayed query list.

5. Click Next to proceed to the next screen, and select the >> button to select all the displayed fields for the chart.

6. Click on the PIE Chart Icon to select it and click the Next button twice, then type the following as a heading on the Title control:

   Max. Marks Each=100, 500 Total

7. Select No, don't display a Legend, then click Finish.

8. Double-click the PIE Chart to change to Edit Mode.

9. Right-click on an empty area of the chart to display the context menu.

10. Select Chart Options from the displayed menu.

11. Select the Data Labels Tab, and put checkmarks in Category Name, Values, and Percentage.

12. Click OK to close the control.

    We will reduce the size of the PIE slice labels to smaller font sizes.

13. Click one of the PIE slice Labels. This action selects all Labels together.

14. Change the Font size to 12 points, using the Font/Size Tool above. 

    The completed design of the PIE Chart is given below.

    

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

15. Click outside the Chart Object in the Form to exit Chart-Edit Mode, but the chart will still be in the selected state.

16. Right-click the Chart to display the shortcut menu, and select Copy to copy the Chart Object to the Clipboard.

17. Save the form with a name and close it if you would like to keep it safe. But we don’t need it anymore.

18. Now, change the frm_Students into Design View.

19. Click in the Detail Section of the Form to make it current.

20. Right-click on the Detail Section and select Paste from the displayed menu.

21. Drag the PIE Chart and place it on the right side of the data fields.

22. Click the top-left corner of the Form (to select Form-level Properties) and display the Property Sheet (F4) of the Form.

23. Select the Event Tab of the Property Sheet and click on the On Current property.

24. Select the Event Procedure from the drop-down list, click the Build (...) Button to open the VBA editing window.

25. Write the following statement in the middle of the Form_Current() Event Procedure:

    Me.Refresh

    When completed, the procedure will look like the following:

    Private Sub Form_Current()
         Me.Refresh
    End Sub

    When you move records on the Form, the Form_Current() event procedure will update the record set with the change on the StudentsQ as well.

26. Close the VBA editing window.

27. Save the frm_Students Form and open it in Normal View.

The first five records on the Form belong to student John. If you move one record at a time, you will not find any difference on the Chart up to the fifth record, because the values of all five records are shown on the PIE Chart. The sixth to tenth records belong to the second student, and the 11th record onwards belongs to the third student. You may type 6 or 11 on the Record Navigation Control to quickly display other students' marks on the PIE Chart.

1. MS-Access and Graph Charts
2. MS-Access and Graph Charts-2
3. Working With Chart Object in VBA
4. Column Chart and VBA
5. Pie Chart Object and VBA
6. Missing Lines in Line Chart
7. Pie Chart and Live Data on Form
8. Scaling Chart Object
9. Cross-Tab Union Queries for Chart

Read More

External References in Conditional Formatting Expression

External References in Conditional Formatting Expression.

Normally, we use a Form/Report Text box value in Conditional Formatting expressions to set the Font or Background color of a control.

For example:

FIELD VALUE IS BETWEEN 1 and 10  - whatever formatting you define will be displayed on the selected field, provided the field value is between 1 and 10.

OR

FIELD HAS FOCUS – the conditional formatting becomes visible when the field becomes active.

OR

EXPRESSION IS [ID]=5 OR [LastName] = "Nancy" – formatting is applied to a field, depending on the current values of two other fields used in the expression.

The key values in capital letters are selected from a drop-down control on the Conditional Formatting Dialog Control. Take the last example of our new method for reference, and let us see how it can be used differently. 

In the above expression, both [ID] and [Last Name] controls are on the same Form/Report and refer to the current record values of the Form/Report.  This is the normal procedure.

A Different Approach.

Keep in mind that the values referenced in an expression don’t always have to come directly from the current form or its record source. They can also be pulled from other sources:

From a control on another open form.

From a field in a different table (or query).

When it comes to tables, you don’t need to keep them open to use their values. Instead, you can rely on the DLookup() function, which allows you to fetch a value directly from a table or query field within your expression.

For example:

=DLookup("[MaxMarks]","tmp_MaxMarks")

This will retrieve the MaxMarks value directly from the tmp_MaxMarks table without opening the table.

This flexibility allows you to combine data from multiple sources—current forms, other forms, and tables—into a single expression that drives your chart or calculation.

When the above example is rewritten with external field reference values, it will look like the expression given below.

EXPRESSION IS Forms![Orders]![OrderID] = Dlookup("Order_ID","tbl_OrderParam")

Assume that the above Conditional Formatting expression is written in a Field (say the Amount field) of frm_myForm.

If the current record OrderID value of the Orders Form (the second form in open mode) and the Order_ID value of the tbl_OrderParam Table (the expression assumes that the Parameter Table has only one record in it) match, then apply the defined conditional format on the active Form's (frm_myForm) Amount Field.

Try it out yourself and find out how it works.

Earlier Post Link References:

• CHDIR and IN Clause of Access Query
• Microsoft DOS Commands in VBA
• DOS Commands in VBA
• Opening a specific Page of a PDF File
• Calculating Time Difference

Read More

Storing Bitmap Images in OLEObject Attachments Fields

Storing Bitmap Images in OLEObject Attachment Fields.

Microsoft Access provides the OLE Object and Attachment field types to store and display images directly in tables, which can then be shown on forms or reports.

For example:

Storing an employee photo in a staff table.

Storing product images in an inventory table.

However, storing images this way can quickly increase the database size. In Access 2007, bitmap image (.BMP files) placed in an Attachment field are automatically compressed to JPG, which helps reduce storage overhead—but the database size still grows with every added image.

If your application involves hundreds or thousands of records with image attachments, the file size will expand and become a serious limitation. An Access database can only grow to a maximum of 2 GB.

Identifying the Image with its Related Record.

However, if you anticipate this issue in your project, you can plan ahead by storing all required images in a dedicated folder on the disk. Either on a local machine or a network server, depending on user requirements. This approach eliminates concerns about database size. The key, however, is ensuring that each image can be reliably identified and retrieved for its corresponding record, so it can be displayed on forms or reports whenever needed.

Organizing the Images.

It is important to organize images on disk so they can be easily matched with their corresponding records. Once this is properly planned and implemented, retrieving the correct image and displaying it in an Image Control on a form or report becomes straightforward.

The simplest method is to name each image file using a unique field value from the related table, such as the EmployeeID for employee photos (e.g., 1.bmp, 2.bmp, etc.). Similarly, product images can be named using their ProductCode, ensuring each record is directly linked to its corresponding image.

When a particular employee’s record is current on the Form, we can read the employee code from the Form, add the image extension (like Me!ID & “.jpg”) to create the image name, and load it from the disk into an image control on the Form. 

To minimize disk space usage, you can choose image formats that are smaller in size, such as PNG, JPG, or GIF. While GIF images are the smallest, they may compromise image quality. It is best to select a single format, for example, JPG, and save all images in that format. This way, your program can load the image into an Image Control on a form or report.

We need only a small Sub-Routine that runs on the Form_Current() Event Procedure to load the picture into the Image control.

Prepare for a Trial Run.

Let us prepare for a trial run of a small Program.

1. Import the Employees Table from the Microsoft Access Northwind.mdb sample database.

2. Use the Form Wizard to create a Quick Form in Column Format.

3. Arrange the Fields so that we can add an Image Control in the form, suitable for displaying sample images you will create now.

   A sample Employees Form Design is given below, with the Image Control on it:

   

4. Select the Image Control from the Toolbox and draw an Image Control on the Form, as shown above.

5. Display the Image Control’s Property Sheet (F4) and change the Name Property value to ImgCtl.

6. Save the Form as Employees.

   Note: As I have mentioned earlier, the Employee ID field values are 1, 2, 3, etc. We will create a few images named: 1.jpg, 2.jpg, 3.jpg, etc.

   If you were able to import the Employees Table from Northwind.mdb, you are lucky—you got the Employees’ photos stored as bitmap images in the OLE Object field. You can save these images to your disk for use elsewhere. I am using Access 2007, so I will explain the procedure for saving images from the Employees Table to disk in this version. If you are using a different version of Access, the menu options may vary slightly.

   • Open the Employees Table in Datasheet View.

   • Find the Photo field of the first record; the Employee ID of this record is 1.

   • Right-click on the Photo field with the Bitmap Image caption, and select Open from the Bitmap Image Object option.  The image will open in the Paint program.

   • Before saving the image to disk, create a new folder C:\images.

   • Use the Save As… option to save the image in the folder C:\images as 1.jpg. 

   • Save a few more employee photos in this way.

   If you could not save images from the Employees Table, then open any image from your disk in the Paint Program and save it as 1.jpg, 2.jpg, 3.jpg, etc., in the C:\images Folder.

7. Open the Employees Form (saved in Step 6) in Design View.

8. Press ALT+F11 to display the VBA Window.

9. Copy and paste the following code into the VBA Window of the Form, below the Module Global Option: Option Compare Database.

   Private Sub Form_Current()
   Dim strImagePath As String, pic As String
   On Error GoTo Form_Current_Err
   'image folder
   strImagePath = "c:\images\"
   'create image name
   pic = Me![ID] & ".jpg"
   strImagePath = strImagePath & pic
   'validate image name and location
   If Len(Dir(strImagePath)) > 0 Then
   'image found, load it into the image control
     Me!ImgCtl.Picture = strImagePath
   Else
   'image not found, make image control empty.
     Me!ImgCtl.Picture = ""
   End If
   
   Form_Current_Exit:
   Exit Sub
   
   Form_Current_Err:
   'Not necessary to display a message here
   Resume Form_Current_Exit
   End Sub

10. Save and close the Form.

    The Trial Run.

11. Open the Form in Normal View.

    If everything went well, then you will see the first employee photo in the image control.  The Sample Screenshot of  the Employees Form with the photo is given below:

    

    Right-click to open the large image in a New Tab/Window.

12. Use the record navigation control to move to the next record and display other images.

Image Display on Report.

A sample Employees' Report Design, with an Image Control, is below.

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

1. Design a Report using a few fields from the Employees table, as shown above, and see that you are using the Employee [ID] Field on the Report.  This is important because we need this number to create the image name for the record in print.

2. Insert the Image Control on the Report, as shown in the above Image.

3. Display its Property Sheet and change the Name Property value to ImgCtl.

4. Click somewhere on the empty area of the Detail Section of the Report.  If you have closed the Property Sheet, then press F4 to display it.

5. Select the On Format Event Property, click the Build (...) Button to open the VBA Window of the Report.

6. Copy and paste the following code and replace the Detail_Format() Event procedure opening and closing statements:

   Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   Dim strImagePath As String, pic As String
   On Error GoTo Detail_Format_Err
   'image folder
   strImagePath = "c:\images\"
   'create image name
   pic = Me![ID] & ".jpg"
   strImagePath = strImagePath & pic
   'validate image name and location
   If Len(Dir(strImagePath)) > 0 Then
   'if found load the image into the image control
     Me!ImgCtl.Picture = strImagePath
   Else
   'make image control empty.
     Me!ImgCtl.Picture = ""
   End If
   
   Detail_Format_Exit:
   Exit Sub
   
   Detail_Format_Err:
   'Not necessary to display a message here
   Resume Detail_Format_Exit
   
   End Sub

7. Save the Report and close it.

   The Trial Run of the Report.

8. Open the Report in Print Preview.

   The Sample Report Preview is given below:

   

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

If you want to give more flexibility to your project, with all four image types (bmp, png, jpg, and gif), then you may use the following modified Code:

Private Sub Form_Current()
Dim strImagePath As String, pic As String
Dim strImagePathName As String, strI(0 To 3) As String
Dim j As Integer, strType As String

On Error GoTo Form_Current_Err

strI(0) = ".bmp"
strI(1) = ".png"
strI(2) = ".jpg"
strI(3) = ".gif"

strImagePath = "c:\images\"
pic = Me![ID]

strType = ""
For j = 0 To UBound(strI)
  strImagePathName = strImagePath & pic & strI(j)
  If Len(Dir(strImagePathName)) > 0 Then
     strType = strI(j)
     Exit For
  End If
Next
  
strImagePathName = strImagePath & pic & strType

If Len(strType) > 0 Then
  Me!ImgCtl.Picture = strImagePathName
Else
  Me!ImgCtl.Picture = ""
End If

Form_Current_Exit:
Exit Sub

Form_Current_Err:
'Not necessary to display a message here
Resume Form_Current_Exit

End Sub

Note: The Report must be in Print-Preview mode to view the Report with images. The Images may not appear in Layout View.

• Microsoft DOS Commands in VBA
• DOS Commands in VBA
• Opening a specific Page of a PDF File
• Calculating Time Difference
• Centralized Error-handler & Error-log

Read More

Opening External Access Report inside Active Database

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.

1. Open DatabaseB.mdb (you may select any database having 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 the 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 ensured that DatabaseA does not have functions with the same name.

6. Save the VBA Module and close DatabaseB.mdb.

7. Open DatabaseA (any database you would like to see the Report/Form open from DatabaseB.mdb).

8. Open the VBA Editing Window (ALT+F11).

   The Reference Library Usage.

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

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

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

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

14. Open a form and create two Command Buttons on it.

15. Select the first command button and display its 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 Subroutine, similar to the program lines given below, except for the middle line.

    Private Sub cmdReport_Click()
       myReportOpen
    End Sub

19. Copy the centerline of the above procedure and paste it into the middle of the VBA subroutine.

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

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 from the library database or from the active database.

Earlier Post Link References:

• DOS Commands in VBA
• Opening a specific Page of a PDF File
• Calculating Time Difference
• Centralized Error-handler & Error-log
• Duplicating fields with Conditional Formatting

Read More

Printing Ms-Access Report from Excel

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:

1. Create a separate Access Application Object.

2. Set its Visible property to Yes so 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 it to the default printer, or in Print-Preview mode (acViewPreview) to view the report before sending it to the printer manually.

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

1. Copy and paste the 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 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.

1. Open Microsoft Excel.

2. Display the VBA Window (Developer ->Visual Basic).

3. Insert a Standard Module (Insert -> Module) in the VBA Window.

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

1. Select the References option from the Tools Menu.

2. Find Microsoft Access 12.0 Object Library (or the available version on your machine) and select it.

3. Click the OK Command Button to close the Control.

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

1. Roundup Function of Excel in MS-Access
2. Proper Function of Excel in Microsoft Access
3. Appending Data from Excel to Access
4. Writing Excel Data Directly into Access
5. Printing MS-Access Report from Excel
6. Copy-Paste Data From Excel to Access 2007
7. Microsoft Excel Power in MS-Access
8. Rounding Function MROUND of Excel
9. MS-Access Live Data in Excel
10. Access Live Data in Excel 2
11. Opening an Excel Database Directly
12. Create an Excel Word File from Access

Read More

Search for Record Macro Action Access2007

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.

1. Import the Employees Table from the Northwind sample database.

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

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

4. Create two TextBoxes and a Command Button in the Form Footer 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 to fstName.

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

7. Save the Form as frmEmployees and close it.

Creating a Macro.

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

2. Select SearchForRecord from the drop-down list in the Macro Action Column.

3. Set Form in the Object Type control, in the property sheet below.

4. Select frmEmployees from the Object Name drop-down list.

5. Select First in the Record control.

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

7. Save the Macro as macSearch and close.

The Form Design Change.

1. Open frmEmployees in Design View.

2. Click on the Command Button at the Footer of the Form to select it.

3. Display the Property Sheet (F4) if it is not visible.

4. Change the Name Property value to cmdRun and change the Caption value to Search For the Record.

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

6. Save the Form and open it in Normal View.  You will see the first record on the form is active now.

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

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

1. Open the macSearch Macro in Design View.

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

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

1. Open the Employees Table, and note down the Last Name and First Name of a few records on paper and close the Table.

2. Open the Form frmEmployees.

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

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

5. 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] & "'"

• Calculating Time Difference
• Centralized Error-handler & Error- log
• Duplicating fields with Conditional Formatting
• How to use form filter on Report
• Saving Report Pages as separate PDF files

Read More

Exporting and Importing Data in Text Format

Exporting and Importing Data in Text Format.

This is all about exporting the data into a continuous stream of characters in text format, without any delimiters, and sending it through the Internet as an E-mail attachment to a remote location.  At the receiving end, the Text data is converted back into its original form and added to an existing table.  No field delimiters, field names, or any other details are sent along with the actual data lines.  The Source and Target Tables should have identical Structures, and these are known to the sending and receiving end users.

Let us take a quick look at some sample data and how it appears when converted into Text Format.

Sample MS-Access Table: Export

MS Access Table: Export text data-image, when exported into Text File: Export.txt.

As you can see from the text image, each record from Access Table: Export is converted into a continuous stream of characters and written into the Text File in separate lines.  The Text File is saved with the same name as the Table: Export with the file extension .txt (Export.txt).

The first two data fields in the Source Table are Text Fields (field size 15 characters each), the third field is Date type, and the next five fields are Numeric type.

Data Field details are given below:

Seq	Field	Type	Size
1.	LastName	Text	15
2.	FirstName	Text	15
3.	DofB	Date
4.	Height	Number	Integer
5.	Weight	Number	Long Integer
6.	H	Number	Single
7.	W	Number	Double
8.	Dcml	Number	Decimal

The VBA txtExport() Function Code

The VBA program below reads the MS-Access Table, record by record, converts it to text format, and writes it out to an external Text File, with the same name as the Source Table.  The text file Export.txt is created in the Default Database Folder.  I have created the sample data Table Export for Demo purposes only.  You can use any Table from your database, but see that it doesn’t contain any field types other than text, date, or Number.

Public Function txtExport(ByVal tblName As String)
'-----------------------------------------------------
'Purpose: Export Data into Text Format
'Author : a.p.r.pillai
'Date   : June, 2013
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------
'Data Types
'----------
'Text - 10 = Actual size
'Date - 8 = 10 characters
'Integer - 3
'Long Integer - 4
'Single - 6
'Double - 7
'Decimal - 20
'Numeric types 3,4,6,7 or 20 = 12 characters

Dim tblSize() As Variant
Dim db As Database, rst As Recordset
Dim fld As Field, fldCount As Integer
Dim j As Integer, tbldef As TableDef
Dim numSize As Integer, dtSize As Integer
Dim fmt As String, outTxt As String
Dim outFileName As String

On Error GoTo txtExport_Err

numSize = 12
dtSize = 10

'Exported Text Filename is same as Tablename with file-extension .Txt
outFileName = tblName & ".txt"

Set db = CurrentDb
Set tbldef = db.TableDefs(tblName)
fldCount = tbldef.Fields.Count - 1
'A singly dimensioned Array of Variant Type 
'is decalred for number of fields in the Table. 
ReDim tblSize(fldCount) 

'The Array is initialized with text data type 
'for appropriate size to hold text, date & Numeric Values 
For j = 0 To fldCount 
   Set fld = tbldef.Fields(j) 
   Select Case fld.Type 
      Case 3, 4, 6, 7, 20 'Numeric data type 
        tblSize(j) = String(numSize, "0") 
      Case 8 'Date Data type 
        tblSize(j) = String(dtSize, "0") 
      Case 10 'Text data type 
        tblSize(j) = String(fld.Size, "x") 'Actual Text Field-size 
    End Select Next 

'Open the Source Table 
    Set rst = db.OpenRecordset(tblName) 
    'Create and open the output text file in the same folder of the database 
    Open outFileName For Output As #1 
    'Read records till the end of Table and convert them into text format 
    Do While Not rst.EOF 
       For j = 0 To fldCount 
          Set fld = tbldef.Fields(j) 
          Select Case fld.Type 
             Case 3, 4, 6, 7, 20 
                fmt = "00000000.000" 
                'position data right-aligned into the text variable 
                RSet tblSize(j) = Format(rst.Fields(j).Value, fmt) 
             Case 8 
                fmt = "dd/mm/yyyy" 
                'position data right-aligned into the text variable 
                RSet tblSize(j) = Format(rst.Fields(j).Value, fmt) 
             Case 10 
                'position data left-aligned into the text variable 
                LSet tblSize(j) = rst.Fields(j).Value 
          End Select 
       Next 
       outTxt = "" 
       For j = 0 To fldCount 
          'join all text data variables into a single text line 
          outTxt = outTxt & tblSize(j) 
       Next 
          'Write into text file 
       Print #1, outTxt 
       'take next record to export 
   rst.MoveNext 
Loop 

'Close the Text File and other files 
Close #1 
rst.Close 
db.Close 

Set db = Nothing 
Set tbldef = Nothing 
Set fld = Nothing 

txtExport_Exit: 
Exit Function 

txtExport_Err: 
MsgBox Err & " : " & Err.Description, , "txtExport()" 
Resume txtExport_Exit 
End Function

Running the Code

You can run the Program directly from the Debug Window Command line:

Syntax: txtExport "Table Name"

txtExport "Export"

Or run it from a Command Button Click Event Procedure

Private Sub cmdRun_Click()
txtExport "Export"
End Sub

The Data Format Change.

1. When Text Type Field values are converted to output text, the actual field size is calculated, and the data is left-aligned within the text field's total size. If the field value is shorter than the field size, the blank positions are filled with spaces.
2. Date Field value uses 10 characters (dd/mm/yyyy) when converted to text.
3. All Numeric Field Values are converted to a 12-character text type and positioned Right-aligned in the output memory image, filled with zeroes at left positions.

NB: Date and Numeric Data text field sizes (Date=10, Number=12) are selected arbitrarily and can be modified if needed.  The MS Access Table should have only the three types of Data (Text, Date & Number) Fields.

The Text File created from the MS Access Table can be sent through E-Mail Attachment to the remote location.  The VBA Program below can be used to convert the Text File back into Access Data and append it to the Table with the same structure as the Source Table.

Public Function txtImport(ByVal txtFileName As String)
'txtFileName is same as Tablename with file-extension .txt
'-----------------------------------------------------
'Purpose: Import Text-Data into Table
'Author : a.p.r.pillai
'Date   : June, 2013
'Remarks: All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------
'Data Types
'----------
'Text - 10 = Actual size
'Date - 8 = 10 characters
'Integer - 3
'Long Integer - 4
'Single - 6
'Double - 7
'Decimal - 20
'Numeric types 3,4,6,7 or 20 = 12 characters

Dim tblSize() As Variant
Dim db As Database, rst As Recordset
Dim fld As Field, fldCount As Integer
Dim j As Integer, tbldef As TableDef
Dim numSize As Integer, dtSize As Integer
Dim fmt As String, outTxt As String
Dim inputFileName As String, I As Integer, k As Integer

On Error GoTo txtImport_Err

numSize = 12
dtSize = 10

inputFileName = txtFileName & ".txt"
Set db = CurrentDb
Set tbldef = db.TableDefs(txtFileName)
fldCount = tbldef.Fields.Count - 1

'A singly dimensioned Array of Variant Type
'is decalred for number of fields in the Table.
ReDim tblSize(fldCount)

'The Array is initialized with text data type
'for appropriate size to hold text, date & Numeric Values
For j = 0 To fldCount
   Set fld = tbldef.Fields(j)
   Select Case fld.Type
      Case 3, 4, 6, 7, 20 'Numeric data type
          tblSize(j) = String(numSize, "0")
      Case 8 'Date data type
          tblSize(j) = String(dtSize, "0")
      Case 10 'Text Field
          tblSize(j) = String(fld.Size, "x") ' Actual Text Field-size
   End Select
Next

'Open the Target Table Export
Set rst = db.OpenRecordset(txtFileName)

'Open the input text file
Open inputFileName For Input As #1

'Read the Text file, convert and add the data to the Target Table
  Do While Not EOF(1)
   'Read text data line
   Input #1, outTxt
   I = 1 'first field value off-set
   'Add a new record into the table
   rst.AddNew

   For j = 0 To fldCount
      Set fld = tbldef.Fields(j)
      'read data size for extracting correct number of characters
      'from the text line
      k = Len(tblSize(j))
     
      Select Case fld.Type 'check the current field type
         Case 3, 4, 6, 7, 20 'if numeric data type then
          'Extract numeric data, convert and write into data field
            rst.Fields(j).Value = Val(Mid(outTxt, I, k))
            I = I + k 'increment to next field off-set
         Case 8 'if it is date then
          'Extract Date data, convert and write into data field
            rst.Fields(j).Value = CDate(Mid(outTxt, I, k))
            I = I + k 'increment to next field off-set
         Case 10 'Text data type
          'Extract Date data, convert and write into data field
            rst.Fields(j).Value = Mid(outTxt, I, k)
            I = I + k 'increment to next field off-set
       End Select
    Next
      rst.Update
      'initialize the variable to read the next line of text data
      outTxt = ""
Loop

'No more text data in the file
Close #1
rst.Close
db.Close
Set db = Nothing
Set tbldef = Nothing
Set fld = Nothing

txtImport_Exit:
Exit Function

txtImport_Err:
MsgBox Err & " : " & Err.Description, , "txtImport()"
Resume txtImport_Exit

End Function

Debug Window Command Line Run Syntax:

txtImport "Text File Name"

txtImport "Export"

NB: Text File Name is entered without the file extension (.TXT). Target Table should have the same structure as the Source Table.

• Centralized Error-handler & Error- log
• Duplicating fields with Conditional Formatting
• How to use form filter on Report
• Saving Report Pages as separate PDF files
• Autonumber with Date and Sequence Number

Read More

Finding Difference between Dates in rows of a Column

Finding the Difference between Dates in Rows of a Column.

Your Company has several Customers who place orders for products, and you maintain the Order detail data in an MS Access table.  The management would like to know each Customer's Order frequency so that the company can plan and acquire adequate stock in advance to meet their requirements in time.

We have a table of Orders (tblOrders) for a particular customer, with the following fields and sample data as shown below:

AutoID	OrderNo	OrderDate	OrderValue	Days
1	2012060500	05-06-2012	100000
2	2012070701	15-07-2012	50000
3	2012109000	25-10-2012	150000
4	2012120050	27-12-2012	125000
5	2013028075	14-02-2013	175000

Our task is to find the frequency of orders, in number of days, from this particular customer.  This can be done by finding the difference between the Order Dates.  The sample data records are organized by AutoID in the first column.  This is very important for the first method we are going to try out.  It is easy to find the OrderDate in the next record with the Dlookup() Function in an MS Access Query.

Organizing the Data.

We will try using only two columns from the tblOrders Table, AutoID & OrderDate, and will create a third column, Days, by finding the difference between Order Dates. 

Here, the data records are organized (as shown above), and the output in the Days Column can be found with a simple Query.  The Query-based solution works only when the AutoID field has consecutive values and the OrderDate is arranged in Ascending Order. 

The SQL of the sample MS-Access Query is given below:

SELECT tblOrders.AutoID,
 tblOrders.OrderID,
 tblOrders.OrderDate,
 DateValue(nz(DLookUp("OrderDate","tblOrders","AutoID = " & [AutoID]+1),"31-12-1899")) AS EndDate,
 IIf([EndDate]-[OrderDate]<0,0,[EndDate]-[OrderDate]) AS Days
FROM tblOrders
ORDER BY tblOrders.OrderDate;

The result of the run of the Query is shown below:

AutoID	OrderID	OrderDate	EndDate	Days
1	2012060500	05-06-2012	15-07-2012	40
2	2012070701	15-07-2012	25-10-2012	102
3	2012109000	25-10-2012	27-12-2012	63
4	2012120050	27-12-2012	14-02-2013	49
5	2013028075	14-02-2013	0	0

Even though the MS-Access Query-based solution looks simple and effective, preparing data with consecutive numbers is not that easy, because you will be filtering and creating output data from a larger data file; the auto-number, if it exists, will not be consecutive.  But you can create auto-numbers in Query Column with a VBA User-defined Function.  You can find the Code and details here.

The VBA-Based Solution.

The VBA-based solution doesn’t need a column with consecutive numbers. But the OrderDate field must be sorted in Ascending Order.  To prepare the data from our MS-Access Table tblOrders, as input for our VBA Program FrequencyCalc(), we need only a SELECT Query with required fields from the tblOrders Table.  The SQL of the sample Query is given below:

Query: tblOrdersQ – OrderDate field value is sorted in ascending order.

SELECT tblOrders.OrderID,
 tblOrders.OrderDate,
 tblOrders.Days
FROM tblOrders
ORDER BY tblOrders.OrderDate;

VBA Code of the FrequencyCalc() Function is given below:

Public Function FrequencyCalc()
'----------------------------------------------------------
'Author: a.p.r.pillai
'Date  : March 2013
'All Rights Reserved by www.msaccesstips.com
'----------------------------------------------------------
Dim db As Database, rst1 As Recordset, rst2 As Recordset
Dim m_diff As Integer

On Error GoTo FrequencyCalc_Error

Set db = CurrentDb
'Open tblOrdersQ's first instance and position on the first record
Set rst1 = db.OpenRecordset("tblOrdersQ", dbOpenDynaset)
'Open tblOrdersQ's second instance and position on the second record
Set rst2 = db.OpenRecordset("tblOrdersQ", dbOpenDynaset)
rst2.MoveNext

'Find difference between dates from first & second instances of OrderDates
'in the same Query.
'update number of days in the second record onwards.
Do While Not rst1.EOF
   m_diff = rst2!OrderDate - rst1!OrderDate
   If Not rst2.EOF Then
     With rst2
        .Edit
        !Days = m_diff
        .Update
      rst1.MoveNext
        .MoveNext
      End With
      If rst2.EOF Then
         Exit Do
      End If
   End If
Loop
rst1.Close
Set rst1 = Nothing
rst2.Close
Set rst2 = Nothing
db.Close
Set db = Nothing

FrequencyCalc_Exit:
Exit Function

FrequencyCalc_Error:
MsgBox Err & " : " & Err.Description, , "FrequencyCalc()"
Resume FrequencyCalc_Exit
End Function

Demo Run Result of VBA Code.

The run result of the Program is given below:

AutoID	OrderID	OrderDate	Days
1	2012060500	05-06-2012
2	2012070701	15-07-2012	40
3	2012109000	25-10-2012	102
4	2012120050	27-12-2012	63
5	2013028075	14-02-2013	49

The VBA procedure updates the frequency Days in the second record onwards, using the sample Query we tried earlier with the Dlookup() Function.

Technorati Tags: Utilities

Earlier Post Link References:

• Duplicating fields with Conditional Formatting
• How to use form filter on Report
• Saving Report Pages as separate PDF files
• Autonumber with Date and Sequence Number
• Assigning Module-level Error-trap Routines

Read More

Shifting Focus from one sub-form to the other

Shifting Focus from one sub-form to the other.

Sample Form with two Sub-Forms:

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

The focus jump path.

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

Last week we saw how to set focus to a particular field in a sub-form from the main form field.  I have not mentioned the relationships that bind these three forms in the earlier example.  But here it is important to know before we attempt to leave Focus from a record in the first sub-form and set focus to its corresponding record in the second sub-form.

Two related Sub-Forms and two issues to solve.

1. Two related sub-forms need to link together via the common Main Form. The first sub-form is directly linked to the main form.
2. Set focus on the Amount TextBox in the second sub-form when the Tab key transfers focus from the last TextBox (the School-Year) on the first sub-form.

Assume that the current record on the first sub-form (frm_Session) is the Session ID (Primary Key) field value 1.  Since the first sub-form is directly linked to the second sub-form (we will explore this aspect, i.e., establishing direct links between two sub-forms, a little later on this page), frm_Payments, the payment record with the Session ID (Foreign Key) field value 1 is displayed on that form too.  We want to transfer focus from the first subform Session record to its corresponding Payment record Amount field in the second subform. 

Normally, when you press the Tab key on the last field of the current record, the insertion point moves to the first field of the next record on the same Form (with Session ID value 2). Automatically, the Payment record linked to the Session record also changes to synchronize with the Session ID value 2, because it is directly linked with the frm_session.  

So the control from the first record is lost with Session ID 1, and we cannot enter the Payment Value in the Amount field of the frm_Payment, for the same Session ID 1. We want the focus to move to the frm_Payment's Amount field to enter the payment value for Session ID 1. If you are lost in the details of the story, then check the second image diagram.

So, the question is how to jump focus from the first record in both subforms when the focus is lost from the last field of the first subform, and transfer the Focus to its corresponding record's Amount field on the second subform?

Linking Both Sub-Forms Together.

Before going into that, let us see how to link the sub-forms to synchronize related records on both subforms.

The first sub-form (frm_Session) is directly linked to the Main Form (frm_Students) by the common Field Student ID (Primary Key) of the Students Table, and Student ID (Foreign Key) of the Session Table.

The current student record on the main form can have several session records on the frm_Session Form.  Each Session record on the Session sub-form will have one or more Payment records on the frm_Payment sub-form and Session ID as its Foreign Key.

The frm_Payments sub-form is directly linked to the frm_Session sub-form on the common field Session ID. 

Linking Both Sub-Forms Together.

Two sub-forms cannot be linked together directly because a sub-form cannot be considered a Master Form by another Sub-Form.  When this kind of link (or relationship) becomes necessary, similar to the above sub-forms, we can do this by simply transferring the first sub-form Key-field value into an Unbound Textbox on the Main form, and the Unbound Textbox that holds the Session ID value becomes part of the Main Form.  The Name of this Textbox can be used in the Link Master Field property of the second sub-form to establish a relationship with the first sub-form.

Check the following Design View of the above Forms:

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

You can see an Unbound TextBox with a yellow background, specifically created to link the second sub-form (frm_Payments) to the first sub-form (frm_Session) through the Unbound Textbox on the frm_Students main Form.  This Textbox Name Property value is set to Session_ID, somewhat different than the actual field names in the sub-forms: SessionID. The child Label Caption I have changed to Session_ID for information.

The Session_ID TextBox’s Control Source Property is set with the following expression, to copy the SessionID value automatically from the current record on the frm_Session sub-form:

=[frm_Session].Form!SessionID

Once this is done, you can link the frm_Payments sub-form with the frm_Session sub-form through the Session_ID Texbox by setting the Link Master Field and Link Child Field properties of the frm_Payments sub-form, as shown below.

Link Master Field = Session_ID (the unbound Textbox name)
Link Child Field = SessionID  (the Payments Form’s Foreign Key field name)

Tackling the Real Problem

Now that we know how the subforms are linked, we can concentrate on the real issue that I pointed out at the beginning of this Article.  We must be able to transfer control from the last field of a record in the first sub-form to its corresponding record in the second sub-form, without changing the current record on the first sub-form.  We need a small VBA program to do that job successfully.

Tip: You can try this with three simple tables (Students, Session & Payments) having sample fields as shown on the Forms.  You may download a sample database, with the sample tables and forms, from the download link given at the bottom of this Article.

If you have the sample Tables and Forms organized as per the design shown above, you may open the frm_Students Main Form and do a sample run of what we are trying to achieve, without the VBA Code.

Tip:  If you have downloaded the sample database from the link at the end of this page, open frm_Session in the design view, press F4 to display the Property Sheet, and click the School Year Field.  You will find [Event Procedure] in the On Got Focus event property.  Select this property and click on the Build (. . .) Button to open the VBA Module.  Highlight and delete the entire code, except the first two lines: Option Compare Database & Option Explicit.  Save and close the form. You can copy and paste the deleted code from this page.

1. Open the frm_Students in Normal View.

2. Select the first record on the frm_Session form to select the record with Session ID value 1.

3. Check for the related record on the frm_Payments sub-form, with Session ID value 1.

4. Press the Tab Key to move the focus to the last field, School Year. 

   Note: When you press the Tab Key one more time, the focus should jump from the current record on the frm_Session sub-form to its corresponding record with Session ID 1 on the frm_Payments sub-form, which must become active. 

5. Now, press the Tab Key to move out of the last field, School Year, on frm_Session to go to the Amount field on frm_Payments.

But it didn’t happen as we expected; instead, the cursor moved down to the next record on frm_Session, with SessionID value 2.  The related records in frm_Payments also changed the foreign key to SessionID value 2 to match the record in frm_Session.

We can do this task only with VBA Code.  The steps of our program are given below:

1. When the focus is set on the School Year field on frm_Session, save the Session ID value into a memory variable SID.

2. When the Focus is Lost (i.e., when the user presses the Tab Key again) from the School Year, the Focus moves to the next record, and at this point the VBA Code searches the form’s RecordsetClone for the SessionID value Variable SID.

3. When the record is found, the RecordsetClone Bookmark is saved into the bkmk String Variable.

4. Copy the record set Bookmark into the frm_Session’s Bookmark control. 

   These steps reset the focus back to the previous record, from the second record, on the frm_Session sub-form. The frm_Payment record changed earlier, and before executing the code,  returns to the one with the Session ID on the frm_Session record.

5. Set Focus on the frm_Payments.

   When this happens, the frm_Payments field with Tab Index value 0 receives Focus.  At this point, we can move the Focus to any other field, if needed.  We will try this by setting the focus on the Amount field.

6. Set focus on the Amount field on frm_Payments.

If you have the above Form ready, then copy and paste the VBA Code given below into the frm_Session’s VBA Module.

1. Open the frm_Session form in the design view.

2. Click on the School Year field to select it.

3. Press F4 to display the Property Sheet of the School Year field.

4. Find the On Got Focus event property and click to select it.

5. Select [Event Procedure] from the drop-down control.

6. Click on the Build (...) Button to open the Form’s VBA Module (Class Module).  You may find the following lines of Code in the Class Module.

   The VBA Code.

   Option Compare Database
   Option Explicit
   
   Private Sub SchoolYear_GotFocus()
   
   End Sub

7. Copy and paste the following lines of code, overwriting the existing lines of code in the Module:
   

   Option Compare Database
   Option Explicit
   'SID is declared as a global variable
   Dim SID As Long
   
   Private Sub SchoolYear_GotFocus()
   'Save the SessionID value in a Global variable
   SID = Me!SessionID
   End Sub
   
   Private Sub SchoolYear_LostFocus()
   '----------------------------------------------
   'This subroutine runs when the Focus is shifted 
   'from the SchoolYear field.
   '----------------------------------------------
   'Author : a.p.r. pillai
   'Date   : Jan/2013
   'All Rights Reserved by www.msaccesstips.com
   '----------------------------------------------
   Dim ctrl As Control, ctrl2 As Control
   Dim bkmk As String, rst As Recordset, j As Long
   Dim rstSID As Long
   
   'The following lines of code prevents shifting the focus
   'to the next record on the frm_Session sub-form, when the focus
   'is lost from the last field of frm_Session, in preparation to set focus
   'on a particular field on the corresponding record on the
   'frm_Payments sub-form.
   Set rst = Me.RecordsetClone 'frm_session's recordset
   rst.MoveFirst
   For j = 1 To rst.RecordCount
   rstSID = rst![SessionID]
   If rstSID = SID Then 'find the record matching the current record on frm_session
      'when match found save the record's recordset bookmark
      bkmk = rst.Bookmark
      'copy the recordset bookmark to the form
      'this will set the focus back on the same record
      'this will also ensure that the SessionID related Payment record
      'will be current on the frm_Payment form
      Me.Bookmark = bkmk
      'set focus on the first field
      Me.SessionID.SetFocus
      'break the loop
      Exit For
   End If
   rst.MoveNext
   Next
   rst.Close
   'transfer control to the frm_Payments Sub-form
   'Now the field with Tabindex number 0 have the default focus
   Set ctrl = Forms![frm_Students].Controls("frm_Payments")
   ctrl.SetFocus
   'Once the focus is shifted on the field with tabindex 0 within frm_Payment sub-form
   'we can move the focus to any other field within that form, if required
   Set ctrl2 = Forms![frm_Students]![frm_Payments].Form.Controls("Amount")
   ctrl2.SetFocus
   
   End Sub
   

8. Save and Close the Form.

   Try out your Forms.

9. Open the frm_Students in the normal view.

10. Click on the first record on the frm_Session Form.

11. Press the Tab Key to move focus to the School Year field and check the corresponding record on the frm_Payments form.

12. Press the Tab Key one more time to jump the focus to the frm_Payments sub-form record, with the same Session ID value, and set the Focus directly on the Amount field.

Download Demo-Database.

Download Demo SubForm.zip

• How to use form filter on Report
• Saving Report Pages as separate PDF files
• Autonumber with Date and Sequence Number
• Assigning Module-level Error-trap Routines
• Utility for inserting VBA Error Handler code

Read More

Setting Focus on a field inside a Sub-Form

Setting Focus on a field in a Sub-Form.

The Main Form (frm_Students) has two Sub-Forms (frm_Sessions and frm_Payment).  A sample image of such a Form is given below:

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

Sub-Form Container and Sub-Form.

Each Sub-Form on the main form is placed within a Sub-Form Container.  The Sub-Form contains other controls (like Textboxes), and we cannot set focus directly on any of these controls from outside the sub-form container.  That doesn’t mean that we cannot address the controls directly to retrieve or set the value into that control through VBA.

The Difference Between Setting Focus and Retrieving Values

Setting focus directly in the Amount field of frm_Payments subform from the Main Form using code doesn't work.

Example:

'this statement will not work when frm_Payments doesn't have focus
Forms![frm_Students]![frm_Payments].Form.Amount.SetFocus

The above statement may highlight the Amount field, but the focus will not be set on that field.

But we can retrieve the value directly from the Amount field of frm_Payments, even when the focus is not on that form, with the following statement.

Example-2:

'this statement retrieves the Amount field value directly.
m_Amt = Forms![frm_Students]![frm_Payments].Form!Amount

When the frm_Payments sub-form has the focus, you can address a control (say the Amount field) within that form to set the real focus on it.

It simply means that it takes a two-step action to address a control within a sub-form to set focus on:

1. Set focus on the sub-form container first.  Setting the Tab Index Value of the frm_Payments to 0 also works.
2. Set focus on any control within the frm_Payments sub-form.

So, it is a two-step process, and the following two VBA statements do the job:

With Me.Payments.form
	.SetFocus
	.Amount.SetFocus
End With

The following version of the above statements is also valid:

With Forms.[frm_Students].[frm_Payments]
	.SetFocus
	.Form.Amount.SetFocus
End With

What Next...

Next, we will see how to jump from the last field of one subform record to its corresponding record-field on the second subform.  If you think it is so easy after learning the earlier lines of code, then try it yourself and come back to the next episode.

• Saving Report Pages as separate PDF files
• Autonumber with Date and Sequence Number
• Assigning Module-level Error-trap Routines
• Utility for inserting VBA Error Handler code
• Prepare a List of Procedure names from a Module

Read More