[Solved] List Box B contents affected by List Box A

[kabing]

I'm wondering how to set up two list boxes on a form so that the value of the first list box dictates the contents of the second list box.

NeoOffice 2.2.3 (OpenOffice.org 2.2.1 codebase), embedded (HSQLDB) database, Mac 10.4.11

Pertinent tables:

• Countries
  • Country_Code (Primary Key; two letters)
    Country_Name
  States_And_Provinces
  • Country_Code (Combined Primary Key; relates to County_Code in Countries Table)
    State_Code (Combined Primary Key; two or three letters)
    State_Name
  Locations
  • Location_ID (Primary Key; integer)
    Country (relates to Country_Code of Countries table)
    State_Or_Province
    etc....

On the data entry form I created for the Locations table, I want the user to be able to first specify the country in a Country list box; then I want the State list box to only list the states for that country.

I presume this will require a macro, if it's possible at all. But I don't know how to go about doing it.

Thanks much,

kabing

[voobase]

Hi Kabing,

There's Drews example, the subs of interest are: sub onStatusChange_STKOUT_Category and Sub limitItems

http://www.oooforum.org/forum/viewtopic.phtml?t=57424

I've now managed to get it working properly in the project I am developing. I found it quite tricky at first as once you have narrowed the list in the second listbox, if you change rows in your form and the bound field contains a value which is no longer in the list it is erased. Also it is nice for the listbox's to reflect the values that have already been entered as you step through your records. I also needed to use a "before record change" event macro which has its own issues as it gets fired twice, both from the controller and the form. You will see an "If" statement there to only allow one of these events or else it throws an error.

I had a go at explaining it to someone else and posted a basic example on mediafire for download in the post below. The example is 90 percent there but needs a bit of a tidy up with better rem's in it. It also doesen't reflect back into the second listbox correctly yet and after you have used the listbox's and then change records in the second listbox every record is again in the list. Both of these problems I have fixed in my project but I couldn't get going correctly in the example. I'll have another go at it soon and re-post the example.

The example also has a form for entering values into the tables that supply the listbox's with their lists. This form is simply a mainform and subform in a one to many relationship using Primary and Foreign Key's.

http://www.oooforum.org/forum/viewtopic.phtml?t=71055

Cheers

Voo

PS. Look for the 9th post in the thread for the link to the "simple" example. The db is called "classes" and it includes a macro file to load separately

[kabing]

Voo,

Thanks for the reply! I have a feeling this may be more complicated than I want to deal with. My experience with macros is limited to copying code found, pasting in a module, and swapping out field and control names. Anything else is beyond me.

I'll have a look at the Classes database and see if I think I can tackle this. First, though I have a really basic question (apologies for the pun). What do I do with the .xlb and .xba files? Can I just move them manually, or do I need to load them some other way?

Thanks,

kabing

[voobase]

Hi again,

It shouldn't be too complicated. The form design is fairly straightforward with mainform, subform and table grid and the two listbox's also in the subform. The macros are triggered by the "On status change" of both listbox's and the "Before record change" and "After record change" of the subform itself. Yes, the macros may look a little complicated but if you have a play with them they might make some sense. I'm just in the process of finishing tidying the example up to re-post it (hopefully 20 mins or so). I have put some more Rem's in to explain bits and pieces and almost got it working 100 percent.

To get the example up and running copy the odb file to to your examples folder and double click it. Once Base has launched you need to first register the database by going to Tools>Options>OpenOffice.org Base>Databases and select "New". Next you need to load the Macro file in. Either press ALT F11 or go to: Tools>Macros>OrganiseMacros>OpenOffice.org Basic. Once Macros box is showing go to: Organized>Libraries>Import and choose the script file. Macros should now be loaded and you can now try the data base.

Of course you're welcome to ask any questions if you don't understand bits. I've given a bit of a quick rundown on how it works towards the end of the post on the other forum. It was a bit of a rushed explanation so you might have some questions.

I'll give a yell soon if I finish tidying up the example tonight.

Cheers

Voo

ps The macro is the script.xlb and xba file.You shouldn't need to load the dialog file.

[voobase]

Hi Kabing,

