Hi,
I’ve been trying to run a simple query using a macro and the database I am using in the default embedded HSQLDB. I’m not sure what the issue is but every time I execute the sql I get a “no data” error.
The customers table has the following fields: Customer_ID and Customer_Name. I’d like to have a query run when the Customer Name list box is changed and make the Customer_ID field populate with the matching ID. The query works fine outside of the macro.
Here is my macro code:
Sub CustNameFilterNewWO
oForm = ThisComponent.Drawpage.Forms.getByName("Filters") 'Get Form
oSubForm1 = oForm.getByName("Customer")
oField = oForm.getByName("Cust Name box")
sSelectedValue = oField.getCurrentValue()
oConn = ThisDatabaseDocument.DataSource.getConnection("","")
SQL = "SELECT ""Customer_ID"" FROM ""Customers"" WHERE ""Customer_Name"" = '" & sSelectedValue &"'"
oQuery = oConn.createStatement()
oResult = oQuery.executeQuery(SQL)
oField = oForm.getByName("Cust ID box")
oField.text = oResult.getString(1)
oField.refresh()
End Sub