# Problems with OpenFormAtRecord()

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 ...
edit retag close merge delete

Sort by » oldest newest most voted

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 ...

more

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?

( 2017-05-14 12:53:50 +0200 )edit

@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.

( 2017-06-10 15:22:49 +0200 )edit

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 :{

( 2017-06-28 15:38:56 +0200 )edit

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.

( 2017-06-28 18:52:10 +0200 )edit