Base Subform Cascading Listbox Refresh

SirMegaV · March 29, 2017, 10:13am

I’m using LibreOffice Version: 5.2.6.2

I’ve made two cascading list boxes (Unit_Motive_ID and Unit_MUL_Role_ID) with the parent list box (Unit_Type_ID) in the mainform using the following macro:

Cascading called from “Unit_Type_ID” Item Status Changed

Sub Cascading_from_Unit_Type_ListBox (oEvent As Object)

oEvent.Source.Model.commit()
oForm = oEvent.Source.Model.Parent
sValue = oEvent.Source.Model.ValueItemList(oEvent.Selected)
sSQL = "SELECT ""Table_Unit_Motive_Category"".""Unit_Motive_Category"", ""Table_Unit_Motive"".""Unit_Motive_ID"" FROM ""Table_Unit_Motive_Category"", ""Table_Unit_Motive"" WHERE ""Table_Unit_Motive_Category"".""Unit_Motive_Category_ID"" = ""Table_Unit_Motive"".""Unit_Motive_Category_ID"" AND ""Table_Unit_Motive"".""Unit_Type_ID"" ='" & sValue & "'"
oListBox=oForm.GetByName("Unit_Motive_ID")
oListBox.ListSourceType=com.sun.star.form.ListSourceType.SQL
oListBox.ListSource=Array(sSQL)
oListBox.Refresh
oListBox.Enabled = 1
sSQL = "SELECT ""Table_Unit_MUL_Role_Category"".""Unit_MUL_Role_Category"", ""Table_Unit_MUL_Role"".""Unit_MUL_Role_ID"" FROM ""Table_Unit_MUL_Role_Category"", ""Table_Unit_MUL_Role"" WHERE ""Table_Unit_MUL_Role_Category"".""Unit_MUL_Role_Category_ID"" = ""Table_Unit_MUL_Role"".""Unit_MUL_Role_Category_ID"" AND ""Table_Unit_MUL_Role"".""Unit_Type_ID"" ='" & sValue & "'"
oListBox=oForm.GetByName("Unit_MUL_Role_ID")
oListBox.ListSourceType=com.sun.star.form.ListSourceType.SQL
oListBox.ListSource=Array(sSQL)
oListBox.Refresh
oListBox.Enabled = 1

End Sub

Refresh when record change called from “MainForm” After Record Change

sub AfterRecordChange_MainForm (oEvent as Object)

oForm = oEvent.source
oListBox = oForm.GetByName("Unit_Type_ID")
sValue = oListBox.ValueItemList(oListBox.SelectedItems(0))
if sValue <> "" then
	sSQL = "SELECT ""Table_Unit_Motive_Category"".""Unit_Motive_Category"", ""Table_Unit_Motive"".""Unit_Motive_ID"" FROM ""Table_Unit_Motive_Category"", ""Table_Unit_Motive"" WHERE ""Table_Unit_Motive_Category"".""Unit_Motive_Category_ID"" = ""Table_Unit_Motive"".""Unit_Motive_Category_ID"" AND ""Table_Unit_Motive"".""Unit_Type_ID"" ='" & sValue & "'"
	oListBox=oForm.GetByName("Unit_Motive_ID")
	oListBox.ListSourceType=com.sun.star.form.ListSourceType.SQL
	oListBox.ListSource=Array(sSQL)
	oListBox.Refresh
	oListBox.Enabled = 1
	sSQL = "SELECT ""Table_Unit_MUL_Role_Category"".""Unit_MUL_Role_Category"", ""Table_Unit_MUL_Role"".""Unit_MUL_Role_ID"" FROM ""Table_Unit_MUL_Role_Category"", ""Table_Unit_MUL_Role"" WHERE ""Table_Unit_MUL_Role_Category"".""Unit_MUL_Role_Category_ID"" = ""Table_Unit_MUL_Role"".""Unit_MUL_Role_Category_ID"" AND ""Table_Unit_MUL_Role"".""Unit_Type_ID"" ='" & sValue & "'"
	oListBox=oForm.GetByName("Unit_MUL_Role_ID")
	oListBox.ListSourceType=com.sun.star.form.ListSourceType.SQL
	oListBox.ListSource=Array(sSQL)
	oListBox.Refresh
	oListBox.Enabled = 1
