Running Form from Shortcut

Creating and using forms
Post Reply
espiritmaddy
Posts: 2
Joined: Tue Mar 04, 2008 2:54 am

Running Form from Shortcut

Post by espiritmaddy »

This might be a stupid question, as I'm new to this whole thing,
but is there some way that I can have a shortcut that will open the form with the database I want
being fed into it, without having that "management" window that's open underneath.

Please excuse if this is a dumb question, but I'd like to be able to open my form all by itself
without having to go through so many steps.

Is this possible?

Thank you in advance,
Maddy
QuazzieEvil
Volunteer
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: Running Form from Shortcut

Post by QuazzieEvil »

the simplest way is to save your form as a writer document, or simply create your form as a writer document. if you save your form created in the base form designer as writer document, you will loose the db connection. open the document in design mode and reset the form connections to the database.

another way is to create a macro that opens the form and create a shortcut to that macro/function

Code: Select all

Sub openForm2(FormName As String)
	Dim Context AS Object
	Dim Conn As Object
	Dim DB As Object
	Dim FormDoc As Object
	Dim Args(1) As New com.sun.star.beans.PropertyValue
	
	Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
	DB=Context.getByName("OOForum") REM YOUR DB NAME
	Conn=DB.getconnection("","")
	Args(0).Name="ActiveConnection" : Args(0).Value=Conn
	Args(1).Name="OpenMode" : Args(1).Value="open"
	FormDoc=DB.DatabaseDocument.FormDocuments.loadComponentFromURL(FormName,"_self",2,Args)
	FormDoc.CurrentController.Frame.ContainerWindow.setFocus()
End Sub
now you can create a short cut or a batch file (*.bat)
you want to execute your macro with ..\program\soffice.exe

Code: Select all

"C:\Program Files\OpenOffice.org 2.3\program\soffice.exe" "macro:///OOForum.Base.openForm2(Form11)"
in this case, the function openForm2(...) is located in module Base which is located in Library OOForum. I am passing form name 'Form11' as the form I wnat to open
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Running Form from Shortcut

Post by Villeroy »

You can register at http://qa.openoffice.org/ and vote for issue 85120
Since this issue is a request for enhancements rather than a bug, the actual implementation depends on the count of votes.
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
espiritmaddy
Posts: 2
Joined: Tue Mar 04, 2008 2:54 am

Re: Running Form from Shortcut

Post by espiritmaddy »

Quazzie, wow----even though I'm feeling inkling of rememberance of my visual basic programing, I'm petty much lost with what you wrote!
BUT, I'm saving it for a few months from now when I'm much more familar with the program. Thank you for taking the time to write that
out for me.

Villeroy, And I did go and vote (that one I could handle) :)

Thanks for your help, both of you!
QuazzieEvil
Volunteer
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: Running Form from Shortcut

Post by QuazzieEvil »

until the shortcut feature is in the works, I have created a quick extension to generate shortcuts for base forms and reports. I have only tested in windows xp home edition and ubuntu 7.10.

in windows it creates a batch (*.bat) file which launches the macro to open the doc. I tried to create a shell link w/ OO basic, but must shamefully submit that I did not know where to start.

in linux you have the option of creating a *.bash script or a *.desktop file shortcut--i prefer this one.

I believe Drew Jensen posted code to open a query object, which may also work for a table in a similar manner; I will try to integrate that if there is interest; or perhaps see what the devs come up with.

hope this helps
Attachments
DBDocShortCuts-1.0.0.zip
Database Document Shortcut Generator.
(9.63 KiB) Downloaded 760 times
User avatar
voobase
Volunteer
Posts: 97
Joined: Tue Jan 15, 2008 3:07 pm
Location: Australia

Re: Running Form from Shortcut

Post by voobase »

Hi QuazzieEvil,

Nice work with this macro. I like many, will find it very useful as you won't need to keep one "writer doc" form external to the rest of the database as other methods require.

