LEARN MS-ACCESS TIPS AND TRICKS: Combo Boxes

Showing posts with label Combo Boxes. Show all posts

TreeView ImageCombo Drop-Down Access Menu

a.p.r. pillaiNovember 02, 2020Combo Boxes, ImageList, ImageList Control, Nodes, Objects No comments

Introduction.

In this session of the TreeView Control Tutorial, we will focus on programming the ImageComboBox Control. Our objective is to build an MS Access project drop-down menu using an ImageCombo control. Additionally, we will use a second ImageComboBox control to display the images along with their key values from the ImageList control. Both ImageComboBox controls will draw their images from a common ImageList control, into which the images were manually uploaded from the computer during an earlier session of this tutorial series.

The sessions of the TreeView Control Tutorial covered so far are as follows:

The TreeView Control Tutorial Session Links.

The Demo View of Both ImageComboBoxes Expanded.

The completed MS-Access Project Drop-Down Menu Image is given below:

The Design View Image of the above Form is given below:

The Drop-Down ImageComboBox Source Data.

The source data for the new MS Access Project Drop-Down Menu is taken from our earlier Access Menu Project.

If you have not already done so, please download the demo database using the link provided in item 4 above. Once downloaded, you will have all the required data objects to proceed with this session.

The database includes three tables: Categories, Products, and Menu. It also contains two forms to display category and product data, along with a parameter form for filtering report data.

Additionally, we will utilize two forms—frmMenu and frmMenu2—which were introduced in our earlier tutorial sessions.

You will also find two reports for previewing the Categories and Products data items.

In addition, there are two macros designed to display simple messages. These macros can also be used to sequence action queries for data processing, particularly when working with more complex reports. In earlier tutorial sessions, we executed these actions by selecting options from the TreeView Control

Project Menu.

We will also need these objects here, as we are about to create a new drop-down menu using the ImageComboBox Control. The goal is to ensure that all objects can be opened through selections in the new drop-down menu, in the same way we accessed them earlier on the frmMenu2 form using the TreeView Control within this same database.

The Menu table image is given below for your reference.

Preparing for the Design of the Drop-Down Menu Form.

Open the Menu Form in Design View, and you will see two menu-related controls:

An ImageList control
An ImageComboBox control

Additionally, another ImageComboBox control has been placed on the right side of the form to display the images stored in the ImageList.

To set this up:

Copy the ImageList Control
- Open the frmMenu2 form, copy the ImageList control, and paste it onto a new form named frmMenu3Combo.
- This ImageList control already contains images that were manually uploaded from the computer in an earlier tutorial session.
- Open the Property Sheet: right-click the ImageList control, select ImageListCtl Object, and then choose Properties to review the images and their key names.
Add the First ImageComboBox Control
- From the ActiveX Controls group, insert a Microsoft ImageComboBox Control onto the left side of the form.
- Rename this control to imgCombo1. This will serve as the drop-down menu.
Add the Second ImageComboBox Control
- Insert another ImageComboBox control on the right side of the form.
- Rename this control to imgCombo2. This control displays the images and their key names from the ImageList0 control in a drop-down list.
Add a Label
- Place a label above the second ImageComboBox control.
- Set its caption to Image List.

The Images Listed in the Image Combo Box control.

We will begin with the second control, imgCombo2, which is used to display the list of images from the ImageList control. Once you understand this code, creating the drop-down menu will become much easier.

The VBA code for the frmMenu3Combo form module has been divided into two parts. Let us start with the first part and examine its contents.

In the global declaration area, the main object variables are defined. The Form_Load() event procedure initializes the ImageList control on the form and assigns it to the object variable 'objImgList'. It then calls the cboImageList() subroutine, which loads the images from the ImageList control into the second ImageComboBox control (imgCombo2).

Now, let’s take a closer look at the code.

The first-part vba code, with the Form_Load() and cboImageList() subroutines listed below:

Dim imgcombo1 As MSComctlLib.ImageCombo
Dim imgCombo2 As MSComctlLib.ImageCombo
Dim objimgList As MSComctlLib.ImageList
Const KeyPrfx As String = "X"

Private Sub Form_Load()

Set objimgList = Me.ImageList0.Object

cboImageList 'load imagelist-combo

'CreateMenu 'Create Drop-Down Menu
 
End Sub

Private Sub cboImageList()
Dim j As Integer
Dim strText As String

Set imgCombo2 = Me.ImageCombo2.Object
imgCombo2.ImageList = objimgList

For j = 1 To objimgList.ListImages.Count
    strText = objimgList.ListImages(j).Key
    imgCombo2.ComboItems.Add , , strText,j,,j
Next
    imgCombo2.ComboItems(1).Selected = True
End Sub

VBA Code Review.

In the global declaration area, we have defined the following variables:

imgCombo1 – the ImageComboBox control used for the project menu.
imgCombo2 – the ImageComboBox control used for displaying images from the ImageList control.
objImgList – an object variable representing the ImageList control on the form.
KeyPrfx – a constant variable assigned the character "X".

Within the Form_Load() event procedure, the objImgList variable is initialized with the ImageList control on the form using the following statement:


Set objImgList = Me.ImageList0.Object

This allows all pre-loaded images in the ImageList control to be accessed through the objImgList object.