end if

end sub

Next I wanted to add another cascading listbox (Planet_ID) with the parent list box (Manufacturer_ID) in a subform table control of the same mainform.

Cascading called from “Manufacturer_ID” Item Status Changed

Sub Cascading_from_Manufacturer_ListBox (oEvent As Object)

oEvent.Source.Model.commit()
oForm = oEvent.Source.Model.Parent
sValue = oEvent.Source.Model.ValueItemList(oEvent.Selected)
if sValue <> "" then
	sSQL = "SELECT ""Table_Planet"".""Planet_Name"", ""Table_Planet"".""Planet_ID"" FROM ""Table_Planet"", ""Table_Manufacturer_Facility"" WHERE ""Table_Planet"".""Planet_ID"" = ""Table_Manufacturer_Facility"".""Planet_ID"" AND ""Table_Manufacturer_Facility"".""Manufacturer_ID"" = '" & sValue & "'"
	oListBox=oForm.GetByName("Planet_ID")
	oListBox.ListSourceType=com.sun.star.form.ListSourceType.SQL
	oListBox.ListSource=Array(sSQL)
	oListBox.Refresh
	oListBox.Enabled = 1 'enable Listbox
end if

End Sub

Added new codes to refresh “Planet_ID” when record change called from “MainForm” After Record Change

sub AfterRecordChange_MainForm (oEvent as Object)

oForm = oEvent.source
oListBox = oForm.GetByName("Unit_Type_ID")
sValue = oListBox.ValueItemList(oListBox.SelectedItems(0))
if sValue <> "" then
	sSQL = "SELECT ""Table_Unit_Motive_Category"".""Unit_Motive_Category"", ""Table_Unit_Motive"".""Unit_Motive_ID"" FROM ""Table_Unit_Motive_Category"", ""Table_Unit_Motive"" WHERE ""Table_Unit_Motive_Category"".""Unit_Motive_Category_ID"" = ""Table_Unit_Motive"".""Unit_Motive_Category_ID"" AND ""Table_Unit_Motive"".""Unit_Type_ID"" ='" & sValue & "'"
	oListBox=oForm.GetByName("Unit_Motive_ID")
	oListBox.ListSourceType=com.sun.star.form.ListSourceType.SQL
	oListBox.ListSource=Array(sSQL)
	oListBox.Refresh
	oListBox.Enabled = 1 'enable Listbox
	sSQL = "SELECT ""Table_Unit_MUL_Role_Category"".""Unit_MUL_Role_Category"", ""Table_Unit_MUL_Role"".""Unit_MUL_Role_ID"" FROM ""Table_Unit_MUL_Role_Category"", ""Table_Unit_MUL_Role"" WHERE ""Table_Unit_MUL_Role_Category"".""Unit_MUL_Role_Category_ID"" = ""Table_Unit_MUL_Role"".""Unit_MUL_Role_Category_ID"" AND ""Table_Unit_MUL_Role"".""Unit_Type_ID"" ='" & sValue & "'"
	oListBox=oForm.GetByName("Unit_MUL_Role_ID")
	oListBox.ListSourceType=com.sun.star.form.ListSourceType.SQL
	oListBox.ListSource=Array(sSQL)
	oListBox.Refresh
	oListBox.Enabled = 1 'enable Listbox
end if
oListBox = oForm.GetByName("SubForm_Table_Unit_Manufacturer").GetByName("Table Control Table_Unit_Manufacturer").GetByName("Manufacturer_ID")
sValue = oListBox.ValueItemList(oListBox.SelectedItems(0))
if sValue <> "" then
	sSQL = "SELECT ""Table_Planet"".""Planet_Name"", ""Table_Planet"".""Planet_ID"" FROM ""Table_Planet"", ""Table_Manufacturer_Facility"" WHERE ""Table_Planet"".""Planet_ID"" = ""Table_Manufacturer_Facility"".""Planet_ID"" AND ""Table_Manufacturer_Facility"".""Manufacturer_ID"" = '" & sValue & "'"
	oListBox=oForm.GetByName("SubForm_Table_Unit_Manufacturer").GetByName("Table Control Table_Unit_Manufacturer").GetByName("Planet_ID")
	oListBox.ListSourceType=com.sun.star.form.ListSourceType.SQL
	oListBox.ListSource=Array(sSQL)
	oListBox.Refresh
	oListBox.Enabled = 1 'enable Listbox
