How to change the content of a combo box

English
, , , , ,
#1

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?

#2

Not sure how to fix your issue, but out of curiosity is there some reason that subRequeryComboErr is used but not defined? Also because the rest of us are not as familiar w/ your code, would you mind adding a few comments to say what you’re trying to do here and there? One thing I might try, is to point it at a local database (built in) and see if that behaves any differently. I’m learning this stuff to, and appreciate your question and your code.

#3

Sorry - I omitted the last two lines after Exit Sub, which are simply the label and a line presenting the error details; this drops through to End Sub.

#4

It’s not clear as to what you are trying to accomplish. From what is stated,you are attempting to put something into a “Combo Box” (an indexed list of data) based upon another field which is several words long.

What your code attempts to say is based upon a value in “cmbSupplierName” (listbox selection?) use a Query to obtain “serItemId”, “strSupplierPartName” and “strSupplierPartNumber” from table “tblStockItems”.

If this is the case, why even use a macro when this could be done using a table filter? There are many posts about filtering in the forum.

If you really need to use a macro, you need to understand each of the processes and code them according to your needs. Unfortunately, there is not just a single location to find everything that you may need. You may end up with many references for just SQL processing in macros. For example, if you want information on ResultSets, a search using “com.sun.star.sdbc.ResultSetType” could lead you to here and a click there on “ResultSet” leads you to here which with a click on XReseltSet leads you here showing the navigation on a data table.

Here is a post with a routine to show the pieces needed to generate and run an SQL statement in a macro: click here.

Also as @EasyTrieve has mentioned, Pitonyak’s book is an excellent source for macro information. His home page also has other documents of interest.

As far as your actual code, the error mentioned is probable due to “subRequeryComboErr” not being defined. It appears this is for error processing of some type but you never coded it. Many other things are incorrect in you code. Of course the SQL is not processed, and instead of two statements it should be done in one. For controls, it depends upon the actual control used. You must also learn the difference between the “model” and the “view” of the control - not particularly easy to comprehend at first. See this post for more information (click here).

#5

Concerning the use of MRI, all you need to do is load the library and when you want to examine an object - mri xxx will work (xxx = object such as oEvent or oEvent.source).

#6

OK, after playing with your code & guessing what controls you are possibly using, I got it to work. There is a difference between a List Box and Combo Box and how you load ListSource.

oControl1.ListSource = strQueryDef1  'this is Combo Box '
oControl2.ListSource = array(strQueryDef2)  'this is List Box '

I would change other code because of inefficiency, but it works the way it is.

#7

Thanks Ratslinger - I overlooked the fine distinction between the ListBox and the ComboBox assignments statements. (Takes note to self: must read more carefully!)

#8

Thanks very much for the comments. I have re-examined the snippet of code I was trying to emulate and found I had made a couple of silly mistakes. I have cleaned up the code to eliminate all my debugging and old comments. This now correctly emulates the code I was given and it runs without error but fails to populate the lists:

REM When the record on frmOrders changes, update the list boxes
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

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")

strSupplierName = oControl3.CurrentValue

strQueryDef1 = "SELECT strSupplierPartName, serItemId FROM tblStockItems WHERE strSupplier = '"  & oControl3.CurrentValue & "' ORDER BY strSupplierPartName"
strQueryDef2 = "SELECT strSupplierPartNumber, serItemId FROM tblStockItems WHERE strSupplier = '" & oControl3.CurrentValue & "' ORDER BY strSupplierPartNumber"

on error goto subRequeryComboErr

REM set new SQL query according to the selected supplier and refresh lists 
oControl1.ListSourceType=com.sun.star.form.ListSourceType.SQL
oControl2.ListSourceType=com.sun.star.form.ListSourceType.SQL

oControl1.ListSource = array(strQueryDef1)
oControl2.ListSource = array(strQueryDef2)

oControl1.refresh
oControl2.refresh
Exit Sub

subRequeryComboErr:
MsgBox "subRequeryComboErr Error " & Err & ": " & Error$ & " - error at line: " & Erl

End Sub

To answer the question of what I am trying to achieve, it is twofold:

  1. I want to learn how to populate my list boxes and combo boxes for future reference: the business case at present is irrelevant.

and

  1. When the supplier name in the parent form changes, perhaps by moving to another record or perhaps by selecting a different supplier for the current record, I want to populate my combo and list with the description of the part and the part number of the part respectively for those items supplied by the given supplier.

By selecting either a part description or a part number, I create a new record in table tblOrderItems which ties a supplier and a list of one or more stock items to an order.

I know I could achieve similar results if I approached the problem from a different direction, but for now I would be happy just to populate my list and combo the way I want.

Annoyingly, I had it working, but like a good programmer I fixed what wasn’t broken and now it doesn’t work any more.

I know the SQL being generated is valid, because I paste it into the SQL page of a new query and it runs successfully.

#9

Usage note: In the future I think it would be best to revise your original question above, rather than to insert your revisions into an answer as you have done here. In other words, ask.libreoffice.org is not an old style forum, but rather it’s a new-style question/answer formatted site. The big idea being that others can search for a question, and if it’s already been answered, quickly find one or more answers. But when they run into your question in the form of an answer, it’s confusing. k?

#10

@owlbrudder, Other than that, a big thanks for the new comments in your code. (Hope you also see my other usage note comment above this comment. I forgot to prefix it with “@owlbrudder”, so you might not have seen it.)

#11

strSupplierName is not used above. (Just looking through various code here for ideas and re-investigated yours.) Otherwise, pretty slick.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.