Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

ListView Control Tutorial-02

Introduction.

Continued from last week's ActiveX ListView Control Tutorial-01.

In this session of the Tutorial, we will learn how to search and find the particular row and column values and display them on a Label Control on Form. This is very useful when we have a large volume of data in the ListView control. We will also learn the usage of some ListView property settings.

First of all, we will see how easy is to rearrange the columns, like we do with Access Datasheet View the way we want them to be on the ListView Control. We have added some TextBoxes, ComboBox, Command Buttons, and Label for easier selection of search parameters and display of search results. 

I have made some changes to last week's demo data. The first column values I have taken from the Employees Table of Northwind.accdb sample database. Created a Query to join the LastName and FirstName Values with the field name Student and EmployeeID used as Key (X01, X02 ...).

ListView Tutorial-02 Screen View

Before going for the search operations, we will check how to re-arrange columns by the drag and drop method.

Note: If you have not gone through the earlier Tutorial Page and would like to continue with this session, then go to the ListView Control Tutorial-01 Page and download the demo database from the bottom of that Page.

Unzip the file and open the Database. The Demo Form will be in Normal View.

  1. Open your Database, with the last session's Demo Form, or the Form that you have created, open it in Normal View.

    Now, we will try to drag and move a column from the middle of the list (say the Weight column), and drop it to the Age column and see what happens. What is expected to happen is that the Age column should shift to the right and insert the incoming column in its place.

  2. Move the mouse pointer on the Column Header with the name Weight, click and hold the left mouse button. When you depress the left mouse button the column header will move slightly down.

  3. Now, try to drag the column to the left and drop it on the column Age.

    Nothing will happen, because we have not enabled this feature in the Property Sheet and that is the only setting, we need to change for this feature to work.

  4. Change the Form in Design View.

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

  6. There is an option 'AllowColumnReorder' on the right side. Put check-mark to select it, then click Apply button followed by the OK button to close the Property View.

  7. Now, try to repeat the above steps 2 and 3 above and see what happens. 

    That is the only setting you need to enable this feature on the ListView Control.  Perhaps you may be thinking, what about rearranging the rows?.

    That function needs programming some Event Procedures as we did earlier in TreeView Control Drag-Drop Events. That part we will do after some time.

  8. You may experiment with any column to move anywhere you like, including the first column as well.

Note: Before you drop the source column see that the target column is covered by the incoming column frame before attempting to drop.  Otherwise, the incoming column may shift to the next column position on the right side.

Next, we will learn how to find some information from the ListView quickly, assuming that we have a large volume of data in it.  

We have added a subroutine to Tutorial-01 Module to load the Column header Names into a Combo Box on the form with the red background color. The Column Name will be used to find the column value (Age, Height, Weight, or Class) of a student.

New VBA Code Added to the Form Class Module.

The following new VBA procedure is added to last week's Tutorial Form's Class Module: 

The txtColCombo creates the list of Column Header Labels (field names) in the ComboBox.  One of these details of the Student's Age, Height, Weight, or Class can be found along with the student's name as part of the search-and-find operation. 

Private Sub txtColCombo()
'Column Header List Combo
Dim lvwColHead As MSComctlLib.ColumnHeader
Dim cboName As ComboBox

Set cboName = Me.txtCol
cboName.RowSourceType = "Value List"

For Each lvwColHead In lvwList.ColumnHeaders
    If lvwColHead.Index = 1 Then
        'Nothing
    Else
        cboName.AddItem lvwColHead.Text
    End If
Next
'cboName.DefaultValue = "=txtCol.Column(0, 0)"

Set lvwColHead = Nothing
Set cboName = Nothing
End Sub

The Combobox will not be loaded with a default value of Column Header name. If selected that column value of the Student is displayed in the Large Label below the student Name. If it is left blank, the search operation will find the student's name only.

The search operation method is very flexible and quick.  We have two methods to find a record.

Find the record by providing the search text.  The search text can be from any of the columns either the text in full or partial few characters from the left. Since we have two categories of object members in a row in the ListView control: ListItem - the first column and other columns are ListSubItems.  The Text search operation on these objects is performed separately. 

An option group with two CheckBoxes is provided next to the search-text input TextBox on the Form to select the search-and-find options. The first option is selected by default and the search is performed on the first Column (ListItem) to look for the given text.

Select the second option to search the text in the ListSubItem columns.  

Note: Re-arranging the columns will not change the objects, but only their display position. Dragging a ListSubItem column and bringing it in the first column will not change it into a ListItem object.

If you want to retrieve an unknown value from a particular column, select a column name from the ComboBox given below the first TextBox on the Form for the search text. For example, you don't know the Height measurement of a student and would like to find out, select the column name Height from the ComboBox. 

After setting the above value(s) click on the Find Item Command Button to go for the search operation.  If the search was successful, then the result will be displayed in the large Label control below the Command Button.

The [Find Item] Command Button Click.

Calls the SearchAndFind() Procedure.

Private Sub SearchAndFind()
'Find by Student Name
Dim lstItem As MSComctlLib.ListItem
Dim strFind As String
Dim strColName As String
Dim strColVal As String
Dim j As Integer
Dim intOpt As Integer
Dim msgText As String

Me.Refresh
intOpt = Me.Opts


strFind = Nz(Me![txtFind], "")
strColName = Nz(Me![txtCol], "")

