Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Database Backup Restore From Desktop

Introduction.

We had an extensive discussion on the following database backup topics earlier:

1. Database Daily Backup.

2. Repairing Compacting Databases from a dedicated database with VBA Programs.

Besides the above, we already have an option in Access to Repair and Compact the current Database, while closing it.

The first topic 'Database Daily Backup' procedure runs from within the database when it is open for the first time in a day. Subsequent opening/closing events will not repeat the backup process on the same day. 

The second procedure is run from within a dedicated database to Compact and Repair several databases given in a list, one by one.

The new method that we are going to try out is VB Script saved in text file and run from desktop Shortcuts, for backup and Restore operations, manually. It is not an automated procedure, as far as invoking the program is concerned.  You can run the code manually by double-clicking on the shortcut, whenever you think it is time to take a backup of your database.  

Restore the corrupted database from its earlier backup(s), using another desktop shortcut, when you find the current database lost its consistency. 

These programs are in VB Script, a light version of Microsoft Visual Basic, mostly used on web pages, and you will not have any difficulty in understanding the Code if you have some familiarity with the VBA language.

  1. Create a Notepad text file on the Desktop.

  2. Rename the desktop shortcut name to CreateBackup.vbs. The shortcut name extension vbs indicates that it is a Visual Basic Script file.

  3. Right-click on the shortcut and select Edit from the displayed menu.

  4. When the text file opens in Notepad, Copy and Paste the code given below into the File.

  5. Select the Save option from the File Menu to save the Code, and select Exit to close the File.

The CreateBackup VB Script:

Call CreateBackup()

Sub CreateBackup()
'======================================================
'Desk-top Shortcut Name: CreateBackup.vbs
'Language              : VBScript - Creates File Backup
'Remark                : Run from Desktop Shortcut
'                      : Edit Backup Path
'======================================================
Dim s, t, p, a
Dim objFSO

Set objFSO = CreateObject("Scripting.FileSystemObject")

' Backup is taken in the File Folder itself
' with Day and Month added (-dd-mm) to the File Name.
' Example: D:\AccTest\Testdb-16-07.accdb

p = "D:\AccTest\NorthWind.accdb" 'Edit

s = InputBox("File PathName:","CreateBackup()" , p)

If objFSO.FileExists(s) Then
  'File Name changes appending with -dd-mm (-day-month) values.
    a = Left(Now(), 5)
    t = Left(s, InStrRev(s, ".") - 1) & "-" & a & Mid(s, InStrRev(s, "."))

'Create the File backup
    a = objFSO.CopyFile(s, t, True)

    MsgBox "Backup Successful!" & vbCr & vbCr & "Source: " & s & vbCr & "Backup: " & t,vbInformation,"CreateBackup()"
Else
    MsgBox "Source PathName: " & s & vbCr & "Not Found, Program Aborted!",vbCritical,"CreateBackup()"
End If
Set objFSO = Nothing
End Sub

Database CreateBackup() Code Line-By-Line.

The Backup procedure is very simple, let us go through it.  The first statement Call CreateBackup() runs the Subroutine given below. This approach is necessary because when you right-click on the desktop shortcut and select Open or double-click on it, the code written within a Subroutine or Function will be ignored by the script running procedure. An explicit call of the subroutine is required to start executing the VBScript enveloped within a subroutine structure.

Note: You may write the Script in a Notepad Text File with vbs extension, without enveloping the code within the Subroutine/Function structure. It will execute the VBScript straight through what is written in the Text File with the .vbs file extension when you double-click on the desktop shortcut.  

The Function classification is acceptable only if you omit the return value part after the function name like:

Function CreateBackup() as Integer - not acceptable

Function CreateBackup() - acceptable

 The required variables are declared at the beginning of the subroutine. The Variable Type declarations, like db_Pathname As String, are not allowed in VBScript. It can be compared with VBA Variant Data type.  The variable Type is changed based on the first value type assigned to it. If you are using VBScript within a VBA Subroutine/Function you can declare variables as we do normally in VBA.

Set objFSO = CreateObject("Scripting.FileSystemObject")

The above statement creates a File System Object and assigns it to the objFSO Variable. 

The variable p is initialized with a pathname that will appear as the default database pathname in the Inputbox() function prompt. If you are using this Desktop Shortcut to backup the same database every time, then set this variable to the required file pathname otherwise change the database pathname appearing in the InputBox() function TextBox to use the required database pathname for backup.

If objFSO.FileExists(s) Then

The FileExists() method of the objFSO script object checks whether the source file pathname given as the parameter exists or not. If it is found, then the backup file name is prepared by adding the current Day  and Month values, in -dd-mm format (example: D:\AccTest\NorthWind-16-07.accdb) by the following statements:

    a = Left(Now(), 5)
    t = Left(s, InStrRev(s, ".") - 1) & "-" & a & Mid(s, InStrRev(s, "."))

The following statement calls the CopyFile() method of the objFSO object and creates a copy of the Source File with the new name in the same folder:

a = objFSO.CopyFile(s, t, True)

The first parameter, s is the source file pathname, and the second parameter t is the target file pathname. The last parameter True indicates that if the target file exists, then overwrite it without warning. After the copy operation, a message is displayed announcing the successful completion of the backup operation. 

If the source pathname is not found then the program will be aborted with a critical message.

Note: If you take more than one backup of the same file on a particular day in the same folder, then the earlier backup file will be overwritten without warning because the third parameter setting is True.

Database RestoreFile() VBScript Code. 

Follow the same procedure for creating the VB Script file with the Notepad explained above, and create the RestoreFile.vbs Desktop Shortcut with the following VBScript code copying and pasting them into it.
Call RestoreFile()

Sub RestoreFile()
'================================================
'Language              : VBScript
'Desk-top Shortcut Name: RestoreFile.vbs 
'Remarks               : Restore File from Backup
'================================================
Dim db_Current
Dim db_Save
Dim db_Backup
Dim f_bkSource,f_Current, f_Save
Dim objFSO

'The following three demo lines can be replaced
'in the InputBox statement, to Input
'File Pathnames directly.
db_Current = "D:\AccTest\NorthWind.Accdb"      'The file needs replacement
db_Save = "D:\AccTest\NorthWind-Save.Accdb"    'save the [dbReplace] file with a new name
db_Backup = "D:\AccTest\NorthWind-17-07.Accdb" 'Restore from this Backup File

f_bkSource = InputBox("Backup File PathName:","Restore()",db_Backup)
f_Current = InputBox("Restore File PathName:","Restore()",db_Current)
f_save = InputBox("Save Current before replace:","Restore()",db_save)

'Create File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(f_Current) then
' Save the existing/corrupt file with a new name
' Third Param:TRUE, overwrites the existing file without warning.
    a = objFSO.CopyFile(f_Current, f_save, True)
    a = objFSO.DeleteFile(f_current) 'delete original file after copying with a new name
End If

'Check the Backup file Exists or Not
If objFSO.FileExists(f_bksource) then 
	a = objFSO.CopyFile(f_bkSource, f_Current, True) 'Restore the original file from backup.

  If objFSO.FileExists(f_Current) then 'check restore operation was successful or not.
   	MsgBox "File: " & f_Current & vbcrlf & " Successfully Restored.",vbInformation,"RestoreFile()"
Else MsgBox "Oops! Something went wrong.",vbCritical,"RestoreFile()"
End If Else MsgBox "Backup File: " & f_bksource & vbcrlf & " Not Found!",vbCritical,"RestoreFile()"
End If Set objFSO = Nothing End Sub

RestoreFile() Code Line-by-Line.

The first three variables: db_Current, db_Save, and db_Backup will be initialized with the current database pathname (the file that needs replacement from backup), and before replacing it is saved with the modified pathname xxxxxx-save.accdb, and the backup file pathname respectively.

Next three variables: f_bkSource, f_Current, and f_Save will accept the pathnames given by the user for the above values.  objFSO will be initialized with the File System Object. 

db_Current = "D:\AccTest\NorthWind.Accdb"      ' db_Current needs replacement
db_Save = "D:\AccTest\NorthWind-Save.Accdb"    'save the [db_current] file with a new name
db_Backup = "D:\AccTest\NorthWind-17-07.Accdb" 'Restore file from this Backup database 

The first three variables are initialized with the sample database pathnames as above which will appear in the InputBox() function Text Box as default Pathnames.  If these are the files involved in the RestoreFile() program's input values, then you can press Enter Key to accept them as it is, or modify them as per your requirements.

Set objFSO = CreateObject("Scripting.FileSystemObject") 

After the InputBox functions are run the above statement creates the File System Object and assigns it to the objFSO object.

