Activex ListView Control Tutorial-01

ListView Control Tutorial.

In Microsoft Access, the ListBox control is often used to display a few columns of data, making it easy to locate and select items. Its data source may be assigned to the Row Source property as a value list, or dynamically from a table or query.

The ComboBox control, by contrast, keeps its list hidden until the user clicks to expand it and make a selection. Both of these are standard Access controls on forms.

There is also another familiar list-style control that we encounter frequently in Access: the Datasheet View. Whether records are displayed from a table or query, the datasheet presents them as a large, scrollable list in tabular format.

In addition to these built-in controls, Microsoft Access also allows us to use ActiveX controls. A common example is the Microsoft Common Dialog Control (often used for file browsing).

The focus of this topic is the Windows ListView control. You can think of it as very similar to Windows Explorer: it can display items as large icons, small icons, a simple list, or in a detailed view with multiple columns. Data from an Access table or query can be loaded into the ListView, giving you the ability to:

• Rearrange columns or rows,

• Sort items interactively,

• Display images next to items,

• Present records in a more flexible, customizable layout.

This control is widely used in other programming environments such as VB6, VB.NET, and C#. In this article, we will explore how it is integrated into a Microsoft Access database.

Below is a simple ListView demo screen displaying sample data:

Right-click to open the Large Image in a New Window.

We will use the image-like display shown earlier as the starting point for this ListView Control tutorial. With just a few lines of VBA code, we have uploaded ten rows of data into the ListView control.

By default, the ListView ActiveX control may not appear in the list of available ActiveX controls in Access. To make it available, we must add the library file 'MSCOMCTL.OCX' from the C:\Windows\System32 folder to the Access references. Once registered, you will see it listed as Microsoft ListView Control, Version 6.0, along with other ActiveX controls.

This library file provides several useful controls, including ListView, TreeView, and ImageList. If you have already followed our earlier TreeView control tutorials, you are familiar with this library.

Adding the Windows Common Controls Library (MSCOMCTL.OCX)

Follow these steps to attach the MSCOMCTL.OCX file to your database:

1. Open your database and press Alt + F11 to launch the VBA editor.

2. From the Tools menu, select References…

3. Click the Browse button.

4. Locate the file MSCOMCTL.OCX (Microsoft Windows Common Controls) in one of the following folders:

   • C:\Windows\System32\ → on 32-bit systems, or on most Windows 11 installations.

   • C:\Windows\SysWOW64\ → on 64-bit systems.

5. Select the file and click Open to attach it to your database.

6. Press Alt + F11 again to return to the database window.

Designing a Sample Form with the ListView Control

We will now design a simple form that matches the sample image shown at the beginning of this tutorial.

1. Create a new blank form in Design View.

2. From the Controls group, select ActiveX Control.

3. In the list of available ActiveX controls, locate and select Microsoft ListView Control, Version 6.0, then click OK to insert it onto the form’s Detail section.

4. Resize the control:

   • Grab the bottom-right resize handle and drag it outward to make the ListView large enough to resemble the sample image.

   • Move the control slightly down and to the right to leave space for a heading label above and some margin on the left.

5. With the ListView still selected, open the Property Sheet and rename the control by setting its Name property to: ListView1

6. Create a Label control above the ListView.

   • Change its Caption property to: ListView Control Tutorial

   • Apply any formatting you prefer (font size, bold, color, etc.) to make the heading stand out.

7. Insert a Command Button below the ListView.

   • Set its Name property to: cmdClose

   • Set its Caption property to: Close

When completed, your form design should look similar to the following layout:

Right-click to open the Large Image in a New Window.

1. Now, save the Form named: ListViewTutorial and keep the Form in the design view.

2. Press Alt+F11 to go back to the Form’s Class Module Window.

   The VBA Code.

3. Copy and paste the following Code into the Form's VBA Module, replacing existing lines of code, if any:

   Option Compare Database
   Option Explicit
   
   Dim lvwList As MSComctlLib.ListView
   Dim lvwItem As MSComctlLib.ListItem
   Dim ObjImgList As MSComctlLib.ImageList
   Const prfx As String = "X"
   
   Private Sub cmdClose_Click()
      DoCmd.Close acForm, Me.Name
   End Sub
   
   Private Sub Form_Load()
       Call LoadListView
   End Sub
   
   Private Function LoadListView()
       Dim intCounter As Integer
       Dim strKey As String
   
   'Assign ListView Control on Form to lvwList Object
    Set lvwList = Me.ListView1.Object
    
    'Create Column Headers for ListView
    With lvwList
       .ColumnHeaders.Clear 'initialize header area
      'Parameter List:
   'Syntax: .ColumnHeaders.Add Index, Key, Text, Width, Alignment, Icon
       .ColumnHeaders.Add , , "Name", 2500
       .ColumnHeaders.Add , , "Age", 1200
       .ColumnHeaders.Add , , "Height", 1200
       .ColumnHeaders.Add , , "weight", 1200
       .ColumnHeaders.Add , , "Class", 1200
    End With
    
    'Initialize ListView Control
     While lvwList.ListItems.Count > 0
           lvwList.ListItems.Remove (1)
     Wend
       
    With lvwList
       For intCounter = 1 To 10
           strKey = prfx & CStr(intCounter) '
      'Syntax: .ListItems.Add(Index, Key, Text, Icon, SmallIcon)
           Set lvwItem = .ListItems.Add(, strKey, "Student " & intCounter)
           'Add next columns of data as sub-items of ListItem
           With lvwItem
         'Parameters =      .Add Index,Key,Text,Report Icon,TooltipText
               .ListSubItems.Add , strKey & CStr(intCounter), CStr(5 + intCounter)
               .ListSubItems.Add , strKey & CStr(intCounter + 1), CStr(135 + intCounter)
               .ListSubItems.Add , strKey & CStr(intCounter + 2), CStr(40 + intCounter)
               .ListSubItems.Add , strKey & CStr(intCounter + 3), ("Class:" & intCounter)
   
          End With
       Next
       'reset lvwItem object
       Set lvwItem = Nothing
   End With
   lvwList.Refresh
   
   End Function

4. Save the Form with the name ListView Control Tutorial-01.

   Demo View of the Form.

5. Open the Form in Normal View to have a look at our creation.

   If you find your form as shown below, then you are on the right track.

   

   Right-click to open the Large Image in a New Window.

   We need to make some adjustments to the ListView control’s property settings. Earlier, we renamed the control to ListView1 using the standard Access Property Sheet. However, the ListView control has its own dedicated property sheet, which provides more detailed configuration options. Some of these settings also appear in the Access Property Sheet, but many are unique to the control itself.

6. To access it, right-click the ListView control, point to ListViewCtrl Object, and then select Properties from the shortcut menu.

7. This will open the ListView control’s own property sheet, as shown in the image below:

   

   Right-click to open the Large Image in a New Window.

8. At the top of the Property Sheet, you will see tabs that group various options. By default, the General tab is active. On this tab, the left side lists option values, while the right side contains corresponding checkboxes.

   For our form, we only need to adjust two ListView properties, which are disabled by default. Once enabled, these allow the ListView to display in different modes—such as large icons, small icons, simple lists, or Report View (as shown in the first image above).

   1. Check the Enabled property on the right side to activate the ListView control.

   2. From the View drop-down list on the left side, select lvwReport.

   3. Click the Apply button to confirm the change.

   4. Click OK to close the Property Sheet.

   Finally, save the form and open it in Normal View. The result should now look like the image shown earlier, except for any differences in form background color or other form-level settings.

