How to Get and Put data from/to form fields with a macro

English
, , , , ,
#1

My environment: Windows 10, LibreOffice Version: 6.4.0.3 (x64) HSQLDB Split Database

Scenario:

I use lots of macros in my forms to handle conditional situations, resulting in intervention in database tables via sql, or changing form field content or presentation (enable/visible or not).
I wish to make a sub-routine for getting a value from a field, and one for putting a value to a field.
That would simplify my coding.

See my code attempt below.

The coding to put or get a field’s value depends on the file type such as --text, label, listbox, checkbox, radio-option, image, button-name.

In the PutToField subroutine, the code below works for fieldtypes text, label, button-name and checkbox, I can even put Now() in the place of “mynewtext” and put a date.
However in spite of many alternative tests I haven’t figured out yet how to get content to a listbox, option or radio-option field.

In the GetFromField subroutine, the code below works for fieldtype listbox, text, label, button-name and checkbox.
However in spite of many alternative tests I haven’t figured out yet how to get content from option and radio-option fields.

Sub PutToField (sTargetName As String, sTargetContent As String, sTargetType As String)
      Dim oForm As Object
      Dim oPutField As Object
      Dim Doc As Object
      Dim DocCtl As Object
      Dim CtlView As Object
      oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
      Doc = ThisComponent
      DocCtl = Doc.getCurrentController()
      oPutField = oForm.getByName(sTargetName)
      CtlView = DocCtl.GetControl(oPutField)
           If sTargetType = "Text" Or sTargetType = "Label" Then
           CtlView.Text = sTargetContent
           End If
           If sTargetType = "Button" Then
           CtlView.Label = sTargetContent
           End If
           If sTargetType = "CheckBox" Then
                If sTargetContent = "True" Then
                CtlView.State = "1"
                Else
                CtlView.State = "0"
                End If           
           End If
End Sub

Sub TryPut
      PutToField("txt-BriefDescription","mynewtext","Text")
End Sub

Sub GetFromField (sTargetName As String, sTargetType As String)
    Dim oForm As Object
    Dim oField As Object
    Dim sGetValue As String
    oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
    oField = oForm.getByName(sTargetName)
      If sTargetType = "ListBox" Then
      sGetValue = oField.SelectedValue
      End If
      If sTargetType = "Text" Then
      sGetValue = oField.Text
      End If
      If sTargetType = "Label" Or sTargetType = "Button" Then
      sGetValue = oField.Label
      End If
      If sTargetType = "CheckBox" Then
         If oField.State = "0" Then
         sGetValue = "False"
         Else
         sGetValue = "True"
         End If
      End If
    MsgBox(sGetValue)
End Sub

Sub TryGet
      GetFromField("listbox-Coordinates","ListBox")
End Sub

As you can see it’s mostly a question of one line of syntax one needs to know.
Can I get some help please in getting the lines that need to be added to the subroutines in order to handle the other types of fields mentioned. Thank you

Writer Macro to collect and insert data from Base Query
How to display conditional text based on value from an input list?
#2

Hello,

Examining each (you have only a partial list thus far) is covered in documentation already.

Here are some links which will be of vital help:

See http://www.pitonyak.org/database/AndrewBase.pdf

Also for a broader view look at Open Office Macros Explained by Andrew Pitonyak. PDF here → OOME

MRI - tool. Current versions found here → MRI releases and tutorial here → Introduction into object inspection with MRI and documentation here → MRI documentation

Edit 2020-03-29:

You original and edited question (please note edited sections when doing this) are very general in what is asked which is why the documentation was provided. Even the comments are too general. The code presented has nothing to do with option (radio) buttons and there are numerous ways to do things such as retrieving/setting list box items. Also, in the last comment you state the code from of the provided links gives errors (no sample of what you did) and

could not find any other reference to option or radio-option macro retrieval or setting

Except the second link in my comment was just that with an example in it. This in itself makes one question what is the sense of providing this information. Here is the macro code within that sample:

Sub sDisplayChoice
	Dim oForm1 As Object
	Dim oCtl As Object
	Dim oGroup As Object
	Dim i as Integer
	Dim iGotState as Integer
	oForm1 = ThisComponent.Drawpage.Forms.getByName("ControlChoice") 'Get Form'
	oForm1.getGroupByName("Type", oGroup)
	i = LBound(oGroup)
  		oCtl = oGroup(i)
		iGotState = (str(oCtl.State))
   		If iGotState = 0 Then
   			With oForm1
				.SpouseName.EnableVisible = False
				.SpouseLabel.EnableVisible = False
			End with
   		Else
   			With oForm1
				.SpouseName.EnableVisible = True
				.SpouseLabel.EnableVisible = True
			End With
   		End If
End Sub

It checks the state of the buttons in the group and sets other controls visibility.

Here is a link to adding items to a list box → How to Programmatically add or remove items in ListBox FORM control

Here is code (one of the methods) to get the selected value of a list box:

Sub ExecuteAction_AccountListBox
 oForm = ThisComponent.DrawPage.Forms.GetByName("LogQueryForm1")
 oControl = oForm.GetByName("lstAccountListBox")
 ShowInt = oControl.ValueItemList(oControl.SelectedItems(0))
 MsgBox ShowInt,0,"AccountListBox" 'Just for debugging
End Sub

Here is code to set an item as selected in a list box:

	Doc = ThisComponent
	DocCtl = Doc.getCurrentController()
'Get VIEW for listbox
  	CtlView = DocCtl.GetControl(oField1)
	CtlView.selectItemPos(iPointer - 1, True)

As you may begin to see, there is no universal method to any of this, which again is why you were pointed to the documentation and why your code in the question is not of help.

