[Tutorial] Cascading Listboxes with macros

Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

[Tutorial] Cascading Listboxes with macros

Postby RPG » Wed Dec 28, 2011 11:52 pm


A regular question here is How can I make a macro for a set of listboxes so that the listboxes limits the number of choices. Villeroy, DACM and Arineckaig did have given nice examples without using macros. In the forum for the tutorials and examples you can find explanation and examples how to do it.

Even with all the explanations and examples it is real difficult for most starters. I have not the idea to make it more easy. For good explanations I will point to them but I think some person like maybe the macro I will add in this post. I have the idea that the macro does have no more bugs and can be used.

Using the macro in this post expect several things
  • Don't change the macros
  • Use this macro for listboxes in a mainform for selecting data in a subform
  • The fieldnames for the listboxes in must be the same for mainform and subform

Don't change the macros
I hope this macro makes it a little more easy for other people. Writing macros is always difficult and I hope when you use this macro you have not to change the code. I have test this macro for three listboxes but I hope you can use it for more listboxes but I have not test it.

Use this macro for listboxes in a mainform for selecting data in a subform
I have test this macro in three listboxes where each listbox limits the listboxes futher in the row. When the last listbox did get a value then the themainform does relod the current record and this does also reload the subforms.

The fieldnames for the listboxes must be the same for mainform and subform
It maybe clear to the readers that there is a realtion between the data in the mainform table and also in the subform table. The only method I can get working this is the field who make this relation must have the same name. When they have not the same name then it can not work. When the table you want use do not have the same name the SQL of OOo-base is powrfull enough that you can use the alias for to take care of this.

Explanation more details
As you can see there are several limits to avoid difficult macros. When you want use this idea then you have design your form real careful.
  • The listboxes you use must have all the same name
  • The taborder in this listboxes is also real important. The taborder is also the order in which way they are processed in the macro. I must say: I believe this, about the taborder, and it works so for me.
  • The designer of the form is responsible for a good query in the listbox. This query is modified each time when the listboxe earlier in the row does get an other value. But the user does not see the modified query there after using the new query who does make a new resultset the old query is stored again.
  • I do use some of the builtin possibillities of OOo.

What are the builtins I use
  • I use the group of the data-form object. For this reason the listboxes must have the same name. The group is complete different what most people knew as grouping controls in designing a form.
  • I do not make the filter with BASIC but use the SingleSelectQueryComposer interface/service. I do use here a little trick and this works in the tests I did. The trick I use is using the same name for fields of different tables. I let compose a filter for the listbox while I use the table of the form.

I hope you enjoy it.


Code: Select all   Expand viewCollapse view
Sub forlistboxchange (oEvent as Object)
call  forlistbox oEvent.source.model
end sub


Sub forlistbox(oListboxModel as Object)
' Do not bind this sub to an event but use an other sub to call this sub.
' The listbox what is the parameter for this sub
dim oSearchGroup()
dim oFormModel,oFomControl,oFormOperations

' Init the oSearchGroup there are all the control with the same name.
oFomControl=thiscomponent.currentcontroller.getFormController(oFormModel) ' This is the view of the form

' This can only work when the is a group.
if  UBOUND(oSearchGroup()) then
' There are several textboxes for searching
   dim oComposerForm

   dim x
   for x=0 to ubound(oSearchGroup)
   ' We go through the tabcontroller
      if x=ubound(oSearchGroup) then
            'print " for last control we do nothing more"
               if oFormOperations.commitCurrentRecord(void) then
               oFormModel.absolute(oFormModel.row) ' This does a reload of the current row. And so also a refresh of the
            end if
               'This sub is made for thre listboxes in a form.
               'When the last listbox is loaded then the subform must be reload.
               'Then we can exit the sub
               exit sub
         else ' We are not the last
         if EqualUnoObjects(oSearchGroup(x),oListboxModel) then
            'Now we knew the place of the listbox in the array
            exit for
         end if
      end if
