How can Open a new form to show detail of record selected from grid

gary_sitler · January 28, 2021, 7:43pm

I have on for that displays a grid control that shows a summary of each row in a database table. I want to open another form that displays that record in detail when a row in the grid is selected. There are other forms in the system that also use a grid control in a similar way. This system worked previously. I don’t know what changed other than updating the version of LibreOffice. I am currently using version 7.0.3.1 (x64). Now, when I set the value of the filter property oTargetForm.Filter = sFilter

I get the error…
BASIC runtime error.
Property or method not found: Filter

Here is the source to the macro

REM The following constants allow parameters to be set based on the name of the 
REM form which contains the object that originated the triggering event

       CONST Schema = "gems"
       CONST MasterFormDoc = "Gems.Catalog"
       CONST MasterFormName = "GemCatalogForm"
       CONST ApFormName = "ApplicationForm"

REM The DeriveData subroutine sets public variables
 
    	sGridControl = "ApplicationFormGrid"
     	sGridColumn = "numID"
    	sTargetForm = Schema & ".catalog"
    	sDrawPage = "GemCatalogForm"
    	sTargetColumn = "Gem_ID"
    	sTargetTable = "catalog"

REM Macro to handle an event from the grid control of the application form.
The name of the object which threw the event will be used to identify a set of additional
parameters for this subroutine... see Public variable definitions and DeriveData Subroutine

Sub ApFormGrid( oEv as variant )
	DIM oModel as object, oForm as object, oTargetForm as object, oDrawPage as object
	DIM sFilter as string, oGrid as object, oColumnList as object
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
    oModel = oEv.Source.model
    oForm = oModel.Parent
    DeriveData( oForm.Name )
    oGrid = oForm.getByName(sGridControl)
    oColumnList = oGrid.getByName(sGridColumn)
    sFilter = oColumnList.getCurrentValue()
	IF Len( sFilter ) > 0 THEN
		oTargetForm = openThisDocumentForm( sTargetForm )
		sFilter = "( """ & Schema & """.""" & sTargetTable & """.""" & sTargetColumn & """ = " & sFilter & " )" 
		MsgBox "Filter ='" & sFilter & "'"
		oTargetForm.Filter = sFilter
		oTargetForm.ApplyFilter=True 
		wait 100
		oTargetForm.Reload() 
	ENDIF
END Sub

Ratslinger · January 28, 2021, 8:32pm

Hello,

With similar process using HSQLDB embedded and LO v7.0.4.2 I have no problem. You do not provide the DB you are using. You also do not show the code for openThisDocumentForm( sTargetForm ) as this seems to be where the problem is - not returning what you think it should return in this instance. I would use Mri to examine oTargetForm if your routine is working for other forms(not clear in question).

If you have further issues, please provide at least the missing information. A sample showing the problem would be best.

gary_sitler · January 28, 2021, 10:39pm

I am using a local instance of MySql as the database. Here is the source of the openThisDocumentForm macro subroutine…

'----------
' Function to open a form from ThisDatabaseDocument by name and return a handle to it
Function openThisDocumentForm( formName as string ) as object
	DIM oForm2 as object
	if ThisDatabaseDocument.FormDocuments.hasByName( formName ) = TRUE THEN
		oForm2 = ThisDatabaseDocument.FormDocuments.getByName( formName ) 
		oForm2.Open 
	else
		MsgBox "ThisDatabaseDocument has no FormDocument named '" + formName + "'"
		MsgBox "FormDocuments are " + join( ThisDatabaseDOcument.FormDocuments.getElementNames, ", " ) 
	endif
	openThisDocumentForm = oForm2
end Function

gary_sitler · January 28, 2021, 10:59pm

I am running windows version 10 for 2004. I tried reverting to version 6,4,7,2 of LibreOffice and the problem remains. The proper form is returned by the openThisDoumentForm function and is opened successfully. I edited the form design and verified that it has the filter ( gems.catalog.Gem_ID >= 0 ). I examined the form object with Xray and it does not show a “Filter” property. What I am trying to do is change the filter in the maco and reload the form to show only the record that was selected in the grid control on the application form.

gary_sitler · January 28, 2021, 11:10pm

Another detail that may be applicable is that the document containing the forms was recovered by LibreOffice after the my laptop rebooted with the document open (I think).

Ratslinger · January 28, 2021, 11:57pm

Hello,

You are accessing the form differently than I would. See sample at the end of my answer here → Base macro that opens a new/clean record in another form

Now you can adjust your code to overcome this. You simply have not accessed the component.

Replace this code of yours:

    oTargetForm.Filter = sFilter
    oTargetForm.ApplyFilter=True 
    wait 100
    oTargetForm.Reload()

with this:

    oTarget = oTargetForm.getComponent.Drawpage.Forms.getByName("YOUR_MainForm")
    oTarget.Filter = sFilter
    oTarget.ApplyFilter=True 
    wait 100
    oTarget.Reload()

Tested with MySQL 8.0.22 on Ubuntu 20.04 with LO v7.0.4.2

gary_sitler · January 29, 2021, 2:04am

The form associate with the oTargetForm object is definitely the form for which I need to reset the filter. I don’t understand the point of using that object to get an object for a different form (YOUR_MainForm).

Ratslinger · January 29, 2021, 2:22am

This line:

oForm2 = ThisDatabaseDocument.FormDocuments.getByName( formName )

is obtaining a form you click on in the main screen of the Base file.

You need to access the internal form name where the controls and table access are located. Thus:

oTarget = oTargetForm.getComponent.Drawpage.Forms.getByName("YOUR_MainForm")

I tagged it as “YOUR_MainForm” since it can be any name you assigned to it. There can also be many internal forms and sub forms and sub sub forms etc.

In accessing your other controls, you already got to the proper place on the starting grid with oEv:

oModel = oEv.Source.model
oForm = oModel.Parent

Bottom line is that it is not a different form; it is the internal form of a form. Yeah - bad naming from a long, long time ago.

gary_sitler · February 9, 2021, 8:18pm

This is the code that eventually worked

    oTargetForm = openThisDocumentForm( sTargetForm )
    oDrawPage = oTargetForm.component.drawpage.forms.getByName( sDrawPage )
    sFilter = "( """ & Schema & """.""" & sTargetTable & """."""  & sTargetColumn & """ = " & sFilter & " )" 
    oDrawPage.Filter = sFilter
	oDrawPage.ApplyFilter=True