Posting values to a table from a form

[PhilipK]

Bizarre problem, which is perhaps due to ODBC rather than LO. (I am using LO Base on a Mac as a front end to an SQLite database.)

I have a control on a form into which, if I want to enter a new rather than an existing (numerical) value, this should be the next unused value in another table. Entering ‘0’ in this (formatted) field prompts a macro (attached to ‘Before Update’ or ‘After Update’ - there appears to be no difference) which successfully fetches this and returns the value to the control: relevant code below.

If oField.text = "0" then
	[Do things to get the required number] 
	oField.text = Cstr(ReqdNo)
	oField.commit()
endif

The required number displays in the control, but when the form moves to another record and back again, I find that the value ‘0’ has been entered. I have tried a variety of alternative ruses, e.g.

CtlView = oCurrentController.getControl(oField)
CtlView.text = Cstr(ReqdNo)

also oForm.updateRow(). The former made no difference, the latter seems regularly to return an SQL ‘Function sequence error.’

How can I force the value displayed on the form to be posted correctly to the underlying table? (If I type it in manually, it behaves exactly as it should.)

[Ratslinger]

Hello,

Have found that getting the bound field and using that resolves this type of issue.

Please refer to the edited portion of my answer here → Table not updating from form when ‘default’ values applied

Use the proper update type for the data you are dealing with.

Also, your statement:

attached to ‘Before Update’ or ‘After Update’ - there appears to be no difference

there is a difference. The processing of the macro actually takes place either before the entry is allowed (useful to check for situation of some type) or after to possibly determine what may be needed to be updated in another place.

[PhilipK]

Thank you. The bound field did the trick. I thought that I had tried that and failed, but there may have been some tiny typo that I didn’t spot. My resulting code (attached to the Before Update event) is

If oField.text = "0" then
    [Do things to get the required number]
    oBoundField = oField.BoundField
    oBoundField.updateInt(RetrievedNumber)
    oField = oForm."[Another field]"
    oBoundField = oField.BoundField
    oBoundField.updateInt(1)
Endif

I also discovered incidentally that a control bound to a numeric field may be defined as a text control, a formatted control or a numeric control, and while it may post data to the underlying field without one noticing the difference, it has to be handled differently in macros. Thus, if it is a numeric or formatted control, its content must be retrieved as oField.value (returning, say, 12), while if it is a text control, its content must be retrieved as oField.text (returning “12”).