Win10, LO 7.3.7.3 HSQL 2.61
Hi,
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 = com.sun.star.sdb.application.DatabaseObject.FORM
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'
Else
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
intXPos=0
intYPos=23
intHeight=705
intWidth=1366
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.Reload()
oObj2.Reload()
oObj3.Reload()
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