Update Table with Button from Dialog Box

[jsvanc]

I have a form that opens a dialog box with a list box in it.

I have this code to populate the list box from information on the parent form:

Code: Select all

Sub PopulatedlgRemoveJobContactListBox()

DataSource = ThisComponent.Parent.DataSource.GetConnection("","")

JobID = ThisComponent.DrawPage.Forms.GetByName("MainForm").GetByName("txtJobID").text
SQLResult=createUnoService("com.sun.star.sdb.RowSet")

SQLQuery = "Select ""JobContacts"".*, ""Contacts"".""ContactName"" AS cntName FROM ""JobContacts"", ""Contacts"" WHERE ""JobID"" = " & JobID & " AND ""Contacts"".""ContactID"" = ""JobContacts"".""ContactID"""
SQLResult.activeConnection = DataSource
SQLResult.Command = SQLQuery
SQLResult.execute

exitOK=com.sun.star.ui.dialogs.ExecutableDialogResults.OK
DialogLibraries.LoadLibrary("Standard")

Dialog=CreateUnoDialog(DialogLibraries.Standard.dlgRemoveJobContact)
DialogField=Dialog.GetControl("lstContacts")

While SQLResult.next
ListBoxItem = SQLResult.getString(4)
DialogField.additem(ListBoxItem, DialogField.ItemCount)
Wend
If Dialog.Execute=exitOK Then
CurrentItemName=Dialog.SelectedItem
End If

End Sub

So now what I need to do is when I click the Remove Contact button, it will delete a row on my table where JobID = "something" and ContactID = "Information from listbox"
I'm tried to do another sub

Code: Select all

Sub RemoveContact()
DataSource = ThisComponent.Parent.DataSource.GetConnection("","")
SQLDelete = "DELETE FROM ""JobContacts"" WHERE ""JobID"" = " & JobID & " AND ""ContactID"" = " & Dialog.SelectedItem(3)
SQLResult.activeConnection = DataSource
SQLResult.Command = SQLDelete
SQLResult.execute
exitOK=com.sun.star.ui.dialogs.ExecutableDialogResults.OK
End Sub

But I'm not sure how to call the information in the list box to make the ContactID = "SelectedItem"
I've added an attachment of the Dialog box, not that it will help, but just in case.

I am new to Base and Macros. Any help is appreciated. Thank you.

[Villeroy]

It is almost trivial to add a "power filtering list box" to your form (or even easier: use the built-in form filter) in order to display what you are going to delete and then push a delete button.

[jsvanc]

I'll check on the power filtering and built-in form filter. Thank you Villeroy.

[Villeroy]

You find many examples in the forum for Base tutorials.
One that demonstrates power filtering and how Base works together with Calc: viewtopic.php?t=88516&p=416210#p416210
It has a one-line macro but can be set up without any macro which requires 3 more clicks per sheet update.