I was having problems with it initially. After launching my form, the form would shut down again with just enough time to see that an error box was called. It seems it is the last line in the macro which was causing this to occur, where it tries to give the form focus. I had a bit of a scratch and think and after getting nowhere experimenting with a few things (like error handlers) I remembered that I had turned off my quickstarter a while back(due to it once crashing my database really hard). I tested this theory and after turning the quickstarter back on, the macro worked as advertised. (The macro did work, by the way, if I already had my database running.)

I am wondering if there is an "If" statement you could put around that line to detect if there are no other OpenOffice things running and if so skip that line. It would mean I could turn the quickstarter off again (as I am not ready to trust it again quite yet!).

Cheers

Voo

ps. I'm on v2.3.1 running Vista.
OOo 2.3.X on MS Windows Vista
QuazzieEvil
Volunteer
Posts: 283
Joined: Tue Dec 04, 2007 6:38 pm
Location: Houston, TX

Re: Running Form from Shortcut

Post by QuazzieEvil »

ah, yes, I keep forgetting to look into that. In my tests it works well if i replace the -invisible option with -minimized -nodefault . see attachement for updated version of extension.
Attachments
DBDocShortCuts-1.0.0.zip
(9.7 KiB) Downloaded 671 times
User avatar
voobase
Volunteer
Posts: 97
Joined: Tue Jan 15, 2008 3:07 pm
Location: Australia

Re: Running Form from Shortcut

Post by voobase »

Hi again Quazzie,

I have been using your macro and it works well except in a couple of situations. The first situation is what I mentioned above (how with the OO quickstarter turned off it fails). The second situation is when the shortcut launches a form that uses a macro that utilizes "StarDesktop". In this case the macro with "StarDesktop" in it fails unless the Base UI is running. It is like the "openForm" macro is not putting the document in the "StarDesktop Container". Here is a link to a post where I recieved some help from Hanya sorting out my particular problem.... http://www.oooforum.org/forum/viewtopic.phtml?t=75209

I have updated your "OpenForm" macro with the solution from Hanya. I included a check to first determine if the form has already been loaded into the "StarDesktop" container and stops it doing it again (which also seemed to cause problems). I did this by detecting if it is possible to navigate from "ThisComponent" to "StarDesktop" using the path thiscomponent.CurrentController.Frame.Creator.Creator. The other thing I have added to your macro is a check to see if the form is already open by checking the "StarDesktop" Components (since the updated code now puts it there). If your form was already open and you unintentionally used the shortcut again, you would refresh the whole form, losing the latest entry. I adapted a bit of code from MS777 for this, from where he helped me out here... http://www.oooforum.org/forum/viewtopic.phtml?t=71452

Hanya's fix also solves the problem of using the "openForm" macro with the OOquickstarter turned off. :)

Code: Select all

Rem... Modified to putform in "StarDesktop" container and to also work with quickstarter turned off.
Rem... It also now checks if the form is already loaded so you don't lose data if you accidently use the shortcut again.
Sub openForm_StarDesktop_Mod (FormName As String)
   Dim Context AS Object
   Dim Conn As Object
   Dim DB As Object
   Dim FormDoc As Object
   Dim Args(1) As New com.sun.star.beans.PropertyValue
   
   	Rem... to check if the form is already loaded, so don't accidently do it twice.
oEnum = Stardesktop.Components().createEnumeration
oDesk = CreateUnoService("com.sun.star.frame.Desktop")
oEnum = oDesk.Components().createEnumeration
while oEnum.hasMoreElements
  oComp = oEnum.nextElement
  if HasUnoInterfaces(oComp, "com.sun.star.frame.XModule") then
    if oComp.Identifier = "com.sun.star.sdb.FormDesign" then
      if left(oComp.CurrentController.Frame.Title, len(FormName)) = FormName then
		With oComp.GetCurrentController().GetFrame().GetContainerWindow()
			.SetPosSize(0,0,,,com.sun.star.awt.PosSize.POS)
			.SetPosSize(,,1440,860,com.sun.star.awt.PosSize.SIZE)
			.setFocus()
		End With 
		msgbox "This form is already open!"
        exit sub
      endif
    endif
  endif
