Ask Your Question

Base - cannot update filtered form

asked 2020-06-03 17:16:11 +0100

Hippy Steve gravatar image

updated 2020-06-04 11:44:47 +0100

LO, Ubuntu 18.04 with all updates. Split data from forms using Firebird back end.


I have a Base Form (Pic below) that shows a list of students in a listbox. It actually consists of :

  • frmStudentSelect - editable details, attached directly to table.
  • frmStudList - holds listbox "lstStudents" of student summary details. Fed by query against students table.
  • sfClasses - subform of frmStudlist, holds listbox "lstClasses" of summary of classes for currently selected student. Fed by SQL string.
  • Secret, a hidden form with a hidden control

Clicking on a specific student in "lstStudents" does 2 things - shows the classes linked to that student in "lstClasses" and also fills "frmStudentSelect" to allow editing. The code below, called by the "Item Status Changed" event of "lstStudents", handles the data retrieval and is activated when the user selects a student from "lstStudents".

sub StudentMainGetSelected(pEvent as objoct)
'###    Called from lstStudent item status changed event'
'###    when student is selected.'
'###    resets code in lstClasses to show classes for this student and'
'###    fills frmStudentSelect ready for edits'

dim oCtl, oFrm as object
dim sSQL as string

'###    set global var to selected student ID'
    oFrm = pEvent.source.model.parent
    gCurrentStudent = pEvent.source.model.getCurrentValue()
'###    now get classes for this student into listbox on subform'   
    oFrm = oFrm.getbyName("sfClasses")
    oCtl = oFrm.getByName("lstClasses")
    sSQL = "Select  ""ClassInfo"", ""StudentID"", ""ClassDate"" from qryClassLangDate where"
    sSQL = sSQL &" ""StudentID"" = " & gCurrentStudent & " ORDER BY ""ClassDate"" DESC"
    oCtl.listsource = array(sSQL)

'###  and finally fill the individual student detail'
    oFrm = pEvent.source.model.parent.parent.getbyname("frmStudentSelect")
    sSQL = " ""StudentID"" = " & gCurrentStudent 
    oFrm.Filter = sSQL
    oFrm.ApplyFilter = TRUE

end sub

The same code is run when the StudentMainLoad, called from the form "When Loading" event, macro runs as it sets the selected item of the listbox.

sub StudentMainLoad(pEvent as object)
'###    Called when loading frmStudList'
'###    Click into listbox to set first student'

dim oCtl, oFld as object

    oFld = pEvent.source.getByName("lstStudents")

    oCtl = ThisComponent.getCurrentController()

    oCtl.getControl(oFld).selectItemPos(1, TRUE)
    wait 100
end sub


When first loaded I can edit the fields in the Detail form (frmStudentSelect) as expected. As many edits as I like, any field, save edits and all is well.

Selecting another student from the listbox does the expected thing of showing the classes linked to that student and putting their details into the relevant place ready for editing.


as soon as I click into any of the fields in the detail form I get an error message "Error updating the current record". Clicking on 'More' informs me that there is an "SQL Status of HY000, No values were modified".

The searches I have done point to a lost DB connection but that cannot be the case, I am able to happily click away and see the relevant data for each student I click on. Just that I can't edit them after I have clicked the listbox to invoke tha same code that ran when the form loaded!

Any pointers as to ... (more)

edit retag flag offensive close merge delete


@Hippy Steve,

This is difficult at best when one needs to piece together what you may have in order to test where you problem lies. Instead of all this posting, a sample (scrubbed) is best. Even with noted split this can be done - zip & rename extension to .odb and note that in posting (zip files not allowed as an extension for download - no known reason).

With what I can see, don't understand the need to any code (except the class - could use table grid). Seems like simple table filtering for the list box and then linking forms and sub and/or sub sub forms.


I should also state I see nothing obviously incorrect. Have done filtering of this type in past without problems.

Ratslinger gravatar imageRatslinger ( 2020-06-03 20:23:43 +0100 )edit

Hello, @Ratslinger, I trust you are well :)

I've modified the post to include a link to a simplified db instead of images, along with a brief description of each of the forms in the package.

Thank you in advance for your time!

Hippy Steve gravatar imageHippy Steve ( 2020-06-04 11:22:22 +0100 )edit

Yes, all is OK here and I am well. Wish the same for you and all others around the world.

Ratslinger gravatar imageRatslinger ( 2020-06-04 18:42:06 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-06-04 18:14:55 +0100

Ratslinger gravatar image


Well without the sample don't know how I may have been able to answer this.

The problem is with your list box(es). These list boxes are only there to provide a list of elements and for the student list to make a selection. They are both attached to SQL and you have assigned a field to them and a binding. There is nothing to update or enter for these selections. The binding is not a problem but the Data field should not have an entry:

image description

All should work with this change.

edit flag offensive delete link more


@Ratslinger Once again, I am indebted to you. As I said, down to my lack of understanding of how these things hold together! With every read of OOME_3, etc, a few more pennies drop into place... Today I managed to do as you suggested, simply using subforms and no unnecessary code. A step closer to becoming an UNO geek!!

Thank you again for your time...

Hippy Steve gravatar imageHippy Steve ( 2020-06-04 18:44:58 +0100 )edit

@Hippy Steve,

You are quite welcome. as for:

I am indebted to you

No. Just pay it forward (as it seems you are)!

Ratslinger gravatar imageRatslinger ( 2020-06-04 19:05:29 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-06-03 17:16:11 +0100

Seen: 47 times

Last updated: Jun 04