Ask Your Question

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

asked 2019-04-29 17:09:34 +0200

LSemmens gravatar image

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 flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2019-05-01 16:24:12 +0200

EasyTrieve gravatar image

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
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
'       oSQL.ResultSetType =                                                 '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 ...
edit flag offensive delete link more


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

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

answered 2019-04-29 19:05:49 +0200

Ratslinger gravatar image


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.

edit flag offensive delete link 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.

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

answered 2019-04-30 02:28:33 +0200

LSemmens gravatar image

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?

edit flag offensive delete link more


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

Ratslinger gravatar imageRatslinger ( 2019-04-30 02:37:47 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-04-29 17:09:34 +0200

Seen: 63 times

Last updated: May 01