Next, the procedure calls the cboImageList() subroutine, which adds all the images to the imgCombo1 control.

For now, the call to the CreateMenu() subroutine has been commented out.

Inside the cboImageList() subroutine, two variables are declared.

Next, the following statement assigns the second ImageComboBox control on the form to the object variable imgCombo2:


Set imgCombo2 = Me.ImageCombo2.Object

Similar to the TreeView control, the imgCombo2 control includes an ImageList property, which is used to link it with the ImageList control. This allows the ImageComboBox to access the properties of the ImageList. The statement below establishes that link:


imgCombo2.ImageList = objImgList

After this, a 'For…Next loop' is executed to iterate through the collection of images contained in the ImageList control. Each image is then processed and added to the imgCombo2 drop-down list.

The first item from the ImageList has its key value ("form_close") stored in the variable strText. In this example, we are using the Key value of the ImageList item as the text (or description) for the corresponding image in the ImageComboBox control.

Since no descriptive text is available apart from the key, it serves as the most suitable option. The Tag property, on the other hand, is left empty because it is for a different purpose later when working with the drop-down menu.

The next statement is an important one that we need to examine closely: the Add method of the ImageComboBox control. This method is used to add items (with images) to the ImageComboBox list.

The general syntax of the statement is as follows:

imgCombo2.ComboItems.Add [Index],[Key],[Text],[Image],[SelImage],[Indentation]

All the parameters of the Add() method are optional. For our initial test run of this control, we will supply values only for the [Text], [Image], and [Indentation] parameters.

After previewing the result of this first test run (the image list view), we will discontinue using the [Indentation] parameter for this ImageCombo control, since it is not required for our intended design.

Note: Keep in mind that we will later need the Indentation property when creating the drop-down menu. This will allow the menu items to visually resemble Root Nodes and Child Nodes, just as they appear in the TreeView control.

At that stage, we will also make use of the [Key] parameter—assigning it the same value as the Text parameter—so that we can reliably access a specific menu item’s Tag property value.

Image List with incrementing Indentation Param Setting.

The result of the first test run, displaying the image list in imgCombo2, appears as shown in the illustration below. This output is achieved by applying incremental values to the Indentation property for each item.

From this trial run, the effect of indentation is clearly visible: each successive item is shifted slightly to the right, one step further than the previous one. This feature is useful for positioning our project menu items so that they visually resemble Root-Level and Child Nodes, similar to the structure in the TreeView control.

After the strText value ("form_close"), The first variable j refers to the ImageList’s index number. The [SelImage] parameter is skipped in our test, and the next occurrence of j is used to set the Indentation level for each list item when displayed in the ComboBox.

For the initial test run, after reviewing the output, you may remove all parameters that come after the image index value, as they are not required for our purpose.

The next statement is:


imgCombo2.ComboItems(1).Selected = True

This selects the first item in the ComboBox. When the Combobox item is selected programmatically (through code), the Change() event of the ImageCombo control is triggered. However, when an item is selected manually on the form, this event is not fired. To address this, the Update() event is used; it ignores manual updates and instead attempts to invoke the event explicitly through code.

Save the form frmMenu3Combo and open it in Normal View. Expand the second ImageList ComboBox control and view the result. Remove the commas and the variable < j > at the end, after the first variable < j >, retained for the ImageList index number.

The VBA Code of the Project Drop-Down Menu.

Now, let us move on to the second part of the form module VBA code, where we will learn how to create the Access Drop-Down Menu. In this section, we will also see how to open Forms, Reports, and Macros by selecting an item from the ComboBox control.

The second part of the VBA code consists of two main procedures:

CreateMenu() – a subroutine that builds the project drop-down menu by adding menu items to the ImageComboBox control.
ImageCombo1_Click() – the event procedure that responds when a user selects a menu option, triggering the opening of the corresponding Access object.

The code for these procedures is shown below:

Private Sub CreateMenu()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strKey As String
Dim strText As String
Dim typ As Integer

Set imgcombo1 = Me.ImageCombo1.Object
imgcombo1.ImageList = objimgList

strSQL = "SELECT ID, Desc, PID, Type,Macro,Form,Report FROM Menu;"

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

Do While Not rst.EOF And Not rst.BOF
    If Len(Trim(Nz(rst!PID, ""))) = 0 Then
        strKey = KeyPrfx & CStr(rst!ID)
        strText = rst!Desc
        imgcombo1.ComboItems.Add , strKey, strText, 1, 2, 1 ' image index 1,2([image],[selectedimage])
        'imgcombo1.ComboItems.Add , strKey, strText, "folder_close", "folder_open", 1
    Else
        strKey = KeyPrfx & CStr(rst!ID)
        strText = rst!Desc
        imgcombo1.ComboItems.Add , strKey, strText, 4, 5, 4 'last param is spacing
        'imgcombo1.ComboItems.Add , strKey, strText, "left_arrow", "right_arrow", 4
     
        'Check for the presense of Type Code
        If Nz(rst!Type, 0) > 0 Then
                typ = rst!Type
                With imgcombo1.ComboItems
            Select Case typ
                'save type Code & Form/Report/Macro Name in Tag Property
                Case 1
                    .Item(strKey).Tag = typ & rst!Form
                Case 2
                    .Item(strKey).Tag = typ & rst!Report
                Case 3
                    .Item(strKey).Tag = typ & rst!Macro
            End Select
                End With
        End If
        
    End If
    rst.MoveNext
