Not quite the expected result

[Renel]

Let’s say that the execution of this procedure below displays the expected result, namely the record according to the sRecordID variable, so if the ID is 299, the record 299 is displayed. That’s correct.

I have in this form, a control table [named Selection] which acts as a select list, so normally all the records in the form appear as a list. It is then easy to select a name and access this recording. The table list still remains intact and complete.

However, when I run this procedure, the control table only shows the record name of the sRecordID as a result. Which is correct, but I lose access to the full list of the control table. If I request a refresh of the form, the first record in the form will show up with the list full list from the select table.

Is it possible that the sRecordID record (this one manipulated by the procedure) is displayed as expected, but with the complete list of the selection table. ? The execution of this procedure was done from a button at the sRecordID record with complete list of the control table which was displayed.

How can I solve this problem, if it is possible to solve it?

Sub SetLink(oEvt, sFormName, sButtonName)
	. . .
	' There is a lot of code in this procedure.
	' The lines below partially represent the end of the procedure.
	. . .

	If sFormName = "frm-X" Then
		 . . . ' Some code lines

	ElseIf sFormName = "frm-FilmsSeries" Then
    	oDoc = ThisDatabaseDocument.FormDocuments.getbyname("FilmsSeries")
		oObj2 = oDoc.getComponent().getDrawPage().getForms().getByName("frm-FilmsSeries")
		sSelect = "( FilmSerieID = " & sRecordID & " )"
	End If
 
    Wait 100
    oCurrentController = oDoc.getComponent().getCurrentController()
    oContainer = oCurrentController.getFrame().getContainerWindow()
 	oObj2.Filter = sSelect	'	Filter: The field of the table linked to the main form = the ID of the current record
	oObj2.Reload()			'	Reload the form to get the specified record.
	oObj2.Filter = ""
End Sub

[RobertG]

Didn’t read all, but shouldn’t the Filter be applied?

oObj2.filter = sSelect
oObj2.ApplyFilter = TRUE
oObj2.reload()

[Renel]

They say a picture is worth a thousand words…

Here is the result after the procedure and before refreshing the form. It’s correct.

If I refresh the form, Base brings me back to the first record.

But here is what I would like. That is, find this record after the execution of the procedure and with the complete selection list.

Is It possible with Base?

[RobertG]

Here a solution for internal HSQLDB. Firebird will work also, but contains better functions …

There is a list of Acteurs in left tablecontrol. Seems like a sorted table. I will call it “tbl_Acteurs”

SELECT "Acteurs", 
(SELECT COUNT("Acteurs") FROM "tbl_Acteurs" WHERE "Acteurs" <= "a"."Acteurs") AS "Row" 
FROM "tbl_Acteurs" AS "a" ORDER BY "Acteurs"

This query will show the Acteurs and a row number for every acteur.
Now you could move the cursor in the table control to this row.

SELECT "Row" FROM (
SELECT "Acteurs", 
(SELECT COUNT("Acteurs") FROM "tbl_Acteurs" WHERE "Acteurs" <= "a"."Acteurs") AS "Row" 
FROM "tbl_Acteurs" AS "a" ORDER BY "Acteurs"
) WHERE "Acteurs" = 'Ji Sung'

This will be be the row number.
By macro you could set

obj2.absolute(RowNumber)

You know how to execute a statement in a macro and get the value you want?

[Renel]

OK, RobertG, I want to make sure I don’t make a mistake, so here are some details:
.
Table: TActors
Table Field: ActorName
Table Field: ActorID (variable RecordID in the macro)
.
The SQL commands that you proposed to me would therefore read like this:

SELECT "ActorName",
(SELECT COUNT("ActorName") FROM "TActors" WHERE "ActorName" <= "a".."ActorName") AS "ActorID"
FROM "TActors" AS "a" ORDERED BY "ActorName"

.
And
.

SELECT "Actor ID" FROM (
SELECT "ActorName",
(SELECT COUNT("ActorName") FROM "TActors" WHERE "ActorName" <= "a".."ActorName") AS "ActorID"
FROM "TActors" AS "a" ORDERED BY "ActorName"
) WHERE "ActorName" = VariableName

(VariableName contains the name ‘Ji Sung’ in the macro)
.
obj2.absolute(RowNumber) (RowNumber will be the record ID)
.
Is that correct or am I making a mistake doing so?

[RobertG]

