[Solved] Macro to change contents of combo and list boxes

[owlbrudder]

I have a form with a combo for Supplier Name. On a subform I have a list box and a combo box whose list content depends upon the name in Supplier Name.

I construct the SQL needed to change the content of each control and print the resulting strings to check they are correct. As expected, I see the single quotes I inserted around the supplier name - needed because supplier names can consist of multiple words.

When I attempt to execute the SQL in reloading the list or combo an error is thrown complaining the SQL contains an error at the supplier name and the SQL string in the error dialog shows the supplier name no longer surrounded by single quotes.

In addition, I have set my own error handler to deal with this error, but it pops up an empty message box.

The following code snippet will show what I am doing:

Code: Select all

	strSupplierName = oControl3.CurrentValue
	print "strSupplierName: "+strSupplierName
	
	strQueryDef1 = "SELECT ""strSupplierPartName"", ""serItemId"" FROM ""tblStockItems"" WHERE ""strSupplier"" = "+"'"+strSupplierName+"'"+" ORDER BY ""strSupplierPartName"""
	strQueryDef2 = "SELECT ""strSupplierPartNumber"", ""serItemId"" FROM ""tblStockItems"" WHERE ""strSupplier"" = "+"'"+strSupplierName+"'"+" ORDER BY ""strSupplierPartNumber"""
	print "strQueryDef1: "+strQueryDef1
	print "strQueryDef2: "+strQueryDef2

on error goto subRequeryComboErr

    'set new SQL query according to the selected supplier and refresh lists 
	varSource() = array(strQueryDef1)
    oControl1.ListSource() = varSource()
	varSource() = array(strQueryDef2)
	'print varSource()
    'oMRI.inspect varSource()
    oControl2.ListSource() = varSource()

    oControl1.refresh()
    oControl2.refresh()
    Exit Sub
    
subRequeryComboErr:
MsgBox "Error " & Err & ": " & Error$

End Sub

The error is occurring at the line "varSource() = array(strQueryDef1)".

I have attached a small screen shot showing the error message and a 'print' of the SQL string I created. You can see that the single quotes have been removed in the error message version and are present in the 'print' version.

Screenshot showing single quotes have been silently removed, causing error.

1. How do I get the SQL string to keep single quotes?
2. What m I doing wrong with my error handler?

Any help would be appreciated.

Cheers,
Doug

[owlbrudder]

It turns out the solution was to change to using the JDBC driver instead of the default one.