Base change data field in a data form in a macro

[jimlo9]

Mysql jdbc used, I have this form:

If I enter an amount in the widthdraw column I would like to have a 0.00 in deposit and vice versa.
I know how to get and change data in a text box, but not in the forms datasheet.

I don’t want it prefilled, only updated to 0.00 if an amount is entered in "oposite field.

Tried:

Sub fixzero
Dim vwd
DIM vdep 
oForm = ThisComponent.DrawPage.Forms.getByName("MainForm") 'Get Form'
oField = oForm.getByName("widthdraw")   'Get access to field'
oField2 = oForm.getByName("deposit")
vdep = oField2.getCurrentValue()
If IsNull(vdep) Then
	oField2.Value = (0)
End If
End Sub

But just errors, that work for regular text boxes is my guess.

Any suggestions? Currently I have to type in 0.00. Currently otherwise it remains blank (null).

[RobertG]

The field you are looking for is inside a tablecontrol. The tablecontrol is inside the form. oForm.getByName(“tablecontrol”).getByName(“withdraw”) could work, if the field is really named “withdraw”.

oForm.getString(oForm.findColumn("deposit")) will show the content of the field “deposit” as string. So you get an empty string if you want to fill it with 0.
oForm.updateDouble(oForm.findColumn("deposit"),0.00) will set the field to ‘0.00’. “deposit” must be the name of the field in datasource, not the name the field has been called in the form.

[jimlo9]

@ RobertG Thanks, I tried but kept getting numerous errors.

I finally just made an update query like:

sql = "UPDATE `checkbook`.`checks` 	SET 	`deposit` = ? 	WHERE `deposit` IS NULL"
     				oStatement = oConnection.prepareStatement(sql)
					oStatement.setDouble(1, 0)
				    oStatement.executeUpdate

Ditto for other field, then run another

Sub reloadAllTables
    Dim Forms : Forms = ThisComponent.DrawPage.Forms
    Dim i%
    For i = 0 To Forms.getCount()-1
        Forms.getByIndex(i).reload()
    Next
End Sub

Does the trick.

However there has to be an easy way to tell base:

If that control (i.e. deposit) is empty during an update (adding record) to make it 0. I’d still like to figure it out.

I also tried with name MainForm_Grid instead of tablecontrol.