I have been trying to update the content of a combo box depending upon the supplier name which may be several words long. Somewhere I found a code example (I can’t lay my hands on it now, of course), so I constructed my code around it.
I am connecting to a PostgreSQL database using the JDBC driver because the postgresql driver would strip out single quotes from an SQL string…
This is my code:
Sub subRequeryCombo(oEvent AS OBJECT)
Dim oDoc As Object
Dim oControl1 As Object
Dim oControl2 As Object
Dim oControl3 As Object
Dim strSupplierName as String
Dim strQueryDef1 As String
Dim strQueryDef2 As String
Dim varSglQuote As Variant
Dim varSource() As Variant
DIM objEventControlModel AS Object
If Not Globalscope.BasicLibraries.isLibraryLoaded("MRILib") Then
Globalscope.BasicLibraries.LoadLibrary( "MRILib" )
End If
oMRI = CreateUnoService("mytools.Mri")
oMRI.inspect oEvent.Source
print oEvent.Source.Name
oDoc = Thiscomponent
oControl1 = oDoc.Drawpage.Forms("frmOrders").getByName("subfrmOrderLines").getByName("cmbStockItem")
oControl2 = oDoc.Drawpage.Forms("frmOrders").getByName("subfrmOrderLines").getByName("lstSupplierPartNumber")
oControl3 = oDoc.Drawpage.Forms("frmOrders").getByName("cmbSupplierName")
oMRI.inspect oControl1
print "cmbStockItem"
varSglQuote = "\'"
strSupplierName = oControl3.CurrentValue
print "strSupplierName: "+strSupplierName
strQueryDef1 = "SELECT ""strSupplierPartName"", ""serItemId"" FROM ""tblStockItems"" WHERE ""strSupplier"" = E'" & oControl3.CurrentValue & "' ORDER BY ""strSupplierPartName"""
strQueryDef2 = "SELECT ""strSupplierPartNumber"", ""serItemId"" FROM ""tblStockItems"" WHERE ""strSupplier"" = E'" & oControl3.CurrentValue & "' ORDER BY ""strSupplierPartNumber"""
print "strQueryDef1: "+strQueryDef1
print "strQueryDef2: "+strQueryDef2
on error goto subRequeryComboErr
print "array(strQueryDef1): "+array(strQueryDef1)
varSource() = array(strQueryDef1)
print "varSource: "+varSource()
oControl1.ListSource() = varSource()
oMRI.inspect oControl1.ListSource()
varSource() = array(strQueryDef2)
oControl2.ListSource() = varSource()
oControl1.refresh()
oControl2.refresh()
Exit Sub
At the line print "array(strQueryDef1): "+array(strQueryDef1) I get the error “subRequeryComboErr Error 91: Object variable not set. - error at line: 149”. The string strQueryDef1 is set to
> SELECT "strSupplierPartName",
> "serItemId" FROM "tblStockItems" WHERE
> "strSupplier" = E'Penn State' ORDER BY
> "strSupplierPartName"
which is valid SQL (it executes correctly when I pste it into the SQL page of phpPgAdmin) so it must be something about the array assignment causing the problem. Perhaps I have misread or misunderstood the code snippet I referred to.
Could someone with a wiser head than mine put me straight?