The Program's Functional Diagram.

Before diving into the VBA code, it’s important to understand how data items are actually loaded into the ListView control. With a ListBox, the data arrangement is fairly straightforward. However, the ListView control uses a completely different approach. The loading process does not follow the logical sequence we might naturally expect.

Once you see how the data flows from the source into a single row—illustrated as a diagram or flow chart—the concept becomes much easier to grasp. With this visual in mind, understanding the VBA code and its role in the process will be far more intuitive.

The Data Flow Diagram.



Right-click to open the Large Image in a New Window.

1. In the diagram, the box at the top-left corner represents the ListView control.

   The first step in preparing the list is to create the column headings. These headings (shown in red in the diagram) work the same way as field headers in a table’s Datasheet View. Each column heading is added to the ColumnHeaders collection of the ListView control using the ColumnHeaders.Add() method. Since our sample has five columns, the method is called five times, once for each heading.

   The next set of actions loads the actual data. Each row of data represents a single record with five fields. However, these fields are not loaded all at once—they are split between two different object members of the ListView control: ListItems and ListSubItems.

   • The first field value (the value for the first column) is added to the ListItems collection using the ListItems.Add method. For example, in the sample image, the value Student1 (from the first column of the first row) is stored in the ListItems object.

   • From the second column onward, the remaining field values are added as ListSubItems of the corresponding ListItem. This is done using the ListSubItems.Add method, called four times—once each for the Age, Height, Weight, and Class values.

   Together, these two steps complete a single row of data in the ListView control. The diagram illustrates this process with two rows of sample data.

   Once you understand this two-level structure—ListItems for the first column, ListSubItems for the remaining fields—the VBA code that builds the ListView will be much easier to follow.

Let us go to the VBA Code Segment-wise.

In the global declaration section of the module, we have declared the ListView object, the ListItem object, the ImageList object, and a constant variable with the string value "LV".

Dim lvwList As MSComctlLib.ListView
Dim lvwItem As MSComctlLib.ListItem
Dim ObjImgList As MSComctlLib.ImageList
Const prfx As String = "X"




The variable lvwList is declared as a ListView object, lvwItem as a ListItem object of the ListView control, and ObjImgList as an ImageList object. The ImageList object is another ActiveX control that can store image icons for use with both the TreeView and ListView controls. For now, we will set the ImageList aside and return to it later.

The constant Prfx is used as the Key value prefix in the ListItems.Add method, which accepts several optional parameters. The Key value must always be a string type.

The LoadListView() function serves as the main program.

On our form, the ListView control is named ListView1. The first executable statement in the program is:

Set lvwList = Me.ListView1.Object 

Assigns the ListView1 control on the Form into the Object variable lvwList declared in the Global declarations area.

Next, we will get prepared to load the Column Header information.  First, we initialize the ColumnHeader object to ensure that it is empty.  When we repeatedly run the program, the control has a tendency to retain the earlier loaded values in the control.  When you open and close this form more than once, after disabling the 'ColumnHeaders.Clear' statement, you will know the difference. The same set of headings is added to the control every time and appears with empty rows below.

You can verify this behavior manually with the following steps:

1. Open the demo form once and then close it.

2. Reopen the form in Design View.

3. Right-click the ListView control, highlight the ListViewCtrl Object option, and select Properties from the menu.

4. In the property sheet, go to the Column Headers tab.

5. You will see the first column heading displayed in a text box, with its Index value (1) shown above.

6. Move the mouse pointer to the right side of the index number box. Arrow buttons (left and right) will appear.

7. Click the right arrow to scroll through and display the remaining column labels, one by one, as their index numbers change.

8. If you open and close the form again, you will notice that the Column Headers tab now contains duplicate sets of the same column labels.

The ColumnHeaders.Add method syntax is as follows:

lvwList.ColumnHeaders.Add(Index, Key, Text, Width, Alignment, Icon)

All parameters are optional.

With lvwList
    .ColumnHeaders.Clear 'initialize header area
'Parameter List:
'Syntax: .ColumnHeaders.Add Index, Key, Text, Width, Alignment, Icon
    .ColumnHeaders.Add , , "Name", 2500
    .ColumnHeaders.Add , , "Age", 1200
    .ColumnHeaders.Add , , "Height", 1200
    .ColumnHeaders.Add , , "weight", 1200
    .ColumnHeaders.Add , , "Class", 1200
 End With 

The Index value is automatically assigned as running serial numbers (1, 2, 3, and so on).

The Key value is of the String data type. Although it is not typically used for column headers, it can be assigned if needed.

The Text value is what appears on the control as the column label.

To control the display width of each column, you can assign an approximate width value in pixels, based on the data expected under that column.

If the Text alignment property is omitted, the default is Left alignment (0 - lvwAlignmentLeft). Alternatively, you can set it to Right alignment (1 - lvwAlignmentRight) or Center alignment (2 - lvwAlignmentCenter).

Once the column headings are loaded, the next step is to insert the first record. Specifically, we start by loading the value in the first column of the first row. But before doing so, we must initialize the ListItems object with the following code segment:

'Initialize ListView Control
  While lvwList.ListItems.Count > 0
        lvwList.ListItems.Remove (1)
  Wend

The next code block loads the record list items one row at a time, generating a total of ten rows with sample values. For demonstration purposes, these values remain mostly constant, with a few variations to highlight the process. This is accomplished by placing the logic inside a For...Next loop, which iterates ten times, thereby creating ten rows of data in the ListView control.

With lvwList
    For intCounter = 1 To 10
        strKey = prfx & CStr(intCounter) '
  'Syntax: .ListItems.Add(Index, Key, Text, Icon, SmallIcon)
        Set lvwItem = .ListItems.Add(, strKey, "Student " & intCounter)
        
  'Add next columns of data as sub-items of ListItem
        With lvwItem
  ' Syntax: .ListSubItems.Add Index,Key,Text,Report Icon,TooltipText
            .ListSubItems.Add , strKey & CStr(intCounter), CStr(5 + intCounter)
            .ListSubItems.Add , strKey & CStr(intCounter + 1), CStr(135 + intCounter)
            .ListSubItems.Add , strKey & CStr(intCounter + 2), CStr(40 + intCounter)
            .ListSubItems.Add , strKey & CStr(intCounter + 3), ("Class:" & intCounter)

       End With
    Next
    'reset lvwItem object
    Set lvwItem = Nothing
End With

The first statement inside the For...Next loop —

strKey = prfx & CStr(intCounter)

— prepares a unique Key value for the first list item (the first column).

All parameters of the ListItems.Add() Methods are optional. However, in this case, the first three—Index, Key, and Text—are used in the same sequence as the Column Headers. The remaining two parameters are reserved for assigning an icon and a small icon image, if needed.

When the value for the first column of a row is assigned to the ListItem (i.e., lvwList.ListItems), that object reference is stored in the lvwItem variable. This allows easy access to its sub-object, ListSubItems, without repeatedly writing the full object reference.

lvwList.ListItems.Item(index).ListSubItems.Add() 

Expressed in the short form with lvwItem.ListSubItems.Add()

Using the short form 'lvwItem.ListSubItems.Add()' We can load the remaining column values into the ListView control.

The ListSubItems.Add() method accepts its first three parameters in the same order as the ListItem (Index, Key, and Text), followed by the optional Icon image reference and Tooltip Text.