Select Case intOpt
    Case 1
        Set lstItem = lvwList.FindItem(strFind, , , lvwPartial)
    
        If Not lstItem Is Nothing Then
            j = lstItem.Index
            'format the display text
            msgText = lvwList.ColumnHeaders.Item(1).Text
            msgText = msgText & " : " & lstItem.Text & vbCr & vbCrLf
        Else
            MsgBox "Text '" & strFind & "' Not Found!", vbOKOnly + vbCritical, "cmdFind_Click()"
            Exit Sub
        End If
    Case 2
        Set lstItem = lvwList.FindItem(strFind, lvwSubItem, , lvwPartial)
        If Not lstItem Is Nothing Then
       'format the display text
            j = lstItem.Index
            msgText = lvwList.ColumnHeaders.Item(1).Text
            msgText = msgText & ": " & lstItem.Text & vbCr & vbCrLf
        Else
            MsgBox strFind & " Not Found!", vbOK + vbCritical, "cmdFind_Click()"
            Exit Sub
        End If
End Select

        If Len(strColName) = 0 Then 'If column name is not selected
            GoTo nextStep
        Else
            'Get the column value
            strColVal = GetColVal(lstItem, strColName)
            msgText = msgText & String(8 - (Len(strColName)), " ") & _
            strColName & ": " & Nz(strColVal, "")
        End If
nextStep:

If Len(msgText) > 0 Then 'assign to form label
    lvwList.ListItems.Item(j).Selected = True
    lblMsg.caption = msgText
End If

End Sub

At the beginning of the program, both the Student Name and Column Name (0ptional), are copied from the TextBoxes into the Variables strFind and strColName respectively after validation checks.

Note: The column name Combo Box's Not-in-List Property is set to Yes.  You can select a valid Value from the list or type it in or leave the combo box blank. If you type in a different value that is not in the list, it will not be accepted.

Based on the search Option selected (1 - ListItem or 2 - ListSubItem) the scan method is directed to the specified Object(s). 

Using either one of these search methods will find the ListItem Object or row that contains the search text. The Index Value of the ListItem is saved in Variable J for later use in the program. 

Note: The system creates the index auto-numbers automatically at the time ListView control items are populated. 

The ListItem.Text value is retrieved.   This information is joined with the first ColumnHeader. Text (like Student: Robert King) and added into the Msgtext string to display in the Label control on the Form.

If the column Header Name is selected in the ComboBox, then the GetColVal() Function is called with the ListItem Object and the Column Header Text value as parameters. This option is good for retrieving unknown information about a Student, like the Height of the student, from the record.

The GetColVal() Function VBA Code.

Private Function GetColVal(lvwItem As MSComctlLib.ListItem, ByVal colName As String) As String
Dim i As Integer
Dim strVal As String
    'first column is student name
    'check for column value from 2nd column onwards
    For i = 2 To lvwList.ColumnHeaders.Count
        If lvwList.ColumnHeaders(i).Text = colName Then 'if col name matches
            strVal = lvwItem.ListSubItems.Item(i - 1).Text 'get column value
            Exit For 'No further scanning required
        End If
    Next
GetColVal = strVal 'return the retrieved the value
End Function

The above function asks for two parameters. The first parameter is the ListItem, where the Student's name is found.  The second parameter is the Column Name. The selected student's Age, Height, Weight, Class values are stored in the ListItem.ListSubItems Objects.  The function looks through the lvwList.ColumnHeader values to find the matching column name, when found that column index number is used for retrieving column value from the ListSubItems Object and returns the value to the calling program.

The [Find By Key] Command Button Click Event Procedure.

We have another method added to find the Student's Name using the Unique Key-Value of ListItem if used while creating the ListItem List. Even though it is optional, it is better to add Unique Key String Value (should start with an alphabet character) rather than ignore it.

For example, if we have to find somebody's information by their identification number like Social Security Number, National Identity Card Number, Passport Number or Driving License Number and so on, one of this information can be used as the Key value to the ListItem. Finding a record with this Unique Value is very easy and swifter rather than the above search-by-text method.

The cmdKey_Click() Event Procedure.

Calls FindByKey() Subroutine.
Private Sub FindByKey()
Dim colHeader As MSComctlLib.ColumnHeader
Dim lvItem As MSComctlLib.ListItem
Dim lvKeyVal As String
Dim lvColName As String
Dim txt As String
Dim msgText As String
Dim varcolVal As Variant

lvKeyVal = UCase(Nz(Me!txtKey, ""))
lvColName = Nz(Me!txtCol, "")

If len(lvKeyVal) > 0 then
On Error Resume Next 
Set lvItem = lvwList.ListItems.Item(lvKeyVal) 'get the item by Key
If Err > 0 Then
    Err.Clear
    MsgBox "Key Value: '" & lvKeyVal & "' Not Found!", vbOKOnly + vbCritical, "cmdKey_Click()"
    On Error GoTo 0
    Exit Sub
End If
Else
	MsgBox "Please Provide a Valid Key-Value!",vbOKOnly + vbCritical, "cmdKey_Click()"
    Exit Sub
End If

txt = lvItem.Text 'get the student name
'format message text
msgText = lvwList.ColumnHeaders.Item(1).Text & " : "
msgText = msgText & txt & vbCr & vbCrLf

If Len(lvColName) > 0 Then 'if column name is given
    varcolVal = GetColVal(lvItem, lvColName) 'get column val of student
    msgText = msgText & String(8 - Len(lvColName), " ") & lvColName & ": " & varcolVal ' add it to display
End If

lvItem.Selected = True 'highlight the item on form
Me.lblMsg.caption = msgText 'assign details to form Label
End Sub

As you can see in the above subroutine we could directly find the ListItem where the Student's name is, with the use of the Key-value, with a single statement: Set lvItem = lvwList.ListItems.Item(xKeyVal).  

The Next line reads the ListItem Text (or name of the Student) into the Variable txt. The next two lines create the message text with the Student's Name in the msgText string variable.

