Is it possible to position the cursor on a specific record within a table control?

[Renel]

In the root form, I have a control table that displays content from the form’s source table. The following macro works perfectly; it does what it’s supposed to do. Obviously, reloading the table returns the cursor to the first row of the grid.
.
Is there a way to reposition the cursor on the row corresponding to nStagingID? I’ve tried various methods like .absolute() and bookmark, but the only method that seemed to work was moving the cursor row by row, which is very tedious when there are many records. Is it possible to position the cursor directly on the nStagingID record?
.

Sub Resolve_Duplicate( oEvent As Object )
    Dim oForm As Object : oForm = oEvent.Source.Model.Parent
    Dim oConn As Object : oConn = oForm.ActiveConnection
    Dim oStmt As Object : oStmt = oConn.createStatement()

    ' Read the columns of the selected line
    Dim nStagingID As Long : nStagingID = oForm.Columns.getByName( "StagingID" ).Value
    Dim nManualPersonID As Long : nManualPersonID = oForm.Columns.getByName( "ManualPersonID" ).Value
    Dim sPersonName As String : sPersonName = oForm.Columns.getByName( "PersonName" ).Value
	Dim sPersonURL As String : sPersonURL = oForm.Columns.getByName( "PersonURL" ).Value
	
    If IsNull( nManualPersonID ) Then
        ShowMsgBox "No duplicates to resolve for this line.", BtnOK, IconWarning, "Resolve Duplicate"
        Exit Sub
    End If

    ' Choice message
    Dim sMsg As String
    sMsg = "« " &  sPersonName & " » already exists in the database with ID " & _
				nManualPersonID & ". Do you want to associate this line with the existing person " & _
              "or import this person as a new one?"

    Dim vDGLResult As Variant
    vDGLResult = ShowMsgBox( sMsg, BtnResolveDuplicate, IconQuestion, "Resolve Duplicate" )

	Dim iUserChoice As Integer
	iUserChoice = vDGLResult(0)
	
    Select Case iUserChoice
		Case 6
		    ' Associate with the existing person
		    Dim oRS As Object
		    Dim sSelect As String
		
		    sSelect = _
		        "SELECT ""ProfessionID"", ""GenderID"", ""AliasTypeID"", ""CountryID"", " & _
		               """AliasName"", ""BirthDate"", ""BirthYear"" " & _
		        "FROM ""PT_Person"" " & _
		        "WHERE ""PersonID"" = " & nManualPersonID
		
		    oRS = oStmt.executeQuery( sSelect )
		
		    If oRS.next() Then
		        Dim sExistingSQL As String
		        sExistingSQL = _
		            "UPDATE ""STG_CastingImport"" SET " & _
		            """PersonID"" = " & nManualPersonID & ", " & _
		            """ProfessionID"" = " & FormatSQLValue( oRS.getLong(1) ) & ", " & _
		            """GenderID"" = " & FormatSQLValue( oRS.getLong(2) ) & ", " & _
		            """AliasTypeID"" = " & FormatSQLValue( oRS.getLong(3) ) & ", " & _
		            """CountryID"" = " & FormatSQLValue( oRS.getLong(4) ) & ", " & _
		            """AliasName"" = " & FormatSQLString( oRS.getString(5) ) & ", " & _
		            """BirthDate"" = " & FormatSQLDate( oRS.getDate(6) ) & ", " & _
		            """BirthYear"" = " & FormatSQLValue( oRS.getInt(7) ) & ", " & _
		            """MatchStatusID"" = 2, " & _
		            """UpdateFilter"" = 1, " & _
		            """ImportFilter"" = 0, " & _
		            """ManualPersonID"" = NULL " & _
		            "WHERE ""StagingID"" = " & nStagingID
		
		        oStmt.executeUpdate( sExistingSQL )
		    End If
		
		    ' -----------------------------------------------------------
		    ' Update the URL in JT_PersonWebsite
		    ' -----------------------------------------------------------
		    Dim sUpdateURL As String
		    sUpdateURL = _
		        "UPDATE ""JT_PersonWebsite"" SET ""URL"" = " & FormatSQLString( sPersonURL ) & _
		        " WHERE ""PersonID"" = " & nManualPersonID
		
		    oStmt.executeUpdate( sUpdateURL )
		 
        Case 7
		    ' Import like a new person
		    Dim sNewSQL As String
		    sNewSQL = _
		        "UPDATE ""STG_CastingImport"" SET " & _
		        """MatchStatusID"" = 1, " & _
		        """UpdateFilter"" = 0, " & _
		        """ImportFilter"" = 1 " & _
		        "WHERE ""StagingID"" = " & nStagingID
            oStmt.executeUpdate( sNewSQL )

        Case 2
        	' Cancel
            Exit Sub
    End Select

    oForm.reload()

