Refresh main form by closing a form using macro

pauly · December 4, 2021, 6:48pm

I found a very good example of opening and closing forms from LibreOffice Media_with_Macros.

The code is here:

REM This module iis for opening a form from another form.
REM At the same time the form, where the button is pressed, would be closed.
REM Will you get back from a form to one mainform (for naviagtion), you have only to start
REM ThisDatabaseDocument.FormDocuments.getByName( “Mainform” ).open
REM from the form in menue Tools → Customize → Events → Document is going to be closed

SUB ToFormFromForm(oEvent AS OBJECT)
DIM stTag AS STRING
stTag = oEvent.Source.Model.Tag 'Input of the tag in “Additional information” of the control
aForms() = Split(stTag, “,”) 'First is written the name for the new form, followed by the old form
ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms(0)) ).open
ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms(1)) ).close
END SUB

SUB ToFormFromFormWithFolder(oEvent AS OBJECT)
REM Form, that should be opened, ist the first
REM Is the form in a separate folder, this folder is defined with “/”.
REM So the subfolder could be found
DIM stTag AS STRING
stTag = oEvent.Source.Model.Tag 'Input of the tag in “Additional information” of the control
aForms() = Split(stTag, “,”) 'First is written the name for the new form, followed by the old form
aForms1() = Split(aForms(0),"/")
aForms2() = Split(aForms(1),"/")
IF UBound(aForms1()) = 0 THEN
ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms1(0)) ).open
ELSE
ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms1(0)) ).getByName( Trim(aForms1(1)) ).open
END IF
IF UBound(aForms2()) = 0 THEN
ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms2(0)) ).close
ELSE
ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms2(0)) ).getByName( Trim(aForms2(1)) ).close
END IF
END SUB

SUB MainformOpen
ThisDatabaseDocument.FormDocuments.getByName( “Frm_VRT” ).open
END SUB

Now when I open another form (i.e. form0), the main form (i.e. form1) automatically closes at the same time.

What I want is when I open form0, form1 will not closes, and when I close form0, form1 get automatic refreshed (reloaded). I need help to achive this.
I attached an example odb file.

Thank you very much in advance.

I use Mac OS, LibreOffice 7.1.5.2, HSQLDB embedded.

Base_Print_Report_Example.odb (60.3 KB)

RobertG · December 4, 2021, 8:15pm

You could try it like this:

GLOBAL oDocF AS OBJECT
SUB FormClose
  oDocF.Component.Drawpage.Forms.getByName("MainForm").reload()	
END SUB
SUB ToFormFromForm(oEvent AS OBJECT)
  DIM stTag AS STRING
  stTag = oEvent.Source.Model.Tag
  aForms() = Split(stTag, ",")
  ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms(0)) ).open
  oDocF = ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms(1)) ) 
END SUB

The procedure FormClose must be linked to the closing event of the second form document.

But when I had a look to your forms it seems to be you don’t want to reload the form. You want to refresh a listbox.

oDocF.Component.Drawpage.Forms.getByName("MainForm").getByName("fmtC_ID").refresh

will refresh this listbox.

pauly · December 4, 2021, 8:49pm

RobertG,

thank you so much!!! Yes, it is correct that i want to refresh listbox, all together 4 listboxes in the form. Will it be possible to refresh all these listboxes together?

May I be able to write the name of the form in “Additional information” of the control from the second form? That will be ideal.

Please have a look at the attachment, I tried to implement (using your code and some additional code) but had no luck.

Base_Print_Report_Example.odb (61.4 KB)

RobertG · December 5, 2021, 8:05am

Open the form document frm_Client. Go to Tools → Customize → Events. Look for the event Document closed and chose the procedure FormClose for this event.

Here the code for refreshing one listbox in frm_VRT, which opened frm_Client. Procedure ToFormFromForm is like the procedure in the answer.

GLOBAL oDocF AS OBJECT

