# Base Subform Cascading Listbox Refresh

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 ...
edit retag close merge delete

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.

( 2017-03-29 16:23:46 +0200 )edit

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.

( 2017-03-30 11:57:49 +0200 )edit

Sort by » oldest newest most voted

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
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 ...
more

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.

( 2017-04-05 10:33:19 +0200 )edit

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.

https://www.dropbox.com/s/qod8dkrmsjo...

( 2017-04-07 11:34:26 +0200 )edit

( 2017-04-08 23:09:23 +0200 )edit

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?

( 2017-04-11 12:38:22 +0200 )edit

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.

( 2017-04-11 16:19:06 +0200 )edit

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.

( 2017-04-11 21:00:28 +0200 )edit

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.

( 2017-04-12 12:03:58 +0200 )edit

Hello SirMegaV, A few things you could try:

1) 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.

2) try add oListBox.BoundColumn = 1 ( for example after oListBox.ListSource=Array(sSQL) )

more

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.

( 2017-03-30 11:38:49 +0200 )edit

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.

( 2017-03-30 11:48:47 +0200 )edit

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

( 2017-03-30 14:17:06 +0200 )edit

Hi librebel. The moment I add

oEvent.Source.Model.Parent.Parent.Parent.GetByName


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?

( 2017-03-31 11:55:41 +0200 )edit

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

( 2017-04-01 04:42:19 +0200 )edit

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.

( 2017-04-04 12:28:59 +0200 )edit

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

( 2017-04-04 18:34:31 +0200 )edit

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

( 2017-04-05 10:40:17 +0200 )edit