wend
   
   
   Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
   DB=Context.getByName("DataBaseName") REM YOUR DB NAME
   Conn=DB.getconnection("","")
   Args(0).Name="ActiveConnection" : Args(0).Value=Conn
   Args(1).Name="OpenMode" : Args(1).Value="open"
   FormDoc=DB.DatabaseDocument.FormDocuments.loadComponentFromURL(FormName,"_self",2,Args)
   'FormDoc=conn.parent.databasedocument.getFormDocuments.loadComponentFromURL(FormName,"_self",2,Args)
   

   ' following lines are added
   Rem... First check if UI is running, where problems may occur,
   Rem... by determining if can "see" Stardesktop from ThisComponent
   If isNull (thiscomponent.CurrentController.Frame.Creator) then
      oFormFrame = FormDoc.getCurrentController().getFrame() Rem... Thanks Hanya
      ' append its frame into the frames container of the Desktop
      StarDesktop.getFrames().append(oFormFrame)
   End if 
    
 
   	With FormDoc.GetCurrentController().GetFrame().GetContainerWindow()
		.SetPosSize(0,0,,,com.sun.star.awt.PosSize.POS)
		.SetPosSize(,,1440,860,com.sun.star.awt.PosSize.SIZE)
		.setFocus()
	End With   
End Sub
Cheers

Voo
OOo 2.3.X on MS Windows Vista
sharves
Posts: 1
Joined: Wed Oct 08, 2008 4:00 am

Re: Running Form from Shortcut

Post by sharves »

Hi there.

I am totally new to db's. I know this is stupid Q but how to use the code you have provided and where to paste it. Plus, how to create a log in page which requires to enter username and password. I need this page to access my form if the password was correct.

Please advise. Thanks in advanced.
OOo 2.3.X on Ms Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Running Form from Shortcut

Post by Villeroy »

sharves wrote:I am totally new to db's.
In this case the above code is totally irrelevant to you. It may be used as the final polishing to make a ready-to-use database a little bit mor convenient. Actually, it is absolutely unrelated to databases and how they work.
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
seglea
Posts: 26
Joined: Tue Nov 18, 2008 1:04 am
Location: Exeter, UK

Re: Running Form from Shortcut

Post by seglea »

Thanks to all who have contributed to this thread, which as a newcomer to ooBase I have found very helpful.

