On the event on load form, I like to jump to the last record or a new one. Is there an existing function, macro that you can use easily?
Hello,
You can set a form to allow entry of new records only which then always displays a new record.
Open your form in edit mode. On the Form Design toolbar, there is an icon for Form Navigator, click that. Right mouse click your form name and select Properties. On the Data tab, set Add data only to Yes. Save the changes.
If you want to have all records available but still be positioned on a new record, you need to use a small macro:
Sub GoToNewRecord()
Dim oForm As Object
Rem Get the internal form
oForm = ThisComponent.Drawpage.Forms.getByName("YOUR_INTERNAL_FORM_NAME")
Rem Set records to be loaded
oForm.FetchSize = 5000
Rem Move to insert row record
oForm.moveToInsertRow()
End Sub
You will need to supply your internal form name (most likely ‘MainForm’). This routine is attached to the When loading event of the internal form. For more information on LO macros see the documentation → LibreOffice Base Handbook. Look in Chapter 9 - Macros.
I tried using the macro above to solve the problem I described here:
When I open the form I get this message:
BASIC runtime error.
An exception occurred
Type: com.sun.star.container.NoSuchElementException
Message: .
It takes me to the macro editor, with the following line highlighted:
oForm = ThisComponent.Drawpage.Forms.getByName(MainForm)
The form I am working on has the internal name “MainForm” (on the “general” tab of its property sheet).
Any idea what’s wrong?
The name of the form must be surrounded by quotes. The code displayed does not show this.
Yes, adding the quotes fixes it. Thank you. On one of my tables that has over 3500 records, the form takes a few seconds to load, as it finds its way to the last record I suppose.
This solution is working great for me, except that when I open the form I cannot navigate back to earlier records. To do that, I have to turn Design Mode on and back off. Otherwise, if I turn the Form Navigation toolbar on, everything is ghosted out.
I’m attiching an image of what I could capture of the Form Properties. Unfortunately, Screen Snip keeps scrolling up whenever I try to capture the rest, so:
Allow deletions: No
Add data only: No
Navigation bar: Yes
Cycle: All Records
I do not know where your problem may reside without a sample as the code works for me. Attaching an HSQLDB embedded sample demoing the macro. It also includes alternate code & link to origination.
.
NewRecord.odb (13.2 KB)
Interesting. When I open that with Macros enabled and open the form, line 11 of the module throws:
BASIC runtime error.
An exception occurred
Type: com.sun.star.sdbc.SQLException
Message: Function sequence error…
This is LO 7.0.6.2(x64) on Windows 10.
Oh, and my situation that wasn’t allowing me to navigate back was a MySQL backend (5.7.19).
No reason on my end you get the error. Have also used this with PostgreSQL and MySQL - v5.7.x in past and current test using v8.0.27
Will test HSQL version on Win 10 but no MySQL on Win 10 for me.
Edit:
Win10 works with sample posted without issue for me.
.
Version: 7.2.5.2 (x64) / LibreOffice Community
Build ID: 499f9727c189e6ef3471021d6132d4c694f357e5
CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: default; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded
Simply sort the record set descending with latest on top. You can define the default sort order in the form properties or you can enforce a sort order in the underlying query.
Thanks! This form is used for entry, so I don’t think there’s an underlying query. And when I set the default sort order of the form, it overrode the fact that the macro sent it to a new record. I’ll try adding a wait to the macro so that it goes to a new record after the sort order is implemented.
SELECT * FROM "Table" ORDER BY "ID" DESC
returns the entire table for editing with latest on top. Macros are an insane waste of time.
Just add another input form to the existing one for new record entry.
1 Like
BiblioForm.odt (11.6 KB)
Simple 3 minutes demo based on “Bibliography”.“biblio”. A form with enforced sort order and another one for new records.