How to use user selected value from combobox1 in combobox2 'SELECT' statement

[johnh009]

I have the following reference table (treatment) - extract as follows:

On a form, to record treatment for an animal (dog) in table “dog_treatment”, I have 2 combo boxes, the first to select ‘treatment_type’ (SELECT DISTINCT (“treatment_type”) FROM “treatment”) and the second ‘treatment_subtype’ (SELECT DISTINCT(“treatment_subtype”) FROM “treatment”). Obviously subtype is dependent on the first selection (type) and I would like to use the value selected in the first combo box as a filter in the 2nd SELECT statement, something like:

SELECT DISTINCT("treatment_subtype") FROM "treatment" WHERE "treatment_type" = ComBoxTreatmentType

I have read numerous posts, including the following and it’s associated links and database:

[(Passing parameters from form to query (View topic) • Apache OpenOffice Community Forum)]

As rudolfo (in the above post) says " . . . you need some programming experience because most probably you will have to make some modifications to match it on you environment. And without experience (including debugging a macro) this will all be like chinese for you." And it is!
Ultimately, this is ‘nice to have’ feature, as the combination of “treatment_type” and “treatment_subtype” is a PK in table “treatment” (& FK in table “dog_treatment”) which prevents the user from entering an incorrect type/subtype combination. It would simply reduce the number of subtypes available for selection within the 2nd combo box.

[jimk]

Since these are combo boxes, the second list is not limited to the filtered values, correct? In other words, other values can optionally be entered that are not in the list. More commonly, I have seen filtered list boxes in this type of setup to prevent values from being entered which do not match.

One solution is to use a FilterCriteria table. When the first combo box is changed, a macro enters values into the table, and the second combo box is filtered based on the table.

A detailed example that stores the results of both boxes into the main table of the form is given in my edited answer at forms - libreoffice base create a list filtered by another list's value - Stack Overflow.

[johnh009]

@ Jim K. Thank you for your prompt response. I will attempt to follow your directions and let you know the outcome.

[Ratslinger]

Edited 1/17/2017 - Additional sample at end of answer.

Hello,

First let me state the answer by @jimk is valid (+1).

Filtering can be done with & without macros. I have posted 10 to 20 on this forum using many variations. There is also Filter/Search with Forms you may want to look at.

With that said, here is what I see in your question. You do not really need combo boxes for new treatments. If it isn’t in the ‘treatment’ table it needs to be added there for a centralization of items. Next is that you are working on the probability that an animal will receive more than one treatment at the same time so possibly using a Table control for data entry and history may be the method of choice. This is where the problem arises. You can certainly have list boxes in a table control. What I haven’t seen is a table control with one list box based upon another in the same record. The question has previously arisen & I have finally came to somewhat of a solution. It does, however, utilize two small macros. While small, to someone not accustomed to macros they are quite large (as you have mentioned).

Here is a sample containing the form with a Table Control. It is somewhat based upon your needs as I created it from a previous answer for you. The DogTreatments form is simple to use. Use the listbox on top left to select a ‘dog’ then press button (Select Dog) which then lists all previous treatments for that animal. New items can be added at the end of the table. ‘treatment_type’ and ‘treatment_subtype’ are both listboxes. A selection in ‘type’ will show only related selections in ‘subtype’. On a NEW record, nothing will show in ‘subtype’ until a ‘type’ is selected.

Sample - Dogsjohnh009.odb - This is revised copy (1/17/2017) correcting error on new entries. ‘subtype’ listbox had incorrect display. Macro code below modified with corrections. Thanks to @johnh009 for find.

Here are the two macros contained within:

REM  *****  BASIC  *****

Option Explicit

Sub RefreshList
Rem This routine is called each time the 'treatment_type' field is exited OR
Rem another record is selected (called from ClearList sub).

	Dim oForm 				As Object
	Dim oSubForm 			As Object
	Dim oTable 				As Object
	Dim oColumn 			As Object
	Dim oStatement 			As Object
	Dim oResult 			As Object
	Dim sSQL		 		As String
	Dim sSelectedValue 		As String
	Dim bCursorTest 		As Boolean
	Dim iItemCount	 		As Integer
	Dim x			 		As Integer
Rem Get the main Form
	oForm = ThisComponent.getDrawPage().getForms().getByName("Form")
Rem Get the SubForm
	oSubForm = oForm.getByName("SubForm")
Rem Get the Table control
	oTable = oSubForm.getByName("Table Control  1")
Rem Get the value selected in 'type'
	oColumn = oTable.getByName("treatment_type")
	sSelectedValue = oColumn.SelectedValue
Rem Use SQL (query) to retrieve the 'subtypes' for 'type'
	oStatement = oForm.ActiveConnection.createStatement() 'Create an SQL statement object'
	sSQL = "Select ""treatment_subtype"" FROM ""treatment"" WHERE ""treatment_type"" = '" & sSelectedValue & "'"
	oStatement.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE
	oResult = oStatement.executeQuery(sSQL)
REM Check if record set returned
    bCursorTest = oResult.first
REM Exit if record set is empty
    If (bCursorTest = "False") and (NOT oSubForm.IsNew) Then
    	MsgBox "Treatment Type is Not Selected"
     	Exit Sub
     End If
Rem  Clear existing items in the listbox
	oColumn = oTable.getByName("treatment_subtype")
	iItemCount = oColumn.ItemCount
	If iItemCount > 1 Then
		for x = 1 to iItemCount
		oColumn.removeItem(0)
		Next
	EndIf
Rem Fill the 'subtype' with the retrieved values
Rem Since this fills from the top & pushes down,
Rem the first item added is actually the last item selected in the query
	oResult.afterLast
	While oResult.previous
		oColumn.insertItem(0,oResult.getString(1),0)
	wend