The next If . . .Then statement checks whether a Column Name Value is entered in the combo box control. If it is found, then calls the GetColVal() Function with the required parameters to find the column value and retrieve it in varColVal Variable and returns to the calling program.  The Column Name and its value retrieved is added to the msgText string variable to display on the Label control on the Form.

The next statement highlights the record Row of the Student as a visual indication that the searched item is found in the row.  The msgText value is displayed in the Label's Caption Property on the Form.

The Full VBA Code on the Form Module.

Option Compare Database
Option Explicit

Dim lvwList As MSComctlLib.ListView 'ListView Control
Dim lvwItem As MSComctlLib.ListItem '
Dim ObjImgList As MSComctlLib.ImageList
Const prfx As String = "K"

Private Sub Form_Load()
    Call LoadListView
    Call txtColCombo
End Sub

Private Function LoadListView()
'Populate the ListView control with Student Details
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intCounter As Integer
Dim strKey As String

'Assign ListView Control on Form to lvwList Object
 Set lvwList = Me.ListView1.Object
 
With lvwList
    .AllowColumnReorder = True
    .Enabled = True
    .Font = "Verdana"
    .Font.Bold = True
    .Font.Size = 9
    .ForeColor = vbBlack
    .BackColor = vbWhite
 End With
 
 'Create Column Headers for ListView
 With lvwList
    .ColumnHeaders.Clear 'initialize header area
    
   'Syntax: .ColumnHeaders.Add Index, Key, Text, Width, Alignment, Icon
    .ColumnHeaders.Add , , "Student", 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

'Student Names and Ids are taken from Employees Table
'through the StudentQ Query.
Set db = CurrentDb
Set rst = db.OpenRecordset("StudentQ", dbOpenDynaset)

With lvwList
    Do While Not rst.EOF And Not rst.BOF
        intCounter = rst![EmployeeID]
        strKey = "X" & Format(intCounter, "00") 'Key Value sample: X01
        
    'Syntax: .ListItems.Add(Index, Key, Text, Icon, SmallIcon)
        Set lvwItem = .ListItems.Add(, strKey, rst![Student])
        
        With lvwItem
    'Syntax: .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:" & Format(intCounter, "00"))

       End With
        rst.MoveNext
    Loop
rst.Close
Set rst = Nothing
Set db = Nothing
Set lvwItem = Nothing
End With
lvwList.Refresh

End Function


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

Private Sub cmdFind_Click()
Call SearchAndFind

End Sub

Private Sub cmdKey_Click()
Call FindByKey
End Sub

Private Function GetColVal(lvwItem As MSComctlLib.ListItem, ByVal colName As String) As String
Dim i As Integer
Dim strVal As String
    'first column is student name
    'check for column value from 2nd column onwards
    For i = 2 To lvwList.ColumnHeaders.Count
        If lvwList.ColumnHeaders(i).Text = colName Then 'if col name matches
            strVal = lvwItem.ListSubItems.Item(i - 1).Text 'get column value
            Exit For 'No further scanning required
        End If
    Next
GetColVal = strVal 'return the retrieved the value
End Function



Private Sub txtColCombo()
'Column Header List Combo
Dim lvwColHead As MSComctlLib.ColumnHeader
Dim cboName As ComboBox

Set cboName = Me.txtCol
cboName.RowSourceType = "Value List"

For Each lvwColHead In lvwList.ColumnHeaders
    If lvwColHead.Index = 1 Then
        'Nothing
    Else
        cboName.AddItem lvwColHead.Text
    End If
Next
'cboName.DefaultValue = "=txtCol.Column(0, 0)"

Set lvwColHead = Nothing
Set cboName = Nothing
End Sub


Public Sub SearchAndFind()
'Find by Student Name
Dim lstItem As MSComctlLib.ListItem
Dim strFind As String
Dim strColName As String
Dim strColVal As String
Dim j As Integer
Dim intOpt As Integer
Dim msgText As String

Me.Refresh
intOpt = Me.Opts

strFind = Nz(Me![txtFind], "")
strColName = Nz(Me![txtCol], "")

Select Case intOpt
    Case 1
        Set lstItem = lvwList.FindItem(strFind, , , lvwPartial)
        If Not lstItem Is Nothing Then
            j = lstItem.Index
            'format the display text
            msgText = lvwList.ColumnHeaders.Item(1).Text
            msgText = msgText & " : " & lstItem.Text & vbCr & vbCrLf
        Else
           MsgBox "Text '" & strFind & "' Not Found in the List!", vbOKOnly + vbCritical, "cmdFind_Click()"
        Exit Sub
        End If
    Case 2
        Set lstItem = lvwList.FindItem(strFind, lvwSubItem, , lvwPartial)
        If Not lstItem Is Nothing Then
       'format the display text
            j = lstItem.Index
            msgText = lvwList.ColumnHeaders.Item(1).Text
            msgText = msgText & ": " & lstItem.Text & vbCr & vbCrLf
        Else
            MsgBox strFind & " Not Found!", vbOK + vbCritical, "cmdFind_Click()"
            Exit Sub
        End If
End Select

        If Len(strColName) = 0 Then 'If column name is not selected
            GoTo nextStep
        Else
            'Get the column value
            strColVal = GetColVal(lstItem, strColName)
            msgText = msgText & String(8 - (Len(strColName)), " ") & _
            strColName & ": " & Nz(strColVal, "")
        End If
nextStep:

If Len(msgText) > 0 Then 'assign to form label
    lblMsg.caption = msgText
    lvwList.ListItems.Item(j).Selected = True
End If
End Sub

