# 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. 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. edit retag close merge delete ## 3 Answers Sort by » oldest newest most voted 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 ...
more

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

( 2019-05-02 04:02:51 +0200 )edit

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.

more

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

( 2019-04-30 02:41:31 +0200 )edit

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?

more

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