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:
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()