Ask Your Question
0

Requery not working for table grid

asked 2020-02-25 22:22:13 +0200

fishingCoder gravatar image

updated 2020-02-26 16:49:20 +0200

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

edit retag flag offensive close merge delete

Comments

Edited question for clarity.

Ratslinger gravatar imageRatslinger ( 2020-02-26 02:15:27 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-02-26 02:20:57 +0200

Ratslinger gravatar image

updated 2020-02-26 02:21:56 +0200

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()
edit flag offensive delete link more

Comments

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 gravatar imagefishingCoder ( 2020-02-26 21:11:34 +0200 )edit

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 ...
(more)
fishingCoder gravatar imagefishingCoder ( 2020-02-26 21:12:44 +0200 )edit

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/en/question/76073/base-error-open-form-with-macro/
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 gravatar imagefishingCoder ( 2020-02-26 21:15:01 +0200 )edit

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!

fishingCoder gravatar imagefishingCoder ( 2020-02-26 21:15:23 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-02-25 22:22:13 +0200

Seen: 48 times

Last updated: Feb 26