I have a search-form with a table-control to show results. The macro below opens a further form when I click on a row and shows related records correctly. Working.
I copied the table-control from form2 to form1. Instead of opening a form I want the second table-control updated by the macro when I click on a row as above.
My form is named “SAorder”.
Then “MainFormOrder” with
subform “SubFormResult” and table-control “TableResult”. and based on a query
Nested is “SubFormEvent” with table-control “TableEvent” based on a table “SAevent”
The field linking is “SAorderID”
Experimented with placing the SubFormEvent at different levels, changed the macro by commenting out the lines to open a new form. But somehow it fails to show records in the table-control
SubFormEvent.
EM ***** BASIC ****
Option Explicit
Sub GetIBitemID()
Dim oForm As Object
Dim oSubForm1 As Object
Dim oSubForm2 As Object
Dim myDoc As Object
Dim oControl As Object
Dim oCurrentController As Object
Dim oContainer As Object
Dim oObj1 As Object
Dim iSAorderID As Long
Dim ObjTypeWhat
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.getByName("MainFormOrder")
oSubForm1 = oForm.getByName("SubFormResult")
Rem Get the control
oControl = oSubForm1.getByName("TableResult")
Rem Get the column with the ID info cbneeded
oObj1 = oControl.getByName("SAorderID")
Rem Save current selection in a global variable
iSAorderID = oObj1.getCurrentValue()
Rem This next section calls routine to close current form (if wanted)
Rem and open another form
Rem sTitle = ThisComponent.Title
Rem iStart = Instr(sTitle,":") + 2
Rem sName = Mid(sTitle, iStart)
Rem ObjName = "SA2orderdetail"
Rem Remove Comment in next line out if you want first Form closed
Rem ThisDatabaseDocument.FormDocuments.getbyname( sName ).close
Rem ObjTypeWhat = com.sun.star.sdb.application.DatabaseObject.FORM
Rem If ThisDatabaseDocument.FormDocuments.hasbyname(ObjName) Then 'Check the form exists'
Rem ThisDataBaseDocument.CurrentController.Connect() 'If the form exists connect to the database'
Rem ThisDatabaseDocument.CurrentController.loadComponent(ObjTypeWhat, ObjName, FALSE) 'Open the form'
Rem Else
Rem MsgBox "Error! Wrong form name used. "+chr(10)+"Form Name = " & ObjName
Rem End if
Rem Get access to newly opened form
myDoc = ThisDatabaseDocument.FormDocuments.getbyname( "SA1ordersearch" )
oSubForm1 = oForm.getByName("SubFormResult")
oSubForm2 = oSubForm1.getByName("SubFormEvent")
oObj1 = myDoc.getComponent().getDrawPage().getForms().getByName("MainFormOrder")
Wait 100
Rem Set dimensions of newly opened form
Rem oCurrentController = myDoc.getComponent().getCurrentController()
Rem oContainer = oCurrentController.getFrame().getContainerWindow()
Rem 'Pixels - my screen = 1366X768 pixels; 27.5"X15.5" = 50 pixels per inch
Rem intXPos=100
Rem intYPos=100
Rem intHeight=700
Rem intWidth=1100
Rem oContainer.setPosSize(intXPos, intYPos, intWidth, intHeight, 15)
Rem Set filter - SAorderID here is the table KEY field
sSelect = "( SAorderID = " & SAorderID & " )"
oObj1.Filter = sSelect
Rem Reload form to get the specified record.
oObj1.Reload()
oObj1.Filter = ""
End Sub