Base - cannot update filtered form

[HippySteve]

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

Hello

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)
    oCtl.refresh()

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

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

Problem:

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.

BUT…

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 what I have got wrong here?

I have tried editing the “StudentMainLoad” macro to start on different students (i.e. not just the first entry) but the same thing happens. Also, if I click on the first student in the list before editing I get the error. It’s clear that something different is happening, I just can’t see what…

Searches reveal nothing more than that HY000 is apparently a CLI specific error???

SteveFormProblem.odb

Worth noting:

This is a learning project for me - there is a lot of unused ‘playing around’ code to be ignored as I am gaining an understanding of how UNO works!

There are three forms in the DB:

zfrm_StudtMain is my original form which worked BUT intermittently the listbox of students will grey out, refusing to repond to clicks. Maybe in a code loop somewhere, I put it down to my lack of understanding of what I was doing.

frm_StudentMain is the form I am referring to here.I created it in the form wizard then replaced the grid control with a listbox. From there I couldn’t get the links to work hence the code…

zqryStudentSummary is one that I knocked together this morning using a slightly different form model. It worked until I put in the list box which then either shows greyed out or the form doesn’t respond to it being clicked.

I’m sure this is something simple that I have done (or not done) due to my lack of experience with LO, but my testing shows that problems arise when I try to replace the grid control with a listbox…

[Ratslinger]

@HippySteve,

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.

Edit:

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

[HippySteve]

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!

[Ratslinger]

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

[Ratslinger]

Hello,

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:

All should work with this change.

[HippySteve]

@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…

[Ratslinger]

@HippySteve,

You are quite welcome. as for:

I am indebted to you

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