[Example] OpenForm with macros

Some examples to be used directly
Forum rules
No question in this section please
For any question related to a topic, create a new thread in the relevant section.
Post Reply
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

[Example] OpenForm with macros

Post by RPG »

I will try to make a tutorial about open a form with macro's. Before you start using macros it is good to learn first more about how OpenOffice.org is organized. This is special true for
a) When you have used Microsoft Office
b) Have never used before OpenOffice.org
c) Have used an other office suite.
d) Have not read an tutorial about OpenOffice.

I think this is nearly for all people. To much people start working with OOo without understanding it. Opening a form is different for standalone forms and for forms embedded in a database. Opening forms without macros is real easy when you use standalone forms. You can do this with insert a hyperlink to an other form. When you use this method you can also pass parameters to other forms. Villeroy and now also Arineckaig are working with forms and databases without using macros.

When you want open a form what is not a standalone form then you need a macro or you have to use the switchboard extension of Benitez.

You can see it is real eay to open a form. You need only this line:

Code: Select all

ThisDatabaseDocument.FormDocuments.getbyname(sNewDocumentName).open
When you use hierarchicalnames then use this code:

Code: Select all

thisDatabaseDocument.FormDocuments.getByHierarchicalName("SubPlantsMap/SelectedPlants").open
This real easy opening of a form is complete true when you do it from a form in the same database. When you want do it on opening of the database or with a macro from outside the database then expect different kinds of problems. Most of the time you notice the problems that data is not stored in the database.

I hope other people can open this database there I have always to connect to the class. In the database there is also an example where I do a lot of testing on names. When the names are wrong you get a message.

Romke

Code: Select all

REM  *****  BASIC  *****
option explicit ' I use this line always Then I must define variables.
' I do use several  methods for opening a form.
' a) a normal standard opening for a form.
' b) Use a selected value for opening a form .In the new form we use this
'    selected value for selecting records.
' c) Using the Basetools
' d) I use here the getByHierarchicalName property for opening a form
' All are activate with a button and then
'This module is using real short code there isno testing.
' The short code makes it more clear what happens

sub OpenForm_1
const sNewDocumentName="Plants" ' This is the variabele for the new form what must be opened
' Use this for opening a data form
ThisDatabaseDocument.FormDocuments.getbyname(sNewDocumentName).open
end sub



'+++++++++++++++++++++++++++++++++++++++++++++++++++++
sub OpenForm_2(oEvent)
const sNewDocumentName="Plants" ' This is the variabele for the new form what must be opened

' This sub is activated by a button in a form
' First define all variables which we need for the form where the button is.
dim oButton, oForm,oGrid,oControl ' This are the objects for the control who I need
' Now define the names in the form where the button is.
' Adjust the names to your need
const sGridName="Table Control  1"
const sControlName="TextField2"

' we want have a text control in a gridcontrol

'Now define all the variables who we need in the form we want open.
dim oNewFormDocument,oNewDataForm
dim sSearchValue
const sNewFormname="PlantsForm" ' The form name as you see in the form navigator
const sSearchfield="Name" 	' Change the name between the double quote according in your table.

' Now start the program
oButton=oEvent.source.model ' Now I have the button. Which is in a form.
oForm=oButton.parent ' the value must be in this form.

' Now locate your control. and test for the names

	oGrid=oForm.getbyname(sGridName) ' First the grid control
	oControl=oGrid.getbyname(sControlName) ' Now the text control in a grid

' Now the action for the new form
' we want have a value  from a grid table
	' Go for the object of the subdocument
' Use this for opening a data form
oNewFormDocument=ThisDatabaseDocument.FormDocuments.getbyname(sNewDocumentName).open
oNewDataForm=oNewFormDocument.drawpage.forms.getbyname(sNewFormname)
' We want filter the new form
dim sFilter
sFilter= chr(34) & sSearchfield & chr(34) & CHR(61)& chr(39) & oControl.TEXT & chr(39)
oNewDataForm.filter=sFilter
oNewDataForm.applyfilter=true
oNewDataForm.reload
end sub



'++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
' In this sub we use:
'  A) a selected value for selecting records
'  B) The Basetools
sub OpenForm_3(oEvent as object)
' This sub is activated by a button in a form
' First define all variables which we need for the form where the button is.
dim oButton, oForm,oGrid,oControl ' This are the objects for the control who I need
' Now define the names in the form where the button is.
' Adjust the names to your need
const sGridName="Table Control  1"
const sControlName="TextField2"