SUB FormClose
  oForm = oDocF.Component.Drawpage.Forms.getByName("MainForm")
  oForm.getByName("fmtC_ID").refresh
END SUB

The var must be GLOBAL. It will be filled when opening frm_Client and must still exist when frm_Client will be closed.
Now get the names of all listboxes in frm_VRT and write a new line for every listbox. You won’t need oform.reload, because you only want to change content of the listboxes and that won’t happen when reloading the form.

pauly · December 6, 2021, 9:48am

Thank you Robert. Good idea to refresh the listbox, but I don’t know why I was unable to make it work. I did Go to Tools → Customize → Events . Look for the event Document closed and chose the procedure FormClose for this event. But for unknown reason, later I went there again, I saw no macro attached to any event.

RobertG · December 6, 2021, 3:08pm

Here the database with working macro. Changed ‘Williams’ to ‘Williams1’ and it will be refreshed in the other form.
Base_Print_Report_Example.odb (61.0 KB)

pauly · December 6, 2021, 8:47pm

Thank you so much, dear Robert! It works!
But the problem now is, I have 9 forms in a database, and there are 4 listboxes in every form. So that will be 36 macros. It will be so nice to design a macro that can be used repeatedly. But thank you again for your effort, I really appreciate.

RobertG · December 7, 2021, 3:35pm

No problem to get it working for all the four of one form:

SUB FormClose
oForm = oDocF.Component.Drawpage.Forms.getByName(“MainForm”)
oForm.getByName(“fmtC_ID”).refresh
oForm.getByName(“fmtZadavatel_ID”).refresh
oForm.getByName(“fmtTajomnik_ID”).refresh
oForm.getByName(“fmtNU_ID”).refresh
END SUB

That are the names for the control in the form you are using. You could also name the forms the controls in every form the same way:
“fmt1_ID”, “fmt2_ID” …
So the macro will work for every form if the form inside the form document is called also “MainForm” in every form.

pauly · December 7, 2021, 5:58pm

Thank you so much! That’s a good solution!

pauly · December 10, 2021, 2:00pm

Hi, it is very strange.

The code from you as following works very well in the example odb file, where there are only 2 macro modules.

REM ***** BASIC *****

GLOBAL oDocF AS OBJECT

SUB FormClose
oForm = oDocF.Component.Drawpage.Forms.getByName(“MainForm”)
oForm.getByName(“fmtC_ID”).refresh
oForm.getByName(“fmtZadavatel_ID”).refresh
oForm.getByName(“fmtTajomnik_ID”).refresh
oForm.getByName(“fmtNU_ID”).refresh
END SUB

I put your code in a module, I named it as CloseRefreshListbox,
together in my working database there are 11 macros modules. (vid attachment pic), then I got this error: Basic runtime error. object variable not set.

What does this error mean? How can I correct? Thanks. This is really beyond me.

RobertG · December 10, 2021, 3:12pm

You will fill the content for oDocF by closing the form.

The variable is set to GLOBAL, so it should work for all modules.

Don’t know why you created so much modules. You could send me the database per private mail so I could have a look.

pauly · December 10, 2021, 4:36pm

Hi Robert, I would be happy to send you the database through private mail, that will be the simplest. The database is quite simple, but unfortunately, I can not do it because i have signed contracts with many agencies that wouldn’t allow me to do it.

Those modules (1-8) and PrintRLSK module are 9 macros for printing 9 reports. It doesn’t look nice, but I don’t know other way to print those reports. So there are so many of them.

Option Explicit

Sub OpenReport
Dim oController As Object
Dim oReportDoc As Object
oController = ThisDatabaseDocument.currentController
if not oController.isconnected then oController.connect
oReportDoc = Thisdatabasedocument.reportdocuments.getbyname(“rptVRT”).open
End Sub