For the Key value of each column, I have appended the current loop counter value plus an offset to ensure uniqueness across all columns. Although the Key parameter can be omitted, it is good practice to use it.

The Method  ListSubItems.Add()  is called four times within the loop to insert values for the second through fifth columns.

These steps repeat nine more times, ultimately creating ten sample records in the ListView control.

The demo database containing this ListView control example is attached, ready to run and explore.

In the next part of this tutorial, we will explore how to search and locate specific values within the ListView control, as well as how to rearrange columns—just like we do in Datasheet View.

DataSavingForm.zip
1. Microsoft TreeView Control Tutorial
2. Creating an Access Menu with a TreeView Control
3. Assigning Images to TreeView Nodes
4. Assigning Images to TreeView Nodes-2
5. TreeView Control Checkmark Add Delete
6. TreeView ImageCombo Drop-down Access
7. Re-arrange TreeView Nodes By Drag and Drop
8. ListView Control with MS-Access TreeView
9. ListView Control Drag Drop Events
10. TreeView Control With Sub-Forms

Read More

MS-Access And Transfer SpreadSheet Command.

Export MS Access Tables to Spreadsheet.

A very useful feature of Microsoft Access is the ability to transfer data between Access and Excel using the built-in Import/Export options. In this session, we will focus specifically on the Export process and examine the challenges that may arise after exporting data, particularly when using various export options available in MS Access.

The simple VBA Command Syntax is:

Docmd.TransferSpreadsheet [Transfer Type],[SpreadSheet Type], _
[Input TableName/Query Name],[Output FilePath], _
True(HasFieldNames),Range,UseOA

1. The first parameter, [Transfer Type], can take one of two values: acImport or acExport.

   The second parameter, Spreadsheet Type, accepts predefined options ranging from 0 to 10. These are part of an enumerated list that also includes support for Lotus worksheets.

   The available options are:

   • acSpreadsheetTypeExcel12Xml – 10

   • acSpreadsheetTypeExcel12 – 9

   • acSpreadsheetTypeExcel9 – 8

   • acSpreadsheetTypeExcel8 – 8

   • acSpreadsheetTypeExcel7 – 5

   • acSpreadsheetTypeExcel5 – 5

   • acSpreadsheetTypeExcel4 – 6

   • acSpreadsheetTypeExcel3 – 0

   • acSpreadsheetTypeLotusWJ2 – 4

   • acSpreadsheetTypeLotusWk4 – 7

   • acSpreadsheetTypeLotusWk3 – 3

   • acSpreadsheetTypeLotusWk1 – 2

   You can pass either the enumeration name or its corresponding numeric value as the second parameter.

2. The third parameter specifies the name of the input Table or Query to be exported (or imported).

3. The fourth parameter is the full path and file name of the output spreadsheet.

4. The fifth parameter is a Boolean value (True or False). Setting it to True ensures that the field names are included as the first row in the exported worksheet.

5. The optional Range parameter applies only when using acImport, allowing you to define the worksheet range from which data should be imported.

6. The final optional parameter UseOA is not defined and is typically not used.

Sample Transfer-Spreadsheet Command.

Docmd.TransferSpreadSheet acExport,acSpreadSheetTypeExcel12xml, _
  ”Products”,”C:\My Documents\Book1.xlsx”,True

• The options acSpreadsheetTypeExcel3 through acSpreadsheetTypeExcel9 create files in the Excel 97–2003 format with the .xls extension. These files can still be opened in Excel 2007, but if you explicitly use the output file type with the .xlsx extension, the file will not open in Excel 2007 or later versions of Excel.

• The option acSpreadsheetTypeExcel12 creates a file with the .xlsb extension. This is a binary-coded Excel file, fully compatible with Excel 2007 and above. It is especially useful for exporting large volumes of records, as it produces significantly smaller file sizes.

• The option acSpreadsheetTypeExcel12Xml (note: often written as Excel12Xml) produces a file with the .xlsx extension, also compatible with Excel 2007 and higher.

• When using acSpreadsheetTypeExcel9 or earlier, the exported file is functional. Still, it inherits the older Office 2003 theme, which can appear outdated in style compared to modern versions — as shown in the sample screenshot below.

Right-click to open the Large Image in a New Window.

Normally, after exporting, you may need to open the output file in your current version of Excel, update the formatting (such as font and font size), and then save it again in the latest Excel format. If you simply add the .xlsx extension to the target file name in the TransferSpreadsheet Command, expecting Excel 2007 or higher to recognize it automatically, the file will fail to open in those versions.

However, there is a simple trick to overcome this limitation. Using this method, the exported file will always be saved in the current version of Excel installed on your system — whether it is Excel 2007, 2010, 2013, or newer — regardless of which worksheet type you selected in the TransferSpreadsheet command.

A Simple Solution

1. First, create a blank Excel Workbook in your current version of Excel and save it in the desired target location.

2. Close the Workbook.

3. Run the TransferSpreadsheet command, using the saved Workbook’s file path as the target file parameter.

The exported data will be placed into a new worksheet within that Workbook. Since the Workbook was originally created in your current Excel version, the output will automatically adopt the default Office Theme of that version. This ensures that your exported data looks modern and properly formatted, as shown in the sample image below:

Right-click to open the Large Image in a New Window.

We have created three slightly different functions, each designed to save the output of the TransferSpreadsheet command in a distinct way.

The Export2ExcelA() Function.

The above Function creates a single WorkSheet as output in the Target WorkBook.

Public Function Export2ExcelA(ByVal xlFileLoc As String, ByVal QryORtableName As String) As String
On Error GoTo Export2ExcelA_Err
Dim tblName As String
Dim filePath As String
Dim xlsPath As String

Dim wrkBook As Excel.Workbook

'xlFileLoc = "D:\Blink\tmp2\"
'QryORtblName = "Products"

xlsPath = xlFileLoc & QryORtableName & ".xlsx"
If Len(Dir(xlsPath)) = 0 Then
    Set wrkBook = Excel.Workbooks.Add
        wrkBook.SaveAs xlsPath
        wrkBook.Close
End If
DoCmd.TransferSpreadSheet acExport, acSpreadsheetTypeExcel12Xml, QryORtableName, xlsPath, True

MsgBox "File: " & xlsPath & " Created ", , "Export2ExcelA()()"

Set wrkBook = Nothing
Export2ExcelA = xlsPath

Export2ExcelA_Exit:
Exit Function

Export2ExcelA_Err:
MsgBox Err & " : " & Err.Description, , "Export2ExcelA()"
Export2ExcelA = ""
Resume Export2ExcelA_Exit

End Function

The Export2ExcelA() function requires two parameters:

1. The target path of the output Excel file.

2. The name of the input Table or Query.

In this example, the function exports data from the Products table, creating a worksheet in a workbook saved at the specified location.

At the start of the code, the function checks whether an Excel file with the specified name already exists.

• If the file does not exist, a new workbook is created in the current version of Excel. The workbook is saved using the same name as the input table/query and then closed.

• If the file already exists, the output worksheet is simply added to that workbook.

Now, what happens if we don’t pre-create a workbook in the current Excel version?

• If no file extension is provided (e.g., C:\My Documents\Products) and the SpreadsheetTypeExcel9 option is selected, Access creates a new file with an .xls extension (Products.xls).

• If the .xlsx extension is explicitly specified with SpreadsheetTypeExcel9, the command still creates the file, but it will not open in Excel 2007 or higher versions.

