Update Text Box from File Selection

johnh009 · February 10, 2018, 6:53pm

Backgound: I have a form the records a Homecheck’s progress. Associated will be a Homecheck report. I wish for the user to be able to browse to find said homecheck report and have that location (file path) displayed in a text box (because, as you are no doubt aware, a Text Box is data aware - data can be written to, and retrieved from the relevant database table column - while the File Selection control is not). I’ve also configured a Push Button to open said report that works.

Progress: I have plagiarised a basic macro (or two!) so that the file path selected via the File Selection control is copied to the relevant Text Box. It is displayed there, and the report can be opened via the aforementioned Push Button.

The Problem: However, the appended file path seems to be temporary, as it is not saved to the database. If one clicks into the Text Box and make the smallest of changes - e.g. space and then delete it (the space) - the record senses a change has been made and can be saved. So the Text Box, despite having the file path appended to it, has not been suitably ‘nudged’ (for the want of a better term) to detect the change. The following is the macro I’m using, invoked from the ‘Text modified’ event on the File Selection control. (I tried a couple of other events, but the result is either the same or nothing happens).

Sub ChangeFNameHC(oEvent As Object) 'For the homecheck(HC) form
Dim oForm			As Object
Dim oSubForm 		As Object
Dim FilePath		As String
FilePath = ""
oForm = ThisComponent.getDrawPage().getForms().getByName("AddAmendHomecheck")
' oForm=ThisComponent.DrawPage.Forms.GetByName("AddAmendHomecheck")
' oForm = oEvent.Source.Model.Parent
oSubForm = oForm.getByName("SubForm")
FilePath = oSubForm.getByName("SelectFile").Text
oSubForm.getByName("txtpath_to_hc_rpt").Text = FilePath
' oForm.getByName("txtpath_to_hc_rpt").updateString(FilePath)
' oSubForm.getByName("txtpath_to_hc_rpt").updateString(FilePath)
End Sub

The statement

oForm.getByName("txtpath_to_hc_rpt").updateString(FilePath)

produces the error:

BASIC runtime error.
An exception occurred
Type: com.sun.star.container.NoSuchElementException
Message: .

The next line produces:

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

Frustrating, to say the least, as I feel as though I’m very nearly there!

peterwt · February 13, 2018, 4:03pm

@johnh009 @Ratslinger I find that neither updateString nor BoundField.updateString work. I got it to work using commit and updateRow() together. I have uploaded a sample db where this works.

Entering an ID between 0 and 5 and clicking “Find Record” shows the record in the Form. The text in the Test field can be changed and the underlying Table is updated. The macro RecUpd runs on the Text Modified event on the Test field. If the commit and updateRow() lines are remmed and either the BoundField.updateString or the updateString lines are used the Table is not updated and the diskette icon, Save Record is bold, showing that the record needs to be updated. However neither of these lines if used creates an error. If an invalid column index is used in update string there is an error but strangely it accepts 0,1, or 2 when it should be only 0 or 1 as there are only 2 columns in the Table.

I would be grateful for any comments.
Test9.odb

Ratslinger · February 13, 2018, 5:11pm

@peterwt I am at fault for two different but related items.

First, you are correct about the actual updating of the record.

Secondly, I was not paying close attention to the question. Past questions were all based upon table controls whereas this seems to be individual fields. My fault for not clarifying. With the table control (and here with minimal testing) the index seems to be relative to ‘1’ and using the updateString with an invalid column does produce an "Invalid column index’ error.

Ratslinger · February 10, 2018, 10:34pm

Hello,

The correct syntax for this is found HERE.

So if you were updating a field on the subform, then

oSubForm.updateString(YOUR_COLUMN_NUMBER, "STRING")

johnh009 · February 11, 2018, 4:27pm

@Ratslinger. Thank you for your response, and the directions to another reference document. Unfortunately, unlike most of the answers you have supplied to date, this one doesn’t appear to work. Having replaced your example values with my own, thus:

oSubForm.updateString("txtpath_to_hc_rpt", FilePath)

. . the follwing error is returned (continued . . . ):

johnh009 · February 11, 2018, 4:41pm

. . .

BASIC runtime error.

An exception occurred
Type: com.sun.star.sdbc.SQLException
Message: Invalid column index…

Information from the db ‘script’ file shows this information about the index (or indices):

SET TABLE PUBLIC."homecheck_progress" INDEX '107016 123328 123328 123328 0

I believe, there is only 1 on the PKey (autovalue integer). After more googling, the addition of ‘BoundField’ works:

oSubForm.getByName("txtpath_to_hc_rpt").BoundField.updateString(FilePath)

.

johnh009 · February 11, 2018, 4:50pm

. . . unless you have an alternative suggestion.

Sorry about the ‘Basic runtime error’ text not all being in the appropriate box. I tried several times, but to no avail.

johnh009 · February 11, 2018, 4:50pm

. . . unless you have an alternative suggestion.

Sorry about the ‘Basic runtime error’ text not all being in the appropriate box. I tried several times, but to no avail.

Ratslinger · February 11, 2018, 5:45pm

@johnh009 I don’t think you looked closely enough at the syntax:

[in] long	 columnIndex,

The column is actually a number not a name as you used. Have used this many times & re-tested before posting.

Ratslinger · February 11, 2018, 5:56pm

Also, if you are going to be doing more with macros, you may want to install an learn to use an object inspector such as MRI or XRay. You can find links and other info in this post. I have tried both but use MRI. Personal preference. With this you can also retrieve references as presented in my answer. Great for testing & finding methods & settings. Can be overwhelming at first.

Ratslinger · February 11, 2018, 6:06pm

If you are unclear as to the column number, it is based upon the table design. The first column (or field) is 0 (zero), the second column is 1 and so forth. That is the columnIndex in the table - relative position of the field. Has nothing to do with table keys. You will find many different references to an index within any type of code. Hope this makes it clearer.

johnh009 · February 12, 2018, 3:10pm

@Ratslinger. Yes, you’re right, I didn’t pay close enough attention to what I was reading, and thank you for your patience and clarification. As for installing MRI or X-ray; I’d like NOT to do more with macros, but in order to create half-decent applications, I guess I have no option! I have previously installed MRI, and, as you say, found it daunting. I guess perseverance is called for. Thanks again.