' We have search through the tab controller.
' we knew the place of the current listbox
' Now prepare for the follwoing things.
' We must init the next listbox with search values given in the previous control in the same tabcontroller.
   dim oPrepareThisListbox
   dim iTakevalue
   for iTakevalue=x to 0 step -1
      oComposerForm.appendFilterByColumn(oSearchGroup(iTakevalue).boundfield ,createunovalue("boolean",true),1)

   oPrepareThisListbox.listsource=array(oComposerForm.getQuery ) ' Place the compose query in listbox
   oPrepareThisListbox.refresh ' Load listbox with the query result
   oPrepareThisListbox.listsource=array(oComposerForm.original) ' store back the original query
   msgbox " There is no listbox group" & chr(13) & "See in your dataform "
end if

End Sub
LibreOffice on openSUSE Leap 15
Posts: 2204
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Tutorial] Cascading Listboxes with macros

Postby RPG » Tue Oct 30, 2012 9:10 pm


This is an update of the macro earlier in this thread. I think it is much more easy then before.
The same is you have to give the same name for all the listboxes. The listboxes must display the values in a good way without filtering. Only the first listbox must not be bound to the macro.

Difference for listboxes
What is the most important difference between the first macro and the second macro? The first macro does look to all the following listboxes when the current listbox is changed.The second macro does look back to the listboxes before the current list-box and makes a filter for the current listbox. This is done when the mouse is inside the list-box. The result is the creator of the form does not have a problem for making complex filters for every listbox. The maker must only give the good query for all the list-boxes. The query must give that value to the listbox when there is no filter.

I restore the original query back after I have update the itemlist. I believe you can use tables, queries and SQL. I think you cannot use Native SQL, value lists and Table fields. I have insert an on error routine. This beeps two time for all errors including when you use a value list, Native SQL or a tableFields.

I'm not sure if all is clear but I hope this method is maybe so easy that also starters can work with it.

A last warning do not chance any thing in this macro unless you understand each character in the macro.


Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****
option explicit

Sub CasCadeListbox (oEvent as Object)
'Use the event: mouse inside
' The first listbox does not need the event
dim oListboxControl,oListboxModel
dim oFormModel
dim oFilterGroup() ' This is the group for the listboxes
const cPropertyNames="ListSource ListSourceType" ' I use this getting and setting properties
dim mPropertyValuesOld(),mPropertyNames

' I use the error sub
' for filtering Value list, Native SQL and table list in the listboxes.
' When one of this option for the listboxes are used then the sub produced
' an error for the command in the querycomposer.

on local error goto PlaceBackListboxValues:

if oListboxModel.implementationname="com.sun.star.comp.forms.OListBoxModel" then

   ' The listbox is in a form and not in a gridcontrol
   ' In a gridcontrol we have no group so we can not use this idea
   ' Init the oFilterGroup there are all the control with the same name.
   if  UBOUND(oFilterGroup())<>-1 then
      'do action
      ' There are several listboxes for searching
      dim oComposer
      oComposer =oFormModel.ActiveConnection.GetComposer(_
            oListboxModel.listsource(0)         )
      ' I use choose for changing the difference between the listboxsource type to
      ' the command type. It does also filter out the zero for value list and
      ' four for the native SQL
      ' Both cases give an error. The error call the on error and beeps.
      dim x,oListbox
      for x=0 to ubound(oFilterGroup)
         if EqualUnoObjects(oListbox,oListboxModel) then
            'Now we knew the place of the listbox in the array
            ' Set the listsource query and the listsource type.
            ' listsource type is 3   
            oListboxModel.refresh '
            exit for
            oListbox.commit 'Be sure the value is commit to the field but not to record
         end if

      msgbox   "When you have no group then you can not use this macro" & chr(13) &_
            "Make a group in the form navigator by giving some control the same name"
   end if

else print "The control must be a listbox in a form not in a grid control"
end if
on local error goto 0

exit sub

on local error goto 0
end sub
LibreOffice on openSUSE Leap 15
Posts: 2204
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Return to Base

Who is online

Users browsing this forum: No registered users and 2 guests