End Sub

Sub ClearList
Rem  This routine to be called each time the record changes

	Dim oForm 				As Object
	Dim oSubForm 			As Object
	Dim oTable 				As Object
	Dim oColumn 			As Object
	Dim iItemCount	 		As Integer
	Dim x			 		As Integer
Rem  Get the Form
	oForm = ThisComponent.getDrawPage().getForms().getByName("Form")
Rem  Get the SubForm
	oSubForm = oForm.getByName("SubForm")
Rem  Get the TableControl
	oTable = oSubForm.getByName("Table Control  1")
Rem  Get the Column affected in the TableControl
	oColumn = oTable.getByName("treatment_subtype")
Rem  Clear existing items in the listbox
	iItemCount = oColumn.ItemCount
	for x = 1 to iItemCount
		oColumn.removeItem(0)
	Next
REM Call this sub to fill listbox if existing record
	RefreshList
End Sub

I have added many comments so they appear larger than needed. Also, to keep it simple many lines which could have been combined are separated for ease of reading.

The RefreshList macro is attached to the When losing focus event of the ‘treatment_type’ column. The ClearList macro is attached to the After record change event of the subform. With that, each time a different record is selected, the ‘subtype’ is cleared then if it is an existing record the list is refreshed based upon ‘type’. Also, each time an item is selected in ‘treatment_type’ the subtype will reflect the choices.

Please note one other change in the form. While the ‘treatment_type’ column is still based upon an SQL select, the 'treatment_‘subtype’ is now based upon a Valuelist with NO items actually in the control itself. It all comes from the macro.

Now of course (if you undertake the task) the biggest hurdle in using this is to fit it into you Base file. Most of the problem will be to get the form & control names correct. Even if you don’t use it, this was certainly not a waste of time by any means. As mentioned earlier, this has come up before. Just never took the time to figure it out until now.

Edit 1/17/2017:

The above relates to working with a table control. Additionally the following sample uses regular controls and requires less code. It has two listboxes with the list of the second based upon the selection in the first.

The basis of the sample is the sample pointed to in answer by @jimk for this question.

Sample #2 - ProgressiveListboxes.odb - two bugs fixed.

Code is in .odb. Also, please pardon me for any bugs still present. Have done quite a bit of tests but you can never test enough. Will try to correct any problems.

[jimk]

Clever solution to do everything inside a table control. A question: You mentioned disagreeing with my answer, but I read through your answer and did not notice any points of disagreement. Using a table control is different, but it sounds like you had something more than that in mind. Did you find information in my answer that was inaccurate or perhaps not the best way to do something?

[Ratslinger]

First, your answer does work as I stated. I have now tried your answer (only read it before) and find it somewhat cumbersome. Too many queries, Filter table probably not needed and different code could be implemented. In actuality, using a table control is hardly any different. Still dealing with list/combo boxes. Some new code I am trying seems to contain an LO bug in one specific area. Re-creating your example with only one internal form, no filter table and less code than my example.

[johnh009]

@Ratslinger. Thanks once again for your help; the examples contained with the DB serve to make the process that much more understandable.
Rather than launch straight in attempting to update my DB, I have been exploring your solution in order to understand more clearly how it all works.
One observation you may be interested in: On selecting a treatment_type (listbox within Table Control 1) & then tabbing, the treatment_subtype listbox would be empty, and would only be populated with the . . . .

[johnh009]

. . . correct items once the partially complete record had been saved. This would not have worked with my DB as both columns are NOT NULL. Re-assigning the RefreshList macro to the ‘After updating’ (from ‘When losing focus’) event fixed the problem. I’ll keep you updated.

[Ratslinger]

@johnh009 Thank you for the response. While I attempted to cover as much territory as possible I realize there may always be another check needed. The table definition is not set to require this field. Simply edit the dt table and set those fields to Entry required = “Yes”. Then a selection MUST be made.
However, I am not clear as to what you are stating since I can’t seem to duplicate. I do see the moving of the macro to a different event will cause other problems. Please state steps.

[Ratslinger]

I also refer you to the form event which calls the “ClearList” sub. At the end of this sub “RefreshList” is called which causes your mod to be a duplication and leaving a hole in when a new type is selected.

[johnh009]

@Ratslinger OK, I don’t know what I did to (apparently) get it to work as desired (after re-assigning the RefrashList macro), but it doesn’t now! Steps: After selecting a dog, mouse click in the treatment_type column and select an item. Tab to, or mouse click in the treatment_subtype column and the drop-down list is empty. Down arrow or mouse click in the next record, therefore saving the previous record (which will not happen if both columns are set to NOT NULL), return to the previous . . .

[johnh009]

. . . record’s treatment_subtype column, and the drop-down list is correctly populated,

[Ratslinger]

I can understand your result if you re-assigned the macro. “RefreshList” must be in When losing focus event of treatment_type. Here is the processing.

Record changed or opening form(record does change): ClearList is called. This wipes out previous subtype. At the end it calls RefreshList to get related subtypes.

Selection is made in treatment_type. When losing focus, it calls RefreshList to get related subtypes. If you change the attached event you have a problem with subtypes.

[Ratslinger]

Have created the situation. Give me a few to look at it. Found cause (new record check) looking for fix.

Needs more testing. Will post when done.

Have re-tested. All seems OK. Changes previously noted were correct. Have modified answer to show corrected code & changed sample which contains corrected code.

[johnh009]

Your (changed & unamended by me) sample works as intended. Will attempt to incorporate into my DB tomorrow. Thanks for your time & effort.

[johnh009]

@Ratslinger I created a new form - practically a copy of yours - and it all works. Thanks again for your time & effort.