Loop
rst.Close
imgcombo1.ComboItems.Item(1).Selected = True
End Sub

Private Sub ImageCombo1_Click()
Dim strObject As String
Dim strTag As String
Dim typ As Integer

strTag = ImageCombo1.SelectedItem.Tag
typ = Val(strTag)
strObject = Mid(strTag, 2)

Select Case typ
    Case 1
        DoCmd.OpenForm strObject, acNormal
    Case 2
        DoCmd.OpenReport strObject, acViewPreview
    Case 3
        DoCmd.RunMacro strObject
End Select

End Sub

Before diving into the VBA code, take a moment to review the Table Image (the third image from the top of this page), especially if you have not already done so in the earlier session on Access Project Menu creation.

The table fields are defined as follows:

ID – An AutoNumber field that provides a unique identifier for each record.
Desc – Contains either the object type group names (Forms, Reports, Macros) or the actual object names of forms, reports, and macros.
PID (Parent ID) – Empty for the object group names (Forms, Reports, Macros). These empty PID values ensure that the group names appear at the leftmost position in the ImageComboBox drop-down menu, with one-character space indentation. All other items (child objects) are indented by four character spaces.

This indentation technique makes the menu items visually resemble Root-Level and Child-Level Nodes, similar to the structure in a TreeView control—except that the connecting tree lines will not be displayed.

In the ImageComboBox control, the selected item’s image is positioned at the leftmost side. The Group items (such as Forms, Reports, Macros) appear with a one-character indentation, while the child items under each group are indented by four character spaces.

The PID field plays an important role in this arrangement.

If the PID field value is empty, the record is treated as a Group Name (e.g., Forms, Reports, Macros).
If the PID field contains a value, the record is treated as an Access Object Name, which must be opened when the user clicks it. These items are displayed as child members of their respective groups.

Although the actual key values in the PID field are not significant in this context, we need the field to determine the hierarchy. Alternatively, the Type field can also serve this purpose.

The Type field contains the object type codes:

1 – Form
2 – Report
3 – Macro

Based on this code, the next three fields—Form, Report, and Macro—store the actual object names. For clarity in design, these names have been kept in separate fields, though they could also be stored in a single column.

The Type Code and Object Name pair (say 2rptCategories) will be saved in the ImageComboBox’s Tag Property.

The CreateMenu() Subroutine.

Now, let’s move on to the VBA code of the CreateMenu() subroutine.

At the start of the procedure, the Database and other working variables are declared.

The imgCombo1 object variable is initialized with the Me.ImageCombo1 control on the form.
The 'imgCombo1.ImageList' property is then assigned the reference of the objImgList object, enabling direct access to the ImageList’s Index numbers and Key values.
The Menu table is opened as a recordset using an SQL string.
For each record, the PID field is checked:
- If PID is empty, the record is treated as an object group name (e.g., Forms, Reports, Macros).
- In this case, the ID value is prefixed with the constant "X" and stored in the variable strKey, while the Desc field value is stored in strText.
The Add() method of the ImageComboBox control is then called, and the first item is added to the drop-down menu.

imgcombo1.ComboItems.Add , strKey, strText, 1, 2, 1

The first parameter (Index) is omitted—Access assigns it automatically.

Key: strKey contains the ID value prefixed with the constant X (e.g., X1).
Text: strText holds the Desc field value.
Image / SelImage: 1 is the index (or "folder_close" key) of the first ImageList image; 2 is the index (or "folder_open" key) of the second image.
Indentation: 1 Indents the item one level.

Example:


' Add a group item (e.g., "Forms")
imgCombo1.ComboItems.Add , strKey, strText, 1, 2, 1
' …or with image keys:
' imgCombo1.ComboItems.Add , strKey, strText, "folder_close", "folder_open", 1

Note: To confirm image index order, refer to the ImageCombo2 display you created earlier. If you like, you can also prepend the image index to the Text (not the Key) for clarity—for example: "1 form_close", "2 form_open".

@@@If the PID field value is non-zero, then the record represents an actual menu option (not a group header). In this case, the item is added under the Else clause.

The process is similar to how we added the group items earlier, but with a few differences:

For the [Image] and [SelImage] parameters, we use the ImageList item index values 4 and 5.
The Indentation parameter is set to 4 character spaces, so that these items appear as child members under their respective group headings.

Example:


' Add a child item (e.g., an actual Form, Report, or Macro)
imgCombo1.ComboItems.Add , strKey, strText, 4, 5, 4

This ensures that the group names (e.g., Forms, Reports, Macros) stay at the root level, while their associated objects are properly indented beneath them, visually simulating a TreeView hierarchy in the ImageComboBox.

Within the ImageCombo item’s Add() method, under the Else clause, we also need to store the Access Object Name along with its Type Code in the item’s Tag property.

For example:


' Save object details (Form Name + Type Code) in the Tag property
ImageCombo1.ComboItems.Item(strKey).Tag = "frmData Entry;1"

Here:

"frmData Entry" is the object name (in this case, a form).
1 is the Type Code, which identifies it as a form (2 = report, 3 = macro).

