Macro to insert data into another table based on selection

[rbroberts]

I have a database I want to use for manually schedule students into class sections. Here is my schema:

The "enrollment" table tracks which members are enrolled in a particular class+section (section is really the primary item in which you enroll). I have a query which shows member currently enrolled in a section, and I a view that shows, for every student, which sections they are not enrolled in. Both that query and view are displayed in table control widgets in a subform. Because the subform is linked to a parent (sub)form which includes the current sectionID value, I only see those students not enrolled in the section displayed. This is all very good

The problem I'm running into is how to select a set of rows from the "not enrolled" set and put them into the "enrolled" set. I have a macro which I've attached to a button which is part of the same form as the "not enrolled" view.

Code: Select all

Sub AddEnrollment (oEvent As Object) 'Button > Execute > event  'Used in form "Copy a part"
    Dim oForm : oForm = oEvent.Source.Model.Parent
    Dim oStatement : oStatement = oForm.ActiveConnection.createStatement()
    Dim sSQL As String

    REM I need to figure out which rows are selected
    dim oGrid
    oGrid=oForm.getbyname("TableControl")

    Dim sColumns As String
    sColumns = sColumns & """personID"""
    sColumns = sColumns & ", ""sectionID"""
       
    sSQL = "INSERT INTO ""enrollment"" ( ""personID"", ""sectionID"") " &_
           "SELECT ""personID"", ""sectionID"" FROM ""UnenrolledView"" " &_
           " WHERE ""sectionID"" = 0"
    REM oStatement.executeUpdate( sSQL ) 'Execute the SQL command
    sSQL = sSQL & " " & personID
    MsgBox(sSQL)
End Sub

This, if I were to execute it instead of just displaying it, enrolls everyone. What I can't figure out how to do is how to get the set of rows which are currently selected. If I put a break point in the macro and inspect the oGrid object, I can't figure out which fields I should be examining to get those selected rows. The value of the Selection member/element/whatever it's called OOO is null. If I look at the RowSet member, I see there is a BookMark field but it appears to be set the current record, where the little arrow pointer is. Since I have multiselect on, that's not good enough. I need to know the set of selected rows which may not even include that one. The rows contain the keys I need for the insert, I just can't figure out how to access them.

This is actually LibreOffice 4.0.2.2/Ubuntu 13.04.

[F3K Total]

Hi,
you select something in a gridcontrol, so you have to search in that object.
Here's a snippet to find the numbers of the selected rows:

Code: Select all

Sub AddEnrollment (oEvent As Object) 'Button > Execute > event  'Used in form "Copy a part"
    Dim oForm : oForm = oEvent.Source.Model.Parent
    Dim oStatement : oStatement = oForm.ActiveConnection.createStatement()
    Dim sSQL As String
    Dim sColumns As String    
    ocontroller = Thiscomponent.currentcontroller
    ogrid = oform.TableControl
    ogridcontrol = ocontroller.getcontrol(ogrid)
    oselection = ogridcontrol.selection
    ..

analyse

Code: Select all

oselection

R

[RPG]

Hello

I think: in stead of moving data from one table to an other it is more easy to use a logical field in your table. You can set or unset the logical field with a checkbox. When you reload the table you have the good names.

Romke

[rbroberts]

How would that work? But unless I'm going to simply create an enrollment table which is a full outer join of section+member and put a checkbox on the ones enrolled, I don't see how I get around the need to perform some action on the selected (or checked) items. I can't figure out how to tie the value of the checkbox back to something I can operate on. A checkbox is the equivalent to finding the selected rows, but "selection" requires no underlying field and a checkbox does.

I've been spinning my wheels on this for several days. Listbox multi-selection doesn't work so I moved to a table control. Table control has no obvious way to find the selected rows.

[RPG]

Hello

The query for the table must then be changed in a way so: when the check box is changed and your form is reloaded you do see other values.

That means you need some knowledge about SQL.

Romke

[rbroberts]

Uhm, my day job is writing Java applications that talk to Oracle. Been doing that for about 2 decades. I have no trouble with database design. I have a problem is Ooo Base. The issue is pretty clear that the tool is limited. That's fine. I'm trying to understand what it can do. Comments like "you need some knowledge about SQL" is not helpful in any way.

Why can I enable multi-select on the Table Control but the macro can't find what rows are selected? Is this a real deficiency or is it just I dont' know how? I've assumed the latter and documentatio is sketchy....

If you think the whole set of relationships need redesign due to some limitation of Base that's fair; what's the limitation and what redesign will get me the effect I need? Give me something concrete to work with.

Look at the relationships I've already provided, you'll find they're fully normalized. Is there a reason I need to denormalize them to work with Base? If so why? Or more to my needs, how will it help?

I've already suggested only possible change, but it would be ugly. That's to build in a view with a full outer join between classes and members. I think I can make that work, but it still needs a table somewhere for the enrollment. And it would still be ugly. But I can live with ugly....

I don't need any Zen statements about "The query for the table must be change in a way so...." That's no more usefuly than telling me I need to fix the query so it works; tautological comments are not helpful.

[rbroberts]