Sub PrintCurrent
Dim oForm As Object
Dim oColumn As Object
Dim iField As Integer
Dim oSubForm As Object
Dim oController As Object
Dim oReportDoc As Object
oForm = ThisComponent.Drawpage.Forms.getByName(“MainForm”)
iField = oForm.getByName(“fmtVRT_ID”).Text
if oForm.isNew() Then
MsgBox “New Record - Cannot Print”
Exit Sub
End If
oSubForm = oForm.getByName(“PrintForm”)
oColumn = oSubForm.Columns.getByName(“Sel_VRT”)
oColumn.updateInt(iField)
oSubForm.updateRow()
oController = ThisDatabaseDocument.currentController
if not oController.isconnected then oController.connect
oReportDoc = Thisdatabasedocument.reportdocuments.getbyname(“rptVRT”).open
End Sub

I don’t use the openReport macro, but i left the code there.

Navigation module is better, because it can be reused. I am only using the ToFormFromForm macro inside to open other forms.

SUB ToFormFromForm(oEvent AS OBJECT)
DIM stTag AS STRING
stTag = oEvent.Source.Model.Tag 'Input of the tag in “Additional information” of the control
aForms() = Split(stTag, “,”) 'First is written the name for the new form, followed by the old form
ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms(0)) ).open
REM ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms(1)) ).close
END SUB

SUB ToFormFromFormWithFolder(oEvent AS OBJECT)
REM Form, that should be opened, ist the first
REM Is the form in a separate folder, this folder is defined with “/”.
REM So the subfolder could be found
DIM stTag AS STRING
stTag = oEvent.Source.Model.Tag 'Input of the tag in “Additional information” of the control
aForms() = Split(stTag, “,”) 'First is written the name for the new form, followed by the old form
aForms1() = Split(aForms(0),"/")
aForms2() = Split(aForms(1),"/")
IF UBound(aForms1()) = 0 THEN
ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms1(0)) ).open
ELSE
ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms1(0)) ).getByName( Trim(aForms1(1)) ).open
END IF
IF UBound(aForms2()) = 0 THEN
ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms2(0)) ).close
ELSE
ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms2(0)) ).getByName( Trim(aForms2(1)) ).close
END IF
END SUB

SUB MainformOpen
ThisDatabaseDocument.FormDocuments.getByName( “Frm_VRT” ).open
END SUB

So those are all macros I have, and I am lacking knowledge so far on Macros, this error is just beyond me.

I added a print button to print a VRT1 report, the button was bond with the PrintCurrent Macro in module 2, but the refresh listbox code (closing form) worked just fine. So apparently it has nothing to do with the increase of the module.
That’s all from me, thank you and regards.

Base_Print_Report_Example-3.odb (69.5 KB)

RobertG · December 10, 2021, 6:40pm

There are differences between the post and the attached database. Did you really close the form and fill the object oDocF with content? I don’t see it in the posted macros. The attached database works here as expected.

pauly · December 10, 2021, 9:33pm

Hi, sorry, haven’t noticed that. How can I fill the content for oDocF by closing the form?

RobertG · December 11, 2021, 7:03am

It’s this part of ToFormFromForm

oDocF = ThisDatabaseDocument.FormDocuments.getByName( Trim(aForms(1)) )

pauly · December 11, 2021, 2:33pm

Hi, yes, it did the trick. But there is another problem.

When I Went to Tools → Customize → Events and selected Document closed and linked the procedure FormClose for this event, it will work but just one time.

After I close the form, and then reopen it, then I go to Tools → Customize → Events and the linked macro is not there. It’s empty. As shown in the last pic.

Really strange.

Can this macro linked to a simple “Save form” button on the Client form instead of in the Document closed event of Tools → Customize → Events ?

I attached some pics.

Thanks!
Screenshot 2021-12-11 at 15.32.07

RobertG · December 11, 2021, 3:07pm

Macro has nothing to do with the frm_Client. So it could also be connected to any button you want. Seems to be a bug the macro will be disconnected from Document closed.