I have tidied up the example and it works much better now. It uses just three events, the "On status changed" events of the two listbox's and the "After record change" event of the SubForm. The "After record change" event just calls the "limit items" sub again and also does a "reset" on the second listbox. It was good to get rid of the "Before record change" event as this simplifies the macro quite a lot.

The other parts of the macro are just to copy the "current value" text of the listbox to the table grid and I show how this is done first by copying these values in the form and then by using a "prepared statement" to copy the values to the data table instead.

I'll put the finished "Classes" example for download here. Let me know if you have any problems.

Don't forget to register the database in Open Office Base options and to import the macro file.

Cheers

Voo

Classes2 160508.zip

Finished listbox narrows other listbox example.

(26.65 KiB) Downloaded 470 times

Code: Select all

REM Basic Macro for Classes Example by Voo 16/5/08
Sub Main

end Sub

Rem This Sub is triggered by the "Categories" listbox "On status changed" event. 
Rem This Sub then passes the event object and Primark Key of Categories selection to the "LimitItems" sub
Rem that limits the items in the "Classes" listbox.
Rem This sub then copies the "Session Name" and "Class Categories" text values to the table grid
sub Class_Listbox_Limit( oEv as object )

   dim oSubForm As object
   dim new_list_sql(0) as string
   dim TableAddr As object
   dim TableLoc As object
   
    	Rem sets the form variable with Event object which gets passed into this macro from the "Classes" listbox.
     	oSubForm=oEv.Source.Model.Parent

	if oEv.Source.Model.CurrentValue <> "" then
  
 		oEv.Source.Model.commit rem ... commits current value to boundfield				
 		
		Rem Calls the LimitItems sub and passes the Form event object and the Primary Key value of the selected item.
    	limitItems( oEv.Source.Model.Parent, oEv.Source.Model.BoundField.String )
	
		   	
Rem The rest of the macro just copies the text fields of the "Session" and "Categories" Listbox to the Table Grid.
    	
       	Rem this is an easy straightforward way to copy text fields to Table Grid
		TableAddr=oSubForm.GetByName("SubForm_Grid").GetByName("Session_Name")
		Tablevar = oEv.Source.Model.Parent.Parent.GetByName("txtSessionName").CurrentValue
   		TableAddr.BoundField.updateString(Tablevar)
    	
    	
    	Rem When on insert row, these three lines copy the text across. 
    	Rem I tried to use a prepared statement here but couldn't get INSERT INTO to work.
    If oSubForm.IsNew = true then
    
    
		TableAddr=oSubForm.GetByName("SubForm_Grid").GetByName("Category_Name")
		Tablevar = oEv.Source.Model.CurrentValue
   		TableAddr.BoundField.updateString(Tablevar)
   		
   		Rem This prepared statement is the more complicated way to copy the text accross. 
   		Rem As this is not a new record it also blanks out Class Name.
    Else
   		dim ooPrepStatement
		dim osSQL as string
'	
    	osSQL = "UPDATE ""SessXREF"" SET ""Category_Name"" = ?, ""Class_Name"" = ? "
    	osSQL = osSQL & "WHERE ""ID"" = ? "
    
   		ooPrepStatement = oSubForm.ActiveConnection.prepareStatement( osSQL )
   		ooPrepStatement.setString( 1, oSubForm.GetByName("lbCategories").CurrentValue ) ' XREF FK
   		ooPrepStatement.setString( 2, "" ) ' Class Name set to blank when overiding an entry
   		ooPrepStatement.setInt( 3, oSubForm.GetInt(1) ) ' Categories FK of the record you are on.
   		ooPrepStatement.executeUpdate()
    End if	   	
    
    		Rem Updates the database table with the latest records. These are reflected back in the Table Grid 	
    	If oSubForm.IsNew then Rem If on the insert row
    		oSubForm.InsertRow()
    	Else    	
    		oSubForm.UpdateRow() 
    	End if   	  	
   end if
end sub 



Rem Borrowed from Drew Jensen example, this limits the second listbox to only the one category.
sub limitItems( aDataForm as variant, aCategory as String )

   dim new_sql(0) As string
   dim olist as variant	

	   	oList= aDataForm.getbyname("lbClass")
		new_sql(0) = "SELECT ""ClassName"", ""ID"" FROM ""Classes"" WHERE ""Category_FK"" = '" & aCategory & "'"
		oList.ListSource = new_sql

		aDataForm.getbyname("lbClass").refresh() Rem loads the new sql list into Classes ListBox