Okay, I've finally found the example I needed on finding the correct control object. Below is the code I finally used. The magic parts were the first three lines of each of the two subroutines. That and a bit of rearranging of the form hierarchy so I could refresh both subforms with the magic oForm.Parent.reload

I'm also attaching the revised odb document. Maybe it will be useful to someone else who wants a starting point for this sort of manual timetabling.

Code: Select all

Sub AddEnrollment (oEvent As Object)
    Dim oForm : oForm = oEvent.Source.Model.Parent
    Dim oTable : oTable = oForm.getByName("UnenrolledGrid")
    Dim oGrid : oGrid = ThisComponent.getCurrentController().getControl(oTable)
    Dim oPidCol : oPidCol = oTable.getByName("personID")
    Dim oSidCol : oSidCol = oTable.getByName("sectionID")
    Dim aSelectedRecords : aSelectedRecords = oGrid.getSelection
    Dim oTotal : oTotal = 0
    Dim oStatement : oStatement = oForm.ActiveConnection.createStatement()
    Dim sSQL As String
    for each nRecord in aSelectedRecords 
        oForm.absolute(nRecord)
        oPID = oPidCol.boundfield.Value
        oSID = oSidCol.boundfield.Value       
        oTotal = oTotal + 1
        sSQL = "INSERT INTO ""enrollment"" ( ""personID"", ""sectionID"") " &_
              "SELECT ""personID"", ""sectionID"" FROM ""UnenrolledView"" " &_
              " WHERE ""sectionID"" = " & oSID & " and ""personID"" = " & oPID
        oStatement.executeUpdate(sSQL)
    next
    oForm.Parent.reload 
End Sub

Sub RemEnrollment (oEvent As Object)
    Dim oForm : oForm = oEvent.Source.Model.Parent
    Dim oTable : oTable = oForm.getByName("EnrolledGrid")
    Dim oGrid : oGrid = ThisComponent.getCurrentController().getControl(oTable)
    Dim oPidCol : oPidCol = oTable.getByName("personID")
    Dim oSidCol : oSidCol = oTable.getByName("sectionID")
    Dim aSelectedRecords : aSelectedRecords = oGrid.getSelection
    Dim oTotal : oTotal = 0
    Dim oStatement : oStatement = oForm.ActiveConnection.createStatement()
    Dim sSQL As String
    for each nRecord in aSelectedRecords 
        oForm.absolute(nRecord)
        oPID = oPidCol.boundfield.Value
        oSID = oSidCol.boundfield.Value       
        oTotal = oTotal + 1
        sSQL = "DELETE FROM ""enrollment""" &_
               " WHERE ""sectionID"" = " & oSID & " and ""personID"" = " & oPID
        oStatement.executeUpdate(sSQL)
    next
    oForm.Parent.reload  
End Sub

[F3K Total]

Jepp,
that's exactly what i wanted to guide you to with my "magic part":

Code: Select all

    ocontroller = Thiscomponent.currentcontroller
    ogrid = oform.TableControl
    ogridcontrol = ocontroller.getcontrol(ogrid)
    oselection = ogridcontrol.selection

If you don't like the recordmarker jumping, except once on reloading, you can work with a clone of forms resultset like this:

Code: Select all

Sub AddEnrollment (oEvent As Object)
    Dim oForm : oForm = oEvent.Source.Model.Parent
    Dim oResult : oResult = oForm.CreateResultset
    Dim oTable : oTable = oForm.getByName("UnenrolledGrid")
    Dim oGrid : oGrid = ThisComponent.getCurrentController().getControl(oTable)
    Dim nPidCol : nPidCol = oResult.findColumn("personID")
    Dim nSidCol : nSidCol = oResult.findColumn("sectionID")
    Dim aSelectedRecords : aSelectedRecords = oGrid.getSelection
    Dim oStatement : oStatement = oForm.ActiveConnection.createStatement()
    Dim sSQL As String
    for each nRecord in aSelectedRecords 
        oResult.absolute(nRecord)
        nPID = oResult.getint(nPidCol)
        nSID = oResult.getint(nSidCol)     
        sSQL = "INSERT INTO ""enrollment"" ( ""personID"", ""sectionID"") " &_
              "SELECT ""personID"", ""sectionID"" FROM ""UnenrolledView"" " &_
              " WHERE ""sectionID"" = " & nSID & " and ""personID"" = " & nPID
        oStatement.executeUpdate(sSQL)
    next
    oForm.Parent.reload 
End Sub

R

[rbroberts]

I actually didn't notice the record marker jumping, but that's a nice touch. Of course, I might not have noticed because I'm going bleary-eyed after having stared at it for so long :-/

[F3K Total]

Looks, as if you had a hard night!
Here's another idea.
If you analyse if the selection is empty, the current row can be choosen although it wasn't selected.

Code: Select all

REM  *****  BASIC  *****