• However, if an existing workbook (e.g., C:\My Documents\myBook.xlsx) is available, the exported data is added as a new worksheet. In this case, the sheet automatically inherits the current version of Excel's default Office theme.

This is why we explicitly create a new workbook in the current Excel version and save it to the target location in advance. Once saved, the workbook must be closed before referencing it in the TransferSpreadsheet command’s output file parameter.

Important: If the target workbook is open when the command executes, an error will occur (“Source file not found”). The workbook must not be in use to avoid this issue.

Finally, the workbook pathname is passed to the TransferSpreadsheet command, and the export is completed successfully.

Export Options:

1. Creating Separate Worksheets in a Single Workbook.
2. Exporting Data into Separate Worksheets or Workbooks

Often, we need to export grouped subsets of data into Excel for reporting or distribution. For example:

• Each region’s sales report is in a separate worksheet of a single workbook.

• Each employee’s performance report is in a separate workbook.

• Or, in our example, products are grouped by category into individual worksheets.

Using the Products table from the Northwind sample database, we’ll demonstrate how to export product data by category.

We can approach this requirement in two ways:

1. Separate Worksheets in a Single Workbook

   • A single workbook (e.g., ProductsByCategory.xlsx).

   • Each worksheet corresponds to a product category.

2. Separate Workbooks per Category

   • Each product category is exported into its own Excel file.

   • E.g., Beverages.xlsx, Condiments.xlsx, etc.

The Export2ExcelB() Function VBA Code:

Public Function Export2ExcelB(ByVal xlFileLoc As String, ByVal QryORtableName As String) As String
'----------------------------------------------------------------
'Creates separate Excel WorkBook for each Group of Records
'based on changing Query criteria.
'Uses Query Name Used for workBook Name
'----------------------------------------------------------------
On Error GoTo Export2ExcelB_Err
Dim strSQL As String
Dim m_min As Integer, m_max As Integer
Dim j As Integer
Dim qryName As String
Dim qryDef As QueryDef
Dim db As Database, rst As Recordset

Dim xlsPath As String
Dim xlsName As String
Dim wrkBook As Excel.Workbook

m_min = CInt(DMin("seq", "QryParam"))
m_max = CInt(DMax("seq", "QryParam"))

    xlsName = QryORtableName & ".xlsx"
    xlsPath = xlFileLoc & xlsName
    
If Len(Dir(xlsPath)) > 0 Then
    Kill xlsPath
End If

    Set wrkBook = Excel.Workbooks.Add
    wrkBook.SaveAs xlsPath
    wrkBook.Close
        
Set db = CurrentDb
For j = m_min To m_max

strSQL = "SELECT Products.[Product Code], QryParam.Category, " & _
"Mid([Product Name],19) AS ProductName, Products.[Standard Cost], " & _
"Products.[List Price], Products.[Quantity Per Unit] " & _
"FROM QryParam INNER JOIN Products ON QryParam.Category = Products.Category " & _
"WHERE (((QryParam.Seq)= " & j & "));"

qryName = "Category_" & Format(j, "000")
On Error Resume Next
Set qryDef = db.CreateQueryDef(qryName)
If Err Then
   Err.Clear
   Set qryDef = db.QueryDefs(qryName)
End If
On Error GoTo 0
    qryDef.SQL = strSQL
    db.QueryDefs.Refresh
    
    DoCmd.TransferSpreadSheet acExport, acSpreadsheetTypeExcel12Xml, qryName, xlsPath, True
   
    db.QueryDefs.Delete qryName
Next
    MsgBox m_max & " Excel WorkSheets Created " & vbCr & "in Folder: " & xlsPath, , "Export2ExcelB()"
    Set wrkBook = Nothing
    Export2ExcelB = xlsPath
    
Export2ExcelB_Exit:
Exit Function

Export2ExcelB_Err:
MsgBox Err & " : " & Err.Description, , "Export2ExcelB()"
Export2ExcelB = ""
Resume Export2ExcelB_Exit
End Function

• Instead of creating a new workbook for each loop iteration,

• You now create the workbook once before the loop starts,

• Then inside the For...Next loop, you export each category’s products as a new worksheet into that same workbook.

That way, the end result is:

• One Excel file (e.g., ProductsByCategory.xlsx).

• Inside it, multiple worksheets, one per product category.

• Each worksheet holds the data filtered by its category.

Here’s a refined explanation of that step:

---

Exporting Multiple Worksheets into a Single Workbook

In the revised code, we first create and save an empty Excel workbook at the target location. This ensures the workbook is in the current Excel version and theme.

Once the workbook is ready, we use a For...Next loop to go through each product category. For every category:

1. A query (or SQL statement) filters the products for that category.

2. The TransferSpreadsheet command exports the filtered dataset into the prepared workbook.

3. Each export creates a new worksheet inside the same workbook.

Because the workbook creation step is outside the loop, only one workbook is created, while multiple worksheets are added as the loop runs.

All Output Worksheets in Different Workbooks.

1. Loop through categories in the Products table.

2. Inside the loop, create a new workbook in the current Excel version.

3. Immediately close the workbook (to release file lock).

4. Call DoCmd.TransferSpreadsheet, passing the new workbook’s path.

5. The export command writes the category’s data as a single worksheet in that workbook.

This way:

• Each category’s data lives in its own Excel file.

• Useful when you need to distribute different files to different departments, customers, or teams.

The Export2ExcelC() Function VBA Code:

Public Function Export2ExcelC(ByVal xlFileLoc As String) As String
'----------------------------------------------------------------
'Creates separate Excel WorkBook for each Group of Records
'based on changing Query criteria.
'Uses Query Name Used for workBook Name
'----------------------------------------------------------------
On Error GoTo Export2ExcelC_Err
Dim strSQL As String
Dim m_min As Integer, m_max As Integer
Dim j As Integer
Dim qryName As String
Dim qryDef As QueryDef
Dim db As Database, rst As Recordset

Dim xlsPath As String
Dim xlsName As String
Dim wrkBook As Excel.Workbook

m_min = CInt(DMin("seq", "QryParam"))
m_max = CInt(DMax("seq", "QryParam"))

Set db = CurrentDb
For j = m_min To m_max

strSQL = "SELECT Products.[Product Code], QryParam.Category, " & _
"Mid([Product Name],19) AS ProductName, Products.[Standard Cost], " & _
"Products.[List Price], Products.[Quantity Per Unit] " & _
"FROM QryParam INNER JOIN Products ON QryParam.Category = Products.Category " & _
"WHERE (((QryParam.Seq)= " & j & "));"

qryName = "Category_" & Format(j, "000")
On Error Resume Next
Set qryDef = db.CreateQueryDef(qryName)
If Err Then
   Err.Clear
   Set qryDef = db.QueryDefs(qryName)
End If
On Error GoTo 0
    qryDef.SQL = strSQL
    db.QueryDefs.Refresh

        xlsName = qryName & ".xlsx"
        xlsPath = xlFileLoc & xlsName
        Set wrkBook = Excel.Workbooks.Add
        wrkBook.SaveAs xlsPath
        wrkBook.Close
    
    DoCmd.TransferSpreadSheet acExport, acSpreadsheetTypeExcel12Xml, qryName, xlsPath, True
   
    db.QueryDefs.Delete qryName
Next
    MsgBox m_max & " Excel Files Created " & vbCr & "in Folder: " & xlFileLoc, , "CreateXLSheets()"
    Set wrkBook = Nothing
    Export2ExcelC = xlFileLoc & qryName & ".xlsx"

