Get table cell value from Form

Attempted to modify example from LO6.4 Base Guide pg 158 (get column value) – this is my adaptation (starting with Basic macro, using “factory reset” LO 6.4 & HSQL). [macro is executed: user puts cursor in cell & clicks on button; also Form event property “lose focus” used to pass cell value. Desire to open the string as html but – my problem is always the same, blank value returned from table, no string contents]. The subroutine executes without errors – here’s the code to get a string value from a table - that doesn’t work.

Sub GOTOURL 
Dim sGOTOURL As String ' no array, single value string, single column
DIM oDoc AS OBJECT
DIM oDrawpage AS OBJECT
DIM oForm AS OBJECT
DIM oTable AS OBJECT
  oDoc = thisComponent
  oDrawpage = oDoc.drawpage
  oForm = oDrawpage.forms.getByName("MainForm")
  oTable = oForm.getByName("SubFormB") ‘ The Form Navigator reports these names... '
  sGOTOURL = getString("Dry”) ' column number…..14 I tried column # and column name “Dry”, zero results '
  MSGBOX "GOTOURL is " + sGOTOURL ‘ In MSGBOX “sGOTOURL” value is blank '
End Sub

Hope someone notices obvious mistake(s) I made - I’m at a loss - hundreds of lines of code & 2 months clueless.

getString() from what? May be you mean

Sub GOTOURL
Dim oDrawPage As Variant
Dim oForms As Variant
Dim oMainForm As Variant
Dim oSubForm As Variant
Dim oColumns As Variant
Dim oDryColumn As Variant
Dim sGOTOURL As String

  oDrawPage = ThisComponent.getDrawPage()
  oForms = oDrawPage.getForms()
  oMainForm = oForms.getByName("MainForm")
  oSubForm = oMainForm.getByName("SubFormB")
  oColumns = oSubForm.getColumns()
  oDryColumn = oColumns.getByName("Dry") 
  sGOTOURL = oDryColumn.getString()
  MsgBox "GOTOURL is " + sGOTOURL 
End Sub
1 Like

Have worked with that suggestion for a few hours, regret receive same error “Type: com.sun.star.container.NoSuchElementException Message: There is no element named ‘Dry’”. The disconnect in obtaining the table/cell value from the subform - searches suggest this is not easy to do but the references indicate it is possible. I will post if I come up with a solution. In the mean time a hyperlink or a code snippet from anyone who has seen it work would be most happy. Thanks again.

I’m sorry I could not help I tried to answer the wrong question… The message “There is no element named ‘Dry’” indicates that there is no column with the same name in the table in SubFormB. (BTW, @ksd, I hope you didn’t try to say that I’m not the “who has seen it work”?)

Edit answer: Regret the ask.libreoffice.org interface is kicking my butt - as Ratslinger noted - sorry about that. With time & luck I should figure it out, maybe. But I got macro code (from other places on this forum) to work to get the cell value from one of many TableControls on a Form (e.g. mouse select cell value then call macro via button or keypress event):

Sub GOTOURL
DIM oDoc AS OBJECT
DIM oDrawpage AS OBJECT
DIM oForm AS OBJECT
DIM oTable AS OBJECT
DIM oGrid AS OBJECT
DIM oColumnis AS OBJECT
DIM myURL AS STRING
oDoc = thisComponent
oDrawpage = oDoc.drawpage
oForm = oDrawpage.forms.getByName("MainForm")
oTable = oForm.getByName("SubFormB")
oGrid = oTable.getByName("TableControlB")
oColumnis = oGrid.getByName("TextField10")
myURL = oColumnis.getCurrentValue()
    MSGBOX "the GoToURL is " + myURL
End Sub

It took me two months. Excellent examples that do better in case anyone has similar issues e.g.
[Solved] Select A Row in a Grid Control in a Macro (View topic) • Apache OpenOffice Community Forum or SOLVED: Read values from selected rows in a table control .

PS All the TableControls on the form will have identical column names “TextField1” etc., unless you changed the column names from the default :slight_smile:

Thank you!

@ksd,

This should not be posted as an answer. Post as comment or edit original post and add there noting it as edited information.

Also, code by @JohnSUN is what is needed. Have modified the sample in the link you provided, changed the form to have a sub form and all works. Your code here does not show getting the subform. Question code shows oTable while not-an-answer code shows oMyTable. Don’t know what you actually have.

Edit: Just tested this same form using code (just names changed) posted by @JohnSUN and had no problem.

1 Like

@ksd,

Edited for clarity. Use preformatted text icon on toolbar.

Also note, Table Controls will default to field names if you establish the form/subform data property BEFORE adding the control. Identical columns eliminated.

1 Like

Hello, thanks to you i learn how to get values from table in form. But also i need to set value to them, do you know how to do it?

@AndrewKorn
Setting values in a table control column is a bit more difficult. For one it depends upon the data type of the column. As in retrieving the data, once the field is obtained, you can move the data into the field with its value or text. The you must update/commit the data. See Table not updating from form when 'default' values applied - #2 by Ratslinger

If you still have issues, please post as a new question (burying questions is not helpful to others) with all relative issues of the data, your specific LO version, OS & version and the database you are using.