[base] How to fill a field with string + other field + string

English
,
#1

Hi everyone,

I’m pretty new to working with databases but not to programming.

When I create a new record in a table using a form I want to automatically fill a field with a relative path to an image based on a field in the record I’m creating. That way I can display images automatically in a from based on the folder structure I’m using.

The folder structure is:

/test_db/photos/[ID]/full.jpg

The SQL code to do this is:

UPDATE "tbl_1" SET "path_photo" = 'photos/' || "ID" || '/full.jpg'

Where [path_photo] and [ID] are both fields in the [tbl_1] table.

When I run the code through the “Execute SQL Statement” tool it works perfectly.

I made this macro to do it for me when I change records:

Sub UpdateRecordPhotoPath
    Dim oStatement As Object
    Dim oResult As Object
    sql = "UPDATE ""tbl_1"" SET ""path_photo"" = 'photos/' || ""ID"" || '/full.jpg';"
    oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
    oStatement.execute(sql)
End Sub

I tried several options in the events tab of the Form Properties window:
Before submitting, Before/After record action, Before/After record change
But none have worked.

The ideal solution would result in real time updating of field and image control object assigned to that field. But I would also be very happy with a solution that updates the field after changing the record or if I have to press a specific button for the image to appear.

Any help would be greatly appreciated!

Thanks,
RobBob

P.S. I’m using Windows 11, LibreOffice version 24.8.5.2 the database is HSQLDB Embedded

#3

You don’t need any macro to update the path. Just run the SQL code via Tools>SQL… and every will be fine.
If the paths (relative to the database document) are correct, and you bind a picture control to the “path_photo” field, the picture control should display the photo.

Alternatively, you can calculate the path instead of storing it in the database:

SELECT <some fields>, 'relative_path/'|| "file_name" as "pic path" FROM <somewhere>
#2

Change oStatement.execute(sql) for oStatement.executeUpdate(sql)

#4

A more direct approach is to fill the field directly through the form object controls method. This method allows the user to “undo” the path change before saving. (see form navigation bar control)

  • Create this macro and place in the After Update event of the form control named “ID” (assuming listbox where ID is bound column).

Sub setPath_photo(oEvent as Object)
Dim objA, objB, intA
objA=oEvent.source.parent
objB=objA.columns.getByName(“path_photo”)
intA=oEvent.source.selectedValue()
objB.value()=“photos/” & intA & “/full.jpg”
End sub

#5

Thanks for your reply, when I run this macro I get an error:

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

#6

How do you run this?
Note the line

So it is quite important, what the parent of your event is…

#7

The parent is a text box with the [ID] data field. The event is “After updating”

#8

I apologize for the oversite. Replace “.controls” with “.columns” in the macro.
I edited the errant post with this correction as well. The method “.columns” refers directly to the underlying table of the form.

  • The “parent” is the form in which the event control resides.
  • “path_photo” is the name of the field (column) which is to be edited in the parent form’s underlying table. (Not name of control)

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