This button puts “beds.bed_id” into a new record on “bed.bed”. I want the “bed” table to refresh automatically so that I can see the new record without needing to click in the table and then click refresh.
This is the query that chatgpt helped me make:
Sub InsertBedWithSQL(Event As Object)
Dim oForms As Object, oBedsForm As Object, oPlotsForm As Object
oForms = ThisComponent.DrawPage.Forms
oPlotsForm = oForms.getByName("Plots")
oBedsForm = oPlotsForm.getByName("Beds")
If oBedsForm.RowCount = 0 Then
MsgBox "No bed selected.", 48, "Insert Cancelled"
Exit Sub
End If
Dim bedID As String
bedID = oBedsForm.getString(oBedsForm.findColumn("bed_id"))
' Check if the bed.bed already exists in the "bed" table
Dim oConnection As Object
oConnection = oBedsForm.ActiveConnection
Dim oStatement As Object
oStatement = oConnection.createStatement()
' Check if "bed.bed" already exists in the table
Dim sSQL As String
sSQL = "SELECT COUNT(*) FROM ""bed"" WHERE ""bed"" = '" & bedID & "'"
On Error GoTo ColumnCheckError
Dim oResultSet As Object
oResultSet = oStatement.executeQuery(sSQL)
oResultSet.next()
If oResultSet.getInt(1) > 0 Then
' Pop-up message if the bed.bed already exists
MsgBox "The bed ID " & bedID & " already exists in the table.", 48, "Insert Cancelled"
Exit Sub
End If
' Proceed with the insert if bed.bed doesn't already exist
sSQL = "INSERT INTO ""bed"" (""bed"") VALUES ('" & bedID & "')"
On Error GoTo ErrHandler
oStatement.executeUpdate(sSQL)
MsgBox "Inserted bed = " & bedID, 64, "Success"
' Re-query the data for the form (this should update the table control)
oBedsForm.reload() ' Reloading the entire form to refresh all controls, including the table control
Exit Sub
ColumnCheckError:
MsgBox "Error checking column: " & Error$, 16, "Column Error"
Exit Sub
ErrHandler:
MsgBox "SQL Insert Error: " & Error$, 16, "Insert Failed"
End Sub`Preformatted text`
here is the database:https://www.dropbox.com/scl/fo/h9kn7rdru9s7e20h1s1f8/AFQNeUMCKouLx093XxMdxfI?rlkey=wdyderdusistlnlbkvedxfxf3&st=rn4rgjdy&dl=0
