[Solved] Get all rows from subform grid

Creating and using forms

[Solved] Get all rows from subform grid

Postby Foryster » Tue Jan 31, 2017 10:15 pm

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   Expand viewCollapse view
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
Foryster
 
Posts: 4
Joined: Tue Jan 31, 2017 10:01 pm

Re: Get all rows from subform grid

Postby Villeroy » Tue Jan 31, 2017 11:32 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24400
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Get all rows from subform grid

Postby Foryster » Wed Feb 01, 2017 8:39 am

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
Foryster
 
Posts: 4
Joined: Tue Jan 31, 2017 10:01 pm

Re: Get all rows from subform grid

Postby UnklDonald418 » Wed Feb 01, 2017 6:18 pm

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 subject line
Apache OpenOffice 4.1.3 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 548
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Get all rows from subform grid

Postby Foryster » Wed Feb 01, 2017 7:22 pm

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   Expand viewCollapse view
allRows = oGrid.getRowSet()
OpenOffice 4.1.3 on Windows10
Foryster
 
Posts: 4
Joined: Tue Jan 31, 2017 10:01 pm

Re: Get all rows from subform grid

Postby UnklDonald418 » Wed Feb 01, 2017 8:17 pm

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.
https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=83531
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the subject line
Apache OpenOffice 4.1.3 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 548
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Get all rows from subform grid

Postby F3K Total » Wed Feb 01, 2017 8:25 pm

The rowset lies on the subform, not in the Grid, which only dispays it.
This code works with attached example:
Code: Select all   Expand viewCollapse view
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 31 times
    MMove 1.0.6
  • Extension for easy, exact positioning of shapes, pictures, controls, frames ...
    my current system
  • Windows 7 OOo, AOO, LOLinux Mint OOo, AOO, LO
F3K Total
Volunteer
 
Posts: 890
Joined: Fri Dec 16, 2011 8:20 pm

Re: Get all rows from subform grid

Postby Arineckaig » Wed Feb 01, 2017 8:46 pm

Code: Select all   Expand viewCollapse view
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).
OOo 4.1.1 on MS Windows XP MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 825
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Get all rows from subform grid

Postby Villeroy » Wed Feb 01, 2017 10:02 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24400
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Get all rows from subform grid

Postby Arineckaig » Thu Feb 02, 2017 1:18 pm

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   Expand viewCollapse view
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).
OOo 4.1.1 on MS Windows XP MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 825
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Get all rows from subform grid

Postby Villeroy » Thu Feb 02, 2017 4:58 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24400
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Get all rows from subform grid

Postby Arineckaig » Thu Feb 02, 2017 5:28 pm

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).
OOo 4.1.1 on MS Windows XP MySQL and HSQLDB
Arineckaig
Volunteer
 
Posts: 825
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Get all rows from subform grid

Postby Foryster » Thu Feb 02, 2017 8:49 pm

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   Expand viewCollapse view
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
Foryster
 
Posts: 4
Joined: Tue Jan 31, 2017 10:01 pm


Return to Forms

Who is online

Users browsing this forum: No registered users and 9 guests