[Solved] Get all rows from subform grid

Creating and using forms
Post Reply
Foryster
Posts: 4
Joined: Tue Jan 31, 2017 10:01 pm

[Solved] Get all rows from subform grid

Post by Foryster »

Hi All,

I'm fighting against macros in openoffice base, basic. I have a grid on the form. I want to iterate through all rows of that grid.

Currently I have

Code: Select all

subForm = mainForm.getByName("SubForm")
oGrid = subForm.getByName("SubForm_Grid")
allRows = oGrid.getRowSet()

FOR I = 1 to allRows.Count      
      print allRows.Columns.getByIndex(0).getString()
   NEXT I
However the RowSet has always only one Row, the selected one. Is there any other method I should use? Any suggestions?
Last edited by Foryster on Thu Feb 02, 2017 8:48 pm, edited 1 time in total.
OpenOffice 4.1.3 on Windows10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Get all rows from subform grid

Post by Villeroy »

Obviously you have a problem with databases and forms. You must not write any macros before you fully understand the appication you are hacking. Have you ever created a working database application without macros? You would be surprised about the less frustrating options you have at hand with a little bit of SQL and normalization logic.
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
Foryster
Posts: 4
Joined: Tue Jan 31, 2017 10:01 pm

Re: Get all rows from subform grid

Post by Foryster »

This is my application I'm "hacking". I have a normalised DB with all the keys and relations. And I'm fully understanding the logic of the app I'm writing. I just need to know if there is any method to get rows from grid one by one?

Simple as it is.
OpenOffice 4.1.3 on Windows10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Get all rows from subform grid

Post by UnklDonald418 »

The rowset service has a number of navigation methods including next() and previous() that allow you to
to get rows from grid one by one
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Foryster
Posts: 4
Joined: Tue Jan 31, 2017 10:01 pm

Re: Get all rows from subform grid

Post by Foryster »

Thx for suggerstion but it doesn't work. The rowset.Count is 1 and rowset contains only one row that was selected. The table has more rows.

So If I use next() or previous() in a loop, I enter the loop only once.

It seems that instruction below returns only the selected row, not all of them.

Code: Select all

allRows = oGrid.getRowSet() 
OpenOffice 4.1.3 on Windows10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Get all rows from subform grid

Post by UnklDonald418 »

Not exactly the same as what you are attempting, but here is a link to an example of manipulating data on a grid using a rowset.
viewtopic.php?f=21&t=83531
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
F3K Total
Volunteer
Posts: 1038
Joined: Fri Dec 16, 2011 8:20 pm

Re: Get all rows from subform grid

Post by F3K Total »

The rowset lies on the subform, not in the Grid, which only dispays it.
This code works with attached example:

Code: Select all

Sub iterate_through_all_rows
    oMainForm = ThisComponent.Drawpage.forms.getbyName("MainForm")
    oSubForm = oMainForm.getbyName("SubForm")
    oResult = oSubForm.createResultSet
    oResult.beforefirst
    count = 0
    while oResult.next
        count = count + 1
        sSN = oResult.Columns.getbyName("SN").getstring
        sFN = oResult.Columns.getbyName("FN").getstring
        print "No. " & count & ": " & sSN & ", "& sFN
    wend
End Sub
Attachments
Subform.odb
(36.28 KiB) Downloaded 356 times
  • MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
  • my current system
  • Windows 10 AOO, LOLinux Mint AOO, LO
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Get all rows from subform grid

Post by Arineckaig »

Code: Select all

 allRows = oGrid.getRowSet() 

The RowSet supplied by a Table/Grid form control tends to refer to the columns in the grid as opposed to the rows. The latter comprise the RowSet of the (data)form that contains the Grid form control: thus should be obtained from the form.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Get all rows from subform grid

Post by Villeroy »

A grid has no row set. A grid is just a bundle of other controls (list boxes, date controls, text boxes etc) showing more than one record at once. There is no "Data" tab in the properties window of a grid. The record set belongs to the underlying logical form which might be oGrid.getParent() [not sure]

No macro programming without MRI or XRay
[Tutorial] Introduction into object inspection with MRI
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
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Get all rows from subform grid

Post by Arineckaig »

A grid has no row set.
This is indeed true and I should apologise for lack of precision. At risk of further confusion I should explain that my comment reflected the [MRI] finding that the object "com.sun.star.comp.forms.OGridControlModel" does somewhat surprisingly have a "RowSet" property so that no error is necessarily raised by:

Code: Select all

allRows = oGrid.getRowSet()
The ElementNames property of the "OGridControlModel" object comprise the names of the columns of the Grid form control. Calling its "RowSet" property however supplies the "com.sun.star.comp.forms.ODatabaseForm" object: i.e., the container of the Grid form control. While the ElementNames and the Count properties of this "ODatabaseForm" object refer to the various Controls that it contains, it does have a RowCount property and the customary .row methods. Thus the RowSet property of the Grid does appear in effect to give access to its containing (data)form's row set.

I would endorse however Villeroy suggestion: the RowSet can be reached just as easily, and indeed more logically, through the Parent property of the Grid form control.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Get all rows from subform grid

Post by Villeroy »

Arineckaig wrote:[MRI] finding that the object "com.sun.star.comp.forms.OGridControlModel" does somewhat surprisingly have a "RowSet" property so that no error is necessarily raised by:
:shock:
Very confusing indeed.
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
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Get all rows from subform grid

Post by Arineckaig »

I have always understood the Grid form control to be a later addition to Base and in several respects, as both a form control and a container of form controls, tends to be a 'law unto itself'.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Foryster
Posts: 4
Joined: Tue Jan 31, 2017 10:01 pm

Re: Get all rows from subform grid

Post by Foryster »

F3K Total wrote:The rowset lies on the subform, not in the Grid, which only dispays it.
This code works with attached example:

Code: Select all

Sub iterate_through_all_rows
    oMainForm = ThisComponent.Drawpage.forms.getbyName("MainForm")
    oSubForm = oMainForm.getbyName("SubForm")
    oResult = oSubForm.createResultSet
    oResult.beforefirst
    count = 0
    while oResult.next
        count = count + 1
        sSN = oResult.Columns.getbyName("SN").getstring
        sFN = oResult.Columns.getbyName("FN").getstring
        print "No. " & count & ": " & sSN & ", "& sFN
    wend
End Sub
This is exactly what I was looking for. Thanks a lot! It works :)
OpenOffice 4.1.3 on Windows10
Post Reply