Page 1 of 1
[Solved] Base: Set Macro Variable to Form Value
Posted: Tue Jul 17, 2018 4:08 am
by jmadero
Hi All,
I'm trying to pass a value that is in a Text Box to a variable. The ultimate goal is to search for the value in the text box in Google.
Code: Select all
Sub GoogleBookTitle
Dim launcher As Object
Dim aWebPage As String
form_container = ThisDatabaseDocument.FormDocuments
myLibraryform = form_container.MyLibrary
book_name = myLibraryForm.getByName("BookTitle").string
launcher = CreateUnoService("com.sun.star.system.SystemShellExecute")
aWebPage = "https://www.google.com/search?source=hp&ei=NUpNW6KJAoritQXXqozYCw&q=Book Title: "&MyLibraryForm
launcher.execute(aWebPage, "", 0)
End Sub
I'm hitting a problem with this line:
Code: Select all
book_name = myLibraryForm.getByName("BookTitle").string
Form name is "MyLibrary", textbox name is "BookTitle".
Re: Base: Set Macro Variable to Form Value
Posted: Tue Jul 17, 2018 6:36 am
by UnklDonald418
Try
Code: Select all
myLibraryform = ThisComponent.DrawPage.Forms.getByName("MyLibrary")
Then your code to get the string from "BookTitle" should work.
If you use the MRI tool you could discover that yourself
[Tutorial] Introduction into object inspection with MRI
Re: Base: Set Macro Variable to Form Value
Posted: Tue Jul 17, 2018 6:52 am
by jmadero
Get a different error when I have that code:
BASIC runtime error.
An exception occurred
Type: com.sun.star.container.NoSuchElementException
Message:
Code: Select all
Sub GoogleBookTitle
Dim launcher As Object
Dim aWebPage As String
myLibraryForm = ThisComponent.Drawpage.Forms.getByName("MyLibrary") 'Get Form'
book_name = myLibraryForm.getByName("BookTitle").getString
launcher = CreateUnoService("com.sun.star.system.SystemShellExecute")
aWebPage = "https://www.google.com/search?source=hp&ei=NUpNW6KJAoritQXXqozYCw&q=Book Title"& MyLibraryForm
launcher.execute(aWebPage, "", 0)
End Sub
Re: Base: Set Macro Variable to Form Value
Posted: Tue Jul 17, 2018 11:40 am
by Villeroy
Most simple macro for all kinds of URLs, files, emails etc:
viewtopic.php?f=100&t=92331
Easy installer in the test database. No customization required within the macro code. Just add your own push button to your own form with a column name as "additional info" and the "approve action" event pointing to the macro.
Re: Base: Set Macro Variable to Form Value
Posted: Tue Jul 17, 2018 3:40 pm
by jmadero
I'd really like to understand why mine isn't working. The craziest thing is that someone on another forum gave me a very simple example that works without a hitch. I copied the code and get the same error as mentioned before.
he form that it's connected to is "MyLibrary". The button is the Google looking button next to "BookTitle".
My db:
https://drive.google.com/open?id=1kCiKT ... PX4nMR3i4E
The actual code is in -> WebsiteButtons -> Module1 ->GoogleBookTitle
Re: Base: Set Macro Variable to Form Value
Posted: Tue Jul 17, 2018 6:22 pm
by UnklDonald418
The craziest thing is that someone on another forum gave me a very simple example that works without a hitch. I copied the code and get the same error as mentioned before.
Copying others macro code without understanding it often leads to these kinds of errors. Part of the problem you are experiencing involves confusion over the word "form" because it can have multiple meanings. Base contributes to the problem by having a Forms area on the main database page. Actually, it should be Form Documents. Looking at the example you referenced,
MyLibrary is the name of a form document. A form document is a collection of 1 or more Forms. Macro code often is not portable when it is written to work with a specific form document. One clue is whenever
getByName() is used in the code you will likely have portability issues.
Looking at the example database you referenced, Open the form document
MyLibrary in the Edit/Design mode.
Near the bottom left of the screen is the Form Design toolbar.
Select the
Form Navigator (5th icon from the left) to open the Form Navigator dialog.
In the Form Navigator dialog looking at the structure of the form document notice that the control BookTitle appears inside the Form named MainForm.
I modified the macro code found at WebsiteButtons -> Module1 ->GoogleBookTitle to read:
Code: Select all
myLibraryForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
Now when I press the Google button to the right of BookTitle my web browser opens to a results page of a Google search on the text entered in BookTitle.
Re: Base: Set Macro Variable to Form Value
Posted: Tue Jul 17, 2018 7:32 pm
by UnklDonald418
You can also get the correct form using
Code: Select all
myLibraryForm = ThisDatabaseDocument.FormDocuments.getByName("MyLibrary").Component.Drawpage.Forms.getByName("MainForm")
But that has the least portability, because it requires 3 calls to getByName() to get the text you need.
Here is an more portable version of the macro. Since the button is on the same form as the TextBox control named "BookTitle" this macro doesn't need to know the name of the form document or the form, it can obtain the form object from the button event.
Code: Select all
Sub GoogleBookTitle (oEvent as object)
Dim launcher As Object
Dim aWebPage As String
myLibraryForm = oEvent.Source.Model.Parent
book_name = myLibraryForm.getByName("BookTitle").Text
launcher = CreateUnoService("com.sun.star.system.SystemShellExecute")
aWebPage = "https://www.google.com/search?source=hp&ei=NUpNW6KJAoritQXXqozYCw&q=" & book_name
launcher.execute(aWebPage, "", 0)
End Sub
Re: Base: Set Macro Variable to Form Value
Posted: Tue Jul 17, 2018 9:45 pm
by jmadero
UnklDonald418 wrote:
Looking at the example database you referenced, Open the form document
MyLibrary in the Edit/Design mode.
Near the bottom left of the screen is the Form Design toolbar.
Select the
Form Navigator (5th icon from the left) to open the Form Navigator dialog.
In the Form Navigator dialog looking at the structure of the form document notice that the control BookTitle appears inside the Form named MainForm.
I modified the macro code found at WebsiteButtons -> Module1 ->GoogleBookTitle to read:
Code: Select all
myLibraryForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
Now when I press the Google button to the right of BookTitle my web browser opens to a results page of a Google search on the text entered in BookTitle.
This did the trick. I really am trying to understand the underlying logic, not seeing where in the Navigator I see form "MainForm". I have the navigator open but not seeing anything with regards to "MainForm".
Thank you so much for the help. I'm sure I'll have 100x more macro related questions as I crawl through this project. Really appreciate it.
Re: Base: Set Macro Variable to Form Value
Posted: Tue Jul 17, 2018 10:19 pm
by UnklDonald418
There is a Navigator for inspecting components of a document that appears on the Standard toolbar along the top of the screen. If the title of the dialog is just "Navigator" then you won't find what you need there.
Right click on the form document MyLibrary and select Edit.
To the left, along the bottom of the screen should be a row of icons, including Select and Design Mode On/Off, Form and Form Navigator.
If you don't find those icons then select View>Toolbars>Form Design so that there is a check-mark in front of Form Design.
The 1st entry on the Form Navigator dialog is Forms. This is an important tool that allows you not only to inspect, but to edit all the elements on a form document. Right click on a control in the Form Navigator and select Properties to open the Properties dialog for that control.
Re: Base: Set Macro Variable to Form Value
Posted: Tue Jul 17, 2018 10:44 pm
by jmadero
UnklDonald418 wrote:There is a Navigator for inspecting components of a document that appears on the Standard toolbar along the top of the screen. If the title of the dialog is just "Navigator" then you won't find what you need there.
Right click on the form document MyLibrary and select Edit.
To the left, along the bottom of the screen should be a row of icons, including Select and Design Mode On/Off, Form and Form Navigator.
If you don't find those icons then select View>Toolbars>Form Design so that there is a check-mark in front of Form Design.
The 1st entry on the Form Navigator dialog is Forms. This is an important tool that allows you not only to inspect, but to edit all the elements on a form document. Right click on a control in the Form Navigator and select Properties to open the Properties dialog for that control.
Nice! Thank you again.
On the same topic now, thought about creating a new thread but it's the same issue. I've successfully made buttons for "Book Title" and I'm moving to the dropdown menu of authors. Hitting another snag and I'm unclear why.
Code: Select all
Sub GoogleAuthor
Dim launcher As Object
Dim aWebPage As String
myLibraryForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
book_author = myLibraryForm.getByName("Author").Text
launcher = CreateUnoService("com.sun.star.system.SystemShellExecute")
aWebPage = "https://www.google.com/search?source=hp&ei=NUpNW6KJAoritQXXqozYCw&q=Author: " & book_author
launcher.execute(aWebPage, "", 0)
End Sub
Error:
BASIC runtime error.
Property or method not found: Drawpage.
Re: Base: Set Macro Variable to Form Value
Posted: Wed Jul 18, 2018 12:35 am
by UnklDonald418
I'm not sure why you got that error message because DrawPages isn't the issue here.
I copied you macro code into my copy of your database and connected it to the Approve Action event for the Goggle button to the right of the box containing the author's name. I get a NoSuchElementException.
Again, using the Form Navigator look for a control named Author. There isn't one!
With the Form Navigator still open, select the box on your form that contains the author's name.
Now look in the Form Navigator and notice the the highlighted item is "List Box 1".
Replace "Author" with "List Box 1" and give it a try.
Uh-oh a different error message. Property or Method not found: Text
This is where learning to use the the MRI tool is essential if you want to write macro code.
If you inspect the ListBox control using MRI you will see that it has no Text property, thus the error message.
Looking through the list of properties there is something that looks promising, CurrentValue, and looking to the right in the MRI dialog there is the author name for the first record in your database.
Replace Text with CurrentValue and try your macro again.
Re: Base: Set Macro Variable to Form Value
Posted: Wed Jul 18, 2018 1:28 am
by jmadero
Yeah I'm going to check out that tool when I'm at home - currently in my office which makes it a bit tougher.
So - now the issue is that it's returning the AuthorID instead of the actual text that is in the dropdown. A Google Search for "11" isn't as useful as a Google search for "Maxine Hong Kingston" :-b
Re: Base: Set Macro Variable to Form Value
Posted: Wed Jul 18, 2018 7:11 am
by UnklDonald418
That is interesting.
I tested the macro in LO and confirm the CurrentValue returns 11. Using MRI I see no easy way to get the name needed for the search.
When I run the macro in AOO 4.15 CurrentValue returns "Maxine Hong Kingston" and the macro works as expected.
Re: Base: Set Macro Variable to Form Value
Posted: Wed Jul 18, 2018 4:23 pm
by jmadero
Confirmed, does work fine in OOo.
What I'm wondering is if there's a way to do a sql search in the macro to search the Authors table to match the Author's name based on the AuthorsID that is returned in LibO. Any thoughts on that approach? I'm going to report a bug with LibO about .CurrentValue returning the wrong value.
More interesting findings!
New Database:
https://drive.google.com/open?id=1kCiKT ... PX4nMR3i4E
Finding: I included nearly identical code for "Series" and connected the new macro ("GoogleSeries") to the appropriate button under the book series. In OOo this is returning the SeriesID and not the SeriesName! This is identical to the behavior in LibO for Series AND identical to LibO for the Author but inconsistent compared to the behaviour that we previously discussed (Author in OOo works fine).
UPDATE: Cannot reproduce but at least on one occasion OOo returned AuthorID instead of AuthorName.
Re: Base: Set Macro Variable to Form Value
Posted: Wed Jul 18, 2018 4:33 pm
by jmadero
Re: Base: Set Macro Variable to Form Value
Posted: Wed Jul 18, 2018 8:55 pm
by UnklDonald418
This works for me in both LO and AOO
Code: Select all
Sub GoogleAuthor
Dim launcher As Object
Dim aWebPage As String
Dim oDoc as Object, oControl as Object
Dim item As Integer
'If Not Globalscope.BasicLibraries.isLibraryLoaded("MRILib") Then
' Globalscope.BasicLibraries.LoadLibrary( "MRILib" )
'End If
'Dim oMRI as object
'oMRI = CreateUnoService( "mytools.Mri" )
oDoc = ThisComponent
'oMRI.inspect oDoc
myLibraryForm = oDoc.Drawpage.Forms.getByName("MainForm")
oControl = myLibraryForm.getByName("Author")
'oMRI.inspect oControl
item = oControl.SelectedItems(0)
book_author = oControl.StringItemList(item)
' book_author = oControl.CurrentValue '.Text
launcher = CreateUnoService("com.sun.star.system.SystemShellExecute")
aWebPage = "https://www.google.com/search?source=hp&ei=NUpNW6KJAoritQXXqozYCw&q=Author: " & book_author
launcher.execute(aWebPage, "", 0)
End Sub
I left the MRI code in the macro in case we need to do any more checking, but I did comment if out so it shouldn't interfere with normal operation.
Re: Base: Set Macro Variable to Form Value
Posted: Wed Jul 18, 2018 9:12 pm
by jmadero
Seems to work! Can I ask, how is this working:
SelectedItems is the dropdown I think, as for the "(0)", I'm not sure why it's 0 vs. some variable to pass AuthorID forward. Any explanation appreciated. I'm not a programmer by trade but I try to understand and learn so I can avoid bothering everyone in the future.
Re: [Solved] Base: Set Macro Variable to Form Value
Posted: Thu Jul 19, 2018 12:24 am
by UnklDonald418
Multiselection is one of the properties of a ListBox. 99% of the time that is set to No, but there are some rare situations where someone needs to select more than on item from a ListBox. To accommodate that situation SelectedItems is an array
http://www.openoffice.org/api/docs/comm ... ectedItems
Arrays in OpenOffice/LibreOffice begin with element 0 so an array with five elements will be numbered 0,1,2,3,4. When Multiselection is set to No there is only one array element, 0.
If you use MRI to inspect oControl and double click on SelectedItems it will display something like (000) = 2 where the left side in parentheses is the array index and the right side is the actual value. So, in this case
item will be 2.
StringItemList is an array of strings, so in MRI if you can double click on it to see the array contents. In this case StringItemList(2) will return the 3rd array element Joel Madero.