Sub AddEnrollment (oEvent As Object)
    Dim oForm : oForm = oEvent.Source.Model.Parent
    Dim oResult : oResult = oForm.CreateResultset
    Dim oTable : oTable = oForm.getByName("UnenrolledGrid")
    Dim oGrid : oGrid = ThisComponent.getCurrentController().getControl(oTable)
    Dim nPidCol : nPidCol = oResult.findColumn("personID")
    Dim nSidCol : nSidCol = oResult.findColumn("sectionID")
    Dim aSelectedRecords : aSelectedRecords = oGrid.getSelection
    Dim oStatement : oStatement = oForm.ActiveConnection.createStatement()
    Dim sSQL As String
    if ubound(aSelectedRecords) = -1 then 'empty 
        redim aSelectedRecords(0)
        aSelectedRecords(0) = oForm.row 
    endif
    for each nRecord in aSelectedRecords 
        oResult.absolute(nRecord)
        nPID = oResult.getint(nPidCol)
        nSID = oResult.getint(nSidCol)     
        sSQL = "INSERT INTO ""enrollment"" ( ""personID"", ""sectionID"") " &_
              "SELECT ""personID"", ""sectionID"" FROM ""UnenrolledView"" " &_
              " WHERE ""sectionID"" = " & nSID & " and ""personID"" = " & nPID
        oStatement.executeUpdate(sSQL)
    next
    oForm.Parent.reload 
End Sub

Sub RemEnrollment (oEvent As Object)
    Dim oForm : oForm = oEvent.Source.Model.Parent
    Dim oResult : oResult = oForm.CreateResultset
    Dim oTable : oTable = oForm.getByName("EnrolledGrid")
    Dim oGrid : oGrid = ThisComponent.getCurrentController().getControl(oTable)
    Dim nPidCol : nPidCol = oResult.findColumn("personID")
    Dim nSidCol : nSidCol = oResult.findColumn("sectionID")
    Dim aSelectedRecords : aSelectedRecords = oGrid.getSelection
    Dim oStatement : oStatement = oForm.ActiveConnection.createStatement()
    Dim sSQL As String
    if ubound(aSelectedRecords) = -1 then 'empty 
        redim aSelectedRecords(0)
        aSelectedRecords(0) = oForm.row 
    endif
    for each nRecord in aSelectedRecords 
        oResult.absolute(nRecord)
        nPID = oResult.getint(nPidCol)
        nSID = oResult.getint(nSidCol)            
        sSQL = "DELETE FROM ""enrollment""" &_
               " WHERE ""sectionID"" = " & nSID & " and ""personID"" = " & nPID
        oStatement.executeUpdate(sSQL)
    next
    oForm.Parent.reload  
End Sub

R

[RPG]

Hello

For some reason I could not forget this thread. Special I could not forget the sub for deleting records. That sub made I did search for an other solution. I do know you cannot use this sub in this database and F3K Total did know that. I special hope F3K Total enjoy this information.

I do use the sub only for making something clear not asked in this thread.

When you want start with macros in OOo-base then you need a detailed knowledge about the controls and forms. Also you need good, I think not detailed, knowledge about SQL. Without the knowledge about both subject you can not work with OOo-base.

Now you can design your forms in a way you can handle your data. When you think you need macros in your form there you cannot do what you want with the normal tools then most of the time the macro can be easy. With the good designed forms you can use the SQL code in your form. It is easy to move some data from one form to an other form there all SQL code is already in your form. You can do this with the boundfields who belong to the controls.

What new for me was how easy you can delete selected records in a gridcontrol. The method does also return an array with the records.

It did me also learn that the bookmarks for the resultset are also important. I have to study more about bookmarks.

And people should not forget when you select records in gridcontrol the most easy method for deleting the records is always: press the delete key.

Romke

Code: Select all

sub select_Kolom_row(oEvent as object)
dim oButton
dim oFormmodel,oformView,oFormoperations
dim oGridmodel,oGridcontrol
oButton=oEvent.source.model
oFormmodel=oButton.parent
oGridmodel=oFormmodel.getbyname("Tabelobject 1")
oGridcontrol=thiscomponent.getCurrentController.getcontrol(oGridmodel)
Dim mSelectThisrows() ' Variant for selecting records
mSelectThisrows=array(2,4,Nothing) 'The array must be end with the object nothing. I do not know why!
oGridcontrol.Select(mSelectThisrows)
dim aSelectedRecords ' I have the idea it is better to work with this array
dim aRecordsDeleted
aSelectedRecords= oGridcontrol.getSelection
wait 2000
aRecordsDeleted=oFormmodel.deleterows(aSelectedRecords)
oGridcontrol.Select(array()) ' De-select all records

print ubound(aRecordsDeleted)+1 ,"Records deleted"
end sub 

[ksd]

Am running LibreOffice 6.4.4.2/WIN10, and the troop13DB.odb does not work. But if you replace the subroutine subAddEnrollment with the replacement sub (in above post) it does work wonderful. I'm searching to obtain the value of text string in a tablecontrol and had given up. In troop13DB.odb the Form is presenting tables, not gridcontrols (i.e. there's no sort, add etc. toolbar for the table). So I thought it would not help me, however MRI indicates exactly the same results in troop13DB.odb as in my own odb! Now I have to figure out the magic you're doing. I wanted everyone to know your code works fine in the latest version of LO. Sweet.