[Solved] What happened to my 'Controls' collection?

Creating and using forms
Post Reply
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

[Solved] What happened to my 'Controls' collection?

Post by owlbrudder »

The Access2Base object model at http://www.access2base.com/access2base.html shows a form has a 'Controls()' collection and a subform also has a 'Controls()' collection.

The following code works down to oControl = oForm.Controls.getbyname("cmbItemDetails"):

Code: Select all

Sub MoveCursor
   dim oForm as object
   dim oControl as object

   oForm = Thiscomponent.Drawpage.Forms.getByName("frmOrders").getByName("subfrmOrderLines")

msgbox(oForm.Name) ' --- this shows the correct name

   oControl = oForm.Controls.getbyname("cmbItemDetails") ' --- this throws an error
End Sub
The error massage says "BASIC runtime error. Property or method not found: Controls."

I have placed a break point on the msgbox statement, so I can examine the oForm object. The nearest I could find to a Controls collection was an array of ControlModels, but trying to access oForm.ControlModels threw the same error as above. When the collection name was changed to 'ElementNames', which was the only other possibility I could find, the error meassage changes to 'Object variable not set'. There certainly was not a 'Controls' collection exposed by the watched object.

Where can I find the up-to-date object model? Alternatively, what am I doing wrong?

Any help would be gratefully received.
Last edited by floris v on Thu Nov 24, 2016 10:27 am, edited 2 times in total.
Reason: Added Solved icon, floris v, moderator
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: What happened to my 'Controls' collection?

Post by UnklDonald418 »

You should install and use either the MRI extension or the Xray tool. You would then be able see that oForm doesn't have a Control property.
I think you may find what you want at

Code: Select all

oControl = oForm.getbyname("cmbItemDetails")
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: What happened to my 'Controls' collection?

Post by owlbrudder »

UnklDonald418 wrote:You should install and use either the MRI extension or the Xray tool.
Thanks very much UnlDonald418 - that put me on the right track. I will look into the MRI extension / Xray tool and learn what they do.

For the sake of anyone following this thread, what worked for me is:

Code: Select all

Sub subTest2
	Dim frmMyForm as Object
	Dim ctlMyControl as Object
	Set frmMyForm = Thiscomponent.Drawpage.Forms("frmOrders")
	Set ctlMyControl = frmMyForm.getByName("subfrmOrderLines").getByName("cmbStockItem")
	msgBox(ctlMyControl.Name)
End Sub
Now I can stop banging my head against the wall. "8-)

What confused me is the object model showing a form has Control(...), which I interpreted as a collection. Sigh. So much to learn.
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: What happened to my 'Controls' collection?

Post by owlbrudder »

owlbrudder wrote:[So much to learn.
UnklDonald418, you might be able to put me right on another topic. I have been using the documentation at access2base http://www.access2base.com/access2base.html and finding it does not describe the version of a2b that came with my L.O. 5.1.6.2.0+. Is there a better / newer / correcter (grin) description of the object model and usage examples that I could lay my hands on?

Also, what is the correct way of getting the current database as an object? access2base says to use Application.CurrentDb, or just CurrentDb, but they return the null object.

I am also having no luck finding how to access the current value in a control.

Just to put this in perspective, I am a long-time Access VBA programmer, so I would have a head-start on all this if I could find the current object model etc.

Kind regards,
Doug
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] What happened to my 'Controls' collection?

Post by UnklDonald418 »

I've never written any code in VBA so I can't be of much help to you there.
My understanding is the while on the surface Access and Base are similar, under the hood they are quite different.

I primarily use the MRI extension which is an object inspection tool that can be quite helpful in locating objects, properties, methods, interfaces and services. Here is a link to a tutorial that can get you started
viewtopic.php?f=74&t=49294
The Xray tool has similar capabilities and can be downloaded from
http://berma.pagesperso-orange.fr/index2.html

Andrew Pitonyak has a book “OpenOffice.org Macros Explained” and some other documents including “OpenOffice.org Base Macro Programming” that can be downloaded from
http://www.pitonyak.org/oo.php

Another resource that I have found helpful is a book by Roberto Benitez “Database Programming with OpenOffice.org Base & Basic” . This book is available for purchase.
http://www.baseprogramming.com/
http://www.baseprogramming.com/resources.html
What confused me is the object model showing a form has Control(...), which I interpreted as a collection.
The object oForm can have elements, which can be controls or other forms. If you open your form in the design mode and select the Form Navigator from the Form Design Toolbar (usually found near the bottom left of the window) you can see a visual representation of the form structure. You can also use that to adjust control properties and how data is loaded and linked in the forms.
Also, what is the correct way of getting the current database as an object? access2base says to use Application.CurrentDb, or just CurrentDb, but they return the null object.
You can get there using an Event on the form or from ThisComponent. Using ThisComponent is can be tricky though. It is wise to assign it using something like

Code: Select all

 oDoc = ThisComponent
early in your macro. ThisComponent returns the currently active window, which can change during the lifetime of the macro, especially if you are using the IDE, MRI or XrayTool.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: [Solved] What happened to my 'Controls' collection?