end if

end sub

But I can’t get the refresh to work properly.

When I open the form, the first record’s “Planet_ID” will show the correct “Planet_Name”. But when I go to the next record that have another “Planet_ID” stored, the “Planet_ID” will be blank. And when I return back to the previous record, “Planet_ID” will also be blank.

Thinking that my SQL is wrong, I’ve tested it in the query and it was correct. Also back in the form, although the “Planet_ID” is blank, but when I press the little arrow button, the correct set of “Planet_ID” is shown correctly.

I’ve made a lot of modification and testing, but I can’t get it to work.

Can someone please help? Please Please Please Please Please…

Update 1:

I updated LibreOffice right before posting my original question. I noticed that the listbox content is behaving differently now. The content of the listbox is showing the value that should be displaying for my last mainform record. I suspect this is the real reason why nothing is showing up in “Planet_ID” since the ‘saved’ value doesn’t exist in the listbox content.

Update 2 (04/05/2017):
Update 3 (04/07/2017):

This is the structure for the form in question.

MainForm (Table Table_Unit)
	ListBox Name/Field "Unit_Chassis_ID"
						(SQL list content from Table_Unit_Chassis)
	ListBox Name/Field "Unit_Type_ID"
						(SQL list content from Table_Unit_Type)
	ListBox Name/Field "Unit_Motive_ID"
						(SQL list content from Table_Unit_Motive)
	ListBox Name/Field "Unit_MUL_Role_ID"
						(SQL list content from Table_Unit_MUL_Role)
	SubForm_Table_Unit_Deployment_Civilian
						(Table_Unit_Deployment_Civilian, Link Field "Unit_ID")
						(Update/Delete Cascade)
		Grid Name "Table Control Table_Unit_Deployment_Civilian"
						(Table_Unit_Deployment_Civilian)
			ListBox Name/Field "Unit_Deployment_Civilian_Category_ID"
						(SQL list content from Table_Unit_Deployment_Civilian_Category)
	SubForm_Table_Unit_Deployment_Military
						(Table_Unit_Deployment_Military, Link Field "Unit_ID")
						(Update/Delete Cascade)
		Grid Name "Table Control Table_Unit_Deployment_Military"
						(Table_Unit_Deployment_Military)
			ListBox Name/Field "Unit_Deployment_Military_Category_ID"
						(SQL list content from Table_Unit_Deployment_Military_Category)
	SubForm_Table_Unit_Equipment
						(Table_Unit_Equipment, Link Field "Unit_ID")
						(Update/Delete Cascade)
		Grid Name "Table Control Table_Unit_Equipment"
						(Table_Unit_Equipment)
			ListBox Name/Field "Equipment_ID"
						(SQL list content from Table_Equipment)
		ListBox Name "Unit_Equipment_Overall_Rating" Field "Unit_ID"
						(SELECT MAX from Table_Unit_Equipment)
	SubForm_Table_Unit_Manufacturer
						(Table_Unit_Manufacturer, Link Field "Unit_ID")
						(No relationship)
		Grid Name "Table Control Table_Unit_Manufacturer"
			ListBox Name/Field "Manufacturer_ID"
						(SQL list content from Table_Manufacturer)
			ListBox Name/Field "Planet_ID"
						(SQL list content from Table_Planet)
			ListBox Name/Field "Reference_Book_ID"
					(SQL list content from Table_Reference_Book)

Table_Unit
	Unit_ID (Primary Key, Integer) 
	Unit_Chassis_ID (Foreign Key, Integer)
	Unit_Model (VarChar)
	Unit_Weight (Decimal)
	Unit_Type_ID (Foreign Key, Integer)
	Unit_Motive_ID (Foreign Key, Integer)
	Unit_MUL_Role_ID (Foreign Key, Integer)
	Unit_Prototype_Year (SmallInt)
	Unit_Production_Year (SmallInt)
	Unit_Extinction_Year (SmallInt)