Export2ExcelC_Exit:
Exit Function

Export2ExcelC_Err:
MsgBox Err & " : " & Err.Description, , "Export2ExcelC()"
Export2ExcelC = ""
Resume Export2ExcelC_Exit
End Function

A Demo Database with all three Function Codes, with sample Data of the Products table, and Queries is attached for Download.

Download TransferSpreadSheet1.zip

1. Running Sum in MS-Access Query
2. Opening Access Objects from Desktop
3. Diminishing Balance Calc in Query
4. Auto Numbers in Query Column Version-2
5. Word Mail-Merge With MS-Access Table

Read More

MS-Access and Creating Desktop Shortcuts.

Access Desktop Shortcuts.

The CreateShortcut() method of the Windows Script Object can be used to create Desktop shortcuts directly from Microsoft Access. These shortcuts can launch frequently used files—such as Access databases, Excel workbooks, Word documents, text files, and more—right from the Desktop. This concept is familiar, but the question is: how do we implement it within Access?

Earlier, we explored the Popup() method of the Windows Script Object to design a custom Message Box in Access that automatically closes after a specified duration. Unlike the standard Access MsgBox, which always requires a user click to proceed, our new 'MesgBox()' function provides greater flexibility. Hopefully, you’ve already tested it and started applying it in your projects.

The VBA ShortCut() Function Prototype.

The simple VBA Function Code that creates a Desktop Shortcut is provided below for your review. All the required parameters are given as constants in the Function for clarity.

Public Function ShortCut()
Dim objwshShell As Object
Dim objShortcut As Object

Set objwshShell = VBA.CreateObject("WScript.Shell")
Set objShortcut = objwshShell.CreateShortCut("C:\Users\User\Desktop\Hello.txt.lnk")
With objShortcut
    .TargetPath = "C:\Windows\Notepad.exe "
    .Arguments = "D:\Docs\Hello.txt"
    .WorkingDirectory = "D:\Docs"
    .Description = "Opens Hello.txt in Notepad"
    .HotKey = "Ctrl+Alt+9"
    .IconLocation = "C:\Windows\System32\Shell32.dll,130"
    .WindowStyle = 2
    .Save
End With
End Function

You can create a Desktop Shortcut using the VBA code shown earlier, with just a few adjustments to the highlighted parameter values:

1. Replace “User” with your own Windows User Name in the path.

2. Create a simple text file (e.g., Hello.txt) containing any text and save it in one of your folders.

3. Update the Arguments value in the code with the correct file path of your Hello.txt file.

4. Set the Working Directory parameter to the folder where your file is saved.

5. Let the remaining parameter values stay as they are.

As an additional feature, the HotKey combination Ctrl+Alt+9 will be assigned to the shortcut. Pressing this key sequence will run Desktop Shortcut and open the file for editing.

The Desktop Shortcut Icon.

In the IconLocation parameter, notice the number 130 at the end. This number determines the icon that will appear on your Desktop Shortcut.

The numeric range is 0 to 305, each corresponding to a different icon available in the system library.

For example, the value 130 produces the following icon image:

You can also change the Shortcut Icon manually at any time:

1. Right-click on the Desktop Shortcut and select Properties from the menu.

2. On the Shortcut tab, click the Change Icon… button.

3. Browse through the available icons, select the preferred one, and click OK.

4. Finally, click Apply to update the Shortcut with the new icon.

Icon Images List.

When you open the Change Icon window, it displays about 76 columns with 4 icons each.
To identify the numeric index of a particular icon, start counting from the top-left icon, moving left to right. Multiply the column count by 4, and you’ll get the approximate icon number.

For example:

• The first column (4 icons) corresponds to numbers 0–3.

• The second column corresponds to 4–7.

• The third column corresponds to 8–11, and so on.

This manual counting method is the only reliable way I’ve found to determine the correct icon index number.
(See the reference image below for a clearer understanding.)

The DesktopShortcut() Function.

We are now ready to create our VBA function that generates a desktop shortcut.
This function requires at least three parameters, which must be supplied at the time of the call.
Based on these inputs, it will build and save a shortcut on the desktop.

The complete VBA code for the function is given below:

Option Compare Database
Option Explicit


Public Function DesktopShortCut(ByVal strShortCutName As String, _
ByVal strProgramPath As String, _
ByVal strFilePath As String, _
Optional strWorkDirectory As String = "", _
Optional ByVal strHotKey As String = "") As Boolean

On Error GoTo DesktopShortCut_Err
'-----------------------------------------------------------------
'Function: DesktopShortCut()
'Author: a.p.r. pillai
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'Remarks: You may modify the Code, but need to keep these
'Rem lines intact.
'Parameters
'-----------------------------------------------------------------
'1. Shortcut Name: Shows below the Desktop Icon
'2. strProgramPath: e.g.: "C:\Windows\System32\Notepad.exe"
'3. strfilePath: File PathName to Open, e.g. "D:\Docs\Helloworld.txt"
'4. Optional strWorkDirectory: e.g. "D:\Docs"
'5. Optional strHotKey: Quick Launch - e.g. Ctl+Alt+9: 1-9,A-Z
'-----------------------------------------------------------------
Dim objwshShell As Object
Dim objShortcut As Object
Dim strPath As String
Dim strProg As String, a As String, b As String
Dim strTemp As String
Dim DeskPath As String
Dim strmsg As String
Dim badchar As String, Flag As Boolean
Dim j, count As Integer

strPath = Environ("Path")

'Validation Checks
GoSub IsValidName
GoSub ValidateParams

