LO Base: SQL for a listbox in a subform

[nobody]

I am trying to use a macro to specify an SQL for a listbox in a subform. The macro is attached to a listbox in the main form for the “item status changed” event.

Here is the macro:

oForm = ThisComponent.getDrawPage().getForms().getByName("FormSalesOrder")

oControl1 = oForm.getByName("buyer name")
buyerid = oControl1.getCurrentValue()

sSql = "SELECT ""buyerpartname"", ""buyerpartid"" FROM ""buyerpartno"" WHERE ""buyerid""='"& buyerid &"'"

oSubForm = oForm.getByName("FormSalesOrderItem")
oColumns = oSubForm.getColumns()
oField = oColumns.getByName("buyerpartid")
oField.ListSource = array(sSql)

When I change the selected item in the listbox in the main form, it gives me the BASIC runtime error, saying “Property or method not found: ListSource.”

Any help would be much appreciated.

[Ratslinger]

Hello,

Your question could utilize better wording. For example, is the SQL for a list box selection or is the SQL for a list box in a sub form?

It is an educated guess than you have a list box on the main form. You also have a sub form. When you make a selection in the list box, you want that selected ID to obtain specific records for the sub form. If this is not what is wanted, please be more specific otherwise…

Your approach is incorrect for a number of reasons. In your macro for the sub form, you are retrieving the field values for the CURRENT record of the table associated with that form. There is no attribute ListSource for a field and you cannot fill that single value with an array.

You don’t even need the SQL statement. What is easiest here is to retrieve the value wanted from the list box, move it into the filter of the sub form and reload the sub form. This method was recently discussed here → Several database backends for a single LibreOffice Base file? and my answer there has another link to actual sample code.

[nobody]

Thanks Ratslinger. I have edited my question.

And, sorry I didn’t make it clear. The form is for the user to input sales orders received from buyers - so, sales order header in the main form and sales order lines in the subform. In the database, there is a table for buyers’ part numbers (i.e., buyers’ product codes), which has columns for buyerpartid (the primary key), buyerpartname (that is, buyer’s part number/code), buyerid.

Now, in the form, what I want to do is, after the user chooses the buyer’s name from the listbox in the main form, in the subform, the part numbers/codes for this particular buyer will be provided in a listbox in a column. The user will then pick one part number/code from that list and proceed to input other data on the same row such as quantity, delivery date, etc.

I seem to be able to grab the buyer’s ID from the main form. The issue is, I don’t know how to use it to select only the required rows from a third table, which is buyers’ part numbers as above.

[Ratslinger]

@nobody Still missing info but will explain. My answer eluded to the problem - getColumns(). This is not a control (guessing you have a table control). Now you haven’t stated whether this is a separate control or one on a table grid & I’ll thing it is the latter. Once you get oSubForm, use that to get the table grid:

 oTableGrid = oSubForm.getByName("MyGridName")

Then get the column in the grid containing the list box:

oListBox = oTableGrid.getByName("MyColumnName")

Now that you have the list box, load the SQL statement:

 oListBox.ListSource = sSQL

Then refresh the control:

 oListBox.refresh()

If you persist with problems, please post a sample of your base file.

[nobody]

Thanks Ratslinger, it works (except for the second last line, which seems requires to put sSQL in array).
I have to admit that I don’t have a clear understanding in Macros for LO Base. I did go through some materials before working, including “OpenOffice.org 3.2 BASIC Guide”, “OpenOffice.org Macros Explained” by Andrew Pitonyak, OpenOffice.org BASIC Programming Guide. I will certainly read them again. Perhaps you have other suggestions? I have some experience in VBA for MS Excel but BASIC for LO Base appears to be quite different. And the learning curves for SQL and BASIC for LO Base seem to be rather steep. Sorry, that’s a bit off topic, but that’s quite foundamental. Thanks a lot.

[Ratslinger]

Glad it is working for you. Macros in LO takes some time to just get started with. Lots to absorb. The API will be confusing and MVD (Model, View, Draw) will require thought for a time. Other sections in the Pitonyak manual (both Writer & Calc I recall) were helpful and trying the samples in all sections is beneficial. All of the AOO Developers Guide is of benefit. Sometimes the best answer is found with a good old internet search.