' If it possible, it is the place to do it.
End Sub

[RobertG]

First step: Before reloading the form you should save the row number. Then you could position the form to this row number after you have reloaded the form. It is the same behavior with table controls, which will show this row numbers of the form also:

SUB FormRowPosition(oEvent AS OBJECT)
	DIM oForm AS OBJECT
	DIM loRow AS LONG
	oForm = oEvent.Source
	loRow = oForm.getRow
	oForm.Reload
	IF loRow = 0 THEN
		oForm.MoveToInsertRow
	ELSE
		oForm.Absolute(loRow)
	END IF
END SUB

[Renel]

It was so simple after all. Thank you RobertG for your help. I really appreciate it.

[Wanderer]

Let’s use a search engine a bit for “LibreOffice base goto record”

How can I convince Base to go to a specific record on opening a form?

There does not appear to be a simple answer to this. All I want to do is Open a Form and go to a specific record, rather than Opening the form and then having to scroll through the records to the specific one. Is there an EASY way using macros. I was able to do it back in M$ days. Background My Philately Database is normalised and consists of a Country Table linked to Stamps linked to MyCollection. At this stage, all I want to do is to return to the last selected Country whenever I open a form…

Can I move specific record in a form in Libreoffice Base via Macro coding?

Hi, nice to meet you, everyone. I have a question about moving records in Libreoffice Base Form. I understand moving the first or last record. eg. oForm.last() Then How can I move to a specific record by specific field (eg. ID number) I assume It will be oForm.Columns.getbyname(“ID”).set = ID value What I want. oForm.move or oForm.Columns.getbyname(“ID”).setvalue(D value What I want) then succinctly moving to that place What I Want. How can I moving to the place what I want to move vi…

formModel.absolute(i) goes to wrong record

I have a form with a subform set up for navigation. Essentially, the subform contains a listbox (tied to a cache table field) of the names of all records in the table of the main form, along with a “Go” button, which launches a macro. The macro grabs the integer value (i) of the selection in the dropdown and then msgbox "Going to: " & CStr(i) oMainForm.absolute(i) Now, if I select in the listbox the name of the very last record in the listbox, the msgbox correctly displays the id number of …

[Villeroy]

Duplicate_Error.odb (26.8 KB)

Persons with uniquely indexed forename, surname and birthdates.

[Renel]

Okay, and thank you! My search was probably poorly targeted.

[Renel]

I appreciate it when you submit .odb files. There’s always something to learn.

[Villeroy]

@Renel

Okay, and thank you! My search was probably poorly targeted.

Well, without any sample document it is always hard to guess what is happening on your side.
My demo demonstrates how to catch and handle SQL errors (violation of SQL UNIQUE constraint). Instead of filtering the form in order to show the existing record, you may jump to it just as well.
Because the duplicate record can’t be stored, you have to read the form controls’ CurrentValue and possibly convert a CurrentValue to something comparable (date struct to ISO string in this case)

[Villeroy]

If you do not use any constraints to prevent duplicates in your table, you may have duplicates stored.
This variant of my sample comes with no SQL constraint and a form that shows an existing duplicate in the red detail form after you stored a new record in the yellow form (default button labeled “OK” stores the new record).
You may decide if you want to delete or edit the new duplicate, delete/edit the old one or keep duplicates.
The right side shows duplicate occurrences in the red grid (including the count of duplicates) and the concrete records in the white grid below where you may delete/edit the records.

Contrary to the “jump to approach”, you keep track of all the duplicates even if there are more than 2 of them.

Duplicate_Detect.odb (25.0 KB) [free of macros]