Table_Unit_Chassis
	Unit_Chassis_ID (Primary Key, Integer) 
	Unit_Chassis (VarChar)
Table_Unit_Type
	Unit_Type_ID (Primary Key, Integer) 
	Unit_Type (VarChar)
Table_Unit_MUL_Role
	Unit_MUL_Role_ID (Primary Key, Integer) 
	Unit_Type_ID (Foreign Key, Integer)
	Unit_MUL_Role_Category_ID (Foreign Key, Integer)
Table_Unit_Deployment_Civilian_Category
	Unit_Deployment_Civilian_Category_ID (Primary Key, Integer) 
	Unit_Deployment_Civilian_Category (VarChar)
Table_Unit_Deployment_Military_Category
	Unit_Deployment_Military_Category_ID (Primary Key, Integer) 
	Unit_Deployment_Military_Category (VarChar)
Table_Unit_Equipment
	Unit_Equipment_ID (Primary Key, Integer) 
	Unit_ID (Foreign Key, Integer)
	Equipment_ID (Foreign Key, Integer)
Table_Equipment
	Equipment_ID (Primary Key, Integer)
	Equipment_Name (VarChar)
	Equipment_Role (VarChar)
	Equipment_Technology_Base (VarChar)
	Equipment_Technology_Rating (VarChar)
	Equipment_Availability_Rating_1 (VarChar)
	Equipment_Availability_Rating_2 (VarChar)
	Equipment_Availability_Rating_3 (VarChar)
	Equipment_Availability_Rating_4 (VarChar)
	Equipment_Prototype_Year (Integer)
	Equipment_Production_Year (Integer)
	Equipment_Common_Year (Integer)
	Equipment_Extinction_Year (Integer)
	Equipment_Return_Year (Integer)
	Equipment_Book_Reference (VarChar)
	Equipment_Reference_Book_Page (VarChar)
Table_Unit_Manufacturer
	Unit_Manufacturer_ID (Primary Key, Integer)
	Unit_ID (Foreign Key, Integer)
	Manufacturer_ID (Foreign Key, Integer)
	Planet_ID (Foreign Key, Integer)
	Reference_Book_ID (Foreign Key, Integer)
	Unit_Manufacturer_Reference_Book_Page (VarChar)
Table_Manufacturer
	Manufacturer_ID (Primary Key, Integer)
	Manufacturer_Name (VarChar)
	Manufacturer_General_Inner_Sphere (Boolean)
	Manufacturer_General_Periphery (Boolean)
	Manufacturer_General_Mercenary (Boolean)
	Manufacturer_General_Home_World_Clan (Boolean)
	Manufacturer_General_Inner_Sphere_Clan (Boolean)
	Manufacturer_General_All (Boolean)
	Manufacturer_General_Faction (Boolean)
Table_Planet
	Planet_ID (Primary Key, Integer)
	Planet_Name (VarChar)
	Planet_Remark (LongVarChar)
Table_Reference_Book
	Reference_Book_ID (Primary Key, Integer)
	Reference_Book_Code (VarChar)
	Reference_Book_Name (VarChar)

Update 4 (04/11/2017):

odb file can be downloaded here:

Ratslinger · March 29, 2017, 2:23pm

Based upon a brief look at your code, I understand what you are seeing. What is not clear is the intent of the List box. Talking just of referenced Planet ID/Name, is the purpose solely to display the category related to the ID in the selected record? Is the same true for the other list boxes? This information is needed to help with the correct direction.

SirMegaV · March 30, 2017, 9:57am

Please look at my updates at librebel tread until I figure out how to revise my original question.

I have another form “Form_Manufacturer” that have a many to many table pointing which manufacturer is located in which planet. My problem form “Form_Unit” have a subform (table_control) to select the manufacturer via a listbox (display all manufacturer). After selecting the manufacturer, the “Planet_ID” listbox should only display planets linked to that manufacturer.

librebel · March 29, 2017, 10:13pm

Hello SirMegaV,
A few things you could try:

