I am new to Libre Office and am entering data into the database which will sometimes require the fields to be duplicated. Is there a shortcut key I can use for a field dup?
+1 Good Q. I’ve given you enough points so that now you can attach images to your questions.
Hello,
Aside from the standard Copy/Paste method, you would need to write macros specific to your needs. Especially for someone “new” this is quite difficult.
For a sampling of this, you can download the sample from my answer in this post → base auto populate new record. Personally don’t think this is good practice.
Edit:
Just remembered another macro; see link in this post → Base: Anyone have a Ctrl-tick macro to quickly copy from field above?
If I’m understanding the question right, there is a way to quickly duplicate a table row record when in table view (or when a table control is used within a form)…
As long as you have an autonumber primary key field set up, things should work as you would expect. I was excited to see this feature, mostly by accident a while back.
Oh, I see… field duplicated, not record. My answer is probably for a different situation. When I’m designing forms, I like to drag an existing control and its label, and after dragging for a second, add CTRL to the drag, and a duplicate field is made. If you hold down SHIFT as well, the field will line up vertically or horizontally with the position of the original field, which is cool. Still maybe not the answer you were looking for, but maybe helpful to other beginners.
I thought there would be a simple answer to this question. i.e. MS Access uses the CTRL key with the key beside the central enter key " ’
There are a number of differences between Base and Access - this is one.
You’re not the first one to ask this question.
Here is the code I use to do this.
If you are new to macros, you can study this and install it as a useful example of what can be done and how. It will obviously take some work for you to learn to set it up. But after you do it’s relatively easy to give a given table control this functionality.
I hope someday to merge this feature into base for everyone to use, but for now this was the best solution I could come up with.
There are two parts. (Apologies for the wide code, but I don’t like narrow editors. Cut and paste the code into a wide editor and look at it there.)
Also please note that because of a bug in the Ask.LibreOffice.org web site, single quotes (ticks) mess up the colors of the screen display for code. For simple code this has a workaround to put a closing quote at the end of comment lines. But for this code which specifically deals with single quotes, I just found it too confusing to use that workaround. So again cut and paste into your basic editor to inspect it.
Credits: I got part of this code from someone else who donated it, I think at the Open Office forum, and then have modified it as I saw fit.
I use this mostly on LO v 5.4. (Plan to upgrade to 6 someday when it’s more stable.)
Part one is in any given database. It determines which fields in a given table control should not be copied. It looks something like this below. Note that this is for my accounting journal and for that I don’t want the ID, Debit, Credit, or Balance fields duplicated, especially when + is used to copy a whole record.
' === If Alt-' or Alt-+ (numeric) are hit, then quickly copy down info to new record ==============
Private Sub TableKeyPressedJournal( oEvent as Object) 'CloneFieldOrRecord
CloneFields.CloneFields(oEvent, Array("Journal ID", "Debit", "Credit", "Balance") ) 'List of fields to NOT record-copy
End Sub
Installed into your table contol here:
Part two is common to all my databases.
Option Explicit 'BASIC ###### BASE TABLE CONTROL FIELD / RECORD COPY UTILITY ######
'Clone last field, or current record:
'
' Select a field in any new or existing record and type the Alt' to copy the corresponding field to from its immediate predecessor; or
' Select any existing record and type Alt+ (num pad) to clone the whole record to a new record.
'
'You can edit new data or abort the changes by pressing ESC before the record is saved.
'
'Issues:
' * An attempt to copy a Primary Key will cause an error when a new or modified record is saved.
' * A time-stamp column, or a time column linked to a time-stamp field in the database, may initially display incorrect times.
' The correct value, however, will be saved automatically and shown as soon as the new or modified record is saved.
'Called with something like this:
' === If Alt-' or Alt-+ (numeric) are hit, then quickly copy down info to new record
'Private Sub TableKeyReleased(oEvent as Object) 'CloneFieldOrRecord
' CloneFields.CloneFields(oEvent, Array("Journal ID", "Debit", "Credit", "Balance") ) 'List of fields to NOT record-copy
'End Sub
' ### THIS IS CALLED BY EVENTS ####################################################################
' === Clone fields(s) if Alt-' or Alt-+ (numeric) is hit ===================================
Private Sub CloneFields(oEvent as Object, sIgnoreFields()) 'CloneFieldOrRecord
Dim KM1 As Integer : KM1 = com.sun.star.awt.KeyModifier.MOD1 'Ctrl KeyModifier (doesn't work in list boxes)
Dim KM2 As Integer : KM2 = com.sun.star.awt.KeyModifier.MOD2 'Alt KeyModifier
If oEvent.Modifiers=KM1 AND oEvent.KeyChar="'" Then CloneFieldAbove_ (oEvent ) :Exit Sub 'ctrl-tick fails in list boxes
If oEvent.Modifiers=KM2 AND oEvent.KeyChar="'" Then CloneFieldAbove_ (oEvent ) :Exit Sub
If oEvent.Modifiers=KM2 AND oEvent.KeyCode=com.sun.star.awt.Key.ADD Then CloneCurrentRecord_(oEvent,sIgnoreFields() ) :Exit Sub 'Plus sign on numeric keypad
End Sub
' ### HELPER SUBS #################################################################################
' === Copy the field above from the last record into the new record =======================
Private Sub CloneFieldAbove_(oEvent as Object)
Dim oTable As Object :oTable = oEvent.Source
' Dim oForm As Object :oForm = oTable.Model.Parent
Dim oRS As Object :oRS = oTable.Model.getRowSet()
Dim oRS_clone As Object :oRS_clone = oTable.Model.Parent.createResultSet() 'Clone ResultSet of the Row set
'Note: a RowSet can be in two modes: prior to running, or after running which is called the ResultSet
If oRS.Row=1 Then Exit Sub 'or: ..Then Print "No previous row to copy from" : Exit Sub
If oRS.IsNew _
Then :oRS_clone.last()
Else :oRS_clone.previous()
END If
'Because the column order is different between RS and clone use name rather than index
' Dim FldName$: FldName$ = oTable.getByIndex(oTable.getCurrentColumnPosition()).Model.BoundField.Name
Dim FldName$: FldName$ = oTable.getByIndex(oTable.getCurrentColumnPosition()).Model.DataField
oRS.Columns.getByName(FldName$).updateString( oRS_clone.Columns.getByName(FldName$).getString() )
'Now move right one
'mri oEvent.Source
If (oTable.CurrentColumnPosition < oTable.Count-1) Then
oTable.CurrentColumnPosition = oTable.CurrentColumnPosition+1
End If
'' SkipToCol(oEvent, "Account ID")
'mri oRS.Columns.getByName(FldName$)
'set dirty flag?
End Sub
' === Copy the entire current record to a new record, less any ingore fields ==============
Private Sub CloneCurrentRecord_(oEvent as Object, sIgnoreFields())
Dim oSource As Object :oSource = oEvent.Source 'the screen cell (or input window)??
Dim oTableModel As Object :oTableModel = oSource.Model 'the data model supporting the cell ??
Dim oForm As Object :oForm = oTableModel.Parent 'the underlying form
Dim oRS As Object :oRS = oTableModel.getRowSet() 'Underlying row set
Dim oRS_clone As Object :oRS_clone = oForm.createResultSet() : oRS_clone.Last() 'Clone allows record motion independent of record updating
'Get names and values from RowSet
If oForm.IsNew Then oRS.Last() 'If new record, then get the last record. Otherwise get the current record.
Dim Name$() 'field's name
Dim Value$() 'field's value
Dim isNullable() As Boolean 'True if field allows nulls (we will convert an empty string to a null)
Dim i As Integer 'count of columns found
For i = 0 To oTableModel.Count-1
ReDIM Preserve Name$(i) : Name$(i) = oTableModel.getByIndex(i).DataField '.BoundField.Name
ReDIM Preserve Value$(i) : Value$(i) = oRS.Columns.getByName(Name$(i)).getString()
ReDIM Preserve isNullable(i) :isNullable(i) = oRS.Columns.getByName(Name$(i)).IsNullable()
Next i
'Write values into new record
oRS.moveToInsertRow()
For i = 1 To oTableModel.Count-1
If Value$(i)="" And isNullable(i) _
Then: oRS.Columns.getByName(Name$(i)).updateNull()
Else: If not(InArray_(sIgnoreFields, Name$(i))) Then oRS.Columns.getByName(Name$(i)).updateString(Value$(i))
End If
Next i
'Exit Sub
'diagnostics
' Dim S As String
' For i = 0 To oTableModel.Count-1
' S = S & i & " " & Name$(i) & " - '" & Value$(i) & "'" & iif(isNullable(i), " IsNullable","") & chr(10)
' Next i
' Msgbox S
End Sub
' --- Sub to return true if string is in sIgnoreFields array --------------------------------------
Private Sub InArray_(sIgnoreFields(), sName As String) As Boolean
Dim i As Integer 'index to search IgnoreFields array
For i = LBound(sIgnoreFields()) To UBound(sIgnoreFields())
If sIgnoreFields(i) = sName Then InArray_ = True : Exit Sub
Next i
InArray_ = False
End Sub
'sub testInArray()
' Dim sIgnoreFields() As String : sIgnoreFields() = Array("Journal ID", "Debit", "Credit")
' if InArray_(sIgnoreFields,"Debit") Then msgbox "x"
'End sub