And no, I do not have a some ‘inventory’ but much is done with searching. Expended some time in just getting this information together. My main advantage is knowing the more obvious places to begin my search which originates from reading a lot of documents.

BASIC example - get information about a selected shape on LibreOffice Draw
Is it possible to open and resize a form dynamically?
#3

Thank you @Ratslinger, fair enough I gather my question is considered too wide so I am being sent to the library to study.
I was hoping for a quick answer on how to get the content of a text field and option field, that’s enough to help me move ahead.
I am not adverse to study documentation, and do so, nonetheless getting a push with some basic syntax brings not only understanding but also better insight and imagination for self-solving the next hurdle.

#4

@Ardee,

This is a question and answer site. Please limit to specific question. Even your comment is multiple questions. Also there are many, many, many posts here which have code samples. Just a couple of these:

retrieving the value from a multi radio button option group

Radio button actuating a text box

How do you put text in a Text Box with a Macro?

Please try searching this site for further samples. There are many more.

#5

@Ratslinger,
ok noted, I don’t have a database inventory of subjects handled here as I believe you do, so will need to spend the time needed to go through any I have missed that could resolve my quest and also dig through the documentation. I already found a few more in my own stack of macros and will update here as I progress.

I was aware of the 5 year old (so maybe older LO version) “retrieving the value from a multi radio button option group” link you gave and tried it but it gives


BASIC runtime error.
Property or method not found: State.

so didn’t help. I could not find any other reference to option or radio-option macro retrieval or setting.

#6

@Ardee,

Please note time has little to do with Base. Much has not changed in many years especially when dealing with macros.

To possibly help, here is a list of many of the controls available → FormComponentType

‘Many’ is stated as there are others which are available only on dialogs such as a Grid Control or Progress Bar (there may be others).

#7

@Ratslinger, I cannot take offense at your reprimands, being a newbie (1 month) in this forum and a newbie to LO.
I DID look at the 2nd link you gave and recognized it as one I had seen before, it does not show any code, and mentions an sql solution. I failed to notice the edit with a link to a sample odb which includes the code you now mention. I humbly acknowledge missing that, but it is not flagrant disregard. Thank you for your code examples, I will study and test to see how I can use that info. Thank you very much for your efforts. I will close this chapter since you have convinced me what I am asking/doing is quite useless and irrelevant to the forum (I tried to delete it but can’t), and will open a separate question if I still have a problem to adress option or radio-option individually showing only what code does NOT work, as you point out. Thank you also for the documentation links.

Comment empecher l'enregistrement dans la base sur erreur au controle
#8

I have figured out myself all the cases most often used.
Here is the final version:

    Sub GetFromField (sTargetName As String,sTargetType As String)
        
    Rem sTargetType "CheckBox" and "Option" gets the value, to get the CheckBox or Option Name use sTargetType "Label"
        
            Dim oForm As Object
            Dim oField As Object
            Dim sGetValue As String
            oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
            oField = oForm.getByName(sTargetName)
              If sTargetType = "ListBox" Then
              sGetValue = oField.SelectedValue
              End If
              If sTargetType = "Text" Then
              sGetValue = oField.Text
              End If
              If sTargetType = "Label" Or sTargetType = "Button"  Then
              sGetValue = oField.Label
              End If
              If sTargetType = "CheckBox" Or sTargetType = "Option" Then
                 If oField.State = "0" Then
                 sGetValue = "False"
                 Else
                 sGetValue = "True"
                 End If
              End If
            MsgBox(sGetValue)
    End Sub
      
      
    Sub TryGet
              GetFromField("listbox-Coordinates","ListBox")
    End Sub
     
   
    Sub PutToField (sTargetName As String,sTargetContent As String,sTargetType As String)
      Rem sTargetType "CheckBox" and "Option" puts the value digit, to put a CheckBox or Option Name use sTargetType "Label"
      Dim oForm As Object
      Dim oPutField As Object
      Dim Doc As Object
      Dim DocCtl As Object
      Dim CtlView As Object
      oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
      Doc = ThisComponent
      DocCtl = Doc.getCurrentController()
      oPutField = oForm.getByName(sTargetName)
      CtlView = DocCtl.GetControl(oPutField)
           If sTargetType = "Text" Or sTargetType = "Label" Then
           CtlView.Text = sTargetContent
           End If
           If sTargetType = "Button" Then
           CtlView.Label = sTargetContent
           End If
           If sTargetType = "CheckBox" Or sTargetType = "Option" Then
           CtlView.State = sTargetContent
           End If           
End Sub    
            
    Sub TryPut
              PutToField("txt-BriefDescription","mynewtext","Text")
    End Sub

These 2 simple subroutines make easy coding to get and place basic data, from there on code for other actions.

EDIT 2020-04-12
You can add “RadioGroup” as sTargetType, handled the same as “Button”.

The way to use this in practice, is to use a global variable replacing the line MsgBox(sGetValue) (which is obviuosly just for demo) with the line

sGlobalMacroResult = sGetValue
as an example Global Variable.

So here a simple usage example where if selected a radio button called option-Media should uncheck and hide the checkbox-FileAsIncoming otherwise the checkbox should be checked.

	Global sGlobalMacroResult As String
	
	Sub Type_AfterUpdate
	    Dim oForm As Object
	    Dim sMacroResponse As String
		    oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
		    PutToField("checkbox-FileAsIncoming",1,"Option")
		    oForm.getByName("checkbox-FileAsIncoming").EnableVisible = True
		    GetFromField("option-Media","Option")
		    sMacroResponse = sGlobalMacroResult
		        If sMacroResponse = "True" Then
		        PutToField("checkbox-FileAsIncoming",0,"Option")
		        oform.getByName("checkbox-FileAsIncoming").EnableVisible = False
		        End If
	End Sub

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.