Public Sub FindByKey()
Dim colHeader As MSComctlLib.ColumnHeader
Dim lvItem As MSComctlLib.ListItem
Dim lvKeyVal As String
Dim lvColName As String
Dim txt As String
Dim msgText As String
Dim varcolVal As Variant


lvKeyVal = UCase(Nz(Me!txtKey, ""))
lvColName = Nz(Me!txtCol, "")

On Error Resume Next
If Len(lvKeyVal) > 0 Then
Set lvItem = lvwList.ListItems.Item(lvKeyVal) 'get the item by Key
    If Err > 0 Then
        Err.Clear
        MsgBox "Key Value: '" & lvKeyVal & "' Not Found!", vbOKOnly + vbCritical, "cmdKey_Click()"
       On Error GoTo 0
        Exit Sub
    End If
Else
    MsgBox "Please Provide a Valid Key-Value!", vbOKOnly + vbCritical, "cmdKey_Click()"
    Exit Sub
End If

txt = lvItem.Text 'get the student name
'format message text
msgText = lvwList.ColumnHeaders.Item(1).Text & " : "
msgText = msgText & txt & vbCr & vbCrLf

If Len(lvColName) > 0 Then 'if column name is given
    varcolVal = GetColVal(lvItem, lvColName) 'get column val of student
    msgText = msgText & String(8 - Len(lvColName), " ") & lvColName & ": " & varcolVal ' add it to display
End If

lvItem.Selected = True 'highlight the item on form
Me.lblMsg.caption = msgText 'assign details to form Label
End Sub

Download the Demo Database from the following Link:



  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:

Tips and Tricks Android App In Google Play Store.

The LEARN MS-ACCESS TIPS AND TRICKS Articles at your Finger-Tip. Take all Articles with you in your Android Phone App, wherever you go, and learn them at your own pace.

 Install the Android App from Google Play Store Now.

Share:

Activex ListView Control Tutorial-01

Introduction.

In MS-Access we have ListBox control and mostly it will have only a few columns of data, to find the item(s) quickly. The source data for this control are either typed directly into the Row-Source Property as a Values-list or loaded from the source Table or Query. The Combo Box control keeps its data hidden and needs a click to reveal the list to select. These Objects are already built-in as Access Controls.

But, there is another List Control that we always use in our database, can you guess what it is? Yes, the Data Sheet View Control. The records are displayed from Table, Query. In all these cases we see data in Datasheet View as a big list.

But there are other groups of Controls too in Microsoft Access, the ActiveX Controls.  We are already familiar with one of these controls - the Common Dialog Control or File Browser control.  

Here, the topic is Windows ListView Control.  You can visualize it as an Object similar to Windows Explorer, where you can display Items with Image Icons, Small image Icons, as a List, or like the Explorer's Detail View Pane. You can load your Table / Query data into this control to display them in Datasheet View, re-arrange the Columns or Rows, Sort the rows, display Images next to items, and so on. Other Programming Languages, like VB6, VB.NET, C#, etc., use Windows ListView Control. We are going to see how we can use it in Microsoft Access Database.

A simple ListView Demo screen, with some quick sample data, is given below:

ListView Demo Screen

We will make the above image-like display the starting point of the ListView Control Tutorial. We have uploaded ten rows of data into the ListView control, with a few lines of the VBA Code.  The ListView ActiveX Control you may not find in the existing list of ActiveX Controls in Access.  We have to add this Control's Library file MSCOMCTL.OCX from C:\Windows\System32 folder into the Access Reference Library.  Once it is added you can find this control with the name Microsoft ListView Control, Version 6.0 among other ActiveX controls.

So, let us add the MSCOMCTL.OCX Library File to our database.  This is the source library of ActiveX Controls like ListView, TreeView, ImageList. If you have already gone through our earlier TreeView control tutorial Pages, then you are already introduced to this control. 

Windows Common Controls Library File.

Do the following to attach the MSCOMCTL.OCX File:

  1. Open your Database and open the VBA Editing Window (Alt+F11).

  2. Select References… from the Tools Menu.

  3. Click on Browse Button to find MSCOMCTL.OCX File (Microsoft Windows Common Controls.)

  4. Look for the above file in C:\Windows\System32\ Folder, if you have a 32 Bit System or you have Windows 11 Operating System.

  5. If you could not find it there, then look for the Folder C:\Windows\sysWOW64\ (64 Bit System), and there you will find this file.

  6. Select the file MSCOMCTL.OCX and Click Open Command Button to attach the file to your Database.

  7. Press Alt+F11 again to come back to the Database Window.

