have been using Google AI to generate answers to questions I enter, but it struck out on the dread How to Duplicate Records in LibreOffice Base? entry
none of the proposed methods work. Attached is the error message I got with the macro for the radio button
I’m not a programmer and don’t want to become one. Why is this simple task so hard for this program to do?
Without a complete view to the macro code you can’t get an answer. There seems to be something you call “ALLRECORDS” and code doesen’t know anything about it.
Inserting:
INSERT INTO "table" ("name", "vorename") VALUES ('Trumpel', 'Dagobert')
And in your Basic code:
SUB Duplicate(oEvent AS OBJECT)
oForm = oEvent.Spource.Model.Parent
stID = oForm.getString(oForm.findColumn("ID"))
IF stID = "" THEN
msgbox "No ID available. Record can't be copied."
EXIT SUB
END IF
stSql = "INSERT INTO ""table"" (""name"", ""vorename"") SELECT ""name"", ""vorname"" FROM ""table"" WHERE ""ID"" = '"+stID+"'"
oConnection = oForm.activeConnection()
oSQL_Statement = oConnection.createStatement()
oSQL_Statement.executeUpdate(stSql)
END SUB
Will copy the named fields into a new row of the table. “ID” needs to be the primary key and Auto Value.
thank you RobertG I will look into this
AllRecords is the name of the table in my .odb file
RobertG
your suggested code confuses me ‘cos it’s in two parts with one beginning INSERT INTO and the other in a separate box starting with Sub
these pieces remind me that when I tried the macro below (which I got from Google AI) as an action to be executed by a radio button on my form it failed via an alert box telling me that the INSERT command was not allowed
if you could be more explicit about what to do with your code pieces I’ll try them
ophis
INSERT INTO “AllRecords” (“Order”, “Family”, “Species”, “CommonName” , “Synonyms” , “Date” , ”Location”, “State”, “County”, “WetlandIndicator” , “Notes” , “Specimen”)
SELECT “Order”, “Family”, “Species”, “CommonName” , “Synonyms” , “Date” , ”Location”, “State”, “County”, “WetlandIndicator” , “Notes” , “Specimen”
FROM “AllRecords”
WHERE “ID” = 904;
Sub DuplicateCurrentRecord (oEvent as Object)
'Get the form from the button’s event source
Dim oForm As Object
oForm = oEvent.Source.Model.Parent
'Get the recordset from the form
Dim oRecordSet As Object
oRecordSet = oForm.ActiveConnection.createStatement().executeQuery(oForm.Command)
'Check if a current record exists
if oForm.IsNew or oForm.isAfterLast then
MsgBox “Please select a record to duplicate.”
exit sub
end if
'Store current record’s field values
Dim FieldValues(oForm.Columns.Count - 1)
For i = 0 To oForm.Columns.Count - 1
'Skip the primary key column (assuming it’s the first one and auto-incrementing)
'Modify this to match your specific primary key logic
If i > 0 Then
FieldValues(i) = oForm.Columns(i).value
End If
Next i
'Move to the insert row to create a new record
oForm.moveToInsertRow()
'Copy values to the new record
For i = 0 To oForm.Columns.Count - 1
'Skip the primary key again
If i > 0 Then
oForm.Columns(i).updateString(FieldValues(i))
End If
Next i
'Update the database to save the new record
oForm.updateRow()
End Sub
The first one is a SQL-command. This you could try to type yourself in the prompt you get when you use the SQL entry in the menu.
.
The second is a subroutine in BASIC. You may recognize the INSERT in the line
You have written such an insert I have described. This code should set instead of ""name"",""forename"" … inside the macro - starting with SUB.
But you could do all this much easier directly: Set “Datasource as table” (Button in navigationbar of the form). Left mouse button on the row-header of the row you want to copy. Press mouse button and move mouse down. (+) will show you try to copy. Then insert this content and all will be copied.
1 Like
Actually it is usually not complicated at all. Robert has already described the method: Just drag a row to duplicate it.
.
But as the world of databases allow a lot of variants there are obstacles. We don’t see your table definition. Is there an primary key defined? Does this auto-increment? Are there additional constraints?
- No primary key renders most tables read-only
- No auto-increment will prevent a “simple” copy of a row, as YOU may need to set/provide the Id/primary index
- Additional contraints may prevent duplicates. But usually you should know this, if you created the database…