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

[LSemmens]

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. Eventually I’d set it up to return to the last selected record in MyCollection. Psuedo code would be

On Exit

Save recno() for MyCollection

Save recno() for Stamps

Save recno() for Country

When next I open the Form I want to

Restore recno() for Country

Restore recno() for Stamps

Restore recno() for MyCollection

Goto recno() for Country

Goto recno() for Stamps

Goto recno() for My Collection

What is the command for “Find Record” in Macros? Is there a list of commands? The documentation written by Andrew Pitonyak appears to be useful, but requires a lot of time to find relevant data and then interpret it.

[EasyTrieve]

I don’t have time to spoon feed this to you, but here is my working code that does this a couple of ways for one table I use. It might give you some ideas. I did have to work around the fact that LO doesn’t have the function that Access has to do this. NOTE that this is data base specific. I use MariaDB (a sister to MySQL). This all may or not help you, but if you dig in I think you’ll find how to do it.

First, I have this sub I use to move to the last record:

Private Sub DrawPage_(oForm As Object)
'mri oForm : stop
	Dim oDoc	As Object : oDoc	= ThisComponent
	oDoc.lockControllers
		oForm.moveToInsertRow
		oForm.Last
	oDoc.unlockControllers
End Sub

Also I have a sub to move the record pointer to a specific record:

' - primitive used above (2 places)  Version: MariaDB / MySQL
Private Sub FindRecord_(oEvent As Object, Text$ As String)
	Dim oForm		As Object :oForm		= oEvent.Source.Model.Parent	'Our form - the form that holds this combo box (and has connects to the database!)

	If (Not IsNull(Text$)) Then

		Dim oConnection	As Object :oConnection	= oForm.activeConnection()					'Connect to our form's database

'		Dim	sLstSrc		As String :SLstSrc	= oForm.getByName(oEvent.Source.Model.Name).ListSource								'HSQLDB 1.8
'		Dim iOrderBy	As Integer:iOrderBy = InStr(sLstSrc, " ORDER BY")-1														'HSQLDB 1.8
'		Dim sSelect		As String :sSelect	=  Left(sLstSrc, iOrderBy)															'HSQLDB 1.8
'		Dim sOrderBy	As String :sOrderBy = Right(sLstSrc, Len(sLstSrc)-iOrderBy)												'HSQLDB 1.8															'HSQLDB
		
		'Get SQL that created the combo box, and make it ready to nest into other SQL statements
'		mri oEvent.Source.Model:stop
		Dim sFROM		As String :sFROM	= " FROM (" & oForm.getByName(oEvent.Source.Model.Name).ListSource & ") AS Foo"		'MySQL
'		Dim sFROM		As String :sFROM	= " FROM (" & sSelect & ") AS Foo "													'HSQLDB 1.8
		
		'1) Get selected ID for combo box.  Note, the recordset in the combo box is like this:
		'	1st field:	must be named "Choice"			(for the WHERE below), and
		'	2nd field:  Must be the record's unique ID	(for the getInt(2) below)
		
		Dim sSQL 		As String :sSQL		= "SELECT *" & sFROM & " WHERE  `Choice`  = ?"										'For MySQL
'		Dim sSQL 		As String :sSQL		= "SELECT *" & sFROM & " WHERE ""Choice"" = ?" 	& sOrderBy							'For HSQLDB 1.8
'msgbox sSQL
'msgbox Text$
		Dim oSQL		As Object :oSQL		= oConnection.prepareStatement(sSQL)			'Create SQL prepared statement; the thing that will carry out the SQL-command
'msgbox(sSQL):stop
'		oSQL.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE 												'For HSQLDB 1.8 (not for MySQL)

		Dim oResults	As Object :oSQL.setString(1,Text$): oResults = oSQL.executeQuery()	'Execute SQL statement with parameter & get results

'		On Error Goto ErrorHandler
'			oResults.First()		'If error here then can't find the lookup string (empty dataset?), so something is wrong.  Mostly ignore it.
'		On Error Goto 0				'Deactivate error handling
'mri oResults:stop
		'Skip if not found (because string is not yet a valid value from the list)
		If oResults.First() Then
			Dim iSelectedID	As Integer: iSelectedID = oResults.getInt(2)					'Extract ID for the selected item (2 = column # 2)

			'2) Get big list of IDs of all records.  Their order needs to be the same as for the underlying table!!!
			sSQL = oForm.ActiveCommand & iif(oForm.Order="",""," ORDER BY " & oForm.Order)
			oResults = oConnection.createStatement().executeQuery(sSQL)

			'The grid to locate the record in must be named "Grid"
'			Lookdown.SetRowNumberFromID_sub(oForm, oResults, iSelectedID)								'Get a row# from the selected ID
			Lookdown.SetRowNumberFromID_sub(oForm.getByName("Grid").getRowSet, oResults, iSelectedID)	'Get a row# from the selected ID
		End If
	End If
'	Exit Sub

'ErrorHandler:
'	MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")"		' ... individual code for error handling
'	On Error Goto 0
'	Exit Sub
End Sub

[LSemmens]

Thank you, will spend some time to inwardly digest. I’ll look at this tonight.

[Ratslinger]

Hello,

First, there is no “Find Record” in Base. And yes, macros for LO require much time to learn.

Don’t have any easy method to accomplish what you want. This can be done through macro code but is specific to the form(s) you have created - mainly because of internal form and control names. It would seem to require keeping record information probably in a separate table/table record. Then, with another macro, when the form is next opened, this data is read and the appropriate data returned.

Another related option, without macros, may be to use table filtering. See this post for some relative information → Filter/Search with Forms (leveraging SubForms).

This method mainly uses list boxes to find records. There are a number of samples on the site for this. try a search of Filter table.

[Ratslinger]

Not certain about actually ‘duplicating’ a record. May only need to save current keys for each table & use to filter when opening the form.

[LSemmens]

I am aware of form specific coding, and had already considered the external table as a link reference. A form Filter might be the way to go but is not what I was hoping for. I’d have considered record navigation to be fairly basic. How would one duplicate a record without needing to re-enter all data?

[Ratslinger]

Please only use answer to respond to original question. Use add a comment for remarks or edit original question if providing additional information.