Base Copy Field or Clone Record - Example
Posted: Thu May 26, 2016 5:18 pm
From time to time there have been requests to match the MS Access facility whereby pressing a key combination automatically copies to a new record the data recorded for that particular field in the previous record.
A macro is clearly required but, due to the idiosyncrasies of the OpenOffice API, portability encourages coding that eschews references to a named or any object that may be specific to a particular application.
For several years I have used Base for a book-keeping application where often new transactions differ in only minor respects from previous records. A macro has permitted data from any selected record to be cloned to the new record entry buffer where changes may be made before a final save to the database.
In the attached demo that macro has been extended to permit either cloning a whole record or copying a single field from its previous record as found in MS Access. The macro permits either pressing the Alt and ' key combination to copy to any selected field (new or existing) the data from the corresponding field in its immediate predecessor, or pressing the Alt and + (num pad) combination to clone any selected record to the new record row.
The macro is offered as is, without proper testing or serious elimination of the inevitable bugs. I would welcome criticisms or comments, but even more discovery of errors.
General design principles
1. The macro presupposes a Table/Grid form control is used being the most likely format for the cloning and display of several records.
2. Both macros are triggered by the Key Released event in the Table/Grid form control. The Alt key is chosen as the modifier key because I was unable to get the Cntrl and ' key combination suitably recognised by the AOO API
3. Further fields may be copied from the same row by selecting another field and pressing the Alt and ' combination to repeat the macro.
4. Care is required as in Base any change of the record pointer will automatically flush the output buffer to a saved record in the database: for this reason a cloned resultset is used in the macros for certain actions.
5. The Base API generally renders it simpler to work with fields in the data set that underlies the form than with the displayed columns of a Grid form control.
6. The Base API permits virtually all data-types in the data-set that underlies a form to be read and updated as if they were strings.
7. For demo purposes the macro comprises two distinct operations triggered depending on the respective key combination from the same event. In a real application it might well be preferable to use one or the other and to adjust the coding accordingly.
Issues
1. Any attempt to copy the Primary Key field will cause an error when an attempt is made to save the modified record. For this reason the macro assumes the Primary Key is located in the left-hand column (index=0) of the grid form control and data in that column is not cloned. If there is no PK field in the grid and all columns are to be cloned a couple of minor changes are required in the coding. Alternatively an SQL query (see the query in the demo) could be used to create an array that would trap and prevent cloning any PK field.
2. Initially an incorrect value may be displayed where a time column in the grid is linked to a time-stamp field in the database. The correct value, however, will be shown as soon as the modified record is saved.
Later edit: The 'cloning' macro can be extended to permit either all fields or a specified selection of fields to be copied to the new record row - see my second post below in this thread together with an updated demo file.
A macro is clearly required but, due to the idiosyncrasies of the OpenOffice API, portability encourages coding that eschews references to a named or any object that may be specific to a particular application.
For several years I have used Base for a book-keeping application where often new transactions differ in only minor respects from previous records. A macro has permitted data from any selected record to be cloned to the new record entry buffer where changes may be made before a final save to the database.
In the attached demo that macro has been extended to permit either cloning a whole record or copying a single field from its previous record as found in MS Access. The macro permits either pressing the Alt and ' key combination to copy to any selected field (new or existing) the data from the corresponding field in its immediate predecessor, or pressing the Alt and + (num pad) combination to clone any selected record to the new record row.
The macro is offered as is, without proper testing or serious elimination of the inevitable bugs. I would welcome criticisms or comments, but even more discovery of errors.
General design principles
1. The macro presupposes a Table/Grid form control is used being the most likely format for the cloning and display of several records.
2. Both macros are triggered by the Key Released event in the Table/Grid form control. The Alt key is chosen as the modifier key because I was unable to get the Cntrl and ' key combination suitably recognised by the AOO API
3. Further fields may be copied from the same row by selecting another field and pressing the Alt and ' combination to repeat the macro.
4. Care is required as in Base any change of the record pointer will automatically flush the output buffer to a saved record in the database: for this reason a cloned resultset is used in the macros for certain actions.
5. The Base API generally renders it simpler to work with fields in the data set that underlies the form than with the displayed columns of a Grid form control.
6. The Base API permits virtually all data-types in the data-set that underlies a form to be read and updated as if they were strings.
7. For demo purposes the macro comprises two distinct operations triggered depending on the respective key combination from the same event. In a real application it might well be preferable to use one or the other and to adjust the coding accordingly.
Issues
1. Any attempt to copy the Primary Key field will cause an error when an attempt is made to save the modified record. For this reason the macro assumes the Primary Key is located in the left-hand column (index=0) of the grid form control and data in that column is not cloned. If there is no PK field in the grid and all columns are to be cloned a couple of minor changes are required in the coding. Alternatively an SQL query (see the query in the demo) could be used to create an array that would trap and prevent cloning any PK field.
2. Initially an incorrect value may be displayed where a time column in the grid is linked to a time-stamp field in the database. The correct value, however, will be shown as soon as the modified record is saved.
Later edit: The 'cloning' macro can be extended to permit either all fields or a specified selection of fields to be copied to the new record row - see my second post below in this thread together with an updated demo file.
Code: Select all
REM ***** BASIC *****
Sub CopyField(oEv as Object)
REM Aborts unless Alt and ' key or ALT and + (num pad key) pressed
IF (oEv.KeyCode=1287 AND oEv.Modifiers=4) THEN CloneAnyRecord(oEv) : Exit Sub
IF NOT(ASC(oEv.KeyChar)=39 AND oEv.Modifiers=4) THEN Exit Sub
oGridV = oEv.Source
oForm = oGridV.Model.Parent
oColNum = oGridV.getCurrentColumnPosition()
oRS = oGridV.Model.getRowSet() : CloneRS = oForm.createResultSet()
IF oRS.Row=1 THEN Print "No previous row to copy from" : EXIT SUB
IF oRS.IsNew THEN
CloneRS.last()
ELSE
CloneRS.previous()
END IF
FldName$ = oGridV.getByIndex(oColNum).Model.BoundField.Name
FldValue$ = CloneRS.Columns.getByName(FldName$).getString()
oRS.Columns.getByName(FldName$).updateString(FldValue$)
End Sub
Sub CloneAnyRecord(oEv as Object) 'Called from CopyField [Alt and + numpad key]
Dim Name$(25), Value$(25)
oGridM = oEv.Source.Model
oForm = oGridM.Parent
oRS = oGridM.getRowSet()
CloneRS = oForm.createResultSet() : CloneRS.Last() 'Clone ensures RowSet fully loaded
IF oForm.IsNew THEN oRS.Last()
FOR I = 1 to oGridM.Count-1
Name$(I) = oGridM.getByIndex(I).BoundField.Name
Value$(I) = oRS.Columns.getByName(Name$(I)).getString()
NEXT I
oRS.moveToInsertRow()
FOR I = 1 to oGridM.Count-1
oRS.Columns.getByName(Name$(I)).updateString(Value$(I)
Next I
End Sub