Base Prevent Form Execute on Page Launch

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 PropertiesData 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 PropertiesData 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.

In addition, the master/slave parameters to link subforms do not seem trivial to control via macros. I can see via MRI that there’s a way, but I haven’t figured it out yet. But that’s a separate question.

Edit: I think you can GROUP BY in command section of query, but not LIMIT/OFFSET as far as I know without using a macro. But the main reason I am using macros is since forms can’t accept “parameter queries” (WHERE id = 12345) launched from one form to another without using a macro.

Hello,

First, please do not make up new terminology - a form is a form not a ‘page’. It’s difficult enough describing to others the difference between internal & external without new questions/answers using unique terms.

Have looked into this previously and not discovered where this return is actually controlled. May even be in the database itself. However, have a possible workaround.

The internal form properties, on Data tab set the Content to blank (nothing, empty). When the form is opened it will not retrieve any records. Then you can execute your macro(s) recalling specific items or even use Limit to retrieve groups of records. Seems to work fine without MaxRows or FetchSize and using straight SQL. Had no problems using Limit in statement either.

Have now also tested with remote MySQL server. Frankly don’t see much difference. Using MySQL(JDBC) there is no problem with form loading. This was using table with 1000 then 11,000 records.

Bumped to 42,500+ records and initial load was slower. Much quicker reading records using Limit SQL. With this table in sub form linked to main form it was quick without SQL.

Various testing done using MySQL(JDBC), JDBC from MySQL and Native SQL. No problems with Base crashing using any connector.

I wish my story was similar. I’ve had nothing but instability, crashes and minutes of waiting. So I’ve resorted to essentially undoing all the stuff Base should do naturally because it doesn’t work. In addition, I can’t use native SQL unless I just want to display read-only information. Every time I use native, I can no longer edit the fields. I realize you will say your experience is different. Nevertheless, this is how it is for me, and therefore I have to hack everything with macros.

During the time when Base is crunching for minutes, all LO windows go into “not responding,” and if I click on anything during that waiting time, LO instantly crashes and closes. It tends to behave better when I control all the queries manually, but it’s a big pain in the butt, especially when I need to do subforms. Since everything is controlled manually, it makes for a lot of work to limit subforms to a particular slave/master link. But it’s possible. Working on it… slowly.

JDBC constantly disconnects and times out, making it unusable for me. Tons of messages relating to microseconds of this and that, and the auto-reconnect checkbox does nothing to solve this. Native extension doesn’t work in 64-bit, so can’t use that. ODBC becomes the only option, but even that doesn’t work letting Base do it’s thing. It only becomes usable when I execute every form manually. Otherwise it hangs and crashes.

Sorry to hear you are having your problems as I and others don’t seem to be experiencing any of this with either local or remote connections. As far as the Native MySQL connector I’ve been using this one in 64-bit for years now without a problem → MySQL Driver for Apache OpenOffice.

Thanks. I remember seeing there were two native MySQL extensions, and then forgot. The other one available only works with LO 32-bit and hasn’t been updated in a long time. I’ll give this one a try. I had also noticed that Base skips its auto-query when SQL command is blank. However, when I do that the master slave relationship no longer filters subforms (for me anyway) based on primary/foreign keys. So I’ve been macroing the crap out of it to insert the where clause based on current record.

The answer given does allow you to open a form without loading any data. The comment also indicates this and simply states the different testing tried. It concludes the use of SQL does indeed improve the response. This, I presume, it what you are looking for.

Fair enough. You’re right. Doesn’t alleviate the need to write excessive code, but I guess I didn’t ask for miracles. Since I seem to be the only one with my particular ongoing issues I’ll accept the answer. Maybe someday a random LO update will fix my woes so I can just use built-in form handling instead of reinventing the wheel.

Nope, that second Native MySQL driver doesn’t work either. When trying to install it throws the error message “MySQL SDBC Driver for Apache OpenOffice does not work on this computer.” Probably related to the 64-bit thing since the other driver does the same, and people reported the reason to relate to 32-bit vs 64-bit. There isn’t a Windows 64-bit option on the download page, only for Linux. Guess I’m stuck with ODBC for now.

Strange since it states Windows under Operating System (bottom of page). Just another reason why I’m happy to have abandoned Windows years ago. Don’t miss it at all.