end sub


Sub Class_Listbox_Copying_and_Reset( oEv as object )

	dim oControl as object
	dim oSubForm As object
	dim new_sql(0) as string
       
		oControl = oEv.Source.Model
		oSubForm=oEv.Source.Model.Parent
	
	if oEv.Source.Model.CurrentValue <> "" then
	
		oEv.source.model.commit rem is this required? Yes it is or else Service does not follow on first location selection.
	
		Rem This is an easy straightforward way to copy the "Session Name" text field to table grid. 
		Rem It is the form that will update the data table with the latest text values when updaterow() is called.
		TableAddr=oSubForm.GetByName("SubForm_Grid").GetByName("Session_Name")
		Tablevar = oEv.Source.Model.Parent.Parent.GetByName("txtSessionName").CurrentValue
   		TableAddr.BoundField.updateString(Tablevar)		

		Rem This prepared statement is a more complicated way to copy the "Class Name" listbox current value text accross.
		Rem It will put it directly in the SessXREF table and it then shows up in the table grid.
		Rem It copies the text to the data table rather than the form. It shows up in the table grid after the updaterow().
	dim oPrepStatement
	dim sSQL as string

    	sSQL = "UPDATE ""SessXREF"" SET ""Class_Name"" = ? "
    	sSQL = sSQL & "WHERE ""ID"" = ? "
    
   		oPrepStatement = oSubForm.ActiveConnection.prepareStatement( sSQL ) ' Uses the Active connection of the subform
   		oPrepStatement.setString( 1, oControl.CurrentValue ) ' Class Name set with listbox control's current value
   		oPrepStatement.setInt( 2, oSubForm.GetInt(1) ) ' GetInt(1) is the first column of row set of the subform i.e the Primary Key
   		oPrepStatement.executeUpdate
 

		oSubForm.UpdateRow()
	endif	
end Sub


Rem This Macro is to call the limit items sub again just after you have moved to a new row. 
Rem The reason it is needed is to stop the "classes" listbox deleting the values from the table grid if they are not in the limited list.
Rem It also keeps the "Class" list narrowed to only display things dependant on the "class category".
Sub After_Record_Listbox

	dim oSubForm as object

		Rem Not using the event to get to the form this time. No reason why, just being different.
		oSubform = ThisComponent.Drawpage.Forms.getByName("MainForm").GetByName("SubForm")
		
		Rem Calling limit items again
		Rem GetInt(4) is the value of column #4, i.e the Foreign Key value from the "Categories" listbox as stored in the row set.
		limitItems ( oSubForm, oSubForm.GetInt(4) ) 
		oSubForm.getbyname("lbClass").reset()
End sub

[kabing]

Thanks, Voo!

I've got a busy weekend, so it will be several days (at least) before I can look at it. My list boxes don't relate to a subform table grid, they're just part of the main form. I take it adusting for that shouldn't be too hard?

Lorinda

[voobase]

No worries, get to it when you have time.

It should almost work exactly the same for a mainform. I'll try to explain a little in my layman's terms.

the "Event Object" is passed into the sub when the sub is targeted by the event of something and you have a variable in the sub's title. This is what the ( oEv as object ) is. It dosen't have to be called oEv and often people call it oEvent etc. As the oEv comes from the listbox's and they are a type of control, then to get to the properties and methods of the listbox you need to use the path oEv.source.model. Now, the listbox is sitting in a form so the path to the form properties are oEv.source.model.parent. This does not change whether it is a mainform or subform so all the paths should be the same for using the listbox's in your mainform. If you wanted to be ethically correct about it you may want to change the variable oSubForm to oForm which you could do by using the find and replace feature in the macro edit window.

In the last sub you will need to change things slightly as I used a different way to get to the form properties. You will just need to change it from:

Code: Select all

ThisComponent.Drawpage.Forms.getByName("MainForm").GetByName("SubForm")

to

Code: Select all

ThisComponent.Drawpage.Forms.getByName("MainForm")

The other things you will need to do for the macro is substitute your listbox names and table grid's name etc. For the sections where it copies the text from the listbox into your table grid you might want to just copy the code of the "easy way" over the prepared statements if you find those too hard to follow.

Anyway, have fun and let me know if you get stuck.

