Writing the Callbacks

To provide the data for the drop-downs, you implement the getItemCount, getItemLabel, and getItemID callbacks you saw earlier. For this, you need a recordset. Start by declaring a Recordset object in the declarations section of the module:

Private rst As DAO.Recordset

Now, implement the callbacks. Because the tag attribute stores the name of the field, you can use that in your callback code:

Public Sub OnGetItemCount(ctl As IRibbonControl, ByRef Count) Dim strSQL As String

' get the unique data from tblContacts strSQL = "SELECT DISTINCT [" & ctl.tag & "] FROM tblContacts"

Set rst = CurrentDb.OpenRecordset(strSQL) Count = rst.RecordCount End Sub

Public Sub OnGetItemLabels(ctl As IRibbonControl, Index As Integer, ByRef label) If Not rst.EOF Then label = rst(ctl.tag) End If End Sub

Public Sub OnGetItemIDs(ctl As IRibbonControl, Index As Integer, ByRef id) If Not rst.EOF Then id = "id" & rst(ctl.tag) rst.MoveNext

If rst.EOF Then rst.Close Set rst = Nothing End If End If End Sub

Figure 12-27 shows the Ribbon when you open the Company drop-down.

Figure 12-27

Figure 12-27

Finally, you need to filter the report when the item is selected. For that, you implement the onAction callback:

Public Sub OnReportFilter(ctl As IRibbonControl, selectedId As String, _

selectedlndex As Integer)

Dim strFilter As String Dim strName As String strName = Mid(selectedId, 3)

strFilter = "[" & ctl.tag & "] = '" & strName & "'" ' filter the report

Screen.ActiveReport.Filter = strFilter Screen.ActiveReport.FilterOn = True End Sub

The resulting Ribbon should look like Figure 12-28. When you select an item in either drop-down, the report is filtered accordingly. This filter is not cumulative — that is, it does not append criteria to use the AND or OR operators. To refresh the controls when new contacts are added, you can implement the onLoad callback for the Ribbon and invalidate the drop-down controls as demonstrated later in the section "Refreshing Ribbon Content."

Figure 12-28
0 0

Post a comment