How to reset fields on a form

[PhilipK]

Using LO version 6.4.3.2 on a Mac I have a form which displays a query based on a dummy table with blank values and a corresponding table full of data.

When values are entered into the (sole) record of the dummy form, the search button refreshes the query and displays only records in the base table which meet the search criteria set in the dummy.
If I want to change or cancel the nature of the query, I can edit each of the search values manually, but I would like to use the single reset button (“Clear settings”) to empty these fields through a macro. Have tried to address them through the form controls:

SUB ResetForm				'Clears settings
DIM oForm as Object
DIM SearchBox as Object
oForm = ThisComponent.Drawpage.forms.getByName("MainForm")
SearchBox = oForm."txtReading"
msgBox "Reading set to : " & SearchBox.text
SearchBox.text = ""
oForm.Reload()

END SUB

The message box demonstrates that the routine gets a handle on the right object, but the next line has no effect.

I also tried addressing the underlying table directly:

SUB ResetForm2
DIM oForm as Object
UPDATE "T-St_types_dummy"
SET "Frame No." = NULL, "Direction" = NULL, "Reading" = NULL
oForm = ThisComponent.Drawpage.forms.getByName("MainForm")
oForm.Reload()

END SUB

But when compiling I get a stop at “Frame No.” - ‘Object not accessible. Invalid object reference.’

This is part of a beautiful database (of makers’ marks on pottery) that I wrote 25 years ago in the late lamented Paradox, and which I am trying to recreate in LibreOffice Base. Any advice would be gratefully received.

[Ratslinger]

Hello,

Using your first example (and based upon the question there will be more fields added to clear) then when you set the field you need to commit the action. Then before reloading the form, save the record. Modified first routine:

SUB ResetForm               Rem Clears settings
    DIM oForm as Object
    DIM SearchBox as Object
    oForm = ThisComponent.Drawpage.forms.getByName("MainForm")
    SearchBox = oForm."txtReading"
    msgBox "Reading set to : " & SearchBox.text
    SearchBox.text = ""
    SearchBox.commit()
    Dim oDocument As Object
    Dim oDispatcher As Object
    oDocument   = ThisComponent.CurrentController.Frame
    oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    oDispatcher.executeDispatch(oDocument, ".uno:RecSave", "", 0, Array())
    oForm.Reload()
END SUB

Edit 2020-05-09:

Having a difficult time following your comments in that I am not clear as to what you have and where… Have stated the macro code SearchBox.text = "" or SearchBox.Value = 0 is dependent upon the control used - text box or numeric field. The table field is Integer in both cases.

Have attached a sample. Has one table with the primary key & a single Integer field. There are two forms. One is using a text box to clear the field to a blank and the other is a numeric field to clear the field to a zero. Each form uses a slightly different macro - Text vs Value.

Sample ----- ClearField.odb

If you have further problems, please post a scrubbed sample.

[Ratslinger]

If you are using a field other than a text field on the form, you may need to access the data with a different method such as SearchBox.Value. This may present yet another problem. If the control is a numeric control, the value has a minimum and maximum. You would not be able to set as a blank field with this control.

[PhilipK]

Apologies for posting reply in wrong place: I hope I have corrected this, but am far from certain. There is certainly a problem with emptying the underlying table field. The revised code is as follows (for just the numeric field)

SUB ResetForm               'Clears settings
DIM oForm as Object
DIM SearchBox as Object
Dim oDocument As Object
Dim oDispatcher As Object
oForm = ThisComponent.Drawpage.forms.getByName("MainForm")
SearchBox = oForm."fmtFrame no."
SearchBox.text = ""
SearchBox.commit()
oDocument   = ThisComponent.CurrentController.Frame
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oDispatcher.executeDispatch(oDocument, ".uno:RecSave", "", 0, Array())
oForm.Reload()

END SUB
This clears the control on form, but fails to alter the value in the underlying table. I changed the query, so that ‘0’ would be a valid value (in view of your comment), but even this is passed only to the control, not the table.

[Ratslinger]

@PhilipK,

Please see edited answer.

[PhilipK]

Thank you. finally solved, in a rather unexpected way. The form had been created using the form wizard, which had assigned to the numeric control (“Frame no.”) the type ‘formatted’, for which the macro did not recognise the attribute ‘value’. When I changed the control type to ‘Numeric’, everything functioned as it should. Much to learn, but such hazards are only to be expected until one has a great deal of experience of this specific software.

[Ratslinger]

There is a better method - using the BoundField. This then takes care of the Formatted field issue. See my answer here especially the edit → Table not updating from form when ‘default’ values applied