Apache OpenOffice Community Forum

Can someone help me, PLEASE! I've spent hundreds of hours searching for a solution to this seemingly easy problem and the more I search the worse it gets. The previous discussions on this forum and the other material I'm able to find is a giant plate of spaghetti. It's no help and I'm not getting anywhere.

I have a form that enters data into a table. They work perfectly, all form controls and all table characteristics work exactly as intended. The table's primary key field is numeric, automatically filled by "<AutoField>." It does not appear on the form and always keeps whatever number the system gives it.

All I want to do is clone/duplicate a record, then edit and save the clone. I'm willing to manually save the record first, but having the cloning operation do the Save would be nice.

The closest I can get is a macro posted by Villeroy on Jan 6, 2008, that begins, "function CopyRow ... ." But it doesn't work. When I attach it to a pushbutton and file it in My Macros > Standard > Module1 and push the button while using the form, I get "BASIC runtime error, Property or method not found: ResultSetConcurrency." And worst of all, that error brings up a macro editor that I can't escape from. To continue working I have to take my machine down with the Windows Task Manager and restart OO.O, going all the way through the file restore process.

The frustration this is causing me is terrible. I can't think of a more common or fundamental data entry requirement than cloning records, but I can't do it because I can't figure out how to get OO.O to accomplish it.

If someone out there can help, I'll be very grateful. Just tell me what the mechanism is and how do I implement it. If it takes a macro, tell me where to find it and where to install it.

And if this solution is published somewhere and I'm not finding it, I sincerely apologize. Believe me, I've tried.

[SOLVED] Please see my after-note, posted in a separate, new reply.

Board index ‹ Getting started ‹ Tutorials ‹ Base ‹ Database Examples

• [Example] Investment Tracking (record cloning with macro)

All I want to do is clone/duplicate a record, then edit and save the clone. I'm willing to manually save the record first, but having the cloning operation do the Save would be nice.

An example which does not use any macro can be downloaded from http://db.tt/FSIrEW7G.
There are two form documents in the file to illustrate two methods for using the relationship between a data form and its sub-form. Depending on the links the content of fields in the selected record in the main form will automatically be supplied by default to any New record in the sub-form where they can be accepted or overwritten as required. The first "Default New Record" form document would appear to match most closely your requirement as quoted above: once the first record has been saved in the main form, repeated additional new records can be entered in the sub-from based on that first record as the default entry.

My sincere and unconstrained gratitude to all who have answered, and in particular to DACM, whose suggestion, after painstaking modification to suit my data structure, worked perfectly.

Warning: It is not a solution for the inexperienced. In addition to some routine changes that are fairly easy, the adjustments necessary in the macro are at the heart of its logic, the embedded SQL code. I consider it a small miracle that I was able to do it, having never seen SQL before or written a single line of it in my life.

For me this experience is a testament to two things:

1. The potential in open-source, community-supported technology. Without this forum and its participants, I could never have solved this problem. While I still have much work to do to figure out why it was so difficult and took so long, I'm very aware how effective it was in the end.

2. The inexcusable lack of the feature in Base. Base is a sophisticated system on which its users have a right to rely. A data entry application without "save-and-clone" is like a car without doorhandles. I don't really understand who it is that's responsible for what BASE is, but whoever you are, if you're reading this, PUT "SAVE-AND-CLONE RECORD" INTO THE PROGRAM!

Thank you again, one and all. I'm in your debt.

geoffrose wrote:My sincere and unconstrained gratitude to all who have answered, and in particular to DACM, whose suggestion, after painstaking modification to suit my data structure, worked perfectly.

Warning: It is not a solution for the inexperienced. In addition to some routine changes that are fairly easy, the adjustments necessary in the macro are at the heart of its logic, the embedded SQL code.

That means that the macro simply utilizes SQL as the usual method to manipulate any type of relational database. The respective API-methods are totally incomprehensible and tend to fail.

I consider it a small miracle that I was able to do it, having never seen SQL before or written a single line of it in my life.

Again, SQL is the standardized lingua franca for all relational databases. Running something like MS Access you can get quite far without SQL but then you learn nothing about databases and you never get beyond a certain point where things become interesting.

For me this experience is a testament to two things:

1. The potential in open-source, community-supported technology. Without this forum and its participants, I could never have solved this problem. While I still have much work to do to figure out why it was so difficult and took so long, I'm very aware how effective it was in the end.

2. The inexcusable lack of the feature in Base. Base is a sophisticated system on which its users have a right to rely. A data entry application without "save-and-clone" is like a car without doorhandles. I don't really understand who it is that's responsible for what BASE is, but whoever you are, if you're reading this, PUT "SAVE-AND-CLONE RECORD" INTO THE PROGRAM!

Thank you again, one and all. I'm in your debt.

