Base: Duplicating a Record in a database

[wyomason]

I want to duplicate a record in my database.

First I select the record:

SELECT “Check”, “Check_Amount”, “Deposit_Amount” FROM “MyMoney”
WHERE “ID” = :Enter_ID_No

This brings up a dialog box where I can enter the primary key (ID) of the record I want to copy.

However, when I create a SQL statement to create a new record from the selected record, LibreOffice balks. It won’t accept the WHERE clause.

INSERT INTO “MyMoney” (“Check”, “Check_Amount”, “Deposit_Amount”)
SELECT “Check”, “Check_Amount”, “Deposit_Amount” FROM “MyMoney”
WHERE “ID” = :Enter_ID_No

If I manually enter the ID number, this works great, just as expected.

INSERT INTO “MyMoney” (“Check”, “Check_Amount”, “Deposit_Amount”)
SELECT “Check”, “Check_Amount”, “Deposit_Amount” FROM “MyMoney”
WHERE “ID” = 9

How can I get around this problem so that I could, for example, select a record from a list, note the ID number, and run the SQL and enter the ID number as a user parameter?

[Ratslinger]

Hello,

Whenever posting, it is most helpful if you also include your OS, specific LO version and when dealing with Base, the database being used.

[wyomason]

I am using LibreOffice Version: 6.3.1.2 on a Mac running MacOS 10.14.6. The database is the Firebird embedded…
Thanks.

[Ratslinger]

Hello,

Parameters within SQL queries (ie :myParam) are handled through the built-in interpreter. This actually replaces the normal parameter of ? which is only a of a single type. However, when turning on Run SQL command directly the interpreter is bypassed.

An INSERT statement is normally not done within the Query section - some exceptions exist. This can be done through the menu on the main .odb screen Tools->SQL.... When there, you are also bypassing any interpreter and therefore no parameter usage.

Another possibility is to write a macro specific to your needs. This can be attached to a push button which reads the data from your list, inserts it into the SQL in the macro and completes the statement. If you have no macro experience this is not something easily learned. This is much documentation available ( see → To learn LibreOffice Base are there introductions or tutorials?) in BASE / MACRO DOCUMENTATION section.

As with all postings/answers, if this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

Edit 2019-09-14:

Attached is a VERY simplified version of duplicating a record using a macro. The button on the form will duplicate (excluding ‘ID’ which is auto-increment) whichever record is selected and reload the form to include the newly created record. If ‘New Record’ is selected, the first record will duplicate - again very simplified. Various checks not instituted as this is a simple demo.

Sample ----- DuplicateRecord.odb

[wyomason]

It is always hard to know how much detail to include when asking a question.

I learned right away that the INSERT command does not work when creating a query in LibreOffice. So I have been trying to use the Tools->SQL menu selection. This is where I enter the SQL statement displayed in my original question and where I get the SQL error pointing to the WHERE clause when I try to use a parameter.

I do not know where to go to turn on the “Run SQL command directly” that you talk about in your first paragraph.

Please let me know what you suggest I do next.

Thank you for your reply.

[Ratslinger]

@wyomason,

In the Query section, either from the menu Edit or on the toolbar is Run SQL command directly. This bypasses the interpreter. But, as you know, you can’t use this. Tools->SQL... automatically runs in that mode and bypasses the interpreter and therefore has no ability to use parameters you want.

As stated, the only other method at this point is to create a macro. Please see edited portion of answer for a sample. Again macros are not easy if you haven’t done this before. This sample need much more work in my estimation and too much to detail here.

You may need to stick with what you already have or learn to code macros.

[wyomason]

I have looked at your DuplicateRecord.odb. This is VERY helpful! Thank you. I will be attempting to incorporate this into my database within the next few days. (This is a personal project with no deadline ) I love the fact that it gets the ID from the active record. I really appreciate the help, and will go ahead and check my question as answered.

[wyomason]

I learned of a change I could make to your macro so it works with ANY form I create to use with my database, not just the form called “MainForm”.

I renamed the macro “DuplicateRecord” so the first line of the macro becomes “Sub DuplicateRecord(oEvent)”

Then I replaced the oForm declaration line with:
oForm = oEvent.Source.getModel().getParent()

I tested it in my “real” database, and it works great. (AFTER I realized that the SQL statement had to be all on one line with no line breaks )

[Ratslinger]

@wyomason,

Using oEvent is very common in macros. As stated the sample was very simplified and needed much work in various checks. I would consider that before enabling this type of routine elsewhere.

As for the SQL, yes it need to be a continuous string, but the code need not be all on one line. See the code in my sample where the string is on two lines joined by & and the conitnuation character _ (underscore) at the end of the first line.