Ask Your Question
0

Base Subform Cascading Listbox Refresh

asked 2017-03-29 12:13:41 +0200

SirMegaV gravatar image

updated 2017-04-11 12:07:58 +0200

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 ...
(more)
edit retag flag offensive close merge delete

Comments

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.

Ratslinger gravatar imageRatslinger ( 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.

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

2 Answers

Sort by » oldest newest most voted
0

answered 2017-04-01 03:26:56 +0200

Ratslinger gravatar image

updated 2017-04-08 23:33:29 +0200

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 ...
(more)
edit flag offensive delete link more

Comments

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 gravatar imageSirMegaV ( 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...

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

Will post update in answer.

Ratslinger gravatar imageRatslinger ( 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?

SirMegaV gravatar imageSirMegaV ( 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.

Ratslinger gravatar imageRatslinger ( 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.

Ratslinger gravatar imageRatslinger ( 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.

SirMegaV gravatar imageSirMegaV ( 2017-04-12 12:03:58 +0200 )edit
0

answered 2017-03-30 00:13:25 +0200

librebel gravatar image

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

edit flag offensive delete link more

Comments

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 gravatar imageSirMegaV ( 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.

SirMegaV gravatar imageSirMegaV ( 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()
librebel gravatar imagelibrebel ( 2017-03-30 14:17:06 +0200 )edit

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?

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

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

librebel gravatar imagelibrebel ( 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.

SirMegaV gravatar imageSirMegaV ( 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

librebel gravatar imagelibrebel ( 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.

SirMegaV gravatar imageSirMegaV ( 2017-04-05 10:40:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-03-29 12:13:41 +0200

Seen: 619 times

Last updated: Apr 11 '17