Builder Ribbons Microsoft Access

Combobox and Dropdown, in practice

If you followed the previous tutorials, you know that you can change the ribbons, at runtime, using the gets attributes (getVisible, getEnabled, getLabel, ...).

Now, you will learn how to use the gets attributes, relevant to the Combobox and Dropdown controls, in order to fill the list dynamically. Will be presented two practical examples:

- Assembling a list of reports;

- Assembling a list of customers, that will serve as a filter to a form.

What is the difference between a ComboBox control and a Dropdown control?

The difference is that in the ComboBox control, you can enter a value that is present or not in the list, what is not allowed in the Dropdown. And at the programming is a small advantage in the use of the ComboBox, because it allows the direct use of the list’s value. In the Dropdown the value returned is the list’s index. But this is no obstruction to using the Dropdown.

Compare the two controls in the XML code:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="fncRibbon">

<ribbon startFromScratch="true">

<tabs>

<tab id = "tb1" label = "ComboBox and DropDown ">

<group id = "gr1" label = "ComboBox and DropDown" >

<comboBox id = "cbx1" label = "Combo dynamic" 

getItemCount = "fncGetItemCountCbx"

getItemLabel = "fncGetItemLabelCbx"

onChange = "fncOnChangeCbx"

/>

<labelControl id = "lb1" label = "----------------------------------------------" />

<dropDown id = "dd1" label = "Drop dynamic" 

getItemCount = "fncGetItemCountDrop"

getItemLabel = "fncGetItemLabelDrop"

onAction="fncOnActionDrop"

/>

</group>

</tab>

</tabs>

</ribbon>

</customUI>

Note that the difference lies in the attributes that perform an action event. The OnAction attribute to the Dropdown and the attribute OnChange to the control Combobox.

Now see the result of the XML code:

Let's start by demonstrating the technique of dynamic filling of the list, using the Dropdown control.

The mechanics of filling: the attribute GetItemCount tells the Dropdown control the amount of items that will be on the list. The Dropdown control uses this information to trigger the attribute geItemLabel, the number of times needed, to get the names (labels) that will be on the list.

Both the amount of items and the names that will fill the list will be captured in a table.

Have a look at the table fields used:

The Description field is used to fill the list. And the order of this list will be controlled by the field idx.

The first get to be triggered by the Dropdown is GetItemCount, which will capture the maximum number of items in the list that corresponds to the number of table records.

Observe the fncGetItemCountDrop function.

Sub fncGetItemCountDrop(control As IRibbonControl, ByRef count)

' Tell the Dropdown, by the variable count, the number of records from the 
' tblListaRelatorios table, which is the maximum amount of lines from the Dropdown.

count = DCount("*", "tblReportList")

End Sub

And what is the use of the argument Control at the above function? It is used in the case of having more than one Dropdown control in the ribbons. See how the function is to control more than one Dropdown:

Sub fncGetItemCountDrop(control As IRibbonControl, ByRef count)

  Select case control.id

    case "dd1" 'Name of a Dropdown control

      count = DCount("*", "NameTable")

    case "dd2" 'Name of Another Dropdown Control
      ' Tell the dropdown, through the count variable, the amount of
      ' tblListaRelatorios table records, which is the maximum amount of
      'lines on the dropdown.

      count = DCount("*", "tblReportList")

  end select

End Sub

Now that the Dropdown control knows the total amount of items that will have on the list, it will capture each of the items (label), through the attribute getItemLabel.

Note the fncGetItemLabelDrop() function that the get will trigger to check the labels:

Sub fncGetItemlabelDrop(control As IRibbonControl, index As Integer, ByRef label)

label = DLookup("description", "tblComboDynamic", "idx =" & index)

End Sub

Our example table has four records, which was the number reported for the Dropdown control. This will pass through fncGetItemLabelDrop() function 4 times. That's right! The function is called the amount of times the length of the list! And every time the control goes over the function, the argument index is increased by 1 (iIndex + 1). Always starting from zero (0).

So it's easy to load the corresponding label, just synchronize the index argument with the idx table field.

See the list loaded:

To complete, let's see the onAction attribute, which will provide functionality to the Dropdown control. The function triggered by this attribute is fncOnActiondrop ().

Sub fncOnActionDrop(control As IRibbonControl,selectedId As String, _

selectedIndex As Integer)

Dim strNameReport as string

'The argument selectIndex brings the number of the item that was selected by the user 

strNameReport = DLookup("report", "tblReportList", "idx =" & selectedIndex)

DoCmd.OpenReport strNameReport, acViewPreview

'Redo the list, cleaning the dropdown box