when you write sValue = oListBox.ValueItemList(oListBox.SelectedItems(0)), then you get an integer value, namely the bound ID field (Unit_Type_ID or Manufacturer_ID in your code example). So instead of checking sValue<>"", you could check if sValue>-1 or so. Likewise remove the extra quotation marks around sValue in your SQL statements, since sValue is supposed to be an integer value.
try add oListBox.BoundColumn = 1 ( for example after oListBox.ListSource=Array(sSQL) )

SirMegaV · March 30, 2017, 9:38am

Hi librebel,

I can’t use sValue >-1 because when sValue = “”, it is still considered true and will cause “Unexpected token” error at the sSQL = "SELECT… line during the second record for SubForm_Table_Unit_Manufacturer.

The quotation marks for sValue in the sSQL statement has been removed.

Tried adding oListBox.BoundColumn = 1 before oListBox.ListSource=Array(sSQL)

Note: Original question updated.

SirMegaV · March 30, 2017, 9:48am

How do I revise my question? Anyway, I updated LibreOffice right before posting my original question. While trying your suggestion, I noticed that the listbox content is behaving differently now. The content of the listbox is showing the value that should be displaying for my last mainform record. I suspect this is the real reason why nothing is showing up in “Planet_ID” since the ‘saved’ value doesn’t exist in the listbox content.

librebel · March 30, 2017, 12:17pm

Hello SirMegaV, at the bottom of your original question there is supposed to be a small button called “edit”. If you click on that button then you can revise your original question.
After calling commit() for the listbox, you should reload the subforms that depend on it, e.g.

oEvent.Source.Model.Parent.getByName( "SubForm" ).Reload()

Before the record cursor changes, update the entire row, e.g.

if oForm.isNew() then oForm.insertRow() else oForm.updateRow()

SirMegaV · March 31, 2017, 9:55am

Hi librebel. The moment I add

oEvent.Source.Model.Parent.Parent.Parent.GetByName
("SubForm_Table_Unit_Manufacturer").Reload()

I get the ‘object variable not set’ on the previous line

oEvent.Source.Model.commit()

And do I put

if oForm.isNew() then oForm.insertRow() else oForm.updateRow()

in the Cascading_from_Manufacturer_ListBox or the AfterRecordChange_MainForm?

librebel · April 1, 2017, 2:42am

no, that last line is typically called BEFORE the record changes or closes

SirMegaV · April 4, 2017, 10:28am

I added to Mainform’s Before record change

sub BeforeRecordChange_MainForm (oEvent as Object)
	oForm = oEvent.source
	if oForm.isNew() then oForm.insertRow() else oForm.updateRow()
end sub

When opening using form, the 1st record loaded normally. But when I go to the next or last record, I get the error “BASIC runtime error. Property or method not found: isNew”

I will try to make the structure clearer by replying Ratslinger.

librebel · April 4, 2017, 4:34pm

sorry … IsNew is a property, not a function, so the line should read:

if oForm.isNew then oForm.insertRow() else oForm.updateRow()

without the () after IsNew

SirMegaV · April 5, 2017, 8:40am

I’m still getting “Property or method not found: isNew” without ( ). Sorry that I’m not helpful, I’m just learning this.

Ratslinger · April 1, 2017, 1:26am

The information provided in the question is not only lacking information but also contains irrelevant information. By doing so, you make it more difficult for people to try to understand just where the problem lies. You have to remember that others cannot see what you are looking at nor can we hear what you are thinking.

After trying for some time to decipher just what you have here, this is what I come up with:

One main form and one subform
six tables involved
five list boxes involved
three sub-routines involved; the first “sub AfterRecordChange_MainForm (oEvent as Object)” is actually in the last.
there is no telling where you get the info for LB “Unit_Type_ID” or LB “Manufacturer_ID”
no mention of relation between form & subform (although must be since first stated as correct)

Now it seems @librebel is on the right track with the refresh of the subform. The problem is with Manufacturer_ID listbox. Where is the data coming from? It is not even clear if Planet_ID is in a table control or Manufacturer_ID is or both! This never seems to be refreshed when something changes on the main form and it plays a part in determining what is being displayed on the subform. It may be this should not be on the subform. Also simply reloading the subform may produce undesired results since Manufacturer_ID listbox is on the subform. Even this cannot be fully determined unless the relation is known between form and subform.