Currently, nobody develops anything for the Base component.
First and foremost, Base is a set of tools designed to pull data from various types of databses into office documents (reports, mail merge, bibliographies, data for calculation models). The forms and form controls which allow to manually write back into some database are very, very simplistic. "Simple" is the opposite of "easy", thus database forms are an expert feature to enable database edits across tables (editing relations).
1) You enter form values and update/insert one row at a time. Deletions may include more than one record with confirmation ("do you really want to delete x records?").
2) Subforms are filtered by the linked fields of their respective parent. New records in a subform inherit the same parent values.
3) List boxes map human readable text to foreign key values.
4) Table controls (grids) bundle other controls in columns showing more than the currently active record.
That's all you need to understand. No more form features. All the other form controls are just gimmicks for special data types and alternative input methods. For the designer everything is accessible from 2 toolbars "Form Design" and "Form Controls". For the form user toolbar "Form Navigation" is the one and only relevant user interface on top of the form.

I prefer the macro free solution demonstrated by Arineckaig which utilizes the above point 2).. More precisely, I use to use forms similar to the second form "DefualtEntry-FilterTable" (I have to investigate the other one).
There is no need to use the green update button if you do not want to apply a filter at the same time. You can enter new default values on top, switch over to the grid when you move to the new record it will inherit the default values from the above default values without updating (filtering) the data form.
Clear the criteria form and update in order to remove any filtering.
This type of form requires a few more clicks when the defaults need to change which should not happen too often otherwise default values make no sense anyway.

geoffrose wrote:...after painstaking modification to suit my data structure, worked perfectly.

Warning: It is not a solution for the inexperienced.

...inexcusable lack of the [push button cloning] feature in Base.

It would be nice to have a built-in "clone" push-button action, but Base is a pretty basic tool out-of-the-box.

Most of the built-in power in Base is literally hidden within form structures (SubForms) that leverage SQL. So by extension, the real source of power in Base is derived from the SQL feature-set built-into the underlying database engine. But then to maximize that power you'll need a proper database design. So inexperienced users have their work cut-out for them. Learn SQL, learn database design concepts, learn the ins-and-outs of Base Forms, and then pick your way through various [Base] bugs and limitations.

So it really boils down to hand-holding by experienced users. That's often done by way of working examples. To that end, Arineckaig provided a demo of the built-in functions that can be used to create a "clone" feature without the "painstaking modification" of the macro-driven examples. You should take the time to reverse-engineer that example as well because it will introduce you to the real power built-into Base, without relying on macros.

Here's a quote that might help get you started:

Base Forms are optimized for data relationships using a hidden structure of MainForms & SubForms, collectively known as "data-Forms.” This sub-structure is revealed in a logical tree-view by selecting: View > Toolbars > Form Design > Form Navigator [5th button] (see image). Each data-Form is based on its own Table, Query, or SQL command. The latter two options (Query and SQL-command) are identical, except that 'SQL-commands' are queries stored within the Form document itself, which enhances portability as a standalone Writer Form. These options (Table, Query, or SQL command) allow us to build Forms to display and input information across multiple Tables and records. SubForms are special data-Forms because they are linked by one or more fields in common with their parent data-Form. For instance, we can link two data-Forms by Client-ID.

These SubForm links serve two functions:

• (1) FILTERING SubForm links effectively filter the records available to the SubForm based on linked-field value(s), as determined by the record selection in parent data-Form. This functions similar to a WHERE clause in SQL. So this filtering function allows us to display related data, such as data in one-to-many (1:n) relationship. Filtering, then, becomes foundational to 'searching.' These terms are often used synonymously in the context of Forms, but 'filtering' is the broader category and the natural function of SubForms. To build a dedicated search function, on the other hand, typically involves a dedicated filer-table (or record) which is used to store user-input temporarily, since storage is necessary for SubForm link function. This technique avoids the creation or destruction of critical record data in the process of establishing search criteria. Either way, macro-free filtering and searching involves SubForms to present the results. Hence the name of this thread: "Filter/Search with Forms (leveraging SubForms)."
  
  (2) AUTO-FILL SubForm links also transfer linked field values between tables, as necessary, when creating new records. The value of the master-link field from the parent-Form table is copied automatically to the slave-link field in the SubForm table. This auto-fill function is quite useful in data-entry tasks across related tables, similar to the function of a List Box, but it also finds utility in record cloning tasks.

In any case, it's all transparent to the end-user, who is presented with a single, contiguous Form with various Controls (Text Boxes, List Boxes, Grids, etc.). But those Controls are actually placed strategically on the underlying data-Form structure as necessary to reflect/leverage data-relationships.

After exhausting the built-in function (which itself is not necessarily simple to learn or apply), macros can then be used to enhance the workflow for the end-user. This approach can eliminate or at least reduce the size and scope of macros in most cases. And that's the goal because macros involve working almost directly with the underlying UNO API which renders a cryptic macro-language interface compared with virtually anything else you might be used to even as an experienced macro programmer (see also this discussion). That said, it can often be easier to adapt a "simple" macro example than learning SQL and applying it to Form structures in Base. Just don't get too comfortable adapting macro-driven examples while avoiding the necessity of learning SQL and SubForms with Base. I find there's a delicate balance between built-in functionality and macros-of-last-resort when building intuitive Forms in Base.

Enjoy Base!