I’m writing macros that fill in the various parameters of a form before executing it and reloading it. For example:
oForm = thisComponent.drawpage.forms.getByName("frmNote")
oForm.escapeProcessing = True
oForm.commandType = 2
oForm.command = "SELECT * FROM `note`"
oForm.filter = " `tablename` = 'account' AND `status` = 1 "
oForm.order = " `importance` DESC "
oForm.setPropertyValue("MaxRows", 10)
oForm.setPropertyValue("FetchSize", 10)
oForm.execute()
oForm.reload()
Above is a sample of code I use to load a form immediately after that “page” is opened. I say “page” because Base’s terminology is confusing in that [page] “forms” (document/page/windowed “thing”) can have [control/data] “forms” inside them. I’m loading a [page] form via ThisDataBaseDocument.CurrentController.Connect() and .loadComponent() functions, and then I wish to load the contained [control/data] form programmatically.
My problem is this: Base tries to load the [control/data] form already with whatever is typed in the Form Properties → Data dialog as soon as the form document is rendered. Therefore, when I also do my execute, it is populating the form twice.
My question is, can I disable the [control/data] form from loading what is in its Form Properties → Data dialog so that I can limit the records myself via a SQL command? I’m trying to circumvent Base’s default behavior on forms tied to large table sources so that the entire table isn’t loaded into memory. It takes far too long and causes Base to become unusable.
In most cases I only want to fetch 1 single row of data or possibly something like the “top ten,” and so I am writing macros to feed the primary key dynamically or else telling it to only give me 10 results ordered by a particular column. The most common situation is where I want to only show one “Edit” record in a form, and I therefore modify the filter to be something like WHERE id = 12345. Base “does its slow thing” before I’m able to feed in these parameters and execute. Then my execute() loads the [control/data] form yet again, so I can’t make lean / limit its queries.
It would be great to be able to disable the default [control/data] form execution immediately after the following lines of code which open a new [page] form so that I can specify the parameters before execution.
Function openFormDocument(sDocumentName As String)
Dim vComponent
vComponent = com.sun.star.sdb.application.DatabaseObject.FORM
If ThisDatabaseDocument.FormDocuments.hasbyname(sDocumentName) Then 'Check the form exists
ThisDataBaseDocument.CurrentController.Connect() 'If the form exists connect to the database
ThisDatabaseDocument.CurrentController.loadComponent(vComponent, sDocumentName, False) 'Open the form
openFormDocument = ThisDatabaseDocument.FormDocuments.getbyname(sDocumentName).getComponent() 'return new document object
**INSERT CODE TO DISABLE DEFAULT FORM EXECUTION**
Else
MsgBox "Error! Wrong form name used. " & sDocumentName
End if
End Function
Is there a command to disable automatic form execution?
As a side note, I don’t understand why groupBy, havingClause, maxRows, fetchSize and other query parameters aren’t exposed in the Form Properties dialog. Only Filter and Order are exposed. If Analyze SQL command is Yes, this means you can’t GROUP BY or LIMIT & OFFSET your results without setting the parameters via a macro. One of the side effects of this behavior is that the form loads all results into memory by default, which makes forms load way too slowly.