Then where and how and if to place a reload statement can’t be determined without knowing more about the list boxes on the subform. I also can’t see the need for the insert/update statement since any occurring trigger already states the record was inserted or updated.

Edit 4/8/17:

@SirMegaV Please in the future, when a sample is posted you do not need to post all the specifics in your question. It is just a waste of space and confuses the question.

Have looked at your sample and you have real design issues. You should not have a listbox is a table control whis depends upon another listbox within the same table control which is related to yet another table and displays data not from the field it is in. I would take another look at your design and move to text boxes and some other macro coding as what you have only becomes more confusing every time you add another control.

The display was always one cycle off because of not reloading the subForm. Here is the corrected macro to fix the immediate problem:

'   *************** Refresh Cascading When No Parent Change ***************
sub AfterRecordChange_MainForm (oEvent as Object)
'print "AfterRecordChange_MainForm"
oForm = oEvent.source
'   *************** Unit_Type_ID Cascading ***************
oListBox = oForm.GetByName("Unit_Type_ID")
sValue = oListBox.ValueItemList(oListBox.SelectedItems(0))
'print "1"
if sValue <> "" then
	sSQL = "SELECT ""Table_Unit_Motive_Category"".""Unit_Motive_Category"", ""Table_Unit_Motive"".""Unit_Motive_ID"" FROM ""Table_Unit_Motive_Category"", ""Table_Unit_Motive"" WHERE ""Table_Unit_Motive_Category"".""Unit_Motive_Category_ID"" = ""Table_Unit_Motive"".""Unit_Motive_Category_ID"" AND ""Table_Unit_Motive"".""Unit_Type_ID"" ='" & sValue & "'"
	oListBox=oForm.GetByName("Unit_Motive_ID")
	oListBox.ListSourceType=com.sun.star.form.ListSourceType.SQL
	oListBox.ListSource=Array(sSQL)
	oListBox.Refresh
'		oListBox.Enabled = 1 'enable Listbox
'print "2"
	sSQL = "SELECT ""Table_Unit_MUL_Role_Category"".""Unit_MUL_Role_Category"", ""Table_Unit_MUL_Role"".""Unit_MUL_Role_ID"" FROM ""Table_Unit_MUL_Role_Category"", ""Table_Unit_MUL_Role"" WHERE ""Table_Unit_MUL_Role_Category"".""Unit_MUL_Role_Category_ID"" = ""Table_Unit_MUL_Role"".""Unit_MUL_Role_Category_ID"" AND ""Table_Unit_MUL_Role"".""Unit_Type_ID"" ='" & sValue & "'"
	oListBox=oForm.GetByName("Unit_MUL_Role_ID")
	oListBox.ListSourceType=com.sun.star.form.ListSourceType.SQL
	oListBox.ListSource=Array(sSQL)
	oListBox.Refresh
'		oListBox.Enabled = 1 'enable Listbox
end if

'ADDED NEXT LINE
oForm.GetByName("SubForm_Table_Unit_Manufacturer").reload()

oListBox = oForm.GetByName("SubForm_Table_Unit_Manufacturer").GetByName("Table Control Table_Unit_Manufacturer").GetByName("Manufacturer_ID")
sValue = oListBox.ValueItemList(oListBox.SelectedItems(0))
'print "3"
	if sValue <> "" then
'		sSQL = "SELECT ""Table_Planet"".""Planet_Name"", ""Table_Planet"".""Planet_ID"" FROM ""Table_Planet"", ""Table_Manufacturer_Facility"" WHERE ""Table_Planet"".""Planet_ID"" = ""Table_Manufacturer_Facility"".""Planet_ID"" AND ""Table_Manufacturer_Facility"".""Manufacturer_ID"" = '" & sValue & "'"
	sSQL = "SELECT ""Table_Planet"".""Planet_Name"", ""Table_Planet"".""Planet_ID"" FROM ""Table_Planet"", ""Table_Manufacturer_Facility"" WHERE ""Table_Planet"".""Planet_ID"" = ""Table_Manufacturer_Facility"".""Planet_ID"" AND ""Table_Manufacturer_Facility"".""Manufacturer_ID"" = " & sValue & ""