'Now define all the variables who we need in the form we want open.
dim sSearchValue
const sNewDocumentName="Plants" ' This is the variabele for the new form what must be opened
'const sNewFormname="PlantsForm" ' The form name as you see in the form navigator
const sSearchfield="Name" 	' Change the name between the double quote according in your table.

' Now start the program
oButton=oEvent.source.model ' Now I have the button. Which is in a form.
oForm=oButton.parent ' the value must be in this form.
'	oControl=oForm.getbyname(sControlName) ' when you use a control not in a grid
oGrid=oForm.getbyname(sGridName)
oControl=oGrid.getbyname(sControlName)
print "basetools without contrl"



' Now we start using the Basetools

' First make sure the library is loaded
if not globalscope.BasicLibraries.islibraryloaded("BaseTools") then 
	' Load now the library
	globalscope.BasicLibraries.loadlibrary("BaseTools")
end if

' We want filter the new form
dim sFilter
sFilter= chr(34) & sSearchfield & chr(34) & CHR(61)& chr(39) & oControl.TEXT & chr(39)
DIM oNewDataForm

databases.InitFromDocument thisdatabasedocument ' We need this 
' Explanation of the parameter
' The name of the formdocument we want open.
' The event of a button i did use
oNewDataForm=runcmd.openFormDocument(sNewDocumentName,oEvent,sFilter) ' I do get the data form  not document.
oNewDataForm.reload ' I need this instruction I don't know if it is normal
end sub



'+++++++++++++++++++++++++++++++++++++++++++++++++++++
sub OpenForm_4(oEvent)
const sNewDocumentName="SubPlants/SubPlants" ' This is the variabele for the new form what must be opened

' This sub is activated by a button in a form
' First define all variables which we need for the form where the button is.
dim oButton, oForm,oGrid,oControl ' This are the objects for the control who I need
' Now define the names in the form where the button is.
' Adjust the names to your need
const sGridName="Table Control  1"
const sControlName="TextField2"

' we want have a text control in a gridcontrol

'Now define all the variables who we need in the form we want open.
dim oNewFormDocument,oNewDataForm
dim sSearchValue
const sNewFormname="PlantsForm" ' The form name as you see in the form navigator
const sSearchfield="Name" 	' Change the name between the double quote according in your table.

' Now start the program
oButton=oEvent.source.model ' Now I have the button. Which is in a form.
oForm=oButton.parent ' the value must be in this form.

' Now locate your control. and test for the names

	oGrid=oForm.getbyname(sGridName) ' First the grid control
	oControl=oGrid.getbyname(sControlName) ' Now the text control in a grid

' Now the action for the new form
' we want have a value  from a grid table
' Go for the object of the subdocument
' Use this for opening a data form
oNewFormDocument=ThisDatabaseDocument.FormDocuments.getByHierarchicalName(sNewDocumentName).open
oNewDataForm=oNewFormDocument.drawpage.forms.getbyname(sNewFormname)
' We want filter the new form
dim sFilter
sFilter= chr(34) & sSearchfield & chr(34) & CHR(61)& chr(39) & oControl.TEXT & chr(39)
oNewDataForm.filter=sFilter
oNewDataForm.applyfilter=true
oNewDataForm.reload
end sub
Attachments
OpenFormExample.odb
Example for opening a form.
(39.94 KiB) Downloaded 4923 times
Last edited by RPG on Sat Aug 23, 2014 12:49 pm, edited 1 time in total.
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
GiuseppeV
Posts: 10
Joined: Sat Jan 29, 2011 4:16 pm
Location: Italy

Re: [Example] OpenForm with macros

Post by GiuseppeV »

RPG wrote: Opening a form is different for standalone forms and for forms embedded in a database. Opening forms without macros is real easy when you use standalone forms. You can do this with insert a hyperlink to an other form. When you use this method you can also pass parameters to other forms. Villeroy and now also Arineckaig are working with forms and databases without using macros.
Hi!

could you post an example for hyperlink?

I've found only hyperlink to a different position of form :crazy:
OpenOffice 3.2 Ubuntu 10.10
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Example] OpenForm with macros

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
GiuseppeV
Posts: 10
Joined: Sat Jan 29, 2011 4:16 pm
Location: Italy

Re: [Example] OpenForm with macros

Post by GiuseppeV »

OpenOffice 3.2 Ubuntu 10.10
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Example] OpenForm with macros

Post by RPG »

Hello