'Find Current User Desktop
strTemp = Mid(strPath, InStr(1, strPath, "C:\Users\"), 25)
DeskPath = "C:\Users\" & Mid(strTemp, 10, InStr(10, strTemp, "\") - 10) & "\Desktop\"
DeskPath = DeskPath & strShortCutName & ".Lnk"

Set objwshShell = VBA.CreateObject("WScript.Shell")
Set objShortcut = objwshShell.CreateShortCut(DeskPath)
With objShortcut
If InStr(1, Trim(strProgramPath), " ") > 0 Then
    .TargetPath = Chr(34) & Trim(strProgramPath) & Chr(34) '="C:\Windows\Notepad.exe"
Else
    .TargetPath = Trim(strProgramPath)
End If
If InStr(1, Trim(strFilePath), " ") > 0 Then
    .Arguments = Chr(32) & Chr(34) & strFilePath & Chr(34) '="D:\Docs\Hello.txt"
Else
    .Arguments = Chr(32) & strFilePath '="D:\Docs\Hello.txt"
End If
'Optional Working Directory
 If Len(strWorkDirectory) > 0 Then
    .WorkingDirectory = strWorkDirectory '="D:\Docs"
 End If
 'Optional Keyboard HotKey
 If Len(Nz(strHotKey, "")) > 0 Then
    .HotKey = "Ctrl+Alt+" & strHotKey '= "Ctrl+Alt+K"
 Else
    .HotKey = ""
 End If
    .IconLocation = "C:\Windows\System32\Shell32.dll,130" '0 - 305
    .WindowStyle = 2
    .Save
End With
DesktopShortCut = True

DesktopShortCut_Exit:
Exit Function

IsValidName:
Flag = True
badchar = "\/:*?" & Chr(34) & "<>|"
count = 0
For j = 1 To Len(strShortCutName)
    If InStr(1, badchar, Mid(strShortCutName, j, 1)) Then
        count = count + 1
    End If
Next
Flag = IIf(count, False, True)
If Not Flag Then
    MsgBox "Shortcut Name: " & strShortCutName & vbCr & vbCr _
    & "Contains Invalid Characters." & vbCr & vbCr _
    & "*** Program Aborted. ***", , "DeskShortCut()"
    
    DesktopShortCut = False
    Exit Function
End If
Return

ValidateParams:
strmsg = ""
'Program Path
If Len(Nz(strProgramPath, "")) > 0 Then
   'Check whether the Program exists in the given path
   If InStr(1, strProgramPath, Dir(strProgramPath)) = 0 Then
     strmsg = "Program Path: " & strProgramPath & " Invalid."
   End If
Else
   strmsg = "Program Path: Not found!"
End If
'File Path
If Len(Nz(strFilePath, "")) > 0 Then
   If InStr(1, strFilePath, Dir(strFilePath)) = 0 Then
     If Len(strmsg) > 0 Then
        strmsg = strmsg & vbCr & "File Path: " & strFilePath & " Invalid."
     Else
        strmsg = "File Path: " & strFilePath & " Invalid."
     End If
   End If
Else
    If Len(strmsg) > 0 Then
        strmsg = strmsg & vbCr & "File Path: Not found!"
    Else
        strmsg = "File Path: Not found!"
    End If
End If
If Len(strmsg) > 0 Then
    MsgBox strmsg, , "DeskShortCut()"
    DesktopShortCut = False
    Exit Function
End If
Return

DesktopShortCut_Err:
MsgBox Err & " : " & Err.Description, , "DesktopShortCut()"
DesktopShortCut = False
Resume DesktopShortCut_Exit
End Function

The DesktopShortcut() function is defined with five parameters, of which the last two—Working Directory and HotKey—are optional.

To ensure reliability, validation checks have been included for the parameter values, along with error-trapping routines. These safeguard the function from unexpected issues and allow it to exit gracefully without crashing. Demo Run of the DesktopShortcut() Function.

The sample Run of the Function from the Immediate Window is given below:

Sample Run-1.

DesktopShortcut "HelloMyDB","C:\Program Files (x86)\Microsoft Office\Office12\MSACCESS.EXE","D:\New Folder\ClassDB.accdb"

Sample Run-2.

DesktopShortcut "HelloMyDoc","C:\Program Files (x86)\Microsoft Office\Office12\WINWORD.EXE","D:\Docs\TelNo2411808.docx","D:\Docs","T"

Download DeskShortcut.zip

The TreeView Control Tutorial Session Links.

1. Microsoft TreeView Control Tutorial
2. Creating an Access Menu with a TreeView Control
3. Assigning Images to TreeView Control
4. Assigning Images to TreeView Control-2
5. TreeView Control Check-Mark Add Delete Nodes
6. TreeView ImageCombo Drop-Down Access Menu
7. Re-arrange TreeView Nodes by Drag and Drop
8. ListView Control with MS-Access TreeView
9. ListView Control Drag Drop Events
10. TreeView Control With Subforms

Read More

Message Box that Closes itself after Specified Time

Message Box That Closes After Specified Time.

The new Message Box in Microsoft Access is quite an interesting addition—one that many of us have wished for for a long time. Imagine a Message Box that closes automatically after displaying some useful information, without requiring any user action to continue code execution. Now, this is possible. We can create a self-closing Message Box that closes after a specified time.

Such a Message Box can even act as a progress indicator during lengthy processes. It can display updates at regular intervals to reassure an impatient user that the program is still running smoothly.

This feature is built on the Popup() method of the Windows Scripting Object.

It accepts all the parameters of the standard Access MsgBox function, except for the [helpfile] and [context] arguments. Additionally, it introduces a new optional parameter—the time value (in seconds). This value specifies how long the Message Box will remain visible on the screen.

• If a time value is supplied (say, 5 seconds), the Message Box will close automatically when the time expires.

• If the user clicks a button before the time runs out, it closes immediately.

• If no time value is provided, this new Message Box behaves exactly like the standard MsgBox and waits for user input.

This makes it especially useful for scenarios where you want to display status updates, progress messages, or alerts about upcoming tasks without interrupting the flow of program execution.

MesgBox() is the New Name.

We have named our new function MesgBox()—notice the extra “e” after the “M” in the original Access MsgBox function. This small change makes it both easy to remember and convenient to type while writing code.

Just like the standard MsgBox, the new MesgBox() function accepts the parameters msgText, buttons, and title. All button options (such as vbOkOnly, vbOkCancel, etc.) and icon options (the vbCritical, vbInformation, etc.) remain valid. You can also combine them to define both the icon and the default selected button, for example:

vbOkCancel + vbCritical + vbDefaultButton2

The difference is that MesgBox() introduces one additional parameter: the time value (in seconds). This parameter specifies how long the message box remains visible before it closes automatically. For example, passing a value of 5 will display the message box for 5 seconds, after which it disappears without waiting for user input.

The sample image of the new MesgBox() with n seconds duration is given below:

The MesgBox automatically closes exactly after the specified number of seconds. The countdown value itself is not displayed in the message box.

If you omit the optional buttons parameter, the default OK button will still appear.

Note: If you want the user to be aware of how long the message will remain visible, you can concatenate the time value into the message text itself.

If the user clicks one of the displayed buttons before the full delay elapses, the message box closes immediately and does not wait for the specified duration. The time parameter is optional—if it is omitted or set to zero, MesgBox behaves just like the standard Access MsgBox, requiring the user to click a button to dismiss it.

Access MsgBox() and New MesgBox() Functions.

The new MesgBox() function requires only a minimal amount of code—just three lines. Before diving into the complete function code, let us first compare the syntax and behavior of the standard Access MsgBox() with the new MesgBox() function.

---

1. Access MsgBox()

• The message box remains visible until the user clicks one of the displayed buttons.

• Code execution is paused until the user responds.

Syntax:

opt = MsgBox(msgTxt, [Buttons] + [Icon] + [DefaultButton], [Title])

Example:

opt = MsgBox("Preparing Report, Please Wait...", vbOkCancel + vbInformation + vbDefaultButton2, "Reports")

---

2. New MesgBox()

• The message box closes automatically after the specified number of seconds, or immediately if the user clicks one of the displayed buttons first.

• Code execution continues after the delay (or instantly upon a button click).

• The delay time is an integer, expressed in seconds, and passed as the second parameter.

Syntax:

opt = MesgBox(msgTxt, [intSeconds], [Buttons] + [Icon] + [DefaultButton], [Title])

Example:

opt = MesgBox("Preparing Report, Please Wait...", 5, vbOkCancel + vbInformation + vbDefaultButton2, "Reports")

---

In the new MesgBox() function:

• msgText is the required first parameter.

• The time delay (in seconds) is the second parameter.

• The Buttons + Icon + DefaultButton combination is the third parameter.

• The Title is the final parameter.

All parameters are optional except the first one, just like the standard Access MsgBox().

When the 'MesgBox' function runs, the message box appears with the Cancel button preselected. If the user accepts the default option, they can simply press Enter to dismiss it or click any other button. Otherwise, the message box will close automatically after 5 seconds.

Omitting the Time Param works exactly like Access MsgBox().

If selecting an option is mandatory, simply omit the Time Value parameter or set it to 0 (zero). In this case, the MesgBox will remain on the screen until the user selects one of the displayed option buttons.

Note: The new MesgBox() function is built on the Microsoft Windows Script Host’s Popup() method. It supports all the parameter values of the Access MsgBox function (except HelpFile and Context parameters), but in a slightly different order. Refer to the Popup() method syntax shown below:

Syntax: 

expression = winShell.Popup(strText, [intSeconds], [strTitle], [intButtons])

Since we have derived our new Function MesgBox from Windows Script Host Object’s Popup() method, we have organized the order of parameters for our function in almost the same order as the Access MsgBox() function. But they will be passed to the Popup() function in the required order.

VBA Code for the New MesgBox() Function

The entire implementation requires only three lines of code, making it both simple and efficient. This custom function leverages the Windows Script Host’s Popup() method to extend the functionality of the standard Access MsgBox.

Public Function MesgBox(ByVal msgText As String, _
    Optional ByVal intSeconds As Integer, _
    Optional ByVal intButtons = vbDefaultButton1, _
    Optional TitleText As String = "WScript") As Integer

Dim winShell As Object

Set winShell = CreateObject("WScript.Shell")

MesgBox = winShell.PopUp(msgText, intSeconds, TitleText, intButtons)

End Function

If the user clicks on one of the displayed buttons, the function returns the corresponding Integer value (same as the Access MsgBox function), which your calling program can capture and evaluate.

You can also experiment with different parameter combinations directly from the Immediate Window (Debug Window) to quickly test its behavior and familiarize yourself with its usage. For example:

? MesgBox("Process Completed Successfully!", 3, vbOKOnly + vbInformation, "Done")

This will display a message box with an OK button and an information icon, which will automatically close after 3 seconds.

? MesgBox("Do you want to continue?", 0, vbYesNo + vbQuestion + vbDefaultButton2, "Confirm")

This version will behave like the standard MsgBox, remaining on screen until the user clicks Yes or No.

The MesgBox() Function Demo Test Subroutine.

We have a demo program to test the new MesgBox() function with different sets of Buttons, Icons, and time values.  When the user clicks on a Button or is allowed to close it, the program checks the returned value and displays a second MesgBox with an appropriate response and disappears after 3 seconds.

Public Sub MesgBox_example()
Dim opt As Integer
Dim Title As String
Dim intSeconds As Integer
Dim optSeconds As Integer
Dim Tip1 As String

Title = "MesgBox_example"
intSeconds = 5
optSeconds = 3
Tip1 = "Click Button before time ends in " & intSeconds & " Seconds" & vbCr & vbCr

'//Enable only one of the four methods given below.

opt = MesgBox("Preparing Monthly Report" & vbCr & "Please wait . . .", intSeconds, vbInformation, "Info")

'opt = MesgBox(Tip1 & "Preparing Monthly Report" & vbCr & "Please wait . . .", intSeconds, vbInformation + vbOKCancel, "Info")

'opt = MesgBox("Cannot Delete Records . . .!", , vbExclamation + vbAbortRetryIgnore + vbDefaultButton3, "Delete")
    
'opt = MesgBox("Database Shutdown . . .?", , vbCritical + vbYesNo + vbDefaultButton2, "Shutdown")

'// Test whether the button clicked or not, if it did then which button/returned value.
Select Case opt
    Case -1 ' No button selected, MesgBox closed automatically after the time specified.
    
        '//In this MesgBox the time parameter is omitted, works like Access MsgBox. Need to click the Button to close.
        MesgBox "No Button Selected." & vbCr & "Click Ok button here to close this MesgBox.", , vbInformation, Title
    
'//The following options work only when the MesgBox button receives the Click.
    Case vbOK '- 1
        MesgBox "Preparing Report" & vbCr & "User's Response Ok", optSeconds, vbInformation, Title
    
    Case vbCancel '- 2
        MesgBox "Not to Prepare Report" & vbCr & "User's Response Cancel", optSeconds, vbInformation, Title
    
    Case vbAbort '- 3
        MesgBox "Record Deletion Aborted.", optSeconds, vbExclamation, Title
    
    Case vbRetry '- 4
        MesgBox "Retrying Record Deletion.", optSeconds, vbExclamation, Title
    
    Case vbIgnore '- 5
        MesgBox "Record Deletion Process Ignored.", optSeconds, vbExclamation, Title
    
    Case vbYes '-  6
        MesgBox "Yes, Shutdown Approved.", optSeconds, vbCritical, Title
        'DoCmd.CloseDatabase
    Case vbNo '- 7
        MesgBox "Database Shutdown Denied.", optSeconds, vbCritical, Title
End Select


End Sub

Save the Code in the Access Global Module.

Copy both the MesgBox() and MesgBox_Example() procedures into a Global Module and save the code. Then, compile the project to ensure everything is in order.

In the MesgBox_Example() routine, the first MesgBox() call is already enabled. Place the cursor anywhere inside the code and press F5 to run it. This will display a message box with only the OK button. After 5 seconds, the MesgBox will close automatically, and the returned value (Opt variable) will be –1.

The returned value is then evaluated in the subsequent Select Case block to display an appropriate follow-up message. In this case, the time parameter is omitted, so the MesgBox behaves like the standard Access MsgBox and requires the user to click a button to close it.

Run the same option once again. This time, click the OK button before the timeout expires. The button click will return the value 1 to the Opt variable, and the follow-up message will be displayed for 3 seconds.

To experiment further, comment out the tested line and enable the next one by removing the comment symbol. You can repeat this method with different button combinations and icons to see how the MesgBox responds in each scenario.

A Critical Message Box:

The MesgBox() function is called directly from the Debug Window (Immediate Window) with the function parameters as given below:

msgTxt = "Database Shutdown . . .?"
MesgBox msgTxt,,vbYesNo+vbCritical+vbDefaultButton2,"Shutdown"

Points to Note.

1. Take Note of these Side effects:

   Since the MesgBox() function is based on the Windows Script Host Popup() method, it behaves differently from the standard Access MsgBox:

   • Even if the Access application window is minimized, the MesgBox will still appear on the Windows Desktop.

   • If the time parameter is omitted, the user must click a button (like in Access MsgBox) to dismiss the MesgBox.

   • If the user clicks anywhere outside the MesgBox, it will move behind the Access application window and remain visible on the Windows Desktop.

   • While the 'MesgBox()' is active, your VBA program execution is paused, waiting for the user’s response. If you try to close the database during this time, the attempt will be ignored without warning. To forcefully close Access, you must use Exit Access from the Office Button.

   We are already familiar with Microsoft Windows Common Controls such as TreeView, ListView, ImageList, and others, which depend on the MSCOMCTL.OCX library. The MesgBox() function, however, relies on the Windows Script Host Object Model (wshom.ocx) and does not require manually adding a reference in the VBA editor.

   If you encounter any issues using this feature, check the official Microsoft Support pages for troubleshooting guidance.

   ---

   MesgBox() Function with Error Handling

   Here is the improved version of the MesgBox() function with error-trap lines included:

Public Function MesgBox(ByVal msgText As String, _
    Optional ByVal TimeInSeconds As Integer, _
    Optional ByVal intButtons = vbDefaultButton1, _
    Optional TitleText As String = "WScript") As Integer

On Error GoTo MesgBox_Err
Dim winShell As Object

Set winShell = CreateObject("WScript.Shell")

MesgBox = winShell.PopUp(msgText, TimeInSeconds, TitleText, intButtons)

MesgBox_Exit:
Exit Function

MesgBox_Err:
winShell.PopUp Err & " : " & Err.Description, 0, "MesgBox()", vbCritical
Resume MesgBox_Exit
End Function

Now that we have compared the advantages and disadvantages of both MsgBox and MesgBox(), the key takeaway is: use them sparingly and wisely.

If you plan to distribute your application with the new 'MesgBox()' function, always test it on the target system to confirm that it works as expected in the new environment.

1. Microsoft TreeView Control Tutorial
2. Creating an Access Menu with a TreeView Control
3. Assigning Images to TreeView Control
4. Assigning Images to TreeView Control-2
5. TreeView Control Check-Mark Add Delete Nodes
6. TreeView ImageCombo Drop-Down Access Menu
7. Re-arrange TreeView Nodes by Drag and Drop
8. ListView Control with MS-Access TreeView
9. ListView Control Drag Drop Events
10. TreeView Control With Subforms

Read More

TreeView Control with Subforms

TreeView Control and Subforms.

In this session of the TreeView Control Tutorial, we will work with a main form (frmTreeViewtab) that hosts a TreeView control and two Subforms. The ImageList control, preloaded with images imported from an earlier demo project, is also included.

We will continue using the same tables from our previous projects: lvCategory and lvProducts.

• The lvCategory table provides the category information. Its primary key field (CID) and description field (Category) are used as the Key and Text parameters of the TreeView node’s Add() method.

• The lvProducts table stores detailed product information, including product code, description, stock quantity, and list price. It also contains a ParentID field that links each product to a category by storing the corresponding CID. This establishes a master–child relationship between the two tables.

On the form, product records are managed through two subforms placed on a Tab Control:

1. First Page (Data View Subform): Displays all products that belong to the category currently selected in the TreeView. Users can view records here and select one for editing.

2. Second Page (Edit Subform): Provides an editable view of the record selected on the first page. Key fields (highlighted in gray) are locked to prevent modification.

This setup allows users to browse products by category via the TreeView, view product details in the first subform, and switch to the second subform to edit the selected record while maintaining data integrity.

TreeView with Subforms Design View.

The Design View of the form frmTreeViewTab is given below:

Right-click to open the Large Image in a New Window.

 

On the main form, the first two unbound text boxes are updated whenever the user selects a Category item from the TreeView control.

The third unbound text box (p_ID) is used to track the current product. By default, it is initialized with the PID value of the first product record. If the user selects a different record in the first subform, the text box is updated with that record’s PID instead.

This ensures that the record currently highlighted in the data view subform is made available in the edit subform, allowing the user to make modifications seamlessly.

Links to Earlier Tutorial Sessions.

The earlier Tutorial Session Links are given below for ready reference:

1. Microsoft TreeView Control Tutorial
2. Creating an Access Menu with a TreeView Control
3. Assigning Images to TreeView Control
4. Assigning Images to TreeView Control-2
5. TreeView Control Check-Mark Add Delete Nodes
6. TreeView ImageCombo Drop-Down Access Menu
7. Re-arrange TreeView Nodes by Drag and Drop
8. ListView Control with MS-Access TreeView
9. ListView Control Drag Drop Events

 The CatID unbound text box on the main form is assigned to the [Link Master Fields] property of the first subform.

Similarly, the p_ID unbound text box (the product code) is linked in the [Link Master Fields] property of the second subform on the Edit tab page.

The value of p_ID is automatically updated whenever the first subform is refreshed or when the user selects a specific record. This ensures that the corresponding product is always available for editing on the second subform.

Normal View of the Screen.

The normal view of the frmTreeViewTab form is given below:

Right-click to open the Large Image in a New Window.

On the second subform, the key fields of the product record are displayed in gray text and are locked to prevent modifications.

The form frmTreeViewTab Class Module VBA Code:

Option Compare Database
Option Explicit

Dim tv As MSComctlLib.TreeView
Dim imgList As MSComctlLib.ImageList
Const Prfx As String = "X"

Private Sub Form_Load()
Dim db As DAO.Database
Dim tbldef As TableDef

'Initialize TreeView Nodes
    Set tv = Me.TreeView0.Object
    tv.Nodes.Clear
'Initialixe ImageList Object
    Set imgList = Me.ImageList3.Object
    
'Modify TreeView Font Properties
With tv
    .Font.Size = 9
    .Font.Name = "Verdana"
    .ImageList = imgList 'assign preloaded imagelist control
 End With
    
   LoadTreeView 'Create TreeView Nodes

End Sub

Private Sub LoadTreeView()
    Dim Nod As MSComctlLib.Node
    Dim strCategory As String
    Dim strCatKey As String
    Dim strProduct As String
    Dim strPKey As String
    Dim strBelongsTo As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    'Initialize treeview nodes
     tv.Nodes.Clear
    
    strSQL = "SELECT lvCategory.CID, lvCategory.Category, "
    strSQL = strSQL & "lvcategory.BelongsTo FROM lvCategory ORDER BY lvCategory.CID;"
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

    ' Populate all Records as Rootlevel Nodes
    Do While Not rst.BOF And Not rst.EOF
        If rst.AbsolutePosition = 1 Then
           Me![CatID] = rst![CID]
        End If
            strCatKey = Prfx & CStr(rst!CID)
            strCategory = rst!Category
            
            Set Nod = tv.Nodes.Add(, , strCatKey, strCategory, 1, 2)
            Nod.Tag = rst!CID
        rst.MoveNext
    Loop
    
    'In the second pass of the the same set of records
    'Move Child Nodes under their Parent Nodes
    rst.MoveFirst
    Do While Not rst.BOF And Not rst.EOF
        strBelongsTo = Nz(rst!BelongsTo, "")
        If Len(strBelongsTo) > 0 Then
            strCatKey = Prfx & CStr(rst!CID)
            strBelongsTo = Prfx & strBelongsTo
            strCategory = rst!Category
            
            Set tv.Nodes.Item(strCatKey).Parent = tv.Nodes.Item(strBelongsTo)
        End If
        rst.MoveNext
    Loop
    rst.Close
    

    TreeView0_NodeClick tv.Nodes.Item(1)
    
End Sub

Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim Cat_ID As String

'Initialize hidden unbound textbox 'Link Master Field' values
Cat_ID = Node.Tag
Me!CatID = Cat_ID
Me![xCategory] = Node.Text

End Sub

Private Sub cmdExit_Click()
    DoCmd.Close
End Sub

Since the usage and functionality of the TreeView and ImageList controls were thoroughly explained in earlier sessions, only a few of those previously introduced VBA subroutines are included in this form’s module.

So far, we have designed several screens using TreeView, ListView, ImageList, and ImageCombo controls in MS Access. I hope you will find these examples a valuable reference for designing the interface of your own projects.

MS Office Version Issues for TreeView Control.

If you encounter any issues running the demo database in your version of Microsoft Access, you may refer to the following link for corrective steps that could help resolve the issue.

In earlier versions, these controls did not function properly on 64-bit systems. However, in September 2017, Microsoft released an updated version of the MSCOMCTL.OCX library. For your reference, an extract from Microsoft’s documentation is provided below.

Click on the document image above to view the full text of the 2017 Update: 1707 release. For additional guidance, you may also refer to the following link, which provides some useful troubleshooting tips.

• SOLVED MSCOMCTL.OCX DOWNLOAD, REGISTER for 64-BIT WINDOWS

By leveraging the TreeView control and related objects, we can design user interfaces that are both more visually appealing and more efficient for our future projects.

Download the Demo Database.

Download TreeViewSubforms.zip

Read More