Ask Your Question
0

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

asked 2018-01-15 20:24:25 +0200

johnh009 gravatar image

updated 2018-01-15 21:27:14 +0200

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

image description

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:

[(https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=41465)]

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2018-01-16 06:54:55 +0200

Ratslinger gravatar image

updated 2018-01-18 04:30:56 +0200

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

Hello,

First let me state the answer by @Jim K 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 ...
(more)
edit flag offensive delete link more

Comments

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?

Jim K gravatar imageJim K ( 2018-01-16 21:24:36 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2018-01-17 17:48:44 +0200 )edit

@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 gravatar imagejohnh009 ( 2018-01-17 19:58:38 +0200 )edit

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

johnh009 gravatar imagejohnh009 ( 2018-01-17 20:03:48 +0200 )edit

@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 gravatar imageRatslinger ( 2018-01-17 20:27:32 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2018-01-17 20:33:34 +0200 )edit

@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 gravatar imagejohnh009 ( 2018-01-17 21:48:46 +0200 )edit

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

johnh009 gravatar imagejohnh009 ( 2018-01-17 21:53:37 +0200 )edit

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 gravatar imageRatslinger ( 2018-01-17 22:04:52 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2018-01-17 22:09:19 +0200 )edit
1

answered 2018-01-15 21:38:34 +0200

Jim K gravatar image

updated 2018-01-15 21:43:08 +0200

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 https://stackoverflow.com/questions/4....

edit flag offensive delete link more

Comments

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

johnh009 gravatar imagejohnh009 ( 2018-01-15 22:07:03 +0200 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2018-01-15 20:24:25 +0200

Seen: 1,198 times

Last updated: Jan 18 '18