Delete record macro for a specific form

[samuel_ribeiro]

Version: 7.5.7.1 (X86_64) / LibreOffice Community
Build ID: 47eb0cf7efbacdee9b19ae25d6752381ede23126
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: pt-BR (pt_BR.UTF-8); UI: pt-BR
Calc: threaded

Morning, I’m looking for a way to edit this macro below so it only deletes the record of a specific form, and not the last one I interacted with, I tried using getbyname, but I wasnt successfull.

sub DeleteRecord
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:DeleteRecord", "", 0, Array())


end sub

I need it to be a macro and not the default options of LO Base because I need to validate if the record was deleted in order to execute the rest of the macro, that will be responsible for the deletion of the image that was linked to the form in its specific folder. The deletion part is working, but I cant find a way to put it in a event that work as I need, when I put it on the action of the button, if the user cancel the record elimination, the image will be deleted anyway, and thats not ok, I tried linking it to the form control event of confirm elimination, but instead of asking if the user is sure, it just erase the record and the image on click, so I had this idea to mix the two erase macros, any help is welcome.

[elmau]

Always mention the version of your SO and LibreOffice.

If you want to access a form by name, use:

doc = ThisComponent
form = doc.FormDocuments.getByName("images")

but beware, if you try to access it from a form event, ThisComponent will no longer be the same object.

Without seeing the rest of your code, it’s hard to help you.

[samuel_ribeiro]

Sub DeleteRecordAndImage
    Dim oFormPrincipal As Object
    Dim oSubForm As Object
    Dim oControl As Object
    Dim oStatement As Object
    Dim sMainFormName As String
    Dim sSubFormName As String
    Dim sControlName As String
    Dim sTableName As String
    Dim sSQL As String
    
    ' Define the names of the main form, subform, control, and table here
    sMainFormName = "MainForm"
    sSubFormName = "SubFormFoto"
    sControlName = "IDFoto"
    sTableName = "DBFoto" ' Replace "DBFoto" with the actual table name
    
    ' Get the main form
    oFormPrincipal = ThisComponent.getDrawPage().getForms().getByName(sMainFormName)
    
    ' Get the subform within the main form
    oSubForm = oFormPrincipal.getByName(sSubFormName)
    
    ' Get the control containing the ID of the record in the subform
    oControl = oSubForm.getByName(sControlName)
    
    ' Get the value of the selected record's ID
    Dim recordID As Integer
    recordID = oControl.getCurrentValue()
    
    ' Confirm if the user wants to delete the record
    If MsgBox("Are you sure you want to delete this record?", 4 + 32, "Confirm Deletion") = 6 Then
        ' Get the database connection
        Dim oConnection As Object
        oConnection = oFormPrincipal.ActiveConnection
        
        ' Create an SQL query to delete the record with the specified ID from the table
        sSQL = "DELETE FROM " & sTableName & " WHERE " & sControlName & " = " & recordID
        
        ' Execute the SQL query
        oStatement = oConnection.createStatement()
        oStatement.executeUpdate(sSQL)
        
        ' Refresh the subform to reflect the changes
        oSubForm.reload()
        
        ' Insert code here to delete the associated image
        ' ...
    End If
End Sub

.
Im tryna using this alternative macro, but Im having trouble on the line:
oStatement.executeUpdate(sSQL)
.

.
This error is occurring even though the name of the Table is DBFoto

[elmau]

Use double quotes both in the name of the table and in the name of the field.

sql = "DELETE FROM ""images"" WHERE ""id""=4"