However I seem to have hit an impasse. I am writing an application which I want to be available to all users on my machines (though only one at once - it isn't a true multiuser situation). So I have stored all the macros on the forms rather than in My Macros, so they don't have to be installed separately for each user. But it looks as though I can't access such a macro until the form concerned is open. If this is right, I can't see how I can get a form to open without going through the main Base management screen (which I would much rather my users didn't ever see). Any ideas anyone?
OOo 2.4.X on Ms Windows XP
User avatar
voobase
Volunteer
Posts: 97
Joined: Tue Jan 15, 2008 3:07 pm
Location: Australia

Re: Running Form from Shortcut

Post by voobase »

But it looks as though I can't access such a macro until the form concerned is open.
Yes you are right. Unfortunately this is one of the drawbacks with storing your macros with the form. You will need to at least store the "OpenForm" macro from this thread with Open Office on each of the machines your users will be using. This means putting the macro in a library in the "MyMacro's" area. I find it easier to keep all my macro's there. You only need to import these to your users computer once, so they won't have to see the UI each time a database form is used. I think OOV3.0 allows you to store macro files with the database itself however I have not explored this option. I am also not sure if the macro from this thread works with OOV3.0. When I tried it, the form would open and then close again straight away. I haven't had a chance to play with OOV3.0 any more since.

Other things to be careful of if you are using this macro.... Check your data changes are being saved when you close down the form. If they are not, you will need to integrate this line into your code at strategic places...

Code: Select all

ThisComponent.Parent.store()
Another problem, specific to using this macro in a multi user environment, is that after you close down the form, soffice does not close down in your task manager and the database file remains locked (on windows at least). This means that other users might not be able to open up the database file. This is a tricky one to solve and the closest I have got to solving this problem so far is as follows...

1/ Create a XClose listener by putting these two lines at the bottom of your "OpenForm" macro.

Code: Select all

oListenerClose = CreateUnoListener("Listensub_", "com.sun.star.util.XCloseListener")
	FormDoc.addCloseListener(oListenerClose)
also declare a global variable at the beginning of your macro file by putting...

Code: Select all

Global oListenerClose as object
at the top of your macro module.

2/ Put these Subs into your Macro file somewhere.... The third one calls the "Shutdown_all_Listen" macro when a form closes but all three sub's are required

Code: Select all

Sub Listensub_disposing(oEvent)
'msgbox("disposing")
End Sub

Sub Listensub_queryClosing(oEvent, bOwnership)
'msgbox("query")
End Sub

Sub Listensub_notifyClosing(oEvent)
Shutdown_all_Listen
End Sub 
3/ Put the following two macro's somewhere...

Code: Select all

Rem... Will Terminate stardesktop with only the last form which is closed.
Rem... This is as long as each of your forms or reports are opened with a similar "Open_Form" macro (with the listener lines included from 1/)
Rem... Only one desktop shortcut should be used. With the rest of your forms, open with a "switchboard" form which uses a similar macro.
Sub Shutdown_all_Listen

dim oForm as object
dim r as integer
dim f as integer
dim a as integer

r = 0
f = 0
a = 0
	 	
Rem... to check which forms are already loaded, to determin if this is the last form (or report) whish is  being closed. First do reports.
oEnum = Stardesktop.Components().createEnumeration
oDesk = CreateUnoService("com.sun.star.frame.Desktop")
oEnum = oDesk.Components().createEnumeration
while oEnum.hasMoreElements
a = a + 1
  oComp = oEnum.nextElement
'  msgbox oComp.CurrentController.Frame.Title
  if HasUnoInterfaces(oComp, "com.sun.star.frame.XModule") then
    if oComp.Identifier = "com.sun.star.sdb.TextReportDesign" then
    	r = r + 1	Rem... There is a report open still
    endif
   Else
   msgbox "Base UI might be open. Close that down afterwards."
   Exit sub
  endif
wend

Rem... and now for forms....
oEnum = Stardesktop.Components().createEnumeration
oDesk = CreateUnoService("com.sun.star.frame.Desktop")
oEnum = oDesk.Components().createEnumeration
while oEnum.hasMoreElements
a = a + 1
  oComp = oEnum.nextElement
  if HasUnoInterfaces(oComp, "com.sun.star.frame.XModule") then
    if oComp.Identifier = "com.sun.star.sdb.FormDesign" then
    	f = f + 1	Rem... There is a report open still
    endif
  endif
wend
Rem... f is the number of open forms, r is open reports and a is the total of everything found in the "stardesktop" container (including UI)
If f = 0 and a = 0 and r = 0 then		
Rem... Store the database if it is the last form
    ThisComponent.Parent.store()	
End if
				
'		msgbox "r " & r & " f " & f & " a " & a
If f = 0 and a = 0 and r = 0 then
	Dim cmdShell As String
    Rem... Must have the soffice.exe path in your Path environment variable
'    Msgbox "about to execute shell command"
		cmdShell = "soffice.exe macro:///MacroLibraryName.MacroModuleName.CallTerminateOpen()" 
		
		Shell(cmdShell,2) 	
Else
'msgbox "could not terminate"
End if
End sub

	Rem... Executed "stardesktop.terminate" via a shell command, as can't terminate stardesktop if using basic which has been run from a form.
Sub CallTerminateOpen()

Wait (500)
	stardesktop.terminate
	
End Sub
It is impossible to successfully call stardesktop.terminate from a basic macro run from a form you are wishing to close. This is because basic is still running when it tries to close soffice. It will generate an error. A way around this (Windows Only) is to use a shell command to call another little macro which then calls stardesktop.terminate. See this thread for details... http://www.oooforum.org/forum/viewtopic.phtml?t=8531

There is one little remaining problem. If you use the desktop shortcut "OpenForm" macro to open your forms and you accidentaly use the desktop shortcut twice, then calling stardesktop.terminate still leaves soffice running in the taskmanager and the database file locked. I have not found a way around this one yet. To then get soffice to close properly, you need to open and then close any open office document or reboot your pc.

I normally have the Open Office quickstarter turned off as that is another culprit for leaving the database locked after you think you have shut it down. To do this you go to Tools>Options>Memory.

If anyone knows of a better way to approach these problems please tell!

Cheers

Voo
OOo 2.3.X on MS Windows Vista
User avatar
voobase
Volunteer
Posts: 97
Joined: Tue Jan 15, 2008 3:07 pm
Location: Australia

Re: Running Form from Shortcut

Post by voobase »

I have modified my adoption of Quazzie Evil's "open form from desktop shortcut" macro to work with OOoBase 3.0
In short the benefits of the adaption are as follows....
- It is not required to have the "Quickstarter" running on the computer.
- Form's are appended to the "star desktop" container so that if a macro references this container the form is listed.

Remaining issue:
If data is added and form closed then data is not saved. See above for macro which uses thiscomponent.store() and "Xcloselistener"

Changes made to macro to make work with OOoBase 3.0... a slightly different path to determin if you can see "stardesktop" from "thiscomponent"
Those lines basically determin if the Base UI is open and if not the form is appended to the star desktop container.

Code: Select all

Rem... Modified to work with OOo.Base 3.0
Rem... Modified to putform in "StarDesktop" container and to also work with quickstarter turned off.
Rem... It also now checks if the form is already loaded so you don't lose data if you accidently use the shortcut again.
Sub openForm_StarDesktop_Mod (FormName As String)
   Dim Context AS Object
   Dim Conn As Object
   Dim DB As Object
   Dim FormDoc As Object
   Dim Args(1) As New com.sun.star.beans.PropertyValue
   
      Rem... to check if the form is already loaded, so don't accidently do it twice.
oEnum = Stardesktop.Components().createEnumeration
oDesk = CreateUnoService("com.sun.star.frame.Desktop")
oEnum = oDesk.Components().createEnumeration
while oEnum.hasMoreElements
  oComp = oEnum.nextElement
  if HasUnoInterfaces(oComp, "com.sun.star.frame.XModule") then
    if oComp.Identifier = "com.sun.star.sdb.FormDesign" then
      if left(oComp.CurrentController.Frame.Title, len(FormName)) = FormName then
      With oComp.GetCurrentController().GetFrame().GetContainerWindow()
         .SetPosSize(0,0,,,com.sun.star.awt.PosSize.POS)
         .SetPosSize(,,1440,860,com.sun.star.awt.PosSize.SIZE)
         .setFocus()
      End With
      msgbox "This form is already open!"
        exit sub
      endif
    endif
  endif
wend
   
   
   Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
   DB=Context.getByName("DataBaseName") REM YOUR DB NAME
   Conn=DB.getconnection("","")
   Args(0).Name="ActiveConnection" : Args(0).Value=Conn
   Args(1).Name="OpenMode" : Args(1).Value="open"
   FormDoc=DB.DatabaseDocument.FormDocuments.loadComponentFromURL(FormName,"_self",2,Args)
   'FormDoc=conn.parent.databasedocument.getFormDocuments.loadComponentFromURL(FormName,"_self",2,Args)
   
   ' following lines are added
Rem... First check if UI is running, where problems may occur,
   Rem... by determining if can "see" Stardesktop from ThisComponent
   If isNull (thiscomponent.CurrentController.Frame.Creator) then
	oFormFrame = FormDoc.getCurrentController().getFrame() Rem... Thanks Hanya
      ' append its frame into the frames container of the Desktop
	StarDesktop.getFrames().append(oFormFrame)
	Elseif isNull (thiscomponent.CurrentController.Frame.Creator.Creator) then Rem.. This adaption to work for OO3.0
		oFormFrame = FormDoc.getCurrentController().getFrame()
		StarDesktop.getFrames().append(oFormFrame)
   End if

      With FormDoc.GetCurrentController().GetFrame().GetContainerWindow()
      .SetPosSize(0,0,,,com.sun.star.awt.PosSize.POS)
      .SetPosSize(,,1440,860,com.sun.star.awt.PosSize.SIZE)
      .setFocus()
   End With   
End Sub
Voo
OOo 2.3.X on MS Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Running Form from Shortcut

Post by Villeroy »

Just store your forms and reports outside that f*ng Base jail and create a desktop shortcut. Just now I found that you do not even need to register the database.
Set the form's data source property to your file:///path/database.odb
If the record set is a table, enter the table name manually since the combo box does not offer the name.
Base will complain that there is no such table, but you know better, don't you?
Shut down the office completely and start again with the form document. If you created the form manually from scratch as I did, you get a connection to the document, the form recognizes it's source and you can add controls now.
Since the form is embedded in a normal Writer document you can call it like any other document from a desktop link, menu or whatever your shell has to offer. And guess what: you can even link that form to subforms which are bound to other databases.
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
djskafish
Posts: 11
Joined: Tue Dec 01, 2009 11:02 pm

Re: Running Form from Shortcut

Post by djskafish »

I connected the final dot by reading a post in the other forum. What isn't explained here is that you can copy the text that the shortcut generator gives you and then paste it in to the box that appears when you right-click the desktop and choose New > Shortcut.
Windows XP Professional en Español, OOo 3.1
DYoung
Posts: 2
Joined: Wed Dec 16, 2009 12:23 pm

Re: Running Form from Shortcut

Post by DYoung »

Villeroy wrote:Just store your forms and reports outside that f*ng Base jail and create a desktop shortcut. Just now I found that you do not even need to register the database.
Set the form's data source property to your file:///path/database.odb
If the record set is a table, enter the table name manually since the combo box does not offer the name.
Base will complain that there is no such table, but you know better, don't you?
Shut down the office completely and start again with the form document. If you created the form manually from scratch as I did, you get a connection to the document, the form recognizes it's source and you can add controls now.
Since the form is embedded in a normal Writer document you can call it like any other document from a desktop link, menu or whatever your shell has to offer. And guess what: you can even link that form to subforms which are bound to other databases.
Hi Villeroy, Happy New Year to you.
This is something I am trying to do... I have a database on drive Z (for example) and a folder on 3 pc's containing about 5 forms, one of them a main menu (this is shortcut on desktop).
I am having trouble opening another form from a button on the 'Main Menu' form, and then having the 'Add_Data' form and 'Modify_Data' form working as they normally would inside the database program.
Is there something I am missing?

Many thanks
Don
OpenOffice Version 3.1.0
Windows XP SP3
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Running Form from Shortcut

Post by Villeroy »

A database on drive Z? What type of database? The embedded database Base can create from scratch is only a single-user database.
Only a server driven database can handle multi-user access. Something like MySQL, PostgreSQL or anything similar.
Then you can connect one Base file per client with a set of forms.

[Server DB] <--> |network| <--> [local Base file with config and queries] <--> forms, reports and other local Office documents (embedded in the Base file or not).
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
djskafish
Posts: 11
Joined: Tue Dec 01, 2009 11:02 pm

Re: Running Form from Shortcut

Post by djskafish »

Hi. I have encountered a new problem with the shortcut (using shortcut generator) after upgrading to OOo 3.2. The shortcut opens and then immediately closes the form. When the full database is open, this does not occur. I imagine, then, that the problem is related to Open Office not running when the form is opened. Any thoughts?
Windows XP Professional en Español, OOo 3.1
Post Reply