Requery not working for table grid

[fishingCoder]

It seems to me, based on what I’ve read here, in Andrew P’s book, etc., that this should work:

sub ClearSelectionComments
	dim rstTemp as Object
	sql = "SELECT * from comments WHERE select_box = 1"
	set rstTemp = CurrentDB.OpenRecordset(sql)
	if rstTemp.RecordCount > 0 then 					' This is how you tell if no records were delivered
		REM Do an update
		sql = "UPDATE comments SET select_box = 0 WHERE select_box = 1"
	    DoCmd.RunSQL(sql)
	endif
    rem requery the form so it refreshes the screen
    dim oForm as Object
    Set oForm = getObject("Forms!comments")
    oForm.Requery
    oForm.Refresh

end sub

It runs without error. The SQL function works and clears the tick boxes in the underlying SQLite3 database. I verify this with DB Browser.
But the screen in LibreOffice BASE never updates. This is a simple form, with a table grid and a button called “Clear selection” which, when clicked, runs the macro above.
I tried doing OForm.SetFocus – no help. I tried doing:

  dim ctrl as Object
  set ctrl = getObject("Forms!comments!Comments_Grid")
  ctrl.Requery
  ctrl.Refresh

For that, I get: “Error #1527 (Method ‘Control.Requery’ not applicable in this context) occurred in a call to function ‘Control.Requery’”

I even tried selecting a specific field in the table grid.

dim ctrl as Object
set ctrl = getObject("Forms!comments!Comments_Grid!select_box")
ctrl.SetFocus
ctrl.Requery
ctrl.Refresh

And got the same error.

I spent several hours reading Andrew’s OpenOffice.org Base Macro Programming and OpenOffice.org Macros Explained. And the better part of a couple of days reading the Getting Started with BASE doc, and the BASE Handbook.

I even thought of opening the recordset under the form, iterating it and clearing the select_box flag from it. But the Access to Base doc says that the recordset and the form will be independent, so that would mean that doing it that way would indeed change it in the underlying table, but not in the form.

I’ve studied the API doc for LO Base for a few hours, and read enough about the interface oriented programming that is required to know that I don’t want to learn that for this small job, which is a quick-and-dirty app for a son of mine. I’ve done a good bit of VBA in MS Access in the past, where you do this with something like " Forms![MainForm]![subformName].Requery"

I’ve also been testing out using Python, with even worse luck, because you either have to know the large native API set, or just call Access2Base, in which case for something small like this you might as well use Access2Base.

I have this nagging feeling that I’m missing something obvious, because the Acess2Base doc sure seems to me to indicate that oForm.Requery ought to work.

Thanks in advance for any advice you can give.
Max

So

[Ratslinger]

Edited question for clarity.

[Ratslinger]

Hello,

Although have very limited experience with both Access2Base and SQLite3, it appears the problem is that the data needs to be reloaded at the Form level. Not certain what Requery is supposed to do but here is how I reload:

oForm = ThisComponent.Drawpage.Forms.getByName("YOUR_INTERNAL_FORM_NAME")
oForm.reload()

[fishingCoder]

Many thanks, Ratslinger, this helped a lot. Using this, and some of the examples in the Access2Base documentation, and another post by you, I created the following:

sub ReloadForm(poEvent as Object)
Dim oeEvent As Object, ocBtn As Object, oForm As Object
Set oeEvent = Events(poEvent)
Set ocBtn = oeEvent.Source		' This should be the button that fired the event
oForm = ocBtn.Parent
oForm = ThisComponent.Drawpage.Forms.getByName(oForm.Name)
oForm.reload()
end sub

This can be bound to a button on a form. It can be called from many buttons on many forms, and will refresh the form that it was called from, so I don’t have to create a separate macro for each button. Good.

[fishingCoder]

Next I discovered that in the control properties on a button, there is the Action property, and one of the values is Refresh form. That works, and so I simply use that action on a button.

Next I discovered that I can read the value of the Additional Information field and use that to pass a parameter to a macro, allowing me to create a generic OpenForm macro:

sub OpenForm(pEvent as Object)
Dim tag as string
tag = pEvent.Source.Model.Tag
' In this case, the tag should be the name of the form to be opened
' Set this in the Additional Information field in the Properties settings of the button that calls this macro
' If you need multiple parameters in some other macro, choose a separator character, use that in the Additional Information Field
' and then parse the tag into separate parameters using the split function on the tag with the separator character, e.g. "formName, controlName"
FormChange(tag)
end sub

[fishingCoder]

As you’ll see from the above, in another post I found your FormChange macro, and adapted it to automatically reload the form:

SUB FormChange( sFormName )
REM From Ratslinger: https://ask.libreoffice.org/t/base-error-open-form-with-macro/20579
REM Adapted with changes below to force reloading the form, in case user changed underlying data
REM Tested on LibreOffice 6.3 on Win 10 with SQLite backend database via ODBC driver.
Dim ObjTypeWhat	
Dim ObjName As String
Dim sName as String
Dim sTitle As String
Dim iStart As Integer
Dim oForm as Object 	' max added this
'Remove comment on these next four lines if you want to close the currently open form  '
'   sTitle = ThisComponent.Title  '
'   iStart = Instr(sTitle,":") + 2  '	
'   sName = Mid(sTitle, iStart)  '
'   ThisDatabaseDocument.FormDocuments.getbyname( sName ).close  '
ObjName = sFormName
ObjTypeWhat = com.sun.star.sdb.application.DatabaseObject.FORM

continued in next comment

[fishingCoder]

Continuation

If ThisDatabaseDocument.FormDocuments.hasbyname(ObjName) Then 'Check the form exists'
	ThisDataBaseDocument.CurrentController.Connect() 'If the form exists connect to the database'
Set oForm = ThisDatabaseDocument.CurrentController.loadComponent(ObjTypeWhat, ObjName, FALSE) 'Open the form  ' Max added the Set oForm = to capture the opened form
' Max added the next, from one of Ratslinger's other posts.  This forces reload of the form.
' In my application, the user on other forms will change the data underlying this form, so I need to reload when I open it.
oForm.Drawpage.Forms.getByName(ObjName).reload()	
Else
	MsgBox "Error! Wrong form name used. "+chr(10)+"Form Name = " & ObjName
	End if
End Sub

All in all, this has been very helpful. Many thanks!