This is extremely embarrassing, as I have gone through at least 15-20 posts on this subject, here and elsewhere, and I still cannot get the listbox to repopulate its values with a macro. I’m attaching a quick sample which I have created. If someone could please point me in the right direction as to where am I going wrong, I’d be very grateful. Thank you in advance for any suggestions.
sample_listbox population.odb (12.8 KB)
It makes no sense to fill a listbox with a SQL statement executed by a macro.
You sample file doesn’t show a form the listbox is connected to.
Here the code you posted with some changes made by me:
sub evt_onDocOpen()
dim oLB as object: oLB = thisComponent.DrawPage.getForms.getByName("Form").getByName("List Box 1")
dim sSQL(0) as string
' oLB.listSourceType = 3 'isn't needed, because it is defined in listbox
sSQL(0) = "SELECT ""name"", ""id"" from ""tblOps"""
oLB.ListSource = sSQL
oLB.refresh()
end sub
SQL-Code will be the first value of an array, so sSQL(0). ListSource should get this array, so SQL.
@Villeroy it could very well be that my database and forms are still improperly set up. I’m very fresh at this and truly I appreciate any suggestions and criticism.
I’ll try to justify my requirement below, but if you still think this is a wrong approach, I’m really open to suggestions.
I thought it would be logical to create my data tables in a way that will as closely represent the real world environment as possible. I’d imagine that professional database architects have precisely the same approach. Thus, I created a set of interconnected tables which describe people, companies, and subsequently I’ll do the same thing for quotation of raw materials, estimates, purchasing, production, invoicing, scheduling, marketing, pricing analysis and the list goes on.
Some of the people in this database do actually work for various companies (commercial clients or suppliers) that we deal with. Some of our suppliers are also our clients at certain times as well. So it gets a little bit tricky. Nonetheless, I set up my people data entry form in such way that they can have several physical addresses, phone numbers and email addresses associated with them. Essentially, I can have a client who is building themselves a house, but currently lives at a different address and wants the invoicing to be sent to the latter. Or I can have another client who is employed by one of the companies that we deal with. At which point they may have two mobile numbers (one private and one for work - happens all the time), same goes for email.
When it comes to the company data entry form, on it I have a subform which associates folks with that place of business. That subform holds a grid with four columns (listboxes): 1, first and last name, 2. department, 3 phone number and 4 email address. In the latter two listboxes I wanted to be able to list only the phone numbers and emails of that particular person and only those which are for business purposes. And this is precisely why I posed that question. I simply could not find a way of making SQL aware of the row selected in the “first and last name” listbox (the first one).
Please see pictures below for visualization of what I just described:
awesome, thank you again!!! it works 
Be careful with loading a listbox in a tablecontrol by macro. The listbox won’t work well if the content changes from one row to the other.
I’m only filling standalone listboxes by macro. So I could change the content for every new row without any problem.
@RobertG Then my idea for the form design is not correct after all. I’d be going down a rabbit hole with this. Thank you for the warning. 
I’m creating such construction for special sub forms, often only one field in a table control. Let’s say I will collect all pupils for a class. There is a table with the pupils and I will choose the pupils by a list box. A chosen person shouldn’t appear any more in the list box. I chose the person. Macro will save the row and goes to next row, list box will be refreshed, chose next person …
Listbox isn’t integrated into the table control for this. So I couldn’t chose a person 2 times.