Cheers

Voo

[kabing]

Thanks for the clarification. Most of that makes sense on a first read, the rest probably will if I go over it again carefully.

One other difference, though. I don't have a table grid at all on my form. It's just a basic data entry form for entering data about one location. I don't want the State listbox to have an overwhelming number of entries for the user to sift through. So I want the State listbox to only show the States (or Provinces) for the Country specified in the Country listbox.

I'll let you know when I've had a chance to play with the form and the macros

kabing

[kabing]

Voo:

Thank you so much! It turned out to be much less complicated than I feared. Thanks to your prolific use of REM statements I was even able to figure out what parts of the first macro I could delete since I don't have a table grid to be concerned about in my form.

Here are the macros as I adapted them:

Code: Select all

Rem This Sub thanks to Voobase
Rem This Sub is triggered by the "country" listbox "On status changed" event.
Rem This Sub then passes the event object and Primary Key of Country selection to the "LimitItems" sub
Rem that limits the items in the "State" listbox.
sub State_Listbox_Limit( oEv as object )

   dim oForm As object
   dim new_list_sql(0) as string
   dim TableAddr As object
   dim TableLoc As object
   
       Rem sets the form variable with Event object which gets passed into this macro from the "Classes" listbox.
        oForm=oEv.Source.Model.Parent

   if oEv.Source.Model.CurrentValue <> "" then
 
      oEv.Source.Model.commit rem ... commits current value to boundfield            
      
      Rem Calls the LimitItems sub and passes the Form event object and the Primary Key value of the selected item.
       limitItems( oEv.Source.Model.Parent, oEv.Source.Model.BoundField.String )
   End if
      
End Sub
   
Rem VoobBase Borrowed from Drew Jensen example, this limits the second listbox to only the one category.
sub limitItems( aDataForm as variant, aCategory as String )

   dim new_sql(0) As string
   dim olist as variant   

         oList= aDataForm.getbyname("lbxState")
      new_sql(0) = "SELECT ""State_Name"", ""State-Country"" FROM ""States_And_Provinces"" WHERE ""Country_Code"" = '" & aCategory & "'"
      oList.ListSource = new_sql

      aDataForm.getbyname("lbxState").refresh() Rem loads the new sql list into State ListBox
end sub

Everything looks to be working fine.

kabing

[voobase]

No worries Kabing,

It's good that you have it working and you are right, its not too complicated. The exercise was good as I simplified the way I needed to go about it for my project whilst working out the example which is great.

Quick question though. Did you find that you didn't need to use the "after record change" part of the macro? I found that without this section the second listbox would not display the value from the form when you were changing through records and in my case it would also delete this bound field.

Cheers

Voo

[kabing]

Oh my! I didn't test carefully enough. You are right; if I navigate through the various records, the State listbox goes empty if the Country of the new record is different that the country the list box was last set to.

I looked at your macro above to deal with it, but it would need major modifications for me to use it. I don't have any table grids on my form, and no subforms. Just one main form, one record per form. Can you guide me through the marco I need?

It looks like I need a macro that would identify the value of the Country field for the displayed record and then properly limit the State listbox. Or....would it work to reset the State listbox to the full list?

Would attaching it to one of the form events work? If so, which one? I'm thinking it needs to change when I move from one record to the next, but I find some of the Events definitions rather cryptic.

kabing

[voobase]

Or....would it work to reset the State listbox to the full list?

Thats how I first dealt with the problem, by using the "before record change" event, but don't use that event if you can help it as it seems to have issues that complicate the macro required. ( Off track here... The short of that story is that the event is called twice, both by the form controller and the form. You need to determine which is calling it or you get an error. I also think this event can cause mainform instability. see.... http://user.services.openoffice.org/en/ ... =13&t=3272 )

What you should try is to implement the "after record change" sub from the example. Just connect it to that event of your mainform. I've changed it slightly to suit using it on a form event. (The form's path is oEv.source, not oEv.source.model.parent - as seen from a control.)

Code: Select all

     Rem This Macro is to call the limit items sub again just after you have moved to a new row.
     Rem The reason it is needed is to stop the "State" listbox deleting the values from the form which are not in the limited list.
     Rem It also keeps the "State" list narrowed to only display things dependant on the "country chosen".
