We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Table not updating from form when 'default' values applied

asked 2020-05-28 12:34:13 +0200

Hippy Steve gravatar image

updated 2021-05-16 23:13:09 +0200

Alex Kemp gravatar image

LO, Ubuntu 18.04 with all updates.


I created a form with the wizard to add a new record to a table. I then added 'Cancel' and 'Save' buttons with the appropriate actions.

I use the code below to populate certain fields with default values. This works, when the form displays it shows the values I want. However, when the 'Save' button is clicked the values entered by the macro do not save into the table, only the data that has been manually entered is saved!

To this end I added the lines you see to set the focus to each control before I set the default values. Still, they do not save.

Is this a bug or have I missed a step? I will get over it by removing the default action from the 'Save' button and using SQL to update the table but this is surely not the way it should be?

sub StudentClassSetupNew(pEvent as object)
'###    puts default values into new record 
'###    called from "Add New Class" button on
'###    frm_StudentClass

Dim oFrm as object
Dim oDocCtl as object
Dim sSQL as string

    oFrm = ThisComponent.Drawpage.Forms.getByName("NewClassForm")
    oDocCtl = ThisComponent.getCurrentController()
'###    bug in date field means we have to put in a dummy value first before the date we want
    oFrm.getbyname("datClassDate").text = "01/01/01"
    oFrm.getbyname("datClassDate").text = format(now()+1,"Ddd dd Mmm YYYY")
    oFrm.getbyname("timClassTime").text = format(now(), "HH:MM")
    oFrm.getbyname("fmtDuration").text =60
    oFrm.getByName("fmtFKPlanRef").text = 0
'###    set up listboxes    

    oFrm = nothing
    oDocCtl = nothing
end sub
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-05-29 01:49:58 +0200

Ratslinger gravatar image

updated 2020-05-30 06:35:59 +0200

@Hippy Steve,

Do not have a complete answer for you as yet but will present what I can.

With text, numeric, formatted type fields you can move the data into the control with .Text or .Value depending upon the control. Then with the control access with oField you the issue:


Now the remaining problem I have is with dates and times. Have done this long ago but currently having problems. Here is a reference for you -> Clearing Date Fields using a Macro

Structure reference for both can be in this list -> com::sun::star::util Module Reference

Will post further if more is found or remembered :)


Turns my date problem was my problem - switched month & day.

Tested with date, time and duration fields only. Duration was set as Text Box & also tried as Numeric control (use .Value).

For date & time needed to use structure.

Here is code used:

sub StudentClassSetupNew(pEvent as object)
'###    puts default values into new record '
'###    called from "Add New Class" button on'
'###    frm_StudentClass'

Dim oFrm as object
Dim oDocCtl as object
Dim sSQL as string

    oFrm = ThisComponent.Drawpage.Forms.getByName("NewClassForm")
    oDocCtl = ThisComponent.getCurrentController()

    Dim aDate As New com.sun.star.util.Date
    Doc = ThisComponent
    DocCtl = Doc.getCurrentController()
    oField = oFrm.getByName("datClassDate")
    CtlView = DocCtl.GetControl(oField)
        aDate.Day = 28
        aDate.Month = 05
        aDate.Year = 2020

    Dim aTime As New com.sun.star.util.Time
    Doc = ThisComponent
    DocCtl = Doc.getCurrentController()
    oField = oFrm.getByName("timClassTime")
    CtlView = DocCtl.GetControl(oField)
        aTime.Hours = 08
        aTime.Minutes = 14
        aTime.Seconds = 00

    oField = oFrm.getByName("fmtDuration")
'    oField.text = 60    '
    oField.Value = 60
end sub

Please excuse my laziness in not defining all Dim statements. Fairly certain you want to make other changes.

You will also see the setFocus statements are not needed.

Edit 2020-05-29:

Found another method in which Formatted field works. Able to access the bound field:

oField = oFrm.getByName("YOUR_Formatted_Field")
oBoundField = oField.BoundField

Here is more on update types:

image description

Brief testing with Formatted, Text, and Numeric fields. Probable for some other fields.

edit flag offensive delete link more


@Ratslinger, after the amount of time you put in to helping out others, not declaring variables is of no consequence!

This works well, thank you. Interesting note here, The Form WIzard seems to have put the numeric fields into Formatted controls, not Numeric hence setting the value property gives an error. The database was not updated via updating the text... I may spend some time trying to figure out the cause but for now I simply changed the control type to numeric and all is well.

Thank you again for your time, and the links are also useful.

Hippy Steve gravatar imageHippy Steve ( 2020-05-29 14:11:30 +0200 )edit

@Hippy Steve,

Please see edited answer for Formatted controls and different method. Guess there is often more than one way to do something.


Using Bound fields is the better method and can do without View.

Ratslinger gravatar imageRatslinger ( 2020-05-30 04:37:08 +0200 )edit

@Ratslinger Thank you :)

Can I ask where you found the 'boundfield' docs? And which debug tools do you use - I miss the functionality of the vba macro editor!

Hippy Steve gravatar imageHippy Steve ( 2020-05-30 12:22:47 +0200 )edit

@Hippy Steve,

The list in my edited answer is from an extension - MRI, an object introspection tool.

Tutorial here -> https://forum-test.openoffice.org/en/...

Releases here -> https://github.com/hanya/MRI/releases

GitHub here -> https://github.com/hanya/MRI

Now in addition, this post contains many links to documents -> To learn LibreOffice Base are there introductions or tutorials?

You may want to look at Open Office Macros Explained by Andrew Pitonyak. PDF here -> http://www.pitonyak.org/OOME_3_0.pdf

and a document by Pitonyak just on Base -> http://www.pitonyak.org/database/Andr...

MRI and the Pitonyak are what I use mostly but there is more scattered around.

HTH :)

Ratslinger gravatar imageRatslinger ( 2020-05-30 19:32:52 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-05-28 12:34:13 +0200

Seen: 133 times

Last updated: May 30 '20