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
.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
fetchSize and other query parameters aren’t exposed in the Form Properties dialog. Only
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.