Sub After_Record_Listbox (oEv as object)

   dim oForm as object

      Rem Using the event to get to the form. 
      oForm=oEv.Source
      
      Rem Calling limit items again
      Rem You will need to change the number in GetInt(4) to reflect the column number from your data table that holds the "countries" listbox bound field.
      limitItems ( oForm, oForm.GetInt(4) )
      oForm.getbyname("lbxState").reset()
End sub

Now the other thing you will need to do is open up the data table for your data entry form and count from left to right to get the number of the column that holds the "bound field" from the "country" listbox. Just substitute that number into the oForm.GetInt(X) part of the macro where the X is.

With a bit of luck this will work now for you. What is happening is that after you navigate to a new record, the sub will get the "bound" country value from the rowset of your form and send it to the "limit Items" sub again. The next line with the "reset()" in it seems to get the "state" listbox to pick up this bound value and refer to it.

Cheers

Voo

[kabing]

I really appreciate the help, Voo.

At first I couldn't get it to work, because I was confused about which table I need to count columns on.

The form is based on the Locations Table. The Country_Code field is column #6.
The Countries table is the source list for the Country list box. The Country_Code field is column#1
The States_And_Provinces Table is the source list for the States table. The Country_Code is column#2

At first I thought you meant the column # from the Locations or the Countries table. In both cases, using that number resulted in an empty States dropdown. It was only when I started this post to tell you it didn't work that it occured to me to try the column# from the States_And_Provinces Table. Then it worked fine.

I originally had another macro attached to the On Record Change event as the Locations table also functions as a source table for a combo box on my Field_Trips data entry form. So I had a macro to force the refresh of the Location combobox on the Field Trips form attached to the after record change event on the Locations form. More on that here..
I'm still looking for a better solution for that issue, in this thread.

In the meantime, though, I have established a work around that lets me use both macros. I copied the Locations form. One is set for new entries only and uses the macro to refresh the combo box on the Field_Trips form. (This Locations form can be invoked by a button on the Field_Trips form). The other copy uses the macro you gave me here, to reset the States lisbox when the record is changed. Both copies use the listbox limiting macros.

Thanks again for your help.

kabing

[voobase]

No worries Kabing,

try the column# from the States_And_Provinces Table. Then it worked fine.

I guess triple check it to make sure its doing what it should be.

Sorry, I probably could have explained it a bit better rather than getting you to hunt for a field namewhich I thought you might have used. The field we were after is as follows. If you look in the properties of your "Country" listbox, the field name in the "Data field" section should be the field name from your "locations" table which we are after. This is the bound field from the "locations" table that the "countries" listbox will put a value in. The field should be from the "locations" data table as the listbox is sitting in your main form. It sounds like this might be data field #2 of your "locations" data table.

I'm fairly sure you have it working with a field from the "locations" table due to the path being oEv.source (that path will return you the properties of the form which the event comes from). We have just defined a variable, "oForm" and put "oEv.Source" in it. What is that #2 field called in your "locations" data table? Otherwise, maybe something neat is happening with your dual primary keys. I don't know enough about them to be able to guess.

Now, about already using the "after record change" event up with another macro. You can, of course, call a sub from within a sub, its quite easy. Make sure the first sub has (oEv as object) next to its title, then this can get passed through to any other subs which are called. From your example on the other thread it might look like this...(guessing you are using the first firelex macro)

Code: Select all

sub Refresh_Location_Combobox_In anotherForm (oEv as object) Rem*** Need this oEv as object to pass on to other sub

Rem... You combobox is in another form so you will need some tricks to refresh it, so firelex solution...

Dim tmp    as Object        
   tmp = thisComponent.Parent.FormDocuments
   if tmp.HasByName("frmMyForm") then      
      tmp = tmp.getByName("frmMyForm")
      if not IsNull(tmp.Component) then
         tmp = tmp.Component.DrawPage.Forms.getByName("frmMyForm")
         if tmp.HasByName("lbxAb") then      
            tmp = tmp.getByName("lbxAb").refresh
         endif
      endif
   endif

Rem*** This is all you need to do to run another sub ***
Rem*** Just add the next line...
After_Record_Listbox (oEv)

end sub

Anyway it shouldn't be too hard to add that to your macro, let me know if you have any difficulties.

Cheers

Voo