Your behavior makes nobody can find anything. So don't expect answers if you have no question.
When you have a question make a new thread an do not pollute other threads who are not related to your question.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
goodvibes
Posts: 22
Joined: Sun May 09, 2010 2:04 am

Re: [Example] OpenForm with macros

Post by goodvibes »

RPG, Many Thanks - Your examples are wonderful!
Open Office 3.3.0 RC10, Windows 7 (64bit)
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Example] OpenForm with macros

Post by Villeroy »

goodvibes wrote:RPG, Many Thanks - Your examples are wonderful!
Although this API is a can of worms.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
barbiebedia
Posts: 3
Joined: Tue Nov 08, 2011 5:50 pm

Re: [Example] OpenForm with macros

Post by barbiebedia »

RPG I'm also thanks the examples.

You can't imagine how much time safe for me :super:
OpenOffice 3.1 on Windows Vista
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Example] OpenForm with macros

Post by RPG »

Hello

I try to make example what I have test as much as possible. I do also assume that people do use this examples for learning how to learn programming in OOo. When you want learn programming in the API then first you must have programming skills if you have never done programming and you want started with programming in BASIC with the API then assume a long time of learning. The most difficult part is maybe working with OOo instead of following your own ideas. This is true for OOo as tool and also for the API.

This is also true for me. I do learn also from time to time that I do not follow the best method for OOo. An other point what I learned is how to present the results in a way so other people can be used in a more easy way.


Some code examples can be use for all databases but the example for compacting the database is only for the HSQLDB database engine.

Making a backup with a time stamp.
When you make a choice you want have a database all in one file then you have a great chance on corrupting the database. I have make a macro what can store the database with a time stamp so you can make easy a backup of the database.

Compacting the database
This code is special for the HSQLDB engine. When you change a lot in the database then the files can be grow. You notice this in slowing down the computer special when startup. I think it can be good to compact the database from time to time. When you use the embedded database then it is always useful. In other cases I think look to the files and see if there is a result. Most of the time there is only a result when you use cached tables in the HSQLDB database. Cached table are stored in the .data file

Registering the database
I have add a little macro what makes registrations of the database. The datasource-name is the same as the database name. This can be useful when you want spread a database for people who can not registrate the database or use a wrong name for the datasource. I have also add code for this so you can add macros to a writer document so you can spread a database without macros. When you use this then be sure all files are in one map. I don't know what the result is when they are in different maps.


Translating SQL to BASIC
This macro translate SQL code in a way you can use it in BASIC. It adds double double quotes for the single quote you normally use. When you use control values in the SQL then you have to change the code. Paste the working code first in the top box translate the code and copy the code from the lower text box. In the macros you also can find a real short code doing maybe the same.

Learning the API
I hope you can copy the code and have not to change the code. When you study the code I do use most of the time only service who are exported by the object who I use in the documents. Most of the time that method makes it more easy to program. But it did cost me a lot of time before I understand it. When you start making macros then try to understand first what you can do with OOo self. Writing macros is real time consuming and does not speed up your work. I think it is better to learn how the forms are working. Also important is understanding what you can do with SQL. Then try to understand what is the relation between forms and SQL.

The best way speeding up your work is not with writing macros but learn working with what you can do in a more simple way. When some people say you have to redesign your database then it can be a real good idea. When I design my database in a better way it was more easy to work with it. This is not only true for the database but also for working with OOo. Do not expect I give you a solution for your problem: Real often I do not even understand your description of your problem.

I hope it helps you.

Romke
Attachments
OpenFormExample.odb
Partly the same examples as in the first file but now more easy. Some new eamples what can help
(116.12 KiB) Downloaded 2917 times
Last edited by RoryOF on Wed Mar 14, 2012 10:13 am, edited 1 time in total.
Reason: Fixed typo to aid better searching.
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: [Example] OpenForm with macros

Post by DACM »

Example for PaulDG
Attachments
Open_Form_Filtered.odb
various macro-driven open form examples
(83.61 KiB) Downloaded 6341 times
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
silvrwood
Posts: 14
Joined: Sat May 19, 2018 6:57 pm

Re: [Example] OpenForm with macros

Post by silvrwood »

Awesome!! Thanks so much! :bravo: :D

In case any other newbs out there make the same mistake, I initially copied the first and simplest macro (sub OpenForm_1) into "My Macros," where it experienced an "Object variable not set" error. :knock: Move the macro to your database file macros, and it works like a charm!
Apache OpenOffice 4.1.5
Win10 Home x64
Post Reply