Let us design a sample Form to match the above Image given at the top of this page.

  1. Create a new Blank Form.

  2. Select the ActiveX Control Button from the Controls Group of options.

  3. Find and select the Microsoft ListView Control from the displayed list and click the OK button to insert a ListView control on the Form’s Detail Section.

  4. Click and hold on to the control’s resizing handle, at the right bottom corner, drag to the right and down to make it large enough like the sample image given above.

  5. Drag the ListView control itself to the right and down to give some margin to the left and leave enough space above to create a Heading Label.

  6. Click on the ListView Control to select it, if it is not in a selected state.

  7. Display the Property Sheet and change the ListView Control’s name to ListView1.

  8. Create a Label control above and change the Caption property value to ListView Control Tutorial. You may format the label Caption with font size, color, etc., the way you like it.

  9. Create a Command Button below the LlistView control and change its Name Property Value to cmdClose and its Caption Property Value to Close.  The completed design will look like the following when your design is complete:

    ListView  Design
  10. Now, Save the Form with the name: ListViewTutorial and keep the Form in design view.

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

    The VBA Code.

  12. 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
  13. Save the Form with the name ListView Control Tutorial-01.

    Demo View of the Form.

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

    If you find your form with the following image-like display then you are on the right track.

    We have to make some changes in the Listview control's Property settings. We have changed the ListView control's name as ListView1 in the Access's Property Sheet. But, ListView control has its own Property Sheet. We will use the ListView control's own property sheet to make changes to the control. Some of the Property Values are appearing on the Access Property Sheet also.

  15. Right-Click on the ListView control and highlight the ListViewCtrl Object option from the displayed options list and select Properties from the displayed shortcut menu.

  16. The Property sheet Image is given below:

    ListView Property View

    On the Property Sheet on the top, there are Tabs with groups of other options. We are on the General tab by default. On the General tab, there are options on the left side of the control and checkboxes on the right. We will make changes on only two Properties, the ListView control on the form is in a disabled state by default, we must enable it.

    The ListView control display can be changed to different modes like List items with big image Icons, with small Image Icons, in ListView, or in Report View - like it appears in the first Image above.

  17. Enable the ListView Control by putting a check-mark in the Enabled Property, on the right-side.

  18. Select the lvwReport option from the View drop-down list at the left side.

  19. Click on the Apply Button on the Control to save the change.

  20. Click the OK button to close the Property Sheet.

  21. Save the Form with the changes and then open it in normal View.

  22. You will find the same result looks like the Image given on top of this page, except for the form background color and other form properties.

The Program's Functional Diagram.

Before going to the VBA Code it will be interesting to know how the data items are loaded into the ListView Control. The data arrangement for a ListBox control is straightforward. But, the ListView control's data loading procedure is entirely different. It is not in the logical order that we normally perceive. Once you know the flow of data from the source to a single row in the form of a diagram, or let us call it a flow chart, it will not be difficult to understand the VBA Code and what it does.

The Data Flow Diagram.

VBA Functional Diagram
  1. The left top corner box represents the ListView control.

  2. As the first step of preparing the List is to create the list's heading labels or Column Headings.  You can see this in the diagram, column headings in red color.  This you can compare with the field headings in Table's Datasheet View.  Each Column Heading is loaded into the ListView control in the ColumnHeaders Object member.  The ListView control's ColumnHeaders.Add() method is called five times for assigning each column label, one after the other, into the ListView control.

  3. The action needed to execute the next five steps are important to note.  They represent a single record with five Data Fields. But they are loaded into the ListView control in two different sets of steps, or let us say that they are being loaded into two different Object members (ListItems and ListSubItems) of the ListView control.

    1. The first field (Column Value) is loaded into the ListView control's ListItems Object's Add method. If you look at the image on the top, the first record's first column value Student1 is loaded in the ListItems Object (ListView.ListItems.Add method)  of the ListView control.

    2. From the 2nd column onwards all other column values are loaded into the ListSubItems Object of the ListItems Object, one after the other. The ListSubItems.Add Method (ListView.ListItems.Item(x).ListSubItems.Add) is called four times to insert the Values into Age, Height, Weight, and Class columns individually.

  4. These two-level steps of actions are required to load a complete row of values into the ListView Control.  The diagram is drawn with two rows of data in the ListView control.

With the above picture in mind, I am sure you will not have any difficulty in understanding what the above VBA Code does in the Program.

Let us go to the VBA Code Segment-wise.

At the Global declaration area 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 lvwList Variable is declared as a ListView Object, lvwItem is declared as a ListItem Object of ListView Control, ObjImgList is declared as an ImageList Object. ImageList Object is another ActiveX control that can be loaded with Image Icons for use in TreeView, ListView controls. We will keep the ImageList control aside for the time being and will take it up later. The Constant Prfx is used in ListItems.Add method's Key-Value prefix, one of the optional Parameters. The Key-value must be of String Type.

The LoadListView() Function is the main program.

Our ListView control's name on the Form is ListView1. The first statement in the program:

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 will appear in the control with empty rows below. 

This you can check and confirm manually.  Do the following:

  1. Open the Demo Form once then close the form,

  2. Open the Form in Design View.

  3. Right-click on the ListView Control, highlight the ListViewCtrl Object Option, and select Properties from the displayed list.

  4. Select the Tab with the Label Column Headers.

  5. There you can find the first Column Heading Name in a Text Control and above the Text control the Index Value 1.

  6. Point the Mouse Pointer to the right side of the Index number box, There a control will appear with arrows pointing to left, right directions.

  7. Click on the right-arrow to display other Column labels one by one in the Text control, with the change of index numbers.

  8. If you open and close the Form one more time the above Tab will have two sets of the same Column Heading 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 1, 2, 3 as running serial numbers. 

The Key value is of String data type, but not used for Column Headers, if needed it can be used.

Text Value is displayed on the control as Column Labels. 

Based on the data width size required to display below the column headings we can assign an approximate width value in Pixels. 

If the Text alignment value is omitted then the Left-alignment (0 - lvwAlignmentLeft) value is taken as default. It can be  Right Aligned (1 - lvwAlignmentRight) or Center Aligned (2 - lvwAlignmentCenter).

After loading the column heading labels the next step is to load the first-row first Column value of the first record.  Before that, 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 and a total of ten rows of some constant values with few changes for demo purposes.  This process we have put within the For...Next loop runs ten times, creating ten rows of data.

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 within the For...Next loop strKey = prfx & Cstr(intcounter) prepares the unique Key value for the first ListItem (first Column).  

All the parameters of ListItems.Add method is optional and the first three parameters Index, Key, Text is assigned in the same order as Column Headers and the other two parameters are an icon and a small icon image reference.

