How to open a subform from a searchform via doubleclick

Win10, LO HSQL 2.61


I have a searchform which searches 4 unrelated tables, working fine. All these tables have in common is that their respective data is displayed in other dataentry forms at the bottom of the forms hierarchy
e.g one form may have Category-SubCategory-Tareas

From the searchform I would like to open the entryform by doubleclicking a record of the Tareas listing.

Now I know I can open a form via doubleclick using Herr Ratslingers beautiful GetID macro which grabs the pk and then filters the form to open accordingly.

At present the SQL of the tablecontrols of the searchform only contain the pk of the subforms but not the related fk s.

So the question is can I open the dataentry forms somehow by just refering to the pk of the subform ?

Hope the included images may make my request a bit clearer.

Thanks for your thoughts.

Further to the original post I have been exploring to filter the three forms as part of the macro.
I seem to be almost there, the entry form opens up filtering the correct CatId, but then bombs out on the second filter and I can not figure why? Also I am not sure if I need an and filter condition for the second filter and three conditions for the third one.

Below error capture and the full code adpted from the original GetId macro.

Sub	GetIDs_WebDoc
	Dim oForm As Object
	Dim myDoc As Object
	Dim oControl As Object
	Dim oCurrentController As Object
	Dim oContainer As Object
	Dim oObj1 As Object
	Dim ObjTypeWhat
	Dim iCatId As Integer
	Dim iSubCatId As Integer
	Dim iUrlId As Integer
	Dim iStart As Integer
	Dim intXPos As Integer
	Dim intYPos As Integer
	Dim intHeight As Integer
	Dim intWidth As Integer
	Dim sName as String
	Dim sTitle as String
	Dim sSelect as String
	Dim ObjName as String
	oForm = ThisComponent.Drawpage.Forms.Filter_Form.getByName("SubForm_Table2")
	Rem Get the control
	oControl = oForm.getByName("SubForm_Table_Grid")
	Rem Get the column with the ID info needed	
	oObj1 = oControl.getByName("uid")
	Rem Save current selection in a global variable
	iCatId = oObj1.getCurrentValue()	
	msgbox " " & iCatId
	oControl1 = oForm.getByName("SubForm_Table_Grid")
	Rem Get the column with the ID info needed	
	oObj2 = oControl.getByName("usid")
	Rem Save current selection in a global variable
	iSubCatId = oObj2.getCurrentValue()
	oControl3 = oForm.getByName("SubForm_Table_Grid")
	Rem Get the column with the ID info needed	
	oObj3 = oControl.getByName("lid")
	Rem Save current selection in a global variable
	iUrlId = oObj3.getCurrentValue()
	msgbox " " &iCatId & " " & iSubCatId & " " & iUrlId
	Rem This next section calls routine to close current form (if wanted)
	Rem  and open another form
	sTitle = ThisComponent.Title
	iStart = Instr(sTitle,":") + 2
	sName = Mid(sTitle, iStart)
	ObjName = "frmWebResources"
	Rem Remove Comment in next line out if you want first Form closed
'	ThisDatabaseDocument.FormDocuments.getbyname( sName ).close
	ObjTypeWhat =
	If ThisDatabaseDocument.FormDocuments.hasbyname(ObjName) Then 'Check the form exists'
		ThisDataBaseDocument.CurrentController.Connect() 'If the form exists connect to the database'
		ThisDatabaseDocument.CurrentController.loadComponent(ObjTypeWhat, ObjName, FALSE) 'Open the form'
		MsgBox "Error! Wrong form name used. "+chr(10)+"Form Name = " & ObjName
	End if
	Rem Get access to newly opened form
    myDoc = ThisDatabaseDocument.FormDocuments.getbyname( "frmWebResources")
    oObj1 = myDoc.getComponent().getDrawPage().getForms().getByName("frmCat")
    oOb2 = myDoc.getComponent().getDrawPage().getForms().frmCat.getByName("subCat")
    oObj3 = myDoc.getComponent().getDrawPage().getForms().frmcat.subCat.getByName("frmUrls")
    Wait 100
	Rem Set dimensions of newly opened form
    oCurrentController = myDoc.getComponent().getCurrentController()
    oContainer = oCurrentController.getFrame().getContainerWindow()