It’s correct. You could try it in a query (Tools → SQL). Have a look at the resulting number. Type this number into the field for the record number in the table control. Should show the row for the “ActorName” you have searched.

Hope there isn’t any duplicate name…

[Renel]

@RobertG,
.
I get this error with the SQL command window in LibreOffice. There is also the same error related to the SQL command when run inside the macro.
.
Unexpected token: ActorName in statement [SELECT "ActorID" FROM (SELECT "ActorName ",(SELECT COUNT("ActorName ") FROM "TActors" WHERE "ActorName " <= "a".."ActorName "]
.
My guess is that it’s the colon "a".."ActorName " which should probably match a command or statement related to ActorName that is causing the error.
.
Also, it may actually have duplicate names. They are differentiated using another field of the table corresponding to the year of birth of the actor, field named: Year. If the year of birth ever duplicates as well, I’d consider a little addition to the actor’s name.
.
The year of birth of the actors is not registered for all the actors, but only when this one is necessary in the case of duplication or certain other situations in connection with the execution of certain macros.

[RobertG]

@Renel

Unexpected token: ActorName

I see you have written "a".."ActorName" - should be "a"."ActorName" The Point is needed to distinguish between “a” (Name of the ‘table’) and “ActorName” (Name of the field in the table).

If there are duplicates: How should they be searched by only input of ‘Acteurs’? You couldn’t see the difference in ‘Selection’ and couldn’t input the year for searching. So the subquery will give more than one row but the content for the RowNumber will be the same.

This query will show the difference also depending on entry in “Year”.

SELECT "Actor ID" FROM (
SELECT "ActorName",
(SELECT COUNT("ActorName") FROM "TActors" WHERE "ActorName" <= "a"."ActorName" AND "Year" <= "a"."Year") AS "ActorID"
FROM "TActors" AS "a" ORDER BY "ActorName", "Year"
) WHERE "ActorName" = VariableName

There was also a typo for sorting. Should be ORDER BY, not ORDERED BY

[Renel]

RobertG,
.
Unfortunately this seems problematic.
.
1: Cannot be in ORDER BY clause in statement [SELECT "ActorID" FROM ( SELECT "ActorName", ( SELECT COUNT( "ActorName" ) FROM "TActors" WHERE "ActorName" <= "a".."ActorName" AND "Year " <= "a".."Year" ) AS "ActorID" FROM "TActors" AS "a" ORDER BY "ActorName", "Year" )]
.
I would like your opinion on this. The macro I am currently using works great and performs all the tasks assigned to it. The only thing that bothers me is the fact that at the end of the procedure the form is refreshed and brought back to the first record of the table according to the sort performed in alphabetical order.So the selection in the control table column is already sorted.
.
In fact, what I would like from there and after the update is done, is to return to the last displayed record as if I selected it in the selection list.
.
Inside the macro, the last record ID was saved in a variable.
.
I hope this has clarified this issue a bit.

[cpb]

@Renel,
so you have a value (ActeurID) and wish to select/view the corresponding form record.
why not search the result set?
.
I know nothing about your database but have attached an example which demonstrates the method.
I used a list box to select “ActeurID” (you use a macro).
a small macro then searches the result set and reloads the form at the relevant position.
.
I used the fields “Country”, “Capital”, “Continent” to replicate your issue.
SearchForActor.odb (30.3 KB)

[Renel]

@cpb, OK! I believe your proposal might work. I will experiment with it in my database using your procedure inside the relevant macro.
.
If your code gives the same result as the attempt I made from this search box, then you will have provided the solution to my problem. I will get back to you later on this.
.

[RobertG]

@Renel

Cannot be in ORDER BY clause

Seems internal HSQLDB couldn’t order the inner select statement. So it helps to set this as a view:

SELECT "ActorName", ( SELECT COUNT( "ActorName" ) FROM "TActors" WHERE "ActorName" <= "a".."ActorName" AND "Year " <= "a"."Year" ) AS "ActorID" FROM "TActors" AS "a" ORDER BY "ActorName", "Year"

So you could use the view to get the right row.

[Renel]

@cpb,
.
Your suggestion achieves what I wanted. I had to adapt your code slightly to make it work in the context of my database macro. It was not easy, but the result is there. Initially, I thought this could be done more easily with SQL commands that can be executed inside the macro, but your solution turns out to be more easily achieved.
.
Thanks to RobertG for taking his time to suggest solutions. I will investigate this avenue when I have a little more time.
.
Once again thank you both.