objRibbon.InvalidateControl ("dd1")

End Sub

The name of the report is captured by the DLookup () function. See that we capture the report name that corresponds to the selectedIndex number, which should coincide with the field idx.

And what about the COMBOX control?

Just the same, except the call attribute onChange, and that will trigger the function fncOnChangeCbx:

Sub fncOnChangeCbx(control As IRibbonControl, strText As String)

dim strNameReport as string

strNameReport = dlookup("report","tblReportList","description='" & strText & "'") 

DoCmd.OpenReport strNameReport, acViewPreview

objRibbon.InvalidateControl ("cbx1")

End Sub

This was a very simple case, where the table had a greatly reduced number of records and it was possible to manually renumber the field idx, which determines the order in which information from the Description field will be loaded in the list control.

As for a table, with a large amount of records and dynamic, using the idx field obviously becomes impossible. The issue is solved in a relatively simple way, which is to capture the table records, sort them into the desired way and store them temporarily in the computer memory, using a variable of the Array kind. This passage of information to memory is done in the function fncGetItemCountCbx, because it is triggered before the function fncGetItemLabelCbx, which gives the names to the list.

Before proceeding, understand a little about Arrays variables.

Arrays are variables that consist of a collection of values, called elements of the Array. Example:

Dim strNomeCliente(20) as string

This instruction creates an Array of 21 elements, each one being a conventional string variable. You create 21 elements because the first element of an array is zero (0). We will store specific information on each of the elements. Example:

strNameClient(0) = "Avelino Sampaio"
strNameClient(1) = "Pontocom Informática"
...
strNameClient(20) = "Maestro Tecnologia"

We have here the name Avelino Sampaio stored in element 0 and the name Pontocom stored in element 1. If we want to capture the name Avelino Sampaio from the variable, simply enter its element. Example:

label = strNameClient(0)

We can change the amount of elements of the variable dynamically, through the ReDim instruction. This allows us to determine the exact number of elements used, which will be equal to the number of records used:

reDim strNameClient(Record number of the table) as string

Pay attention to the code used, which will capture the customers' names to the variable strNomeCliente

Sub fncGetItemCountCbx(control As IRibbonControl, ByRef count)

Dim rs As DAO.Recordset

Dim strSql As String

Dim j As Long

' For the combobox frmClients form, we will make two tasks:
' 1st - Inform the quantity of items in the list for the combobox.
' 2nd - store in the computer memory, the names of clients who will fill the
'       list of the ComboBox control.
'       This memory contents will be used in the fncGetItemLabelCbx function 

'       that will be triggered soon.

' Build a query of the table tblClients to obtain the records sorted
' by the client name.

strSql = "SELECT cli_name FROM tblClients ORDER BY cli_name;"

' Opens query 

Set rs = CurrentDb.OpenRecordset(strSql)

rs.MoveLast: rs.MoveFirst

' Tell the Combobox, by the argument Count, the number of items that
' will be used.

 count = rs.RecordCount

' Determines the number of elements that will be stored at the variable

 ReDim strNameClient(rs.RecordCount) As String

' Here its passed to the strNameClient() variable the name of customers, 

' record by record.

 j = 0

 Do While Not rs.EOF

    strNameClient(j) = rs!cli_Name

    j = j + 1

    rs.MoveNext

 Loop

 rs.Close

 Set rs = Nothing

End Select

End Sub

Now the combobox knows how many names will have to load on the list, and go through the function fncGetItemlabelCbx the number of times required to load the names on the list. See how the function is very simple:

Sub fncGetItemlabelCbx(control As IRibbonControl,index As Integer, ByRef label)

 label = strNameClient(index)

End Sub

Note that we are capturing the values stored in the variable strNomeCliente() and the Index argument determines the value to be captured. The capture is being performed in sequence. StrNomeCliente (0), strNomeCliente (1), strNomeCliente (2), ..., StrNomeCliente (n). The names will be sorted in alphabetical order, as determined in the query sort.

How we use the value selected from the list to perform the filtering on the form ?

The function fncOnChangeCbx brings, in the strText argument, the value selected on the list. With this we can use the name of the client to perform the filtering. Follow the code:

Sub fncOnChangeCbx(control As IRibbonControl, strText As String)

Forms!frmClients.Filter = "cli_name='" & strText & "'"

Forms!frmClients.FilterOn = True

' Rewrites and updates the list of the combobox to a new search.

objRibbon.InvalidateControl ("cbx1")

End Sub

See, the ComboBox control, used for filtering the form:

Below is the example for download, with the two examples above:

ribbonCbxDrop.zip

Success!