If objFSO.FileExists(f_Current) then
' Save the existing/corrupt file with a new name
' Third Param:TRUE, overwrites the existing file without warning.
	a = objFSO.CopyFile(f_Current, f_save, True)
        a = objFSO.DeleteFile(f_current) 'delete the original after copying with a new name
        
 End If

 The above code segment checks whether the current file name given for replacement exists or not. If found, then it is copied with the modified name for safe keep.  If the file doesn't exist, then bypass the above code segment. There is no status value returned in variable a, idicating the copy operation's success or failure. This Syntax simply allows us to use parenthesis around the function parameter values. The following statement is equally valid.

objFSO.CopyFile f_Current, f_save, True

Next, the corrupt file is deleted using the DeleteFile() method of the objFSO Object.

The following code segment runs a validation check with the FileExists() method on the Backup database file, if found, then makes a copy of it with the original database name, overwriting it, if already exists, but we have deleted it in the earlier operation:   

'Check the Backup file Exists or Not
If objFSO.FileExists(f_bksource) then 
	a = objFSO.CopyFile(f_bkSource, f_Current, True) 'Restore the original file from backup.

  If objFSO.FileExists(f_Current) then 'check restore operation was successful or not.
   	MsgBox "File: " & f_Current & vbcrlf & " Successfully Restored.",vbInformation,"RestoreFile()"
  Else
   	MsgBox "Oops! Something went wrong.",vbCritical,"RestoreFile()"
End If Else MsgBox "Backup File: " & f_bksource & vbcrlf & " Not Found!",vbCritical,"RestoreFile()"
End If

After the restore operation, it runs a check for the original file pathname, to ensure that the restore operation was successful and displays a message on that effect. If something went wrong with the copy operation, then a critical message is displayed.  

If the backup filename itself is not found, then a critical message is displayed and the objFSO object is released from memory before the program ends. 

Tip: You can backup and restore any type of file besides databases.


  1. Get Disk Free Space - Windows API
  2. Access And Windows API ShowWindow
  3. ListView Control Drag Drop Sort Events.
Share:

Get Disk Free Space Windows API

 Introduction.

Last week we learned the usage of ShowWindow and PostMessage Windows APIs.  We could easily change the window state of Forms and Reports using their built-in windows-handle hwnd Property values. We have not used the FindWindow API to retrieve the Form's windows identification number.

Here, we will learn another simple and useful Windows API Function, the GetDiskFreeSpace Function. The Windows API declaration is as follows:

Private Declare PtrSafe Function GetDiskFreeSpace Lib "kernel32" Alias "GetDiskFreeSpaceA" _
    (ByVal lpRootPathName As String, _
    lpSectorsPerCluster As Long, _
    lpBytesPerSector As Long, _
    lpNumberOfFreeClusters As Long, _
    lpTotalNumberOfClusters As Long) As Long

The GetDiskFreeSpace API needs five parameters:

  1. The first parameter is the RootPathName of the Disk Drive like C:\ or C:.

    The next four Parameter Variables must be declared as Long Integer Type and pass their references as parameters while calling the GetDiskFreeSpace API.

    If the API call was successful and was able to retrieve the disk information then:

  2. The second parameter Variable lpSectorPerCluster will contain the count of Sectors in a single Cluster on the Disk. 

    The disk capacity is measured in bytes, the number of bytes in a Sector, the number of Sectors in a Cluster, free space, and total space on the disk is in Clusters.

    The GetDiskFreeSpace API returns free space on the disk in Clusters only.

  3. The lpBytesPerSector variable will have the quantity of data saved in a Disk-Sector, normally about 512 Bytes.

  4. The 4th parameter lpNumberOfFreeClusters will have the free space left on the disk, returned in Clusters.

  5. The last parameter lpTotalNumberOfClusters has the total capacity (the partition capacity) information in Clusters. 

The DiskFreeSpace Wrapper Function.