When the user selects this item from the drop-down menu, the Click() event of the ImageComboBox fires. In this event procedure:

The Tag property value of the selected item is retrieved.
The value is split into the object name and the Type Code.
The Type Code is checked:
- If it equals 1, the item is a form, and the form name is opened using:
```
DoCmd.OpenForm "frmData Entry"
```

This same logic will later be extended for Reports and Macros, using their respective Type Codes.

@@@With this, all the records from the Menu Table are successfully added to the ImageComboBox control.

The statement:


imgCombo1.ComboItems.Item(1).Selected = True

sets the first item as the default selection in the ImageComboBox. When this line of code executes, the Change() event is triggered; however, selecting an item directly in Form View does not fire the Change event.

Note: Before running the form to test the drop-down menu, make sure to remove the comment symbol from the CreateMenu call in the Form_Load() event procedure. This line was commented out earlier during trial runs when we were testing image display in the ImageCombo2 control.

The ImageCombo1_Click() event fires whenever the user selects an item from the drop-down menu. In this procedure, the selected item’s Tag property is parsed to retrieve the Type Code and the Object Name, and the corresponding Access object is opened using:


DoCmd.ObjectType ObjectName

Finally, for your reference and practice, the Demo Database (ProjectMenuV221.accdb) is provided in .zip format for download.

Download ProjectMenuV221.zip

DICTIONARY OBJECT

Display Records from Dictionary to Form

a.p.r. pillaiMarch 10, 2019Add, Array, Collection Object, Combo Boxes, Command Button, Dictionary Object, Forms 2 comments

Introduction.

We will now perform a similar exercise to what we did earlier with the Collection Object—displaying table records on a form based on a key value selected from a Combo Box.

This time, we will use a Dictionary Object to store Employee Records, using the Last Name as the Key.

Design a sample form:

Create a Combo Box in the Header Section (to list all employees' last names).
A few TextBox controls in the Detail Section (to display the selected employee’s information).

We will then write the Form-based event procedures in the Form Class Module.

Load data records from a Table or Query into the Dictionary Object.
Retrieve a specific record from the Dictionary, based on the selected key (Last Name) from the Combo Box.
Populate the TextBoxes on the form with the retrieved record values.

Note: You can download a Demo Database, with the Form and VBA Code, from the bottom of this page.

Let us start with the preparation steps so that you will know what it takes to complete this Project. You will be better informed of the whole process if you plan to implement this method in one of your own projects.

The Employees Table for Sample Data.

We need some data to load into the Dictionary Object.

Import the Employees Table from the Northwind sample database.
Copy and paste the following SQL String into the SQL editing window and save it with the name: EmployeesQ
```
SELECT Employees.[Last Name], Employees.[First Name], Employees.[E-mail Address], Employees.[Job Title], Employees.[Business Phone], Employees.[Home Phone]
FROM Employees;
```
The Sample Form with a Few Textboxes and a Combo Box.
We will take only a few fields of data from the Employees table. We can quickly design a Form with the field names from EmployeesQ, but without attaching the EmployeeQ to the Form as a record source. The next steps will be needed to add Text Boxes with correct data field names, without typing them into the Name Property of the Text Boxes.
NB: You can give any name to the TextBoxes; it works with any name.
Select the Design Form option from the Forms Group of the Create Menu. It will open a Blank Form.
Right-click on the Form and select Form Header/Footer to insert Header and Footer sections to the Form.
The Sample Design of the Form is given below.
Right-click to open Large Image in New Tab/Window.
Click on the Detail Section of the Form to make it an active Section.
Now, we will add six Text Boxes from the Employee record with field names as the Text Box Names.
Click on the 'Add Existing Field' Button from the Tools Buttons Group in the Design Menu.
Find the Employees Table and click on the [+] Symbol to show the Employees Table Fields.
Double-click on the following list of Fields, one by one, to insert them into the Detail Section of the Form:
- Last Name
- First Name
- E-mail Address
- Job Title
- Business Phone
- Home Phone
NB: This is an Unbound Form, and the inserted Field controls also must be Unbound Text Boxes.

Keep the Text Box's Name Property Value (Field Name) and remove the Control Source Property Value.
Click on the first Text Box to select it.
Display the Property Sheet (F4) of the selected Text Box.
Remove the data Field Name from the Control Source Property to make the text box Unbound. Ensure that the Name Property Value remains intact.
Remove other Text Box's Control Source Property Values.
Next, we need a Combo Box on the header of the Form with the list of the Last names of Employees.
Select the Combo-Box control from the Controls Group under the Design Menu and place the Combo-Box control in the Header Section of the Form. If the Control Wizard is active, then follow steps 14 to 18, and go to step 19
If Control Wizard is on, then select the first option and click Next.
Select the Query Option on the next screen, select EmployeeQ, and click Next.
In the next Screen, double-click on the Last Name to select and insert it into the right panel, and click Next.
Select the Last Name in the first text box to sort Last Names in Ascending Order, and click Next.
On the next screen, click Finish.
Change the Name Property Value of the Combo-Box to cboLastName.
Find the Limit to List Property of the Combo Box and change the Value to Yes.
Copy and paste the following SQL into the Row Source Property of the Combo-Box:
```
SELECT EmployeesQ![Last Name] FROM EmployeesQ;  
```
Insert a Command Button in the Footer Section of the Form.
Change the Caption of the Command Button to Exit and the Name Property value to cmdClose.
Select the View Code Button from the Tools Group to display the Class Module of the Form.

Highlight the entire VBA Code below, copy and paste it into the Form’s Class Module, overwriting the existing lines of Code:

The Form's Class Module VBA Code.

Option Compare Database
Option Explicit

Private D As Object
Dim txtBox() As String

Private Sub Form_Load()
Dim db As Database
Dim rst As Recordset
Dim Rec() As Variant
Dim fldCount As Long, ctl As Control
Dim k As Long, frm As Form, Sec As Section
Dim strKey As String

'Restore the Form to it's actual design size
DoCmd.Restore

'instantiate Dictionary Object
   Set D = CreateObject("Scripting.Dictionary")
   

'Open Recordset Source to save in Dictionary
   Set db = CurrentDb
   Set rst = db.OpenRecordset("EmployeesQ", dbOpenDynaset)
'get recordset fields count
   fldCount = rst.Fields.Count - 1

'Redimension Field Names Array (Rec) for number of fields in Table
   ReDim Rec(0 To fldCount) As Variant

   'Add records to Dictionary Object
   Do While Not rst.EOF
     'Get current record field values into Rec Variant Array
     For k = 0 To fldCount
        Rec(k) = rst.Fields(k).Value
     Next
     'Last Name as Dictionary Key
     strKey = rst.Fields("[Last Name]").Value
     
    'Add record to Dictionary Object with 'Last Name' Key
     D.Add strKey, Rec
     rst.MoveNext
   Loop
   
   'Set current Form
   Set frm = Me
   
'Set Detail Section of Form to look for Text Boxes
   Set Sec = frm.Section(acDetail)
   
'Redim txtBox Array to save Textbox Names on the Form
'To display field values
   ReDim txtBox(0 To fldCount) As String
  
  'Get Text Box Names,from Detail Section of Form, and save them into Array.
  'this will be used in the ComboBox AfterUpdate Event
  k = 0
  For Each ctl In Sec.Controls
     If TypeName(ctl) = "TextBox" Then
        txtBox(k) = ctl.Name
        k = k + 1
     End If
  Next
   
   rst.Close
   Set rst = Nothing
   Set db = Nothing
End Sub


Private Sub cboLastName_AfterUpdate()
  Dim strD As String, R As Variant
  Dim j As Long
  Dim L As Long
  Dim H As Long

'Get Selected Key from ComboBox
 strD = Me![cboLastName]
  
  'Retrieve the record from Dictionary
  'using KEY and load the field
  'Values into the Variant Array
  R = D(strD)
  L = LBound(R)
  H = UBound(R)
'Read Field Values from Array and display
'them into it's corresponding Textbox names on the Form
  For j = L To H
    Me(txtBox(j)) = R(j)
  Next
  Me.Refresh

End Sub

Private Sub cmdClose_Click()
  DoCmd.Close
End Sub


Private Sub Form_Unload(Cancel As Integer)
  'Clear Dictionary Object from Memory
  Set D = Nothing
End Sub

Save the Form with the name Dict_Employees or any other name you prefer.

How it All Works Together.

There are four Subroutines in the above Code.

Private Sub Form_Load() Event Procedure.
Private Sub cboLastName_AfterUpdate()
Private Sub cmdClose_Click()
Private Sub Form_Unload(Cancel As Integer)

In the Declaration area of the Module, an Object variable D is defined for the Dictionary Object. The txtbox() array variable is declared for storing the Text Box names from the Form’s Detail Section and will be used for displaying the selected record’s field values.

The Dictionary Object is created in the Form_Load Event Procedure,

Immediately after instantiating the Dictionary Object, the EmployeesQ Recordset is open for adding records to the Dictionary Object. A Select Query is created to pick only selected fields of the Employees Table, rather than using the Employees Table directly.

The Rec() Variant Array is re-dimensioned for the number of fields in the record. Each field value is added to the Rec Variant Array element, and the whole array is inserted into the dictionary Object as a single Item (a record), with the Last Name field value as a unique Dictionary Key.

In the next stage of the code, the Dict_Employees Form’s Detail Section area is scanned to identify text boxes, and their names are saved into the textbox() array for use in the cboLastName_AfterUpdate() Event Procedure. The txtbox() Array was declared in the Global area of the Module.

When the user selects a name from the Combo Box, the Private Sub cboLastName_AfterUpdate() Event Procedure is executed. The Form's Normal View image is given below:

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

When the user selects Last Name from the cboLastName Combo Box, it is saved into the String Variable strD. The statement R=D (strD) reads the corresponding employee record field values array into the Variant Variable R.

We have not explicitly defined the Variant Variable R as an Array. But when we read an Array of values from a Dictionary Object Item into it, it automatically re-dimensions itself for the number of required elements and loads each field value into its elements.

The next two steps determine the Array Index range values.

Within the For ... Next Loop, the record values are displayed in the Form Text Boxes, using the test box names saved in the Form_Load() Event Procedure.

You may select other names from the Combo box to display their details on the Form. When you are ready to close the Form, click on the Exit Command Button.

When the user clicks on the Exit Command Button, the Form is closed. Before closing the Form, the Form_Unload Event is triggered, and the Dictionary Object is cleared from Memory.

Download the Demo Database.

Download Dictionary2003.zip

Download Dictionary2007.zip

MS-ACCESS CLASS MODULE

Table Records in Collection Object and Form

a.p.r. pillaiJanuary 23, 2019Array, Collection Object, Combo Boxes, Controls, Form 2 comments

Introduction.

Data records from a table will be added as items in a Collection object, with the Description field values used as the Key parameter for each item.

The source values for a ComboBox on a form are also taken from the Description field of the table. Selecting an item from the ComboBox will use its value as the key to retrieve the corresponding record from the Collection object and display the field values in unbound text boxes on the form.

For experimentation, we have created a small table, Table 1, with a few sample records.

The Table image is given below:

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

The Table Structure image is given below for reference.

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

The Sample Demo Form.

We have designed a small form with a Combo box on the header of the Form. The Row Source Property of the Combo box is set with the following SQL:

SELECT [Table1].[Desc] FROM Table1 ORDER BY [Desc];

To pick the Desc field value as the Row Source of the Combo Box.

Four text boxes with their Child Labels are added in the Detail Section of the Form. The Text Box Name Property values are set with the same name as each field on the Table, for easier reference in the Program, in the same order, they appear on the Table.

The design image of the frmtable1 is given below:

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

The Normal View of the Form frmTable1, with data displayed from the Collection Object, is given below for reference. The Combo box contents are also shown in the Form.

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

The following code runs in the frmTable1 Form’s Class Module. If you have already designed the above Form, ensure that the text boxes are set with the field name of the Table structure shown above. The Combo box name is cmbDesc. You can download a database with the Code from the link given at the end of this Page.

Form Module Code.

Copy and paste the following Code into the frmTable1’s Class Module:

Option Compare Database
Option Explicit

Private Coll As Collection
Dim txtBox() As String

Private Sub Form_Load()
Dim db As Database
Dim rst As Recordset
Dim flds As Long, k As Long
Dim frm As Form, Sec As Section, ctl As Control
Dim Rec() As Variant, strKey As String

'Open Table1 to upload records into Collection Object
     Set db = CurrentDb
     Set rst = db.OpenRecordset("Table1", dbOpenDynaset)
   
'get record fields count
     flds = rst.Fields.Count - 1

'Set Detail Section of Form to scan for Text Boxes
     Set frm = Me
     Set Sec = frm.Section(acDetail)

'Redim txtBox() to save Textbox names from Form
'to display field values
     ReDim txtBox(0 To flds) As String
  
'Get Text Box Names & save into txtBox() Array from Detail Section of Form
'this will be used in ComboBox AfterUpdate Event Procedure
     k = 0
     For Each ctl In Sec.Controls
       If TypeName(ctl) = "TextBox" Then
          txtBox(k) = ctl.Name
          k = k + 1
       End If
     Next
'instantiate Collection Object
    Set Coll = New Collection
'Redimension Rec Array for number of fields in Table
    ReDim Rec(0 To flds) As Variant
   
'Add each record into the Collection Object
   Do While Not rst.EOF
'Get current record field values into Rec Variant Array
     For k = 0 To flds
        Rec(k) = rst.Fields(k).Value
     Next
'Description Field Value as Key
     strKey = rst.Fields("Desc").Value
     
'Add record to the Collection Object with Key
     Coll.Add Rec, strKey
     rst.MoveNext
   Loop
   rst.Close
   Set rst = Nothing
   Set db = Nothing
End Sub
Private Sub cmbDesc_AfterUpdate()
  Dim strD As String, R As Variant
  Dim j As Long, L As Long, H As Long

'Get Selected Collection Key from ComboBox
 strD = Me![cmbDesc]
  
  'Retrieve the record from Collection
  'using Collection KEY and save the field
  'Values into the Variant Variable
  R = Coll(strD)
  
  L = LBound(R)
  H = UBound(R)
'Add Field Values into corresponding Text Boxes
  For j = L To H
    Me(txtBox(j)) = R(j)
  Next
  Me.Refresh
End Sub


Private Sub Form_Unload(Cancel As Integer)
  'Remove Collection from Memory on Form Close
  Set Coll = Nothing
End Sub

This is how it works:

All Records from the Table are added as the Collection Object Items in the Form_Load() Event Procedure. The record description Field (Desc) value is used as the Key parameter value of the Item method.
The Desc field values are also used as Combo Box List values on the Form.
When the user selects an item from the Combo Box, the cmbDesc_AfterUpdate() event procedure retrieves the record from the Collection Object, using the Key value chosen from the combo box, and displays the record field values in the Text Boxes on the Form.
The Objects are cleared from memory when the Form is closed.

In the declaration area of the module, we declare the Collection object Coll and an empty array txtBox().

Within the Form_Load event procedure, the Database object db and Recordset object rst are declared. Next, the variable flds is declared to store the count of record fields.

Form, Section, and Control objects are also declared. These are used to locate text boxes on the form, collect their Name property values, and store them in the txtBox array.

A Variant array Rec() is used to temporarily hold record field values before adding them as a single record item to the Collection object.

The string variable strKey is used to assign the record’s Description field value, which will serve as the key for the current record in the Collection object. Each key in the Collection must be unique.

Note: The VBA code lines are commented appropriately. Go through the code line by line a second time to fully understand its purpose.

The Form_Load() event procedure does the following:

The procedure opens Table1 and reads the field count of the first record, storing it in the variable flds.
The form’s Detail Section is assigned to the sec object variable.
All TextBox controls within the Detail Section of the form are located, and their Name properties are collected into the txtBox() array.
Next, the Collection object is instantiated as the object variable Coll.
At the start of the Do While…Loop, the field values of the current record are added to the Rec Variant array.
The Description (Desc) field value is saved into the string variable strKey.
The statement Coll.Add Rec, strKey adds the current record’s values from Rec as a new item in the Collection, using strKey as the key.
The statement rst.MoveNext advances the record pointer to the next record, and this process repeats until all records in the table have been added to the Collection.
Finally, the Table1 recordset is closed.

At the Form Load Event Procedure, all the records in the Table are loaded into the Collection Object. The Combo Box in the Form’s Header Section is populated with the values from the table’s Description field.

When a user selects an item from the Combo Box, the cmbDesc_AfterUpdate() event procedure is triggered.

The selected Combo Box value is stored in the variable strD, which is then used in the statement R = Coll(strD) to retrieve the corresponding record array from the Collection using strD as the Key. Alternatively, R = Coll.Item(strD) works equally well.

Notice that the Variant variable R is not explicitly declared as an array. VBA automatically determines the correct data type and dimensions based on the record retrieved from the Collection.

The next steps in the VBA code calculate the lower and upper bounds of the array and use them as control values in a For … Next loop. This loop copies the record field values into the corresponding Text Boxes on the Form, using the Text Box names stored in the txtBox array.

Download the Demo Database.

Download TableColl2003.zip

Download TableColl2007.zip

Updating Combobox when Not in List is Active

a.p.r. pillaiDecember 06, 2013Combo Boxes No comments

Introduction.

This is all about the Limit to List Property setting of the Combo Box. When this property value is set to Yes, you cannot type a new value, other than what you already have, into the combo box control. The user is forced to select/type an existing item from the drop-down list of the combo box.

Typing a new value in the combo box control will trigger an error message and will force you to select an item from the existing list.

Names of Months or Weeks etc., in a Combobox, are of constant nature, and never change or addition is not required. But, names of new employees or names of Products, clients, and so on need to be added to the combo box list, as and when the need arises.

A new item can only be added to the Source Table directly through a separate Form. The user has to open the source table editing form and add a new record. Even if the user is prepared to do that the new item added to the source table will not appear on the combo box list on the form immediately. The user must close and open the form with the combo box to refresh and make the new item appear on the list. It is not a user-friendly approach and a lot of time is wasted.

An Easy Solution.

But, we can make it very easy for the user with a VBA Program. The user can type a new value into the combo box itself and with his/her permission, we can add the new item in the source table and refresh the combo box instantly.

When a new value is typed into the combo box the Limit to List property setting will run our program from the On Not in List Event Procedure. After getting confirmation from the user we can add the new item to the combo box source table and refresh the combo box.

The VBA Program.

The following program adds ProductID and Product Name into the Products Table and refreshes the cboProd Combo box to make the new item appear in the list immediately:

Private Sub cboProd_NotInList(NewData As String, Response As Integer)
Dim strProd As String, strName as String
Dim rst As Recordset, db As Database
Dim msg as string

On Error Goto cboProd_NotInList_Err

'continue without displaying error message
Response = acDataErrContinue

strProd = NewData
msg = "Product ID: " & strProd & " Not found in List!" & vbCr & vbCr & "Add it in Source File...?"

If MsgBox(msg, vbDefaultButton2 + vbYesNo + vbQuestion, "cboProd_NotinList()") = vbYes Then
    'Get Product Name from User
    strName=""
    'Repeat the loop if user presses enter key without entering a value
    Do While strName=""
        strName = InputBox("Product Name: ","cboProd_NotinList()","")
    Loop
    'Add ProductID and Name to the source Table
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Products", dbOpenDynaset)
    With rst

      .AddNew
      ![ProductID] = strProd
      ![PName] = strName
      .Update
      .Close
    End With
    
    'make combobox control source empty
    'in preparation to refresh the combobox
    Me!cboProd = Null
    'refresh the combobox
    Me.cboProd.Requery
    'now the combobox list have the new entry
    'place the new code in the combobox control source
    Me![cboProd] = strProd
 
    Set rst = Nothing
    Set db = Nothing
Else
   'if user refuse to add the new entry into source file
   'then display the error message and exit
   Response = acDataErrDisplay
End If

cboProd_NotInList_Exit:
Exit Sub

cboProd_NotInList_Err:
MsgBox Err & " : " & Err.Description,,"cboProd_NotInList()"
Resume cboProd_NotInList_Exit

End Sub

The above code is run from the On Not in List Event Procedure of the cboProd Combo box.

Limit to List Combo Box

a.p.r. pillaiJune 28, 2010Combo Boxes, msaccess controls 5 comments

Introduction.

Combo Boxes on Tables/Forms are for inserting frequently used common values quickly and easily into Data Fields. The Source Data Values of the Combo Box come from Table, Query, or from Value List. The User is expected to click on the Drop Down control of the Combo Box to display the items and select the required one to insert into the data field. The User can type Values directly into the Source Control of the Combo Box too.

But, the first Property setting out of the following two Property settings of Combo Box allows the user to select items from the existing list only and prevents from typing invalid values directly into the Target Field:

Limit to List = Yes
On Not in List = [Event Procedure]

When the Limit to List Property Value is set to Yes, you must select/type values available in the Combo Box list only and other values keyed in manually are not accepted in the Target Field. You must add new items in the Source Table of the Combo Box Control first before they can be used for the Combo Box.

For example, assume that you have a Table with a list of Fruits (only two items on the list now: Apple and Cherry) and you are using this list in a Combo Box on the Sales Form. When the Limit to List Property Value is set to Yes; you will not be allowed to enter the value Orange into the Target Field of the Combo Box.

The On-Not-in-List Event.

When the On Not in List Property is set to an Event Procedure; it is executed when the user enters a new value (Orange) manually into the Control-Source Field of the Combo Box. We can write code in the Event Procedure to add the entered new value into the Combo Box Source Table directly (after taking confirmation from the User) and update the Combo Box on the Form.

This method can save time otherwise needed for opening and adding new items in the Combo Box source Table manually. Besides that adding new values manually in the Source Table will not automatically refresh the Combo Box contents.

Let us try this out using the above example items as Source Data.

Combo Box Row Source Table.

Create a new Table with a single Field Name: Fruit and select the Data Type Text.
Save the Table Structure and name it Fruitlist.
Open the Table in Datasheet View and key in Apple and Cherry as two records.
Close and Save the Table with the records.

Create another table with the following Structure:

**Table Structure**
Field Name	Data Type	Size
ID	AutoNumber
Description	Text	50
Quantity	Numeric	Long Integer
UnitPrice	Numeric	Double

Before saving the Structure click on the second Field Data Type (Text) Column to select it.
Click on the Lookup Tab on the Property Sheet below.

Combo Box Property Settings.
Click on the Display Control Property and select Combo Box from the drop-down control.
The Row Source Type Property Value will be Table/Query, if it is not, then select it from the drop-down control.
Click on the drop-down control of the Row Source Property and select the Table Fruit list from the displayed list of Tables.
Change Column Width Property and List Width Property Values to 1".
Change the Limit to List Property Value to Yes.
Save the Table Structure with the name Sales.
Open the Table in Datasheet View and add a new record with Apple, 100, and 1.5 in Description, Quantity, and UnitPrice Fields respectively.
Close and save the Table with the record.
Click on the Sales Table to select it and select Form from Insert Menu.
Create a Form using the Form Wizard in Column Format and save the Form with the name Sales.

Testing Settings.
Open the Sales Form in the normal view.

Since we have added the Combo Box on the Table Structure it already appears on the form.
Press Ctrl++ (or click on the New Record control on the Record Navigation control) to add a new blank record on the Form.
Click on the drop-down control of the Combo Box and you will find the list of fruits: Apple and Cherry in it.
But, you Key-in Orange into the Description field and press Enter Key.
You will be greeted with the following error message:
If you want to enter the value Orange on the Form, first you must add that item to the Fruit list Table.
Open the Fruit list Table, and add Orange as a new record and close the Table.

But, this action will not refresh the Combo Box contents automatically to add Orange to the list. You have to close the Sales form and open it again before you are able to select Orange from the list. Or you must add a Command Button on the Form and write Code for requery the Combo Box contents.

What we did manually in response to the above error message can be automated by writing a VBA Routine that can be run through the On Not in List Event Procedure. You don't need to close and open the Form to refresh the Combo Box contents either.

Add New Item through VBA

Open the Sales Form in Design View.
Click on the Description Field to select the Combo Box control.
Display the Property Sheet (View - -> Properties).
Find and click on the On Not in List Property.
Select Event Procedure from the drop-down list.
Click on the build button (. . .) To open the VBA Module.

Copy and paste the following Code into the Module overwriting the top and bottom Procedure lines already appearing in the Module:

Private Sub Description_NotInList(NewData As String, Response As Integer)
Dim strmsg As String, rst As Recordset, db As Database

If Response Then
    strmsg = "Entered Item not in List!" & vbCr & vbCr & "Add to List...?"
      If MsgBox(strmsg, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in List") = vbYes Then
       Set db = CurrentDb
       Set rst = db.OpenRecordset("FruitList", dbOpenDynaset)
       rst.AddNew
       rst![Fruit] = NewData
       rst.Update
       rst.Close
       Me.Description.Undo
       Me.Description.Requery
       Me![Description] = NewData
       Me.Refresh
    End If
    Response = 0
End If
End Sub

Save and Close the Sales Form.

Trial Run Program.
Open it in a normal view.
Now, type the name of any fruit that is not in the Combo Box list (say Peach) in the Description field.

You will be greeted with the following Message Box:
Click the Command Button with the Label, Yes to add the new item keyed in the Description Field into the Fruit List Table and refresh the Combo Box List automatically.
Now, click on the drop-down control of the Combo Box and you can see that the new item is added to the list and accepted in the Description Field as well.