[Solved] Delete all records in a form with a macro example

Discuss the database features

[Solved] Delete all records in a form with a macro example

Postby RPG » Sat Feb 27, 2021 1:35 pm

On the Dutch forum was a question about to delete all records in a table. I did search on this forum and found this
Macro to insert data into another table based on selection. In that thread is explained with an example how to work with a selection in a gridcontrol. I think be carefull when you place a button in a form there it delete all records without questions. Test it first and have backups. Organizing questions is for the programmer. Be careful that the button is in the same form as from with you delete the data, check this in the form navigator.

I think it does not matter if there is a gridcontrol in that form. A gridcontrol connects to the same rowset as the form but from little tests it seems I can better use it in the form. There a gridcontrol also is used as datasource in a database I think the sub can also be used in such a way but I have not test it.

I think when you want delete the complete table this sub is not what you must use. The use of this sub is you filter your table and you want delete this records. The sub is real basic and can be expand with some testing there my goal was finding how it works.

When you work with the API then OpenOffice does know what happens with the database. When you execute a SQL statement then OpenOffice does not know what happens with the database and the database must be reloaded

Code: Select all   Expand viewCollapse view
sub DeleteAllrecords(oEvent)
' The event is from a button
'It is only test in a simple form with a simple gridcontrol in one table
' I think this can not be used with joined tables but I think that can also not done with the normal working
dim oRowset ' I use the name Rowset there I hope , it can also used in grid for a datasource
oRowset = oEvent.source.model.parent ' I use the word Rowset but it points to a FormModel as in the formnavigator

' I think it is not good to use it for a lot of records.
' the number depends of several but do some times a test or look if all records are deleted.
if  oRowset.rowcount > 0 then
   ' there are records to delete
   oRowset.last ' We must have all records. I Have not test if it needs more time
   ' the method deleterows needs a sequence of bookmarks
   dim a( oRowset.rowcount-1) ' Make an array first step for the sequence
   dim x
   oRowset.first
   ' the for - next fills the sequence
   for x=0 to oRowset.rowcount-1
      a(x) =oRowset.getbookmark
      oRowset.next ' move to the next
   next
   oRowset.deleterows(A())
end if
end sub
LibreOffice 6.3.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2204
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Solved]Delete all records in a form with a macro exampl

Postby Villeroy » Sat Feb 27, 2021 1:55 pm

For the records: it can be done manually with a few clicks.
If the form shows a single record at a time (no table control), click the last button on the navigation toolbar. It opens an additional grid view.
Click the upper left corner to select all records.
Right-click anywhere on the row selectors and choose "Delete".
Confirm the warning message which also tells about the record count you are going to delete.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
 
Posts: 29713
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved]Delete all records in a form with a macro exampl

Postby RPG » Sat Feb 27, 2021 2:22 pm

Villeroy you are right and I did told the same as you do now. But I wanted know on that moment how I must do it and so I place the example here with a link on the Dutch forum.
LibreOffice 6.3.3.2 on openSUSE Leap 15
RPG
Volunteer
 
Posts: 2204
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands


Return to Base

Who is online

Users browsing this forum: No registered users and 8 guests