When the row value of the first column is assigned to the ListItem (lvwList.ListItems) this object reference is saved in the lvwItem object for easily calling the next level sub-object (ListSubItems object) to avoid writing a lengthy object reference:

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

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

The ListSubItems.Add() method's first three Parameters and passing order is the same as ListItem after that comes the Icon image reference followed by the Tooltip Text parameter. 

To the Key value of each column, I have added the For...Next Loop's control variable's current running value + some value to make it unique on all columns.  The Key parameter value can be omitted, but it is a good idea to get used to it.

The ListSubItems.Add() method is called four times to add the second column onwards into the ListView control.

These steps are repeated nine more times to load all ten sample records into the ListView Control.

The above ListView Control Demo database is attached for instant running and learning.

In the next session of our tutorial, we will learn how to search and find value from the list view control and how to rearrange the Columns like we do in Datasheet View.

  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:

MS-Access And Transfer SpreadSheet Command.

Introduction.

A very useful Command to transfer data between Microsoft Access and Excel using the Import/Export Options.  Here, we will concentrate on the Export aspect and what challenges we encounter after exporting the data, using some export Options out of several of them provided with this feature 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 is either acImport or acExport.
  2. For the second parameter SpreadSheet Type, in-built Options are available from 0 to 10, as an enumerated list, including transfer to Lotus Worksheets as well.

    The Enumerated List is given below:

    1. acSpreadsheetTypeExcel12xml  -  10
    2. acSpreadsheetTypeExcel12  -  9
    3. acSpreadsheetTypeExcel9  -  8
    4. acSpreadsheetTypeExcel8  -  8
    5. acSpreadsheetTypeExcel7  -  5
    6. acSpreadsheetTypeExcel5  -  5
    7. acSpreadsheetTypeExcel4  -  6
    8. acSpreadsheetTypeExcel3  -  0
    9. acSpreadsheetTypeLotusWJ2  -  4
    10. acSpreadsheetTypeLotusWk4  -  7
    11. acSpreadsheetTypeLotusWk3  -  3
    12. acSpreadsheetTypeLotusWk1  -  2

    You can use either the Enumerated List item or the numeric value it represents as the second parameter.

  3. The input Table or Query Name must be the third parameter.

  4. Next, the Output File Path Name.

  5. Next, the parameter True indicates that the Field Names to be output as the first Row Value in the Worksheet.

  6. The optional Range parameter is used along with the acImport Option only.

  7. The last optional parameter UseOA is not defined and not used.

Sample Transfer-Spreadsheet Command

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

The Option acSpreadsheetTypeExcel3 to 9 creates Excel File versions compatible with Excel 97 – 2003 format with .XLS extension, which can open in Excel 2007.  But, if we give the output file name with the .xlsx extension explicitly then the output file cannot be opened in Excel 2007 or in higher versions.

The acSpreadsheetTypeExcel12 Option creates an Excel File with .XLSB extension and opens in Excel 2007 and Higher Versions.  XLSB extension denotes that the workSheet is a Binary Coded File. When you have a large volume of records this format is ideal because of its reduced file size.

Option acSpreadsheetTypeExcel12xlm creates an Excel File with extension .xlsx and compatible with Excel 2007 and above.

The output option acSpreadSheetTypeExcel9 or an earlier version, when selected the output, doesn’t look attractive because of its old-fashioned Office Theme.  Like the sample Screenshot given below:

transfer SpreadSheet

We must open the output file in the current version of Excel and change the Format with the new Font and Font-size to make it look better and save it in the current version of the file.  Besides that if we explicitly add the .xlsx file extension,  to the target file parameter, assuming that the Target File will be created in Excel 2007 or higher Version Default Theme, the Excel file thus created will not open in Excel 2007 or higher versions.

But, with a small trick, we can solve all these problems and can save the output in the current version of Excel, whether it is 2007, 2010, 2013, or whatever version of Excel you have.  Doesn’t matter which version of WorkSheet Type you have selected in the TransferSpreadSheet command the output will be saved in the current version of Excel you have installed in your machine.

A Simple Solution.

  1. Create an Excel Workbook in the Current version of Excel and Save the file in the target location.

  2. Close the Workbook.

  3. Execute the above TransferSpreadSheet command with the saved Workbook file Pathname as the target file parameter.  The output worksheet will be saved in the target Workbook in a new Worksheet.

  4. When the WorkSheet is saved in the current Excel Version Workbook the Default Office Theme is automatically applied to the output WorkSheet and the Data Format looks better like the sample Image is given below:

We  written three slightly different functions to save the TransferSpreadSheet Command's output WorkSheet(s) in three different ways.

The Export2ExcelA() Function.

This 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 needs two parameters. The output Excel file’s target Path is the first parameter.  The second parameter is the input Table/Query name as the second parameter.  In this example, the function creates a WorkSheet using the Products Table and saves the output WorkSheet in a Workbook.

At the beginning of the Code, it checks the presence of an Excel file on the Disk with the specified name in the transfer spreadsheet command.  If not found then create a new WorkBook in the Current Version of Excel, with the same name of the input table/query name. The Workbook is then closed. If the specified file exists then the Output Worksheet is saved in that workBook.

Suppose, we don’t create the current version of Excel WorkBook and provide it as the target file for the Excel WorkSheet then what will happen?  Let us take a look at it.

  • If  don’t specify the Excel file extension like C:\My Documents\Products and select the SpreadSheetxl9 output type option then the command creates a new Excel file with XLS extension like Products.XLS. 

  • If we explicitly give the .xlsx file extension in the pathname and the SpreadSheet output type selected is SpreadsheetTypexl9 then a Target Excel output file will be created with that file extension. But, the file will not open in Excel 2007 or in higher Versions.

  • But, the WorkBook C:\My Documents\myBook.xlsx if already exist then the output will be saved in that Workbook as a separate WorkSheet. In this case, the Worksheet will be formatted with the current Excel Version Default Office theme.

  • This is the reason why we are creating a new WorkBook in the current version of Excel and saves it to the target location in advance. After saving the file we must close it and give the reference in the TransSpreadSheet Output file Path parameter.

  • If the target Workbook is already in use then it will end up with an error message; Source File not found