We have created a wrapper function DiskFreeSpace to call the Windows API GetDiskFreeSpace Function from within, to retrieve and calculate the free space on the disk. The disk or computer memory capacity-related quantitative terms that we normally use are Gigabytes, Megabytes, or KiloBytes for communicating. So, we need to convert the cluster values into these measurements.

  1. Create a new Standard Module in your Database.

  2. Copy and Paste the above Windows API Code into the global declaration area of the Module.

    The DiskFreeSpace Function Code.

  3. Next, Copy and Paste the following User function code below the Windows API code in the same Module:

    Private SectorPerCluster As Long
    Private BytesPerSector As Long
    Private FreeClusters As Long
    Private TotalClusters As Long
    
    Private gbf As Double
    Private mbf As Double
    Private kbf As Double
    
    Private tb As Double
    Private gb As Double
    Private mb As Double
    Private kb As Double
    Private fmt As String
    Private msg As String, msg2 As String
    
    Public Function DiskFreeSpace(ByVal strPath As String) As String
    Dim Rtn As Long
    Dim ClusterBytes As Double
    
    On Error GoTo DiskFreeSpace_Err
    
    Rtn = GetDiskFreeSpace(strPath, SectorPerCluster, BytesPerSector, FreeClusters, TotalClusters)
    
    fmt = "#,##0"
    msg = ""
    msg2 = ""
    
    If Rtn Then
    
    'Bytes in a Cluster = Sectors * BytesPerSector
        ClusterBytes = SectorPerCluster * BytesPerSector 'Bytes per cluster
        
    'msg2 = "        Disk Drive: " & UCase(strPath) & vbCrLf & _
            "Sector Per Cluster: " & SectorPerCluster & vbCrLf & _
            "  Bytes Per Sector: " & BytesPerSector & vbCrLf & _
            "     Free Clusters: " & FreeClusters & vbCrLf & _
            "    Total Clusters: " & TotalClusters
            
    'Debug.Print msg2
    
        gbf = ClusterBytes / (1024# ^ 3) 'GB Factor per Cluster
        mbf = ClusterBytes / (1024# ^ 2) 'MB Factor     "
        kbf = ClusterBytes / (1024#)     'KB Factor     "
    
    'free Space
        tb = Int(TotalClusters * gbf) ' Total Space in Gigabytes
        gb = Int(FreeClusters * gbf)  ' Free Space  in     "
        mb = Int(FreeClusters * mbf)  '       "     in Megabytes
        kb = Int(FreeClusters * kbf)  '       "     in Kilobytes
    msg = " Disk Drive: " & UCase(strPath) & vbCrLf & _ "Total Space ( GB ): " & Format(tb, fmt) & vbCrLf & _ " Free Space ( GB ): " & Format(gb, fmt) & vbCrLf & _ " Free Space ( MB ): " & Format(mb, fmt) & vbCrLf & _ " Free Space ( KB ): " & Format(kb, fmt) Else MsgBox "Disk Drive PathName: " & UCase(strPath) & vbCrLf & _ "NOT FOUND!", vbOKOnly + vbCritical, "DiskFreeSpace()" DiskFreeSpace = "" Exit Function End If DiskFreeSpace = msg DiskFreeSpace_Exit: Exit Function DiskFreeSpace_Err: MsgBox Err & " : " & Err.Description, , "DiskFreeSpace()" DiskFreeSpace = "" Resume DiskFreeSpace_Exit End Function

Our new function needs only one parameter the disk's Root Pathname.  If you look at the Windows API GetDiskFreeSpace Parameter declarations the first Parameter is declared with ByVal qualification, other parameters are not qualified as such because they are declared as ByRef parameters by default.

The first parameter can be passed as a value directly, like "C:\" or a variable initialized with the disk Root Pathname.  Other parameters must be declared as Long Integer Types first, and pass their References so that Windows API can save the returned information directly into those Variables.

All Variables except Rtn and ClusterBytes I have declared at the global declaration area so that our own Function DiskFreeSpace Code looks better and less crowded.

The user-defined function DiskFreeSpace needs only one parameter: the Root Pathname of the disk, like "C:\".  The GetDiskFreeSpace Windows API is called with all five parameters, from within our function.

The Disk Space Value Conversion Calculations.

If everything went on well then the variable Rtn will have the Value 1 otherwise 0.

So testing the variable Rtn is necessary to proceed with converting the disk information into Gigabytes, Megabytes, or Kilobytes.

When the API is run successfully the returned values are in Bytes per Sector, Sectors per Cluster, Disk Free Space in Clusters, and Disk's total capacity in Clusters.  

The disk capacity is logically grouped into Sectors of 512 Bytes (characters) and a group of 8 Sectors or more known as a Cluster. This is the amount of data the computer can read/write at one attempt. This may change depending on the type of disk drives like Hard Disk, SSD, Zip-Drive, or MicroSD Drive in use and their formatting type: NTFS, FAT32, etc. 

The Bytes per Sector found 512 on most of the disk types but the Sectors per cluster may change, like 8, 16, or 32.  You may test this function on your own machine with different Disk Types to find out.

We are familiar with the terms like Gigabytes (GB), Megabytes (MB), or Kilobytes (KB) the terms which we normally use to communicate the disk's capacity.  So we will convert the Clusters into bytes and then convert them into GB, MB, or KB.

[Total Bytes per Cluster] = [Bytes Per Sector] * [Sectors Per Cluster] = 512 * 8 = 4096 bytes.

GBF = [Total Bytes per Cluster] / (1024#^3) : Gigabytes Factor.

MBF = [Total Bytes per Cluster] / (1024#^2) : Megabytes Factor.

KBF = [Total Bytes per Cluster] / (1024#) : KiloBytes Factor.

With these values, we can easily convert the Free Cluster Values into any of the above three Values, like:

GB = [Free Space Clusters] * GBF will give the Disk Free Space value in Gigabytes.

Our Function DiskFreeSpace() returns a String Value containing information formatted in such a way that the returned value can be displayed in a MsgBox, printed in Debug Window, or displayed in a Label Control on Form that is wide enough to display 5 lines of text.

The sample output Image in Debug Window and in MsgBox side-by-side is given below for information:

The DiskFreeSpace() function can be run from the Debug Window directly, from within some other function, or from an Event Procedure on Form.

The Demo Database is attached for Download and ready to Run.



  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation
Share:

Access And Windows API ShowWindow

Introduction

This is all about controlling Form/Report Views and changing them from one state to another, like Hide, Minimized, Maximized, and Normal with the use of Windows APIs. I was aware of  Windows APIs and came across their usage in MS-Access long back, it was a Form that Fades-in and after a few seconds it disappears slowly in the Application Window. 

This VBA Code is still with me. I could not go after Windows APIs till now and thought of doing something about it.

Recently, I came across some discussions about finding Access Form's Window handle value and the limitations of using FindWindow API Function.  The FindWindow API finds only the open Forms with its Popup or Modal Property value set to Yes.  Once the Form's window identification number is obtained we can change the window State as mentioned above with the use of ShowWindow API Function. 

The Windows API Functions.

We will learn the usage of the following Windows API Functions in our trial runs to know how they work in MS-Access:

Public Const SW_HIDE As Long = &H0
Public Const SW_NORMAL As Long = &H1
Public Const SW_MINIMIZED As Long = &H2
Public Const SW_MAXIMIZED As Long = &H3
Public Const SW_CLOSE As Long = &H10 '16

Public Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long


Public Declare PtrSafe Function ShowWindow Lib "user32.dll" ( _
     ByVal hwnd As Long, _
     ByVal nCmdShow As Long) As Long
    
Public Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" _
    (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
    ByVal lParam As Long) As Long

Experiments in VBA Debug Window.

Open one of your Access Databases, copy and paste the above Code into a Standard Module at the Global Declaration area and save the Module. If your Computer is a 32 Bit System then the above line may run into Errors. In that case, remove the word PtrSafe from the declaration lines and save the Module.

  1. Open one of your existing Forms in Normal View.

  2. Note down the Title of the open Form.

  3. Come back to the VBA Window (Alt+F11).

  4. Select Immediate Window (Ctrl+G) option from the View Menu.

  5. Now, type the following command (to run the FindWindow() API Function) directly on the Immediate Window to obtain the Windows Identification Number of the open Form.

 
? FindWindow(vbNullString,"Form1")
0

The FindWindow API takes two Parameters. The first string parameter is the Class Name of the Application. MS-Access Application class name is OMain.

The second string parameter is the Window's  (Form's) Title Text.

Even though both parameters are mandatory only one of these parameter values is given and the other parameter is passed as NullString. In the above example, we have passed the vbNullString constant as the first parameter, and the second parameter is the Form's Title. The FindWindow() function will not find the Form's Title Text if it is not a Popup Form and returns 0. 

  1. Now, open Form1 in Design View. 

  2. Open the Form's Property Sheet and change the Popup property value to Yes.

  3. Save the Form and Open it in Normal View again.

  4. Try the above FindWindow command in the Debug Window one more time.

? FindWindow(vbNullString,"Form1")

Result: 197654

If the above command was successful and returned a number, then you have succeeded in getting the Windows handle number similar to the above example.

If you were able to get the windows identification number, then we can call the ShowWindow () API Function from the Debug Window as follows to change its Form View to different modes:

x = ShowWindow(197654, SW_MINIMIZED)

Form1 is minimized and stationed at the bottom of the Application Window.

Application, Forms/Reports Windows Handl Property hwnd.

The MS-Access Application Object has a property that holds the Access Application Window's handle value.  From within the Access Application, we can read the Application Window's Handle value as shown below:

hwnd = Application.hwndAccessApp 
OR
hwnd = FindWindow("OMain",vbNullString)

We cannot make all Forms and Reports into Popups for the sake of the ShowWindow API or for any other.

The Built-in .hwnd Property

Fortunately, we don't have to depend on the Popup Property or FindWindow API to get Form or Report Windows identification number.  

All Forms and Reports have the expression.hwnd Property that is set with the Windows handle Number when it is open.  All you have to do is to read this value directly and use it in the ShowWindow API function to change its view mode to one of the constant values given in the Code at the top of this page, except the last one: SW_CLOSE.

  1. Open one of your Forms (say Employees) in Normal View.

  2. Go to the VBA Immediate Window (Alt+F11).

  3. Type the following command to display the Employees Form's windows handle value:

hndl = Forms("Employees").hwnd 
? hndl
Result: 1508749

If you want to change the Employees form's current NORMAL View mode to MINIMIZED then call the ShowWindow API Function, with the form's window number, like the following example on the Debug Window:

whndl = Forms("Employees").hwnd
rtn = ShowWindow(whndl, SW_MINIMIZED)

OR

rtn = ShowWindow(Forms("Employees").hwnd,2)

You may try out other options 0-3, hide, normal, minimize, and maximize options on the open Form.  

Open a Report in Print Preview Mode and try the above example as you did in the Employees Form.

To try out the SW_CLOSE option we need another Windows API the PostMessage() Function.

rtn = PostMessage(whndl, SW_CLOSE,  0, 0)

The PostMessage() Function accepts four parameters. The first parameter is the windows handle and the second parameter accepts the action to perform.  The third and fourth parameters are not optional and zero values are passed. 

The Sample Demo Database.

There is a Demo database with a few sample Forms and Reports attached.  You may download and try it out.  The Image of the Main Form is given below:

Win API Demo

When the above Form is open the first two ListBoxes will be empty.  Click on the 'Open Forms' Command Button to open sample forms and create their list in the ListBox. The second ListBox is for sample Reports.  Only one of the first two ListBoxes will be enabled at one time.

Click on the Command Button on the top with the Caption: Click to Enable a disabled ListBoxSelect an Item (Form or Report) from the List will enable the Windows State options.  Click on the Window State option that you would like to set on the selected Form/Report.

If you select another Form or Report to set the selected window's state, then you need to click again on the already selected windows state option.

The Close All Command Button closes all open Forms/Reports including the above Main Form.

  1. ActiveX ListView Control Tutorial-01.
  2. ListView Control Tutorial-02.
  3. Assigning Images To ListView Items.
  4. ListView Control Drag-Drop Sort Events
  5. ListView Control With MS-Access TreeView
  6. ListView Control With MS-Access TreeView
  7. TreeView/ListView Controls Drag-Drop Events
Share:

ListView Control Drag-Drop Sort Events

Rearranging Rows of Data in ListView Control.

In the earlier episode of this Tutorial, we have learned how to rearrange the Columns by enabling this feature: AllowColumnReorder option on the Property Sheet. But, relocating a row is done by dragging and placing it on another row. For rearranging the ListView control rows, the ListItem Drag and Drop action requires enabling this feature on the Property Sheet. But this alone will not work, needs VBA Code to rearrange the item to its required order.

Let us create a sample Access Form with controls and VBA Code in our database for this exercise. The sample image of the Form with ListBox and ListView Controls is given below.

We have created a list of Tables and Queries (not Action Queries) in the List Box. Selecting one of the list items will display the records instantly on the ListView control, as we see them in the DataSheet view.

The Design Task.

  1. Create a new Table with a single Text field, with the field name DataList.

  2. Save the Table with the name lvTables (lv stands for ListView).

  3. Open the Table in Datasheet View.

  4. Add a few table names and Select Query names from your database into the Table. I have imported Tables from the Northwind sample database for my list.

    Note: The Attachment Field is not valid in ListView Control. Create Select Queries for tables with attachment field and select all fields except Attachment field. 

  5. Create and open a new Form in Design View.

  6. Insert a ListBox Control on the form, display the Property Sheet and change its Name property value to List0.

  7. Change its child label Caption value to Tables.

  8. Display the Property Sheet of ListBox control and set the Row Source property value to the lvTables name.

  9. Check whether the Row Source Type is set as Table/Query and the Bound Column property value is 1.  If different then change.

  10. Insert a ListView Control from the ActiveX Controls List and change its Name Property Value to ListView1.

  11. Resize both the controls as shown on the demo Form image given above.

  12. Insert a Label above the Controls and change its Name and Caption Property Values to Heading.  The Caption value will be changed from vba code when a Table or Query is selected from the ListBox.

  13. Create a Command Button below the Controls and change its Name property value to cmdClose and the Caption property value to Close.

  14. Right-Click on the ListView Control, highlight the ListViewCtrl Object option, and select Properties.

  15. Change the property settings to match with the settings in the General Tab image given below.

  16. ListView Control Property Sheet image - General tab view is given below:

    ListView Control Property Sheet

    Some of these options we have already set in the earlier sessions. Here we need the following options for our Drag Drop action:

    • OLEDragAutomatic - 1

    • OLEDropManual - 1

    • FullRowSelect - True

    • HotTracking - True

Ensure that the above settings match with your property sheet then save the Form.

Display the VBA Module of the Form.

The Form Module VBA Code.

Copy and Paste the following VBA Code into the Module, overwriting the existing Code Lines, if any:

Option Compare Database
Option Explicit

Dim lvwList As MSComctlLib.ListView
Dim strTable As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Private Sub Form_Load()
    Set lvwList = Me.ListView1.Object

End Sub


Private Sub Form_Unload(Cancel As Integer)
On Error GoTo Form_Unload_Err
Dim lvItem As ListItem
Dim tmp As Long
Dim criteria As String
Dim strfield As String
Dim flag As Boolean
Dim fld As String

If strTable = "" Then
Set lvwList = Nothing
    Exit Sub
End If

Set db = CurrentDb
Set rst = db.OpenRecordset(strTable, dbOpenDynaset)
flag = False
For Each lvItem In lvwList.ListItems
    tmp = lvItem.Index
    strfield = lvwList.ColumnHeaders(1).Text
    criteria = strfield & " = " & Chr(34) & lvItem.Text & Chr(34)
    
    rst.FindFirst criteria
  
    If Not rst.NoMatch Then
       If (rst.Fields(strfield).Value = lvItem.Text) _
       And (rst.Fields(1).Value = tmp) Then
         'GoTo nextitem
       Else
            rst.Edit
            rst.Fields(1).Value = tmp
            rst.Update
       End If
    Else
        MsgBox "Item: " & tmp & " Not Found!"
    End If
Next
rst.Close

Set lvwList = Nothing
Set lvItem = Nothing
Set rst = Nothing
Set db = Nothing

Form_Unload_Exit:
Exit Sub

Form_Unload_Err:
MsgBox Err & " : " & Err.Description, , "Form_Unload()"
Resume Form_Unload_Exit

End Sub

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As Object)
' When a ColumnHeader object is clicked, the ListView control
' sorts the data of that column. On the first Click on the Column
'will sort in Ascending Order, second Click will sort in Descending
With Me.ListView1
    ' Set the SortKey to the Index of the ColumnHeader - 1
    .SortKey = ColumnHeader.Index - 1
    
' Set Sorted to True to sort the list.
 If .SortOrder = lvwAscending Then
    .SortOrder = lvwDescending
 Else
    .SortOrder = lvwAscending
 End If
 
    .Sorted = True
End With

End Sub

Private Sub List0_Click()

strTable = List0.Value

Call LoadListView(strTable)

End Sub

Private Sub LoadListView(ByVal s_Datasource As String)
On Error GoTo LoadListView_Err
    Dim j As Integer
    Dim tmpLItem As MSComctlLib.ListItem
    Dim strHeading As String
    
    strHeading = UCase(s_Datasource)
    With Me.Heading
        .caption = strHeading
        .FontName = "Courier New"
        .FontSize = 20
        .FontItalic = True
        .FontBold = True
    End With
    
   'Initialize ListView Control
    lvwList.ColumnHeaders.Clear
    lvwList.ListItems.Clear
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(s_Datasource, dbOpenSnapshot)
       
    'Initialize ListView & Column Headers Property Values
     With lvwList
        .Font.Size = 10
        .Font.Name = "Verdana"
        .Font.Bold = False
        .GridLines = True
    End With
    
    With lvwList
        'Syntax: .ColumnHeaders.Add Index, Key, Text, Width in Pixels, Alignment, Icon
       For j = 0 To rst.Fields.Count - 1
        .ColumnHeaders.Add , , rst.Fields(j).Name, IIf(j = 0, 3000, 1400), 0
       Next
    End With
   Dim I As Long
    rst.MoveFirst
    Do While Not rst.BOF And Not rst.EOF
    'Syntax: lvwList.ListItems.Add Index, Key, Text, Icon, SmallIcon
        Set tmpLItem = lvwList.ListItems.Add(, , rst.Fields(0).Value) 'Name column
        
         'Syntax: tmpLItem.ListSubItems.Add Index, Key, Text, ReportIcon, ToolTipText
          With tmpLItem
                For j = 1 To rst.Fields.Count - 1
                    .ListSubItems.Add , , Nz(rst.Fields(j).Value, "")
                Next
          End With
        rst.MoveNext
    Loop
    rst.Close
    
    With lvwList
        If .ListItems.Count > 0 Then
            .ListItems(1).Selected = True
        End If
    End With
  
    Set db = Nothing
    Set rst = Nothing
    
LoadListView_Exit:
Exit Sub

LoadListView_Err:
MsgBox Err & " : " & Err.Description, , "LoadListView()"
Resume LoadListView_Exit
End Sub


Private Sub ListView1_OLEDragOver(data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single, State As Integer)
    'Highlight the item when draged over it
    Set ListView1.DropHighlight = ListView1.HitTest(x, y)

End Sub

Private Sub ListView1_OLEDragDrop(data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)

'Item being dropped
Dim lvwDrag As ListItem
'Item being dropped on
Dim lvwDrop As ListItem
'Item being readded to the list
Dim lvwTarget As ListItem
'Subitem reference in dropped item
Dim lvwSub As ListSubItem
'Drop position
Dim intTgtIndex As Integer
Dim j As Integer

Set lvwDrop = lvwList.HitTest(x, y)
Set lvwDrag = lvwList.SelectedItem 'save a copy of draged item

'Ignore overlapping drag or drop Item actions
If (lvwDrop Is Nothing) Or (lvwDrag Is Nothing) Or (lvwDrop = lvwDrag) Then
    Set lvwList.DropHighlight = Nothing
    Set lvwDrop = Nothing
    Set lvwDrag = Nothing
    Exit Sub
End If

'Save the droped position Index Number
intTgtIndex = lvwDrop.Index
'Remove Dragged Item from its old position
lvwList.ListItems.Remove lvwDrag.Index

'For j = intTgtIndex To ListItems.Count
    
'Creates a new Item in the Target Item position
'with the Dropped Item Index Number and Dragged Item.Text.
'Saves the new Item reference in lvwTarget Item.

'* The original Droped-on Target) Item will be moved down
'* by incrementing its original Index Number
Set lvwTarget = lvwList.ListItems.Add(intTgtIndex, , lvwDrag.Text)

'Copy the original Draged Item's subitems to the new item
If lvwDrag.ListSubItems.Count > 0 Then
    For Each lvwSub In lvwDrag.ListSubItems
        lvwTarget.ListSubItems.Add , lvwSub.Key, lvwSub.Text
    Next
End If

'Highlight the draged item in its new position
lvwTarget.Selected = True

'Destroy all objects
Set lvwTarget = Nothing
Set lvwDrag = Nothing
Set lvwDrop = Nothing
Set lvwList.DropHighlight = Nothing

End Sub

Private Sub cmdClose_Click()
    DoCmd.Close acForm, Me.Name
End Sub

You are familiar with the above VBA Code except the newly added subroutines: ListView1_OLEDragOver(), ListView1_OLEDragDrop(), Form_Unload(), and the ListView1_ColumnClik() procedures. The first two procedures will help us drag an Item (row) and Drop it over another Item to insert it into a new location. The procedures Form_Unload() and ListView1_ColumnClick() will sort the Items.

The following Images show the Drag and Drop action in sequences of its execution

The first image below shows the drag and drop action sequence. The ListItem, with EmployeeID 7, is dragged up by the user and drops over the ListItem with ID 3.

The second image shows the move of ListItem in reverse order.

When the mouse pointer moves over a row with the dragged item, between the source and target rows,  will highlight one after the other on the way up.

The Drag and Drop Action in Pictures.

Row on the Move
The row with Employee ID 7 is dropped on the Item with Employee ID 3 above.
Record Moved up and Dropped

The VBA Code Segment-wise Analysis.

  An item selection from the ListBox the List0_Click() event procedure runs and loads the records into the ListView Control.

Private Sub List0_Click()
Dim strTable As String

strTable = List0.Value

  Call LoadListView(strTable)

End Sub

The selected Table/Query name saves in the strTable string variable. The LoadListView() subroutine runs with the strTable variable as the parameter. We have gone through this Code more than once in earlier sessions, and you may visit those Pages using the Links provided at the bottom of this page for details. You may find a few minor changes I have made in this Code.

We have not used the ImageList Control in this episode the Icon, SmallIcon Parameter values in the ListItems.Add() Method and ReportIcon, TooltipText parameter values in the ListSubItems.Add() method also not used.

Let us look at what is happening in the ListView1_OLEDragOver() and ListView1_OLEDragDrop() VBA Code Segments.

The ListView1_OLEDragOver() Procedure.

Private Sub ListView1_OLEDragOver(Data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single, State As Integer)
    'Highlight the item when draged over it
    Set ListView1.DropHighlight = ListView1.HitTest(x, y)
End Sub 

This procedure automatically executes when you attempt to click and hold on a row, start to drag and move over other rows on the way towards the target row. The drag action moves over another row it will get highlighted.

The ListView1.HitTest(x, y) function reads the x, y coordinates that determines the row position on the ListView Control and highlights that row. This process continues when you are over other rows till you drop it on the target row by releasing the mouse button. The drop action triggers the ListView1_OLEDragDrop() procedure and executes the source row's change over procedures.

The ListView1_OLEDragDrop Procedure.

Private Sub ListView1_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)

'Item being dragged
Dim lvwDrag As ListItem
'Item being dropped on
Dim lvwDrop As ListItem
'Item being added to the list
Dim lvwTarget As ListItem
'Subitem reference used in For . . .Next loop
Dim lvwSub As ListSubItem 'Drop position index Dim intTgtIndex As Integer Set lvwDrop = lvwList.HitTest(x, y) 'save the source item Set lvwDrag = lvwList.SelectedItem 'save a copy of draged item 'Ignore overlapping drag or drop Item actions If (lvwDrop Is Nothing) Or (lvwDrag Is Nothing) Or (lvwDrop = lvwDrag) Then Set lvwList.DropHighlight = Nothing Set lvwDrop = Nothing Set lvwDrag = Nothing Exit Sub End If 'Save the droped position Index Number intTgtIndex = lvwDrop.Index 'Remove Dragged Item from its old position lvwList.ListItems.Remove lvwDrag.Index 'Creates a new Item in the Target Item position 'with the Dropped Item Index Number and Dragged Item.Text. 'Saves the new Item reference in lvwTarget Item. '* The original Droped-on Target) Item will be moved down '* by incrementing its original Index Number Set lvwTarget = lvwList.ListItems.Add(intTgtIndex, , lvwDrag.Text) 'Copy the original Draged Item's subitems to the new item If lvwDrag.ListSubItems.Count > 0 Then For Each lvwSub In lvwDrag.ListSubItems lvwTarget.ListSubItems.Add , lvwSub.Key, lvwSub.Text Next End If 'Highlight the draged item in its new position lvwTarget.Selected = True 'Destroy all objects Set lvwTarget = Nothing Set lvwDrag = Nothing Set lvwDrop = Nothing Set lvwList.DropHighlight = Nothing End Sub

Let us review this procedure part-by-part and understand what is happening there. The following Code Segment declares necessary Object Variables to handle the Drag and Drop action:

'Item being dragged
Dim lvwDrag As ListItem
'Item being dropped on
Dim lvwDrop As ListItem
'Reference of the Item being added to the list
Dim lvwTarget As ListItem
'Subitem reference used in For . . .Next loop
Dim lvwSub As ListSubItem
'Drop position index
Dim intTgtIndex As Integer

Set lvwDrop = lvwList.HitTest(x, y)
Set lvwDrag = lvwList.SelectedItem 'save a copy of draged item

The first three ListItem temporary Objects declares with different names.

The lvwDrag ListItem object will hold the copy of the row we pick to drag to a new location.

The lvwDrop ListItem Object will save the reference of the row on which we drop the dragged list item.

During the change-over of ListItems action, we will delete the Source item from its original location, then creates it in the target location, with the source ListItem Index number. The references of this new ListItem saves in the lvwTarget ListItem object variable.

The lvwSub Variable declared as a sequencing Object Variable in the For . . .Next Loop. This looping requires to sequence through the ListSubItems, (2nd Column onwards) one-by-one, from the lvwDrag object. Even though we have deleted the original ListItem we have saved a copy of it in the lvwDrag ListItem object.

The lvwDrop ListItem Index number is saved in the intTgtIndex Variable.

The lvwList.HitTest(x, y) Function reads the x, y coordinates of the ListView Control and identifies the target ListItem where we have dropped the source ListItem and makes a copy of it in lvwDrop Object.

We will select a ListItem first before we drag it to the new position.

The lvwList.SelectedItem Property will be set as True. With the help of this property status, we make a copy of the selected ListItem into the lvwDrag ListItem Object. The next Code segment validates both Source and Target ListItem Objects.

Validation Checks on the Drag-Drop Action.

'Ignore overlapping drag or drop Item actions, 
'OR drag and drop happens on the same ListItem.
If (lvwDrop Is Nothing) Or (lvwDrag Is Nothing)  Or (lvwDrop = lvwDrag) Then
    Set lvwList.DropHighlight = Nothing
    Set lvwDrop = Nothing
    Set lvwDrag = Nothing
    Exit Sub
End If

The above code segment validates the drag and drops action. If these actions did not start or end on a valid item then the lvwDrop or lvwDrag Objects or both of them will be empty. Or another invalid move can happen as the user moves a row up or down but may change his mind and drop it back on the same row.  Detection of these kinds of wrong moves will terminate the program.

If the above test proves valid then the program will continue executing the next procedure to rearrange the Rows.

'Save the dropped position ListItem Index Number
intTgtIndex = lvwDrop.Index

'Remove Dragged Item from its old position
lvwList.ListItems.Remove lvwDrag.Index

'Creates a new Item in the Target Item position
'with the Dropped Item Index Number and Dragged Item.Text.
'Saves the new Item reference in lvwTarget Item.

'* The original Droped-on Target) Item will be moved down
'* by incrementing its original Index Number
Set lvwTarget = lvwList.ListItems.Add(intTgtIndex, , lvwDrag.Text)

'Copy the original Draged Item's subitems to the new item
If lvwDrag.ListSubItems.Count > 0 Then
    For Each lvwSub In lvwDrag.ListSubItems
        lvwTarget.ListSubItems.Add , lvwSub.Key, lvwSub.Text
    Next
End If

'Highlight the draged item in its new position
lvwTarget.Selected = True

The above nine lines of executable Code (other lines are comments) actions are somewhat straightforward.

The intTgtIndex = lvwDrop.Index statement saves the target ListItem's Index number in intTgtIndex Variable.

Since we have already saved the Source Row listItem in the temporary Object lvwDrag, the next step is to remove the source ListItem from the ListView Control. The ListItems.Remove() procedure is called, with the statement lvwList.ListItems.Remove lvwDrag.Index.

In short, the Drag Drop action is to delete a ListItem from its original location and create it again at the target location with the target row index number.

The statement Set lvwTarget = lvwList.ListItems.Add(intTgtIndex, , lvwDrag.Text) creates the new ListItem with its target location index number intTgtIndex and the Text value of Source ListItem saved in the lvwDrag Object earlier.

While creating the ListItem for the first time, we have used only these two values, the index, and the Text parameter values. We have not used the other parameter options Key, Icon, and SmallIcon otherwise we must include those parameter values also from the lvwDrag object.

As per our Drag Drop example Images shown above we have moved the 7th ListItem and dropped it on the 3rd ListItem. After that, we have deleted the 7th Item (or source ListItem) from the ListView Control. Created a new ListItem with the target index number 3.

Now, there are two items with the same Index number 3, the existing one with the index number 3 and the new one we have created with index number 3. All other information is taken from the lvwDrag Object (or the 7th ListItem saved in lvwDrag Object earlier).

The System automatically increments the existing ListItem 3 onwards to the next sequence numbers 3,4,5 . . . to 4,5,6 . . . and moves them forward to give space for the incoming item to insert in between.

The Impact of Delete a Row and Create it elsewhere. 

Assume that we make that move in the reverse order, like drag ListItem number 3 from above and drop it on item number 7 then what happens?

Naturally, we will delete the 3rd Item and will attempt to create a new item with index number 7 in the new location. When Item number 3 is deleted item number 4 onwards will shift up or 4,5,6,7,8,9 will become 3,4,5,6,7,8 (to make all the items in sequence) or the earlier item with index number 7 will become 6.

When we create the new Item with index number 7 the existing 7,8 will become 8,9 again. When we watch the movement of rows while row deletion and creation time the first example will move the target row down to make way for the incoming item. In the second example explained (moving 3 to 7) the destination row will move up.

Note: Watch the Employees ID value for its placing as a clue for ListItem shifting down or moving up when we rearrange ListItem.

I have mentioned ListItem everywhere in drag-drop operations. The ListItem refers to the first Column of the ListView Row only. Other column values are ListSubItems or the child-items of the ListItem. That means you will be able to Drag and Drop the first Column only. Other Columns or ListSubItems will be moved under the ListItem with VBA Code.

ListItem Column Drag and Drop

This is true if you have not enabled the FullRowSelection on the ListView Control Property Sheet on the General Tab.

ListItem Column Dropped

If enabled you can select any column, but the System refers to the ListItem Index for Rows re-order purposes.  Compare the above two images with another set of two sample images, the third and fourth images from the top of this page.

The Drag and Drop action will not work if the following two property values are not Set on the ListView Control Property Sheet on the General Tab.:

  • ccOLEDragAutomatic = 1
  • ccOLEDropManual = 1

The next five statements will move the ListSubItems, if any, to the ListItem newly created in the new location.

Next, the newly created ListItem is highlighted.

Next, all temporary objects created are cleared from memory.

Note: Another important point to note here is that this arrangement is a temporary one and is lost when you close the Form or load another Table/Query on the ListView Control. 

If we want the changed order of ListItems to remain permanent, or until the order is changed next time, then we must be able to update the current indexed order number on the table itself.  We have added a new Integer field with the field-name ID on the Employees Table. 

The sample screen with the Employees data rearranged in Alphabetical order is given below:

Since the Employees ID field is an AutoNumber field and linked with other related tables we have added a new Number Field with the field name ID. This field value is set initially with the same sequence numbers from the Employees ID manually.  This field value will be initially in this order.  But, the ListView Rows data may change their order when you rearrange the data on the ListView Control due to Drag and Drop action.

Look at the EmployeesQ Query SQL given below:

SELECT [FirstName] & " " & [LastName] AS EmployeeName, 
Employees.ID, 
Employees.EmployeeID, 
Employees.TitleOfCourtesy, 
Employees.Title, 
Employees.Address, 
Employees.City, 
Employees.Region, 
Employees.PostalCode, 
Employees.Country, 
Employees.HomePhone, 
Employees.Extension, 
Employees.Notes
FROM Employees
ORDER BY Employees.ID;

The above Query is used as Data Source for the ListView Control and they are sorted on the ID Field. The ID field is updated with the changed order of Index Numbers on the ListView Control.  The updating process runs from the Form_Unload() Event Procedure when you close the Form.  This method ensures that when you open the ListView Control next time the data will be in the order you reordered last time.

The Form_Unload() Event Procedure VBA Code.

Private Sub Form_Unload(Cancel As Integer)
Dim lvItem As ListItem
Dim tmp As Long
Dim criteria As String
Dim strfield As String
Dim fld As String

If strTable = "" Then
Set lvwList = Nothing
    Exit Sub
End If

Set db = CurrentDb
Set rst = db.OpenRecordset(strTable, dbOpenDynaset)

For Each lvItem In lvwList.ListItems
    tmp = lvItem.Index
    strfield = lvwList.ColumnHeaders(1).Text 'EmployeeName
    criteria = strfield & " = " & Chr(34) & lvItem.Text & Chr(34)
    
    rst.FindFirst criteria
  
    If Not rst.NoMatch Then
       If (rst.Fields(strfield).Value = lvItem.Text) And (rst.Fields(1).Value = tmp) Then
         'GoTo nextitem
       Else
            rst.Edit
            rst.Fields(1).Value = tmp 'replace ID number
            rst.Update
       End If
    Else
        MsgBox "Item: " & tmp & " Not Found!"
    End If
Next
rst.Close

Set lvwList = Nothing
Set lvItem = Nothing
Set rst = Nothing
Set db = Nothing

End Sub

Check the EmployeeName Field Value in the above image. They are arranged in Alphabetical order.  The new ID field value on the Employees Table will be updated with their current ListView Control ListItem index number sequence.

If you note the following points you can easily understand what we do with the above code:

  1. The ListItem's (first column) Text parameter value is the employee name and arranged in Alphabetical Order.

  2. The ListItems on the ListView Control has index numbers from 1 to 9 in the order it is shown on the screen, i.e. the first item's index number is 1 and the last one is 9. The original data on the Employees Table ID field value is not in this order.

  3. We take the Text Value (Employee Name) of the first ListItem and search for the name on the table.

  4. When the record is found the current ListItem's Index number is updated (replaced) on the ID field on the table. 

  5. This process was repeated for all the remaining records on the table. 

Let us go through the VBA Code. In the beginning, we check whether the Source data Table/Query was loaded into the ListView Control or not? 

If the strTable Variable is not initialized with the Query name then the ListView Control is empty. If this is the case then the user opened the Form and closed it without selecting the Query name to load the data into the ListView control.  The Form_Unload Event Procedure is aborted at this point and closes the form.

If the ListView control has data then the next step is executed and opens the Source data query  EmployeesQ to update. 

The next step is to go through each ListItem and update the index number in the ID field of the Employees record.

First, the current row index number is saved in the tmp Variable.

The first lvwList.ColumnHeader name EmployeeName and the employee's name is taken from the ListItem.Text into an expression in the Criteria string variable, like EmployeeName = "Andrew Fuller".

The rst.FindFirst Criteria command searches the Source data table to find the record with the given name.  When the record is found the current ListItem Index number is updated on the ID Field.

This process is repeated for all the rows on the ListView Control and when finished the Form is closed.

Next time you load the records from this Query into the ListView Control they will be displayed in the same order when you closed the form last time.

Note: The Query became necessary here to sort the data on the ID field and display them in the changed order on the ListView Control.  

All this work was for saving the data in the last sorted order so that next time you open the Form the data on the ListView Control will be in that order.

Windows Explorer Like Sorting Method.

In Windows Explorer, you can sort the displayed list in Ascending or Descending Order by clicking on any Column Heading. The Column Header will work like a Toggle Button. Repeated clicks on the Column Header will Sort the column data in Ascending/Descending Order by the following ListView1_ColumnClick() Event Procedure:

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As Object)
' When a ColumnHeader object is clicked, the ListView control is
' sorted by the subitems of that column.

With Me.ListView1
' Set the SortKey to the Index of the ColumnHeader - 1
    .SortKey = ColumnHeader.Index - 1
    
 If .SortOrder = lvwAscending Then
    .SortOrder = lvwDescending
 Else
    .SortOrder = lvwAscending
 End If
 ' Set Sorted to True to sort the list.
     .Sorted = True
End With
End Sub

Note: The Sorting of all data is in text compare mode only.  The ListItems and ListSubItems Add() method's third Parameter, the displayed information on the ListView Control is Text type. Date and Numeric Values are all treated as Text only.

Windows Explorer saves the last sorted order of items in the folder.  When we open that folder again the list will be displayed in the earlier sorted order. 

With the Form_Unload() Event Procedure this feature of Windows Explorer become possible on the Employees Table.  When you close the Form after sorting on any column that indexed order sequence will be saved on the Employees Table in the ID field.  The EmployeesQ Query always sorts the data on the ID field when opened.

The Demo database is attached for Downloading.  There are two demo forms in the Database.  The first Form demonstrates the opening of Tables and Queries in the ListView Control to view the data in Datasheet View. The second form uses only the EmployeesQ Query alone for Drag, Drop, Sort, and saving of last sort order of data for future use.



  1. ActiveX ListView Control Tutorial-01.
  2. ListView Control Tutorial-02.
  3. Assigning Images To ListView Items.
  4. ListView Control Drag-Drop Sort Events
  5. ListView Control With MS-Access TreeView
  6. TreeView/ListView Controls Drag-Drop Events
Share:

Assigning Images To ListView Items Tutorial-03

The ImageList ActiveX Control.

For Assigning Images to the ListView control, we need the help of another ActiveX Control: the ImageList Control.  We have seen Icons in Windows Explorer view, Folder-like images in the folder-closed state, when you click on the folder it displays an open Folder image and different types of images based on the file type. We don't have that much flexibility here, but we can display Icon images in the ListView Control's ColumnHeaders, ListItems, and ListSubItems object members while populating their contents. 

The Sample Demo Images.

The sample image of NorthWind Trading Employees listing with their Photos (small images) in the ListView Control:

Larger image sizes will increase the row height of records, but the photos can be larger than this.

A sample Image of ListView Control is given below (in the right-side Panel) that we have used along with the TreeView Control. The TreeView ActiveX Control was introduced to you in an earlier Series of Tutorials on TreeView Control.  You can find the List of TreeView Control Tutorial Series links at the bottom of this page.

In the above picture, I have used Icon images in all data columns and on column header labels to demonstrate the possibility of image display on ListView Control. 

The folder close and open states are displayed in the left panel on TreeView Control Nodes and they work differently than on ListView Items. The folder-open image is displayed when the TreeView Node receives a Click. A second click on the same Node displays the folder-closed image.

The ListView Control Programming Tutorial Series.

Hope you have gone through the ListView Control's earlier Tutorial Sessions-1 and 2 and are ready to take up this new Episode on the usage of ImageList Control along with the ListView Control.  The earlier Tutorial links are given below for review and to get ready to continue with this Session. Some of the basics of ListView Control are already presented and explained there with VBA Code and you will be in a better position to continue here and understand what is happening here.

  1. ListView Control Tutorial-01.

  2. ListView Control Tutorial-02.

Source Data and Demo Form.

Let us start with a new Form and the Employees Table for our new ListView Control Demo Project.  Import the Employees Table from the NorthWind.accdb sample Database.  

  1. Create a new SELECT Query with the SQL given below and save it with the name EmployeesQ.

    SELECT [TitleOfCourtesy] & " " & [FirstName] & " " & [LastName] AS [Employee Name], 
    Employees.EmployeeID, 
    Employees.Title, 
    Employees.HireDate, 
    Employees.Address, 
    Employees.City, 
    Employees.Region, 
    Employees.PostalCode, 
    Employees.Country, 
    Employees.HomePhone, 
    Employees.Extension, 
    Employees.Notes
    FROM Employees;
    
  2. If your Employees Table structure is different doesn't matter.  For the first Column value only I have combined three column values together to form the [Employees Name] as the first Column. Other column name values you can take as you have them and in any order, all of them or fewer as you please.

  3. Create a new Form and open it in Design View.

  4. Insert a Microsoft ListView Control from the ActiveX Controls List.

  5. Insert a Microsoft ImageList Control also from the ActiveX Controls List.

  6. Resize the ListView control like the sample image on the Form given below.  Move the ImageList Control and place it at the top right corner of the ListView control as shown in the image. You can place it anywhere in a convenient location on the Form.  It will not appear on the Form when the Form is in Normal view.

    • Select the ListView Control and display the Property Sheet.

    •  Change the Name Property Value to ListView1. 

    • Select the ImageList Control, display its Property Sheet, and change the Name Property value to ImageList0.

    • Note: Both the above controls have their own dedicated Property Sheets.  Their property names and values may appear in Access Property Sheet also.  If we make some changes in the Access Property Sheet then all of them may not update on the ListView and ImageList Controls. We have to make changes to the Control's own Property Sheet.

      ListView Control Property Sheet.

  7. Right-Click on the ListView Control, highlight the ListViewCtrl Object option in the displayed list, and select Properties.  The General Tab of the ListView Control Property Sheet will look like the Image given below.  

  8. Change the Property Values on the General Tab as shown in the Image above.

    First of all, we will load the Employees' data in the ListView Control.

    The Form Module VBA Code

  9. Copy and Paste the following VBA Code into the Form's Class Module:

    Option Compare Database
    Option Explicit
    
    Dim lvwList As MSComctlLib.ListView
    Dim lvwItem As MSComctlLib.ListItem
    Dim ObjImgList As MSComctlLib.ImageList
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    
    Private Sub cmdClose_Click()
       DoCmd.Close acForm, Me.Name
    End Sub
    
    Private Sub Form_Load()
     Call LoadListView("EmployeesQ")
    End Sub
    
    Private Sub LoadListView(ByVal tblName As String)
        Dim strFldName As String
        Dim intCounter As Integer
        Dim j As Integer
        Dim strLabel As String
    
    'Assign ListView Control on Form to lvwList Object
    Set lvwList = Me.ListView1.Object
    'Set ObjImgList = Me.ImageList0.Object
        
    'Assign Form Header labels Caption Text
     strLabel = UCase(tblName) & " " & "IN LISTVIEW CONTROL - TUTORIAL-03"
     Me.Label8.caption = strLabel
     Me.Label9.caption = strLabel
     
     With lvwList
        '.Icons = ObjImgList
        '.SmallIcons = ObjImgList
        '.ColumnHeaderIcons = ObjImgList
        .Font = "Verdana"
        .Font.Size = 10
        .Font.Bold = True
     End With
     
     Set db = CurrentDb
     Set rst = db.OpenRecordset(tblName, dbOpenSnapshot)
     
     'Create Column Headers for ListView
     With lvwList
        .ColumnHeaders.Clear 'initialize header area
        For j = 0 To rst.Fields.Count - 1
            strFldName = rst.Fields(j).Name
       'Syntax:
       '.ColumnHeaders.Add Index, Key, Text, Width, Alignment, Icon
            .ColumnHeaders.Add , , strFldName, iif(j=0,3200,2000)
        Next
     End With
     
     'Initialize ListView Control
      While lvwList.ListItems.Count > 0
            lvwList.ListItems.Remove (1)
      Wend
    
     With lvwList
     Do While Not rst.EOF And Not rst.BOF
    
       'Syntax  .ListItems.Add(Index, Key, Text, Icon, SmallIcon)
            Set lvwItem = .ListItems.Add(, , CStr(Nz(rst.Fields(0).Value, "")))
            
       'Add next columns of data as sub-items of ListItem
            With lvwItem
       'Syntax     .Add Index,Key,Text,Report Icon,TooltipText
             For j = 1 To rst.Fields.Count - 1
                .ListSubItems.Add , , CStr(Nz(rst.Fields(j).Value, ""))
             Next
    
           End With
           rst.MoveNext
    Loop
    rst.Close
        'reset lvwItem object
        Set lvwItem = Nothing
    End With
    
    Set rst = Nothing
    Set db = Nothing
    End Sub
    
    

    Note:  The Red-Colored VBA lines of ImageList control are Commented out from executing for the time being and we will enable them shortly.

  10. Save your Form with the Name frmEmployees.

  11. Open the Form in Normal View.

    The EmployeesQ Query Records Listing will look like the following Image:

Review of the VBA Code

We have already gone through the above VBA Code in the earlier ListView Control Tutorial-01 and 02  Sessions, except for a few lines on ImageList Control declaration, initialization, and a few lines for the Font-Name, Font-Size, and Font-Style settings.  Another change we have made in the earlier VBA Code here is the LoadListView() program, it needs a Table/Query Name as a parameter.  All Query Types, except Action Queries, Access Tables, and Linked Table Names are Valid. The Table or Query name is passed when the program is called from the Form_Load() Event Procedure.

All the Table/Query Field Names are used as ColumnHeader Label Text (the third parameter) in the ColumnHeaders.Add() method. The first parameter Index and second parameter Key Values are not used. The Index sequence numbers will be inserted by the system automatically. 

The fourth parameter is the Column-Width value in pixels and we have arbitrarily assigned the first column width value of 3200 pixels and for all other columns 2000 pixels.  The first column displays the Employee Name and it needs more width to display it properly.

The Alignment and Icon parameter values for Column Headers we have not used here.  By default, the left Alignment is assumed. The available Alignment options are given below.

  • 0 - lvwColumnLeft
  • 1 - lvwColumnRight
  • 2 - lvwColumnCenter

You can view the above options on the Column Headers Tab on the ListView Control Property Sheet. To view the above options:

  • Click on the Insert Column Button, enter some temporary column-name in the Text Box below.

  • Click on the Alignment Property and view the above Options.  

  • Click on the Remove Column Button to Delete the temporary column name. 

  • Note: If you would like to add Column Header Labels manually, rather than loading field names through VBA Code, you can type them one by one here. They will appear as Column Header Labels when you display data.

The sample view of the Icon Image at the left side of the Header Column Names can be seen in the right-side panel in the second demo image on the top of this page.

EmployeesQ first Column (Employee Name) is taken as the ListItems.Text in its Add Method. In this method also we have omitted the Index and Key Parameter values. Index numbers will be added automatically by the system as serial numbers.

From the second field onwards all column values are loaded through the ListSubItems.Add() method  of the ListView Control.

Note: All the values are added to the ListItems.Text and in ListSubItems.Text parameter as Text data type only, irrespective of its original data type in the source Table/Query.  In the Code, we are performing a validation check on Field Values, just in case any of them contains a Null Value, and convert it into text value with the CStr() built-in function.

The ImageList control.

The ImageList Control initializing statements we have commented out in the Main Program are shown in red color in the Code segment given below. We will explain and enable them when we are ready with our preparations for uploading Images into the ImageList Control.

'Assign ListView Control on Form to lvwList Object
Set lvwList = Me.ListView1.Object
'Set ObjImgList = Me.ImageList0.Object
    
'Assign Form Header labels Caption Text
 strLabel = UCase(tblName) & " " & "IN LISTVIEW CONTROL - TUTORIAL-03"
 Me.Label8.caption = strLabel
 Me.Label9.caption = strLabel
 
 With lvwList
    '.Icons = ObjImgList
    '.SmallIcons = ObjImgList
    '.ColumnHeaderIcons = ObjImgList
    .Font = "Verdana"
    .Font.Size = 10
    .Font.Bold = True
 End With
 

The first statement with red color above initializes the ObjImgList Object with ImageList control ImageList0 on the Form frmEmployees. Before making changes to the Code let us see what options we have for uploading some images into the ImageList Control.

About Uploading Images.

The next step is to upload some sample images into the ImageList Control.  This can be done in one of two ways. 

Before attempting this step, please create or get at least two small images (any of the popular image types like .jpg, jpeg, .bmp, .png, etc.), preferably .bmp type. The Image size options available on the ImageList Control, on the General tab of the Property Sheet, are 16 x 16, 32 x 3248 x 48 pixels, or Custom size.  

Right-Click on the ImageList Control, highlight the option ImageListCtrl Object, and select Properties. Before selecting any image for uploading select one of the above image sizes on the General Tab.  

  • If you have big Images and want to retain the original Image-Size then select Custom Option. 
  • Selecting any of the other options will reduce the Image to the selected size. This may reduce the image quality. Using very large images may occupy more space on the ListView Control when displayed.  
  • Icon-type images will be more ideal to use.  
  • Experiment with big, small, and very small images and with different options to get some experience on the correct Image/Option selection for your needs.

You can use one of two ways to upload the Images into ImageList Control:

1. Upload Images from disk through VBA Procedure.

The sample VBA Procedure will look like the Code Segment given below, taken from TreeView Control Tutorial:

  
  Set objImgList = Me.ImageList0.Object
  objImgList.ListImages.Clear
  
strFolder = "D:\Access\TreeView\"
With objImgList
    With .ListImages
         .Add Index:=1, Key:="FolderClose", Picture:=LoadPicture(strFolder & "folderclose2.bmp")
         .Add Index:=2, Key:="FolderOpen", Picture:=LoadPicture(strFolder & "folderopen2.bmp")
         .Add Index:=3, Key:="ArrowHead", Picture:=LoadPicture(strFolder & "arrowhead.bmp")
    End With
End With

With tvw 'TreeView Control
    .ImageList = objImgList 'assign imagelist Object to TreeView Imagelist Property
End With

The first statement initializes the objImgList Object with ImageList0 control on the Form.

The next statement ensures that the existing images in the image list control, if any, are cleared in preparation for uploading from the disk. For this approach to work every time, the images must be always available on the disk.

The objImgList.ListImages.Add() method is called to upload images from disk using the named parameters.  When parameter names are used in the Add() method the parameter values can be given in any order like the Index:=1 can be given at the end of the line or Key:="FolderClose" as the first item and so on. Without the parameter names the Add() method parameters order will be as follows:

         .Add 1, "FolderClose", LoadPicture(strFolder & "folderclose2.bmp")

To display the Image on our listView control we can either use the Image Index Number 1  or the Key value "FolderClose" Text as the Icon or SmallIcon parameter values in the ListItems.Add() method.

We have used the above method in the TreeView Control Tutorial earlier.  You may visit that Page and download the Demo Database.

This method loads the Images into the ImageList Object Instance in memory and the physical object on the form is not changed.  The Source Images on the Disk must be always available every time the frmEmployees is open.

2. Uploading Images from disk Manually.

This is a one-time exercise, finding the images on disk and uploading them into the ImageList Control.  

The main advantage is that once the images are uploaded into the ImageList Control they stay intact.  The ImageList control with Images can be copy-pasted for other Projects if the same images are required for more than one Project. Not necessary to load the images from Disk again. The ImgeList Control with Images can be shared with friends as well.

So, let us go for the better method of manual uploading of Images.  Create two .bmp images of 50 x 50 pixels resolution (image1.bmp, image2.bmp) and keep it ready in your folder, say D:\Access\ for reference.

  1. Open frmEmployees in design view.

  2. Right-Click on the ImageList Control, highlight ImageListCtrl Object option and select Properties.

  3. On the General tab select the Custom Option to upload images with the original resolution.

    The General tab view of ImageList Control.

    The Images tab View of the ImageList Control

    Note: After trying out the uploaded images on the ListView control if you would like to try out other options 48 x 48, 32 x 32, 16 x 16 you must Remove all the uploaded images first then go to the General tab, select the required option and then upload the images again.  The selected images will be reduced to the selected image size.

    As you can see in the sample images tab I have uploaded two images by selecting Insert Picture Command Button and picking the images from my disk.  

    The first image is in the selected state and slightly in the raised position.  The Index control shows the value 1 and the Key textbox shows the text First. The Index value will appear automatically but the Key value (some meaningful value that you can easily memorize and relate the image to the data) may enter manually.

    We can use either the Index number or the Key text value in the Icon and in the SmallIcon Parameter of ListItems.Add() method.

    Even if you plan to use the index number sequence then the Image uploading sequence must Sync with the data you plan to upload in the ListView Control, like Employees Name should match with their photos in the correct sequence. 

    A better method in Employees' case their First-name can be used as Key Text and very easy to relate to the record. Generalized images don't need to match with this kind of relationship checking but their Key names will help to indicate what they do, like folder_closed or folder_opened.

  4. Click on the Images tab.

  5. Click on Insert Picture and find your D:\Access\Image1.bmp image and select it, click the Open Button to upload the image into the ImageList Control.

  6. Type some text value in the Key textbox (the Key Values must be unique).

  7. Repeat steps 5 and 6 for the second image, type the Key-value.

    We are ready with our ImageList Control with sample images and ready to display them on the ListView Control.

    Assigning ImageList Object to ListView Object Properties.

    The following ListView Object Properties must be assigned with the ImageList Object in order to use the Image references on the ListView Control:

    1. ListView.ColumnHeaderIcons
    2. ListView.Icons
    3. ListView.SmallIcons

    The next step is to assign the ImageList Object to the required ListView Object in VBA Code through the Object Properties: lvwList.ColumnHeaderIcons, lvwList.Icons, lvwList.smallIcons before we are able to use the image references (Index or Key values) in the ColumnHeaders.Add(), ListItems.Add(), and ListSubItems.Add() methods. We have already added the required VBA Code in the main program and kept them disabled.  All we have to do is to enable those lines of Code by removing the Comment symbol from them and adding the required image references in the above Add method's parameters.

  8. Remove the comment symbols ( ' ) from all the four VBA Code lines shown above with red color in the LoadListView() Procedure. 
  9. Modify the following statements, shown with red-color in the main program LoadListView() as shown with Icon Index number 1 & 2 in the Icon and SmallIcon parameter positions respectively like in the Code segment with bold black letters given below:
     With lvwList
     Do While Not rst.EOF And Not rst.BOF
    
       'Syntax  .ListItems.Add(Index, Key, Text, Icon, SmallIcon)
           ' Set lvwItem = .ListItems.Add(, , CStr(Nz(rst.Fields(0).Value,"")))
           'Change to 
             Set lvwItem = .ListItems.Add(, , CStr(Nz(rst.Fields(0).Value,"")), 1, 2)
            
       'Add next columns of data as sub-items of ListItem
            With lvwItem
       'Syntax     .Add Index,Key,Text,Report Icon,TooltipText
             For j = 1 To rst.Fields.Count - 1
               ' .ListSubItems.Add , , CStr(Nz(rst.Fields(j).Value, ""))
               'Change to           
                 .ListSubItems.Add , , CStr(Nz(rst.Fields(j).Value, "")),,"Click"
             Next
    
           End With
           rst.MoveNext
    Loop
    rst.Close

Since you have only two images the First Image with index number 1 is used as the Icon Parameter and 2 is in the SmallIcon parameter position. The Icon Image is displayed only when you change the ListView display Option to 0 - lvwIcon.  In the ListSubItems.Add() method we have not added an image reference and for the next parameter Tool-Tip text "Click" is added. The Click Text will display when the mouse pointer rests on any of the columns, on the second column onwards.

After making the above changes in the VBA Code Save the Form frmEmployees with the changes.

Open the Form in Normal View.  The view should look like the sample Image on the top of this page.

The smallIcon will be visible in all other ListView Options. Check the sample ListView Images of Employees data given below.

0 - lvwIcon View

ListView Icon View

2 - lvwList View

The first Image on the top of this page is the 03 - lvwReport View. Only in this view, all column values are displayed in the DataSheet like display.

Change the Form into Design View.  Display the Property Sheet of the ListView Control. Change the View options and try out each view and find out how different views looks like.

Download the Demo Database.

 

  1. Microsoft TreeView Control Tutorial
  2. Creating Access Menu with 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
Share:

Translate

PageRank

MSA GURU : Access Tips & Tricks App

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Newsletter


Feed

Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Android App Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code