LibreOffice Base field.Text Issue

[KenazFox]

Hi there,

I’ve been having an issue with programming a macro for a Base database form. I’ve been searching for an answer for hours and can’s seem to find a solution to my particular issue.

I’m trying to get a button to copy values from a the last record of a table into two entry field. The code works and the values appear in the fields on the form but I get an error when trying to save the record to the table saying that the fields are null. If I manually enter values into the fields or even just change one charater of the copied values then it will work. See code and error below.

Thanks in advance for any help.

Code:

query = "SELECT DatePurchased,OrderNumber FROM tblKeys WHERE ID = (SELECT MAX(ID) FROM tblKeys)"
oConn = ThisDatabaseDocument.DataSource.getConnection("","")
oQuery = oConn.createStatement()
oResult = oQuery.executeQuery(query)
If oResult.Next() Then
	dateField.Date = oResult.getDate(1)
	orderField.Text = oResult.getInt(2)
End If

Error:

Attempt to insert null into a non-nullable column: 
column: DatePurchased table: tblKeys in statement 
[INSERT INTO "tblKeys" ( "Key","ModelName","SerialNumber") VALUES ( ?,?,?)]

Edit: I am saving the record using another button with the Action set to “Save Record”.

[RobertG]

Error comes from an INSERT, but there is no INSERT in your macro. Might be the form tries to save data but couldn’t because the field “DatePurchased” is NULL, but it is defined as NOT NULL.

[KenazFox]

I’m saving the record using a button with the Action set to “Save Record”. All of the required fields are filled out but for some reason the record wont save (producing the error quoted) if the DatePurchased and OrderNumber fields have been filled in with the macro and not typed in manually.

[RobertG]

Might be this helps: Getting the wrong value using .text
You try to fill a SQL-date in a struct:
dateField.Date.Day
dateFiled.Date.Month
dateFiled.Date.Year
is needed.
Read the data from the database as a string.
Write the data to the bound field as string.
dateField.BoundFiled.UpdateString(oResult.getString(1))
Haven’t tested but hope it will work well.

[KenazFox]

I will give that a try. Could you direct me to the documentation for text and date field objects? I havent been able to find it. Wherever I could find info about the various properties such as Date.Day, .Month, .Year and the text field properties such as .Text.

[RobertG]

You could use Documentation - Base Guide 7.3. There will be a chapter about macros - more than 100 pages.

[KenazFox]

Thank you very much. I have figured out what my issue is. I needed to run .commit() for each of the fields that i was modifying using the macro before saving the record to the table. I will leave the modified code below for anyone who comes across this post in the future.

Code:

query = "SELECT DatePurchased,OrderNumber FROM tblKeys WHERE ID = (SELECT MAX(ID) FROM tblKeys)"
oConn = ThisDatabaseDocument.DataSource.getConnection("","")
oQuery = oConn.createStatement()
oResult = oQuery.executeQuery(query)
If oResult.Next() Then
	dateField.Date = oResult.getDate(1)
	orderField.Value = oResult.getInt(2)
	dateField.commit()
	orderField.commit()
End If