In the next step, the Workbook Pathname is passed as a parameter to the TransferSpreadsheet command.

Separate WorkSheets in a Single WorkBook.

There are times we need to create separate worksheets,  for data grouped on some criteria for distribution.  These probably need as separate WorkSheets in a single WorkBook or each workSheet in a different WorkBook.

We have used the Products Table of Northwind.accdb sample database for grouping of records on Product Category. 

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

The above Code creates a WorkBook and saves the file in the specified target location and then closes the WorkBook.

We have put the WorkBook creation code above the For . . . Next Loop and creates only a single workbook and saves all the Output workSheets created for Products Group in the same WorkBook. 

All Output Worksheets in Different WorkBook.

In this case, we will shift the Excel Workbook creation Code Segment within the For . . . Next Loop. It creates a different WorkBook, for each output WorkSheet for products group, and passes the WorkBook reference in the  Transfer Spreadsheet Command. All Worksheets will be saved in a separate Excel Workbook in the next Function.

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 the three Function Code with sample Data of Products table and Queries is attached for Download.


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

MS-Access and Creating Desktop Shortcuts.

Introduction.

The CreateShortcut() method of the Windows Script Object can be used for creating Desktop Shortcuts in Microsoft Access.  The Desktop Shortcut can launch frequently used Files like MS-Access, MS-Excel, MS-Word,  Text Document, and others from the Desktop. This fact is known to all of us and nothing new.  But, how do we do it from Access?

We have used the Popup() method of the Windows Script Object earlier for another Project.  We have created a new Message Box in Microsoft Access that closes itself after a specified time.  The Access MsgBox always requires the user to click on one of the displayed Buttons to close and continue with the Code execution.  Hope you have already tried it out and started using it in your Projects.

The VBA ShortCut() Function Prototype.

The simple VBA Function Code that creates a Desktop Shortcut is given below for a quick look at it. 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 with the above VBA Code with few changes on the highlighted portion of the Parameter Values.

  1. Replace the User with your own Windows User-Name.
  2. Create a Text File with some text in it and name the file as Hello.txt. 
  3. Save the File in one of your Folders. 
  4. Change the File Path Name correctly in the. Arguments Value shown highlighted.
  5. Change the Working Directory of your File in the next line.
  6. The rest of the Values can remain as they are.

The HotKey Ctrl+Alt+9 Keys Combination launches the Desktop Shortcut and opens the File for editing.

The Desktop Shortcut Icon.

In the IconLocation Parameter, check the number 130 at the end and it gives the required Desktop Icon.  This Numeric Value Range is from 0 to 305 and gives different Icons for your Desktop Shortcut.

The Number 130 gives the following Icon Image:

You can change the Shortcut Icon manually too.

  1. Right-Click on a Desktop Shortcut Icon and select the Properties from the displayed list.
  2. Click on the Change Icon Command Button on the Shortcut Tab.
  3. Select the required Icon, click OK to close the Icon List.
  4. Click Apply Command Button to update the change.

Icon Images List.

It displays the Icon Images of about 76 columns of 4 Images each.  To find a particular Icon’s number start counting from the left top items to the right and multiply the count by 4 and find the Icon’s Number.  I could not find any other way to find the Icon Image number easier than the above method. Check the Image given below:

The DesktopShortcut() Function.

Now, we are ready for our VBA Function that can accept the minimum three required Parameters, which can be passed to the Function at Call time, and Create a Desktop Shortcut.  The VBA Code 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 and the last two are Optional.  The Working Directory and HotKey Parameter Values are optional.

We have added Validation checks on the passed parameter values and Error Trapp Lines to avoid crashes due to unexpected Errors and to exit from the Function gracefully.

Sample Run of 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"


The TreeView Control Tutorial Session Links.

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

Message Box that Closes itself after Specified Time

Introduction.

The new Message Box in Microsoft Access is an interesting one, which was in all of our minds, I would say, for a long time.  A Message Box that closes itself, after displaying some useful information and that doesn’t need any response from the User to resume Code execution.  Now, we could do that. We will create a new  Message Box that disappears itself after completion of the specified time.

It can be used as a progress meter within a process, if it takes a long time to complete.  It can display useful information about the progress of the process, at intervals of certain fixed cycles of the process to the impatient User.  

This Message Box is based on the Popup() Method of the Windows Scripting Object.

This message box accepts all the parameters of the Access MsgBox function, except [helpfile] and [context] parameters.  In addition to that it has another optional parameter, the time value in seconds. The time value in seconds determines how long this message box stays visible on the screen.  When the time completes the message box disappears, it will not wait for you to click on a button to dismiss it.  If you click on one of the displayed buttons, it closes immediately and will not wait to complete the specified time.  So it works in both ways, if you omit the time parameter the message box will wait for a click from the user.

Assume that the time parameter value specified is 5 seconds, It will disappear automatically after 5 seconds.  This is good for displaying useful information to the user if some process or program takes too long to complete, or what process is going to execute next, and so on, to keep the user informed.

MesgBox() is the New Name.

We have given a suitable name MesgBox() to our new Message Box functionThe letter e is inserted between the letter M and s of our Access MsgBox function name.  I think it is easier to remember while writing Code.

