How to have two features associated with a drop-down list?

English
#1

I have a drop-down list in a form that is used to select the first record associated with a letter. For example, if I select the letter A, all records starting with A will be displayed as a list in a control table provided for this purpose. It is therefore also a filter for the selected letter.
.
By default, this control table contains all records sorted alphabetically when the form is opened.
.
The drop-down list is dynamic. That is to say, it only contains the letters, symbols or numbers of the titles or names existing in the table linked to the main form. For example, if there is no title or name starting with Z, the letter Z does not exist in the drop-down list either.
.
The content type of the list is an SQL query ‘ql_TargetFS’ which looks like this:

SELECT DISTINCT "Target", "row_num" FROM ( SELECT '' AS "Target", 0 AS "row_num" FROM "TFilmsSeries" LIMIT 1 UNION ALL SELECT UPPER(LEFT("TitreAnglais1", 1)) AS "Target", MIN(r) AS "row_num" FROM ( S ELECT c1."English Title1", 1 + COUNT(c2."FilmSerieID") AS r FROM ( SELECT "FilmSerieID", "English Title1", UPPER("English Title1") AS u FROM "TFilmsSeries" ) c1 LEFT JOIN ( SELECT "FilmSerieID", "English Title1", UPPER("English Title1") AS u FROM "TFilmsS eries" ) c2 ON c1.u > c2.u OR (c1.u = c2.u AND c1."FilmSerieID" < c2."FilmSerieID")
GROUP BY c1."TitreAnglais1", c1."FilmSerieID"
)
GROUP BY UPPER(LEFT("TitreAnglais1", 1))
)
ORDER BY "Target" --
//
// Query for the [lst-Lettres] drop-down list in the [frm-FilmsSeries] main form.
// It generates a blank as the first element of the list and a list of the initial capital letters
// of the actor names in the [TFilmsSeries] table and associates with each letter the minimum line number
// among the actors whose names begin with that letter.
// The results are sorted alphabetically by initial capital letters.
// Dummy columns are used to handle sorting issues in embedded HSQLDB.

The dropdown list is also associated with the ‘Modified’ event and thus a Basic macro ‘MoveToTarget’. Here is the macro:

Sub MoveToTarget(oEvent As Object)
Dim oModel As Object, oForm As Object
Dim sFilter As String

oModel = oEvent.Source.Model
oForm = oModel.Parent

' Get the selected value from the drop-down list
sFilter = oEvent.Source.SelectedItem

' Apply the filter
If sFilter = "" Then
' If no letter is selected, display all records
oForm.Filter = ""
Else
' Apply the filter to display only records starting with the selected letter
oForm.Filter = "UPPER(""TitleAnglais1"") LIKE '" & sFilter & "%'"
End If

' Refresh the form to apply the filter
oForm.Reload()
End Sub

Actually, I would like the behavior when faced with the selection made in the list can react in two ways:
.
The first is as it is currently either: if I select a letter, the records are filtered and only the records corresponding to this letter are displayed. It works.
.
The second is that I would like all records to always be visible regardless of the selected letter and that the control table displays the first record associated with this letter.
.
I tried some things with control keys like, the Command key and shift, but the events linked to a drop-down list do not detect this kind of ‘Modifiers’ as for buttons for example.
.
Do some of the users have suggestions to obtain this double functionality for a drop-down list?

#2

Have a look.
qa108896.odb (73.3 KB)

#3

I found the solution regarding the request made in this topic that I posted.
.
Thank you Villeroy for the example provided in your DB. Although it was not directly useful for this solution. I keep it in my references.
.
Here is an example of how the drop-down list of my DB works. Unlike you Villeroy, I wanted to avoid using buttons to confirm, refresh or restore the state of the form.

ListBoxFilter.odb (99.2 KB)

So, the use of a macro was necessary for this replacement. For the macro, I used the ‘Mouse button released’ event in order to have access to the ‘Modifiers’ property which allows to detect the presence of a control key.
.
By default, this control table contains all the records sorted in alphabetical order when the form is opened. This drop-down list is therefore used to select the first record of the form associated with a letter. For example, if I select the letter A, all records starting only with A will be displayed as a list in a control table.

This line of code in the macro is used to filter the table linked to the form:

oForm.Filter = "UPPER(""TitleAnglais1"") LIKE '" & sFilter & "%'"
oForm.Reload()

In the drop-down list, there is an empty line at the beginning of the list that is used to undo the filter or restore the complete list of all records in the form.
.
This line of code is used to restore the original state of the list:

oForm.Filter = ""
oForm.Reload()

The new functionality linked to the drop-down list allows you to move the cursor from the control table to the first record corresponding to the selected letter without filtering. Therefore, all records always remain visible, only the cursor moves. To do this, I needed to use a key like the Control or Command key to detect its presence when selecting a letter. So, selecting a letter with the command key pressed simply moves the cursor from the control table to the list of records.
.
These four lines of code are needed to accomplish this task:

oForm.Filter = "" ' Reset the filter
oForm.Reload() ' Refresh the form to apply the filter
iTargetRow = oModel.currentvalue ' target row
oForm.absolute(iTargetRow) ' Move the cursor from the form to the row specified by iTargetRow

The drop-down list is also dynamic. That is, it only contains the letters, symbols or numbers of the titles or names existing in the table linked to the main form. For example, if in my database, there is no title or name starting with the letter Y. This letter will not exist in the list. If we add a new record and write in the ‘English Title’ field the title ‘Yardbird Suite’. When the form is refreshed, the letter Y will appear in the list.
.
The following SQL query ‘ql_TargetFS’ that generates the list content of the drop-down list is responsible for this dynamic update.

SELECT DISTINCT "Target", "row_num" FROM ( SELECT '' AS "Target", 0 AS "row_num" FROM "TFilmsSeries" LIMIT 1 UNION ALL SELECT UPPER(LEFT("EnglishTitle1", 1)) AS "Target", MIN(r) AS "row_num" FROM ( SELECT c1."EnglishTitle1", 1 + COUNT(c2."FilmSerieID ") AS r FROM ( SELECT "FilmSerieID", "English Title1", UPPER("English Title1") AS u FROM "TFilmsSeries" ) c1 LEFT JOIN ( SELECT "FilmSerieID", "English Title1", UPPER("English Title1") AS u FROM "TFilmsSeries" ) c2 ON c1.u > c2.u OR (c1.u = c2.u AND c1."FilmSerieID" < c2."FilmSerieID")
GROUP BY c1."TitreAnglais1", c1."FilmSerieID"
)
GROUP BY UPPER(LEFT("TitreAnglais1", 1))
)
ORDER BY "Target" --
//
// Query for the [lst-Letters] drop-down list in the main form [frm-FilmsSeries].
// This query generates a list of the first letters in uppercase of the English titles of the movies/series,
// with an associated rank number, and adds a blank line at the beginning. It then sorts the results by the first letter.
// Dummy columns are used to handle sorting issues in embedded HSQLDB.

Now it works as I want.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.