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

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

edit retag close merge delete

Sort by » oldest newest most voted

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

more

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:

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.

more

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.

( 2020-03-29 00:12:46 +0100 )edit

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.

( 2020-03-29 01:18:57 +0100 )edit

@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.

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

( 2020-03-29 03:04:12 +0100 )edit

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).

( 2020-03-30 00:12:22 +0100 )edit

@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 ...(more)

( 2020-03-30 11:16:13 +0100 )edit