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

Discuss the database features
Post Reply
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

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

Post by RPG »

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

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 7.1.4.2 on openSUSE Leap 15.2
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

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

Post by RPG »

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 7.1.4.2 on openSUSE Leap 15.2
Post Reply