Libre Office BASE - Open form at specified record without a filter

[hguelbert]

Hello everyone,

Thank you all in advance for your comments. All input is welcome and appreciated.

I have seen this topic raised before but have been unable to find a definitive answer.

Is there a way to make a form open and display a specified record without applying filter?

I want to open the form at the specified record but still allow the user to scroll previous & next through the records.

If I apply a filter, this prevents scrolling.

Many thanks

Hans

[CRDF]

As nobody experient answers, I’ll guess…
I THINK there are not native methods along the lines of VBA’s .Find.
You would need workaround database dependent like, say in Firebird:

1. create a View
   SELECT ROW_NUMBER() OVER (ORDER BY target_column) […]
   to retrieve respective Form’s row number;
2. source your Form from Query same ORDER BY target_column so the rows order are the same;
3. via code (“macro”) perform a SELECT “row number” from the View
   WHERE < your criteria >;
4. store the first “row number” from the SELECT (query) into a variable Long:
   Dim rn As Long;
5. YourForm.absolute(rn).

I THINK…

[RobertG]

How do you want to find the

@hguelbert

specified record

If this record is unique by the specified values it shouldn’t be a problem to set the filter of the form, which could be removed in the navigation bar of the form.
I you are using internal Firebird you could try it the other way with a WINDOW-function like @CRDF posted. Then you start the form and move to the absolute value of the row.

[hguelbert]

@CRDF

YourForm.absolute(rn).

OK, this was amazingly helpful. Thank you…

[CRDF]

This is a working example of positioning cursor at first match of search by segment on text “historico”.
Insert the search criterion on TextBox and on leaving it [When losing focus] cursor positions at matching row. Or at row 1 if no match (SELECT returns NULL).
Of course the View (vRN) and the query source to the TableControl must have same ORDER BY for rows to be the same.

Option Explicit
Sub SetRow(Evt As Object)
	REM DECLARE VARIABLES:
	Dim F As Object, TBCriterio As Object, Con As Object, Stmt As Object, Rst As Object
	Dim sSQL As String
	Dim rn As Long ' the row number
	REM SET REFERENCES TO OBJECTS:	
	TBCriterio = Evt.Source.Model ' text box for the search segment
	F = TBCriterio.Parent ' the form
	Con = F.ActiveConnection()
	Stmt = Con.createStatement()
	REM COMPOSE QUERY AND RUN SEARCH FOR ROW NUMBER:
	sSQL = "SELECT ""r"" FROM ""vRN"" WHERE ""historico"" LIKE '%" & TBCriterio.Text & "%'"
	Rst = Stmt.executeQuery(sSQL)
	If Rst.next Then
		rn = Rst.getLong(1)
		Else
			rn = 1
	End If
	F.absolute(rn)
End Sub

 

[hguelbert]

Thank you that is really helpful.