Post by owlbrudder »

Thank you once again UnklDonald418 - -you have answered my questions thoroughly and I appreciate your time. Now for more study!
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] What happened to my 'Controls' collection?

Post by UnklDonald418 »

I just noticed I failed to post the last part of my last message
I am also having no luck finding how to access the current value in a control.

try adding this to your code

Code: Select all

oFormDoc = oForm.Parent.Parent.Parent   
oControlView = oFormDoc.CurrentController.getFormController(oForm)
strcmbItemDetails = oControlView.Model.getByName("cmbItemDetails").Text
print strcmbItemDetails
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: [Solved] What happened to my 'Controls' collection?

Post by owlbrudder »

Brilliant! Exactly what I was looking for, thank you. I have installed MRI and it will answer 99% of my questions, but that one would have stumped me. Thank goodness for Dr. Google.
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: [Solved] What happened to my 'Controls' collection?

Post by owlbrudder »

Hmmm ... strcmbItemDetails = oControlView.Model.getByName("cmbItemDetails").Text throws an error "Property or method not found: text". I will keep bashing away with MRI and the documentation to see if I can crack this problem. Don't you love learning curves? <grin>
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] What happened to my 'Controls' collection?

Post by UnklDonald418 »

Ok here is a slightly more robust version for you to try.

Code: Select all

 oFormDoc = oForm
Do     
          oFormDoc = oFormDoc.Parent
Loop Until oFormDoc.ImplementationName =  "SwXTextDocument"  

if oFormDoc.CurrentController.isFormDesignMode() then
          beep ' can't access data when form is in the design mode
else
           oControlView = oFormDoc.CurrentController.getControl(oControl)
'oMRI.inspect oControlView
	strcmbItemDetails = oControlView.Text
	strcmbItemDetails = oControlView.Model.Boundfield.string
   print strcmbItemDetails
end if
The version I sent before assumed that cmbItemDetails was on subform, this version doesn't make that assumption. It loops through the forms looking for the form document.
It uses the method getControl() instead of getFormController(). I've included two ways to see the data. oControlView.Text is the more direct method, but if you are going to make changes to the data using oControlView.Model.Boundfield.String, using the Boundfield is considered the safest way to make changes. But this only makes the changes to the screen so to actually save the data in the table you will also need to use

Code: Select all

oControlView.model.commit
Don't you love learning curves?
Yes, and the complicated API coupled with the somewhat opaque documentation adds to the challenge.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: [Solved] What happened to my 'Controls' collection?

Post by owlbrudder »

somewhat opaque documentation - what an understatement! I have found a number of documents all of which disagree with each other and reality.

Thanks so much for taking the time to help me. The fresh example you gave will be studied with interest.

I see you refer to MRI as 'oMRI', as if you have instantiated it as an object - would that be right? If so, I would be interested to see the assignment statement, as I expect it must reference the MRI library in some way.

Once again, many thanks.
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: [Solved] What happened to my 'Controls' collection?

Post by owlbrudder »

Hooray! I seem to be getting the hang of this. The element details available in the 'Watch' window allowed me to figure out why 'Text' threw an error in your latest code snippet. I changed it as follows:

Code: Select all

   strcmbItemDetails = oControlView.SelectedItem
   strcmbItemDetails = oControlView.Model.Boundfield.string
oControlView.SelectedItem returns the string value of the combo box and oControlView.Model.Boundfield.string returns the value of the bound field, in my case an integer correctly returned as 83 (at present) (perhaps it is the string representation of an integer - I'll have to investigate further).

Now I'll read the MRI documentation and that should get me streets ahead. "8-)

Cheers,
Doug
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Solved] What happened to my 'Controls' collection?

Post by RPG »

Hello

I think start reading this two links.
Interface XFormController
Interface XFormOperations

Read first the text and then try to understand what it means.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: [Solved] What happened to my 'Controls' collection?

Post by owlbrudder »

I think start reading this two links.
Thanks for the suggestion Romke. I will have to learn the language involved in order to make much sense of it. I gather this is what access2base actually 'sits' on, so it will be worth my while getting to understand it.

Cheers,
Doug
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] What happened to my 'Controls' collection?

Post by UnklDonald418 »

perhaps it is the string representation of an integer
Yes. While using MRI look at the methods for Boundfield. There is a list of getXXX() functions including getInt().

While looking at the Boundfield using MRI select the Type property and press the button IDL Ref.
If you have MRI set up properly pressing the button will open the OO Developers Guide page for the property Type. Follow the DataType link in the displayed information to see all the supported types.

Actually Benito Benitez has all the code listings from his book posted on
http://www.baseprogramming.com/resources.html
Chapter 4 includes Function getXXX(col) As Variant
Then you can use it

Code: Select all

strcmbItemDetails = getXXX(oControlView.Model.Boundfield)
strcmbItemDetails will return formatted with the type defined by Boundfield.TypeName.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: [Solved] What happened to my 'Controls' collection?

Post by owlbrudder »