The Access MsgBox parameters msgText, buttons, Title are all valid for the new MesgBox function.  Buttons like vbOkOnly, vbOkCancel, and others.  The Icons vbCritical, vbInformation, and others.  To specify the selected default button like in vbOkCancel+vbCritical+vbDefaultButton2.  In the new MesgBox function there is one more parameter the time value in Seconds, to specify how long the message box should remain on Screen before disappearing, expressed in Integer Seconds.

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

Exactly after n seconds, it will close itself.  The time value is not displayed on the message box.

Even if you omit the Optional button parameter the Ok button will appear by default.

Note: If you think that it is necessary that the User should know the duration of the message on the screen then concatenate the time value as part of the message.

If you click on one of the displayed Buttons before the full-delay time it will close immediately and will not wait for completing the specified time value as the parameter.  The time value parameter is optional if omitted or is zero value then it behaves like Access MsgBox.  The user should click on one of the displayed buttons to dismiss it.

Access MsgBox() and New MesgBox() Functions.

The new MesgBox() Function needs only a bare minimum of three lines of Code.  Before taking up the complete function Code let us compare Access MsgBox() and new MesgBox() Function Syntax.

1.  Access MsgBox stays on screen and the Code execution stops till the user clicks on one of the displayed Buttons.

NB: If you are on a Mobile device then to view the full length of the Code line touch on the Code Window and slide to the left.

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

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

2.  The new MesgBox closes itself and the Code execution continues after the time specified in seconds as one of its parameters or immediately after the user clicks on one of the displayed buttons, whichever happens first. The delay time expressed in Integer Seconds and is passed as the second parameter to the Function.

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

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

In the new MesgBox Function, the delay time value in seconds is the second parameter after the msgText parameter, the Buttons+Icon+defaultbutton is the third and the Title comes last.  Like the Access message box, all parameters are optional except the first one.

When the above MesgBox function call executes this message box appears with the Cancel Button already selected by default.  If the selected button option is acceptable to the user he can press Enter Key immediately to dismiss the message box or Click on his preferred choice of option.  If not then after 5 seconds the message box will close itself. 

Omitting Time Param works like Access MsgBox().

If selecting an option is mandatory then omit the Time Value parameter, or enter 0 (zero) value for the time parameter to keep the MesgBox on Screen till the User selects one of the displayed Option Buttons.

Note: Our new MesgBox Function is based on Microsoft Windows Script Host’s Popup() Method and accepts all the Access MsgBox Function Parameter Values  (except HelpFile and Context parameters) in a different order. Check the Syntax of Popup() Method given below:

'Syntax:

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

Since we have derived our new MesgBox Function 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.

MesgBox Function VBA Code.

Here is the VBA Code of the new MesgBox() Function.  It takes only three lines of code.

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 its corresponding Integer value is returned to the Calling Program.

You may call the MesgBox() function from the Immediate Window (Debug Window) with a different set of Optional Parameters and test it yourself in various ways to familiarize its usage.

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 allowed to close itself then 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 Access Global Module.

Copy the MesgBox() and the MesgBox_Example() VBA Code into a Global Module and save the Code.  Compile the Code to ensure that everything is in order.

In the MesgBox_example() the first MesgBox() Function Calling line is already enabled.  You may click somewhere within the Code and press the F5 Key to Run the Code.  It will display the message box with the Ok Button alone and after five seconds the MesgBox will close itself.  The returned value is –1 in Opt Variable.

The returned value is tested in the next section and displays an appropriate message.  In this particular message line under the Case –1 testthe time value parameter is omitted and the message box behaves like the Access MsgBox.  When the time parameter value is omitted or zero (0) then you must click on a button on the message box to close it. 

Run the same option a second time and this time click on the Ok button before the message box closes. The Clicked button-value is returned to the Opt Variable.  The Ok button-click returns the value 1.  Based on the returned value it displays an appropriate message and closes it after 3 seconds.

Disable the tried-out line by inserting the comment symbol at the beginning and enable the next line by removing the comment symbol.  Test the enabled line with different buttons, Icons by repeating the above method.

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 it is Windows Script-based control, even if you minimize the Access Application Window the MesgBox will appear on the Windows Desktop.

  2. If the Time Value parameter is omitted then the user needs to click on a Button, like Access MsgBox to dismiss the MesgBox Control. In between if the user clicks somewhere other than the MesgBox control, it will disappear and goes behind the Access Application Window, and stays on the Windows Desktop.

  3. Our VBA Program is still in executing state and waiting for the response of the User on the MesgBox Control. At this time if you try to close the Database it will ignore the attempt and will not provide any clue.  But, you may use the Exit Access Option from Office Button to Shutdown Access Application altogether.

We are familiar with Microsoft Windows Common Controls, like TreeView, ListView, ImageList, and others.  The above function works with Windows Script Object’s Popup() method.  Unlike Windows Common Control (the MSCOMCTL.OCX) you don’t need to attach the Windows Script Host Object Model (C:\Windows\SysWOW64\wshom.ocx file) to the Reference Library List.

If you are facing any issues in using this Windows feature please visit the Microsoft Support Page for suggestions to correct your issue.

Here is the Code of MesgBox() Function with Error-trap lines inserted.

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

Since you understood the advantages and disadvantages of both the functions MsgBox and MesgBox use them sparingly.  If you share your Application with the new MesgBox() Function then ensure that it works in the new location.

The TreeView Control Tutorial Session Links.

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

Want to Post Free Ads on the Web

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 Graph Charts Query VBA msaccessQuery Calculation Event List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form ImageList Control Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button ListView Control 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 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