'	Pixels - my screen = 1366X768 pixels; 27.5"X15.5" = 50 pixels per inch
    oContainer.setPosSize(intXPos, intYPos, intWidth, intHeight, 15)
	Rem Set filter - CUSTID here is the table KEY field

   	sSelect =  "( uid = " & iCatId & " )"
	oObj1.Filter = sSelect
	sSelect1 =  "( usid = " & iSubCatId & " )"
	oObj2.Filter = sSelect1
	sSelect2 =  "( lid = " & iUrlId & " )"
	oObj3.Filter = sSelect2
	Rem Reload form to get the specified record.
	oObj1.Filter = ""
	oObj2.Filter = ""
	oObj3.Filter = ""
End Sub

Hmm, not sure why the edit appears at the top now. Anyway I would be grateful if someone can spot my mistake, thanks

Solved - it was the obvious, a typo
oOb2 = myDoc.getComponent().getDrawPage().getForms().frmCat.getByName(“subCat”)

…oObj2 should be oObj3

Hallo ggk, I would like to do the same than in your 2nd image where you have one search-entry field and than 4 table-controls to output finds in 4 different tables. What is the macro behind the search button?

Hello Yumi,

There is no macro behind the button, the button just refreshes the subform(s) after changing any criteria.

There are different ways going about it. You can have a search where you have a number of cascading criterias or just one criteria searching across all fields in a table. In both cases you will need a query or an SQL statement and a filter table.
LIKE_user_input_filter_with_concat.odb (86.4 KB)
MultiSearch.odb (29.7 KB)

There are example databases but can not find the links. Therefor attaching the files for your reference.

The first one you should be able to trace if the download does not work.

LIKE_user_input_filter_with_concat.odb - this one is a classic featuring 5 examples of which you may wish to study examples 4 and 5

SecondOne I am not sure where I got it from and I renamed the original.
Anyway hope the samples work


Thanks, I succeeded with one input field and searching across 3 tables and all fields. For each table I have a query and a subform with a table-control. Each subform has a button to refresh it. Works!
Testbase.odb (15.3 KB)

If you insert Mai then it finds in Persons somebody as well as a company.
The Mainform also has a button “Refresh all” but this is not working.
My objective now is to get rid of the 3 subform buttons and only have 1 button to refresh all 3 subforms - but how to do it.
Personal question - are you German? I am and living in Beijing.


Here is one macro to refresh all forms

Sub reloadAllTables
Dim Forms : Forms = ThisComponent.DrawPage.Forms
Dim i%
For i = 0 To Forms.getCount()-1
End Sub

You also might like to have another button to clear the criteria

Sub ClearSearch()
rem this part works, need to refresh the table then refresh the form
oConn = ThisDatabaseDocument.DataSource.getConnection("","")
SQL = "UPDATE ““tblFilter”” SET ““F1"” = Null”
oQuery = oConn.createStatement()
End Sub

I am Austrian living in Chile and spent half my life in Australia.

Hope the above helps.

Your sample Base file has a problem. The Refresh All button is on the same form as the text box. When you input something into the text box and then press the button, the filter does not update. With the button on a separate form, the filter table will update without intervention.
Attached is your original sample modified. The sub forms are not necessary as there is no direct relation to the main form. So each is now a main form. The individual buttons were deleted and for convenience, the macro(s) from @maya1 are included:

Testbase.odb (17.9 KB)

Thank you Maya1 for the Macro. Ratslinger build it into the button and changed the form a little. Works like a charm, very smoothly. I will try to understand the logic on how the improvements improved the form.
Now I have to figure out how to use the form ergonomically in an application (working environment). It is very practical to filter for data in several unrelated files and across a number of fields in each one. For example foreign names when it is not clear which is First- and which Family-Name. Or for products for example.
Maya1 I am from Passau on the Austrian Border.

Hello Michael,

Aha, ein Bayer!

I grew up near the Bavarian border in Lindau a.B.

Glad its all working, here are a couple more research resources for you:

The latest Base Handbook

Another forum:

A German forum



Oops, forgot the urls …

You can simply edit your previous comment instead of new comments.
Also see this link for code formatting: This is the guide - How to use the Ask site? - #6 by erAck
Your posting above can cause issues especially with quote marks and apostrophes.

Thanks for the guide.

The sub forms are not necessary as there is no direct relation to the main form. So each is now a main form. The individual buttons were deleted and for convenience, the macro(s) from @maya1 are included.

Strange, responding by email removes urls from message,