Problems with OpenFormAtRecord()

[meakasteve]

I would be grateful for any help the community can give me solving the problem I outline below.

On October 4th 2016 I asked the question on this LibreOffice Forum “what code do I need in a Base macro to access a field in highlighted row in a datasheet.” The answer that I received (from Ratslinger) worked perfectly. The code is shown in Listings A below.

I am now trying to modify this code to deal with a slightly different situation. In the original code the iCustID is obtained from a form (form #1). Specifically it is obtained from a grid in the MainForm of this form (which has a listing of customers and the iCustID corresponds to the customer on the row highlighted with the mouse). The selected iCustID is then used in a second form (form #2) which displays not only information about the customer but also information about the history of that customer’s contacts contained in a sub-form grid on this second form.

What I am now trying to do is to mark a particular contact by the customer on the subform grid of the form #2 and on pressing a button take that primary key of this contact, iCustConID, and open a third form (form #3) that shows details about this contact as well as containing a sub-form grid which contains fields from a list giving information about what happened on this particular contact. To accomplish this I have modified the code so that it looks into the subform rather than the mainform for the ID number but have left everything else the same. The modifications are shown as ** in Listings B below:
As can be seen the only subroutine that I believe needs modifying is the first one, Sub GetID().

Unfortunately the code does not work. It runs well until entering the sub OpenFormAtRecord then fails. Up till that point it has correctly identified the value for iCustConID but instead of opening the form#3 with the correct data for iCustConID it opens the form#3 displaying the data associated with the lowest iCustConID value in the table. In other words the iCustConID value gets lost and the lowest value becomes the default.

I think my problem is that I do not understand properly how the Sub OpenFormAt Record() works as well as whether the last three lines in sub FormChange need modifying or initiating.

Can anyone help me?

Listings A

  'Option Explicit 
  Global iCustID as Integer

  Sub GetID()       'Attached to Button on first form 
  Dim oForm As Object
  Dim oControl As Object
  Dim oObj1 As Object
  oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
 'Get the grid control
  oControl = oForm.getByName("MainForm_Grid") 
  oObj1 = oControl.getByName("CUSTID")  'Get the column with the ID info needed	
  iCustID = oObj1.getCurrentValue()  'Save current selection in a global variable
  'This next line calls routine to close current form and open another with a name of CUSTDATA
  
  FormChange("CUSTDATA")
  End Sub	

  SUB FormChange( sFormName )  'Used to close old form and open new form
  Dim ObjTypeWhat
  Dim ObjName As String
  Dim sName as String
  Dim sTitle As String
  Dim iStart As Integer
  sTitle = ThisComponent.Title
   iStart = Instr(sTitle,":") + 2
  sName = Mid(sTitle, iStart)
  ObjName = sFormName
  'Comment next line out if you do not 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
  End Sub

  Sub OpenFormAtRecord()  'This subroutine is called from the open document event
    Dim oForm As Object
	Dim sSelect As String
	oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
   'if form opened without call from another form exit
	If iCustID = 0 Then Exit Sub
   'Set filter to global variable
   'CUSTID here is the table KEY field
	sSelect =  "( CUSTID = " & iCustID & " )"
	oForm.Filter = sSelect
    ' Reload form to get the specified record.
	oForm.Reload()
	oForm.Filter = ""
	iCustID = 0
    End Sub

Listing B

  'Option Explicit  

    **Global iCustConID as Integer 
     Global sFormName as String

    ** Sub GetConID() 
    Dim oForm As Object 
    **Dim oSubForm as Object		 'needed because the iCustCon is on a sub-form 
  Dim oControl As Object 
     Dim oObj1 As Object 
 oForm = ThisComponent.Drawpage.Forms.getByName("mf_cust_edit")
 **oSubForm = oForm.getByName("SubForm") 	'Get the sub-form  
    'Get the grid control 
     **oControl = oSubForm.getByName("SubForm_Grid") 
     'Get the column with the ID info needed 
      oObj1 = oControl.getByName("CustConID") 
      'Save current selection in a global variable	
     **iCustConID = oObj1.getCurrentValue() 
     'This next line calls routine to close current form and open  
      'another form with a name contact_data 
       sFormName = "contact_data" 
       FormChange(sFormName) 
       End Sub



    SUB FormChange ( sFormName )  'Used to close old form and open new form
     Dim ObjTypeWhat 
 Dim ObjName As String 
 Dim sName as String 
 Dim sTitle As String 
 Dim iStart As Integer 
 sTitle = ThisComponent.Title 
 iStart = Instr(sTitle,":") + 2  
 sName = Mid(sTitle, iStart, 12) 'name of form to be closed can only be 12 characters 
 ObjName = sFormName	 'This is the name of the form to be opened 
 'Comment next line out if you do not want first Form closed	 
 ThisDatabaseDocument.FormDocuments.getbyname( sName ).close 
 ObjTypeWhat = com.sun.<p>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 
 'Attached to Button on first form
     Else 
 MsgBox "Error! Wrong form name used. "+chr(10)+"Form Name = " & ObjName 
     End if 

     End Sub 




     Sub	OpenFormAtRecord() 'This subroutine is called from the open document event 
    Dim oForm As Object 
Dim sSelect As String 
    oForm = ThisComponent.Drawpage.Forms.getByName(sFormName) 
'if form opened without call from another form exit 
If iCustConID = 0 Then Exit Sub 
    'Set filter to global variable 
sSelect = "( contact_ID = " & iCustConID & " )" 
   	oForm.Filter = sSelect 
    'Reload form to get the specified record. 
oForm.Reload() 
oForm.Filter = "" 
 iCustConID = 0 
     End Sub

[Ratslinger]

In the original process there were three routines involved.

-1 GetID() - this was attached to push button. It accesses the table grid, then accesses the column in the grid for the needed key for the next screen, saves this in a global variable (so it is available for another routine to use later), then sends a call to FormChange routine.

-2 FormChange(sFormName) - This routine uses the parameter sFormName sent by the caller. In this case the GetID() routine statement FormChange("CUSTDATA"). “CUSTDATA” is in the variable (parameter) sFormName. This routine uses the current form information to close itself & uses the parameter to open the new form.

-3 OpenFormAtRecord() - is called with the Open Document event of the newly opened form. It first checks to see if a number actually exists in the global variable. If valid (not zero), it places this & specific field name in form for selection by filtering it from the other records. The form is “Reloaded” so the filtered record appears.

I wrote the above because the code you presented appears you had no knowledge of the specifics inside.

To do what you now want:

-1 The first three routines must stay intact (including the global variable).
-2 Create a new routine similar to GetID(). This routine must access the table grid in the sub-form, get the necessary column for the key information, then get the value and save it in a different global variable. You can open the new form & close the existing with the same line as in GetID() just with the different form name to be used.
-3 Create a new routine similar to OpenFormAtRecord() which is needed for the third form. The newly created global variable (previous step) will be used, along with the proper field name, to display the wanted record.

Edit 5/14/17

For the most part your code looks OK, but you did things I would call “rookie” mistakes. First, you seem to have duplicated Sub FormChange(sFormName) which already exists and (unknown reason) a global variable for “sFormName”. Your call in GetConID() will use the routine as I originally gave you and the the form name is passed by parameter therefore the duplicate sub and global variable are totally unnecassary.

You third routine uses the same sub name ( OpenFormAtRecord() ) as the one used for the first opening. While this may work with careful planning, it is not only bad coding but also causes great confusion as to when to use which routine in what situation. Rename the sub. Then make sure contact_data form has the Open Document event pointing to this new name.

In this same routine you seem to be trying to use the Actual form name instead of the internal form name. This is certainly a major complaint of mine in Base. Form can mean different things depending upon the context. When you run Base you have forms to open and have various uses. When this form is opened, it contains controls which are located on internally named forms (different use “Form” than the one opened). This internal form can also have sub and sub-sub (and so on) forms attached to it, each of which has its’ own internal name. With that, in this routine you are using an Actual (external) form name instead of the necessary internal form name. Change that line:

oForm = ThisComponent.Drawpage.Forms.getByName(USE_INTERNAL_FORM_NAME_HERE)

Now, based upon that, if everything was set correctly before (namely the Open Document event), that particular line should have given you an error.

Hopefully, with these changes you should get better results although I may have missed something else wrongly placed in your code.

[meakasteve]

Thanks Ratslinger for your detailed analysis in your 5/14/17 edit. My remaining problem, is understanding how the last sub (OpenFormAtRecord) is called. There is no reference to the sub in the proceeding code. You say that the form ‘contact data’ should be pointed at by the open document event, but how is this achieved?

[Ratslinger]

@meakasteve Very strange. My message says you posted this 4 hrs ago yet the date on it says 5/14?

This concerns the form to be called. Open it in edit mode. Toolbar - select Tools->Customize. On Events tab, lower left Save In, make sure the form name is selected (NOT LibreOffice), then select Open Document, click macro button and point to the routine.

[meakasteve]

Got rid of ‘rookies’ and initialized Open Document event as you described. Everything works wonderfully. Many many thanks. P.S. Long delays in my replies were due to illness :{

[Ratslinger]

Glad all worked out. I believe you now have the basics down for this. Delays are never a problem. Health is much more important. Best to you.