'		print sSQL
	oListBox=oForm.GetByName("SubForm_Table_Unit_Manufacturer").GetByName("Table Control Table_Unit_Manufacturer").GetByName("Planet_ID")
	oListBox.ListSourceType=com.sun.star.form.ListSourceType.SQL
	oListBox.ListSource=Array(sSQL)
'oListBox.BoundColumn = 1
	oListBox.Refresh

'ADDED NEXT LINE
oForm.GetByName("SubForm_Table_Unit_Manufacturer").reload()

end if
end sub

Now with this change you problem to the original question is fixed. However it reveals problems with another macro for the listbox change event. This is not a 100% fix but get around the basic problems:

Sub Cascading_from_Manufacturer_ListBox (oEvent As Object)
oSubForm = ThisComponent.Drawpage.Forms.getByName("MainForm").GetByName("SubForm_Table_Unit_Manufacturer") 'Get Form
oField = oSubForm.GetByName("Table Control Table_Unit_Manufacturer").getByName("Manufacturer_ID")
sValue = oField.SelectedValue
If sValue = NULL then exit sub
if sValue <> " " then
	sSQL = "SELECT ""Table_Planet"".""Planet_Name"", ""Table_Planet"".""Planet_ID"" FROM ""Table_Planet"", ""Table_Manufacturer_Facility"" WHERE ""Table_Planet"".""Planet_ID"" = ""Table_Manufacturer_Facility"".""Planet_ID"" AND ""Table_Manufacturer_Facility"".""Manufacturer_ID"" = " & sValue & ""
	oListBox=oSubForm.GetByName("Table Control Table_Unit_Manufacturer").GetByName("Planet_ID")
	oListBox.ListSourceType=com.sun.star.form.ListSourceType.SQL
	oListBox.ListSource=Array(sSQL)
	oListBox.Refresh
end if
End Sub

Frankly I have spent too many hours on this as it is confusing, and as stated earlier, poorly set-up. I leave this in your hands.

If this answers your question please click on the (upper left area of answer).

SirMegaV · April 5, 2017, 8:33am

Apologies for the irrelevant info, I was trying to explain that the same method doesn’t work for cascading in the subform. Updated above with form structure with all tables, listboxes and subforms involved. You and @librebel is correct, it should be a refresh, but I can’t find the place to “refresh” it.

SirMegaV · April 7, 2017, 9:34am

I’ve updated above. Hope it’s clearer now, but I find these information confusing. You are right, link to file below. Open file, open Form_Unit, take note of the Manufacturer grid’s Planet. Jump to last record (the only record with different planet), planet field will be empty. Press planet listbox, list not current. Jump back to 1st record, planet will be blank now, listbox list display last record’s list.

Ratslinger · April 8, 2017, 9:09pm

Will post update in answer.

SirMegaV · April 11, 2017, 10:38am

Thank you very much and sorry for the confusion. Should I update my original question (remove the codes and form/table stucture)? Tested your solution and it solved the immediate problem, but the manufacturing cascading is no longer working. Will try to solve it myself first. I know its poorly setup, tried normalizing it as much as I could. Any advice much appreciated. Should I click ‘answered’ and open another question if you are willing to give advice?

Ratslinger · April 11, 2017, 2:19pm

Don’t know what you did, but the Manufacturer list box works the same for me with or without the changes. You may have changed something else.

Ratslinger · April 11, 2017, 7:00pm

The only advice I can give you at this time is to use different controls & code. I have not looked at the actual data structure nor do I want to. For me to offer advice on the construction, it would take many hours of analysis after trying to grasp the concepts of what the end result is to be. It is clear, however, that using a listbox which depends upon another listbox in the same table control is poor design.

SirMegaV · April 12, 2017, 10:03am

Tried again with copy and paste. The planet now is having similar symptom. When selecting a new manufacturer, the planet list is updated 1 manufacturer selection behind. Anyway, thank you very much for everyone’s help especially yours. I’ll be marking this question as answered. Thanks again.