Chapter 6. Query Parameters

Let's make our table more interactive. Run Reload, Configure Query Parameters:

Check the AccountID, CompanyID, and ItemID fields in the W (WHERE) column:

 

The SaveToDB add-in places the selected fields to the ribbon. So, our users can filter data:

Let's choose Rose, Inc.

 

This feature allows working without auto-filters and loading fewer data.

Configuring WHERE fields using VBA

Here is the code used to configure ribbon parameters:

' Configures WHERE parameters of the Payments table, Chapter 6

Sub Chapter06_1_SetPaymentsWhereParameters()

    Dim addIn As Object
    Set addIn = GetAddInAndCheck()

    If addIn Is Nothing Then Exit Sub

    Dim ws As Worksheet
    Set ws = GetWorksheet(ActiveWorkbook, "Payments")

    If ws Is Nothing Then
        MsgBox "Worksheet Payments not exists"
        Exit Sub
    End If

    ws.Select

    Dim lo As ListObject
    Set lo = GetFirstListObject(ws)

    If lo Is Nothing Then
        MsgBox "Worksheet Payments does not contain a table"
        Exit Sub
    End If

    addIn.IsRibbonField(lo, "AccountID") = True
    addIn.IsRibbonField(lo, "CompanyID") = True
    addIn.IsRibbonField(lo, "ItemID") = True

    ' Use ParameterValue to set id values
    ' addIn.ParameterValue(lo, "AccountID") = Nothing
    ' addIn.ParameterValue(lo, "CompanyID") = Nothing
    ' addIn.ParameterValue(lo, "ItemID") = Nothing

    ' Use ParameterRibbonValue to set name values
    ' addIn.ParameterRibbonValue(lo, "AccountID") = Nothing
    ' addIn.ParameterRibbonValue(lo, "CompanyID") = Nothing
    ' addIn.ParameterRibbonValue(lo, "ItemID") = Nothing

End Sub

Use the IsRibbonField property to add or remove ribbon fields of tables and views.

Use the ParameterValue property to set field or stored procedure parameter values using values like ID.

Use the ParameterRibbonValue property to set values translated by ValidationList handlers.