Chapter 11. Framework Query List

Let's create a worksheet, name it as Reports and connect to the viewPayments view.

The Select Object screen has significantly changed since the last connection:

It includes much more objects and additional columns like Translated Name and Description.

First of all, Translated Name and Description are shown as we added the SaveToDB Framework, added the object translation, and selected English in the Options. This feature helps users to understand database objects better.

The second, the wizard shows installed SaveToDB Framework objects.

 

Select the Framework Query List item in the Select Query List combobox, and the wizard displays source objects:

You can filter objects, also. Type pay, for example.

Let's select the dbo67.viewPayments view and leave Enable Query List on the ribbon checked.

 

Let's check the AccountID, CompanyID, and ItemID in the W column, and insert the view at cell B3.

We see a new feature. The ribbon Query List allows us to change the query.

This is a very useful feature. Users can use a few worksheets to work with multiple database objects.

Moreover, when developers add new objects to a database, users just reload the query list and can connect.

Creating the Reports worksheet using VBA

The following code is similar to the previous chapter codes:

' Creates the Reports worksheet, Chapter 11

Sub Chapter11_1_CreateReportsWorksheet()

    Dim addIn As Object
    Set addIn = GetAddInAndCheck()

    If addIn Is Nothing Then Exit Sub

    Dim wb As Workbook
    Set wb = ActiveWorkbook

    Call addIn.InsertAddInSheets(wb)

    Dim connString As String
    connString = GetConnectionString()

    Dim ws As Worksheet
    Set ws = GetOrCreateConnectedWorksheet(wb, _
        "Reports", connString, "dbo67.viewPayments", "VIEW", True)

    If ws Is Nothing Then Exit Sub

    ws.Select

    Dim lo As ListObject
    Set lo = GetFirstListObject(ws)

    If lo Is Nothing Then Exit Sub

    addIn.QueryList(lo) = "xls01.viewQueryList"
    addIn.QueryLocked(lo) = False

    Call addIn.ReloadQueryList(lo)

    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

It calls two new methods used to set the query list object and reload it:

addIn.QueryList(lo) = "xls01.viewQueryList"

Call addIn.ReloadQueryList(lo)