UnklDonald418 and Romke, I owe you both a beer (or coffee or something - whichever you prefer). With the information you have provided and the MRI tool to work with, I am well on my way.

What did we do before the Internet? We would never have met and I would still be floundering.

Thanks again.

Cheers,
Doug
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: [Solved] What happened to my 'Controls' collection?

Post by owlbrudder »

This still relates to controls and collections, but is looking at them from a different perspective.

I have a heirachy of three forms: Main, sub and sub sub. I can reference Main and sub OK, but I can't get to the sub sub form.

Here is the code I am using:

Code: Select all

Dim oFormDoc as Object
	Set oFormDoc = Thiscomponent.Drawpage.Forms("frmOrders").getByName("subfrmOrderLines").getByName("subsubfrmStockItems")
The heirachy is correct and the form names are correct. If I run this code in a subroutine, it throws an error "NoSuchElementException".

I have been over the code with a fine-toothed comb and I am certain all the spellings are correct. I can set oFormDoc to the first subform (subfrmOrderLines) successfully, so it must be to do with my way of addressing the sub sub form (subsubfrmStockItems").

Am I doing something wrong? "8-|
Attachments
The form heirachy in Form Navigator
The form heirachy in Form Navigator
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Solved] What happened to my 'Controls' collection?

Post by RPG »

Hello

You have an error after forms.
Do it step by step.
Use also hasbyname

see
Working with Forms - Apache OpenOffice Wiki

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: [Solved] What happened to my 'Controls' collection?

Post by owlbrudder »

owlbrudder wrote:I have a heirachy of three forms: Main, sub and sub sub. I can reference Main and sub OK, but I can't get to the sub sub form.
The mystery deepens: I can see the sub sub form in the Controls collection of the sub form when I iterate through it see image attached), yet cannot get to it through getByName(sub sub form name). Am I using the wrong syntax?

... Later ...

The following code is a work-around, but I would love to know while original statement threw an error. The working code is:

Code: Select all

	Set oFormDoc = Thiscomponent.Drawpage.Forms("frmOrders").getByName("subfrmOrderLines")
'	Set oFormDoc = oFormDoc.getByName("subsubfrmStockItems")
	Controls=oFormDoc.createEnumeration()
	control=Controls.nextElement()
	While Controls.hasMoreElements() AND control.Name <> "subsubfrmStockItems"
		print control.Name
		control=Controls.nextElement()
	Wend
	If control.Name = "subsubfrmStockItems" Then
		Set oFormDoc = control
	End If
Romke, thanks for the hint - hasByName returned true, so I went back to the original chained statement. I am going to find a hard wall and start beating my head against it! The chained getByName is now working and I don't know why it failed before and I don't know why it is working now:

Code: Select all

	Set oFormDoc = Thiscomponent.Drawpage.Forms("frmOrders").getByName("subfrmOrderLines").getByName("subsubfrmStockItems")
:crazy:
Attachments
Code showing the subsubform name in the Controls collection
Code showing the subsubform name in the Controls collection
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] What happened to my 'Controls' collection?

Post by UnklDonald418 »

Did you have a MRI inspection call or stop point set on or before the line

Code: Select all

   Set oFormDoc = Thiscomponent.Drawpage.Forms("frmOrders").getByName("subfrmOrderLines").getByName("subsubfrmStockItems")
that has since been removed?
As I pointed out in an earlier post it would be safer to start your macro with

Code: Select all

oDoc = ThisComponent
and then

Code: Select all

   Set oFormDoc = oDoc.Drawpage.Forms("frmOrders").getByName("subfrmOrderLines").getByName("subsubfrmStockItems")
can be safely used anywhere in the macro.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
owlbrudder
Posts: 93
Joined: Tue Nov 08, 2016 8:04 am

Re: [Solved] What happened to my 'Controls' collection?

Post by owlbrudder »

UnklDonald418 wrote:Did you have a MRI inspection call or stop point set on or before the line

Code: Select all

   Set oFormDoc = Thiscomponent.Drawpage.Forms("frmOrders").getByName("subfrmOrderLines").getByName("subsubfrmStockItems")
that has since been removed?
Yes, I was stepping through the code with watch and MRI telling me what was going on. Great tool MRI. I could not get Xray working the first time, but I will have another go just to see what it is all about.
As I pointed out in an earlier post it would be safer to start your macro with

Code: Select all

oDoc = ThisComponent
and then

Code: Select all

   Set oFormDoc = oDoc.Drawpage.Forms("frmOrders").getByName("subfrmOrderLines").getByName("subsubfrmStockItems")
can be safely used anywhere in the macro.
Yes, you did mention that and I forgot! "8-(

Your way I get a semi-permanent handle on ThisComponent which can no doubt be used for other purposes. I will amend my code as you suggest. Indeed, my original way - not using oDoc = ThisComponent - may have had something to do with the strange behaviour I was seeing.

Thanks for your help again. "8-)
LibreOffice Version: 5.3.3.0
Fedora 26 Linux x86_64
Core i7, 8Gb
Post Reply