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)