Clearing form controls of previous selections on opening form

[chetd]

Hi
I’m on a laptop running Linux Mint 19.3 and LibreOffice 7.2.6.2
and on a desktop running Linux Mint 19.3 and LibreOffice 7.3.3.2
and I’m using Base for a split database with hsqldb set up using a split database wizard. I had an older version of hsqldb but just updated to 2.3.2

I’m building this database to try to learn about databases while organizing my mom’s old picture albums, and my problem is mostly just a minor annoyance.

I have a form in my database (or maybe the name in the Forms pane actually refers to a form container or something, and anybody with the appropriate terminology please speak up) which contains a main or parent form I named FilterForm where you can make several selections. Those selections change the values of a record in a table called tbl_Filter.

There is also a subForm I named DistinctPicsForm with several push buttons to Refresh, and go to Next and Prev records whose source is a query of various tables (or actually a query of another query running back eventually to some tables) using the criteria of the FilterForm selections.

There is a subForm of the DistinctPicsForm called PicsTableForm whose data source is the table of all the pictures including the path to where they are stored outside of the database and the only control on that form is an image control.

I don’t know that any of that will have any bearing on what my problem is, but I thought I would include it.

The actual problem. I was trying to find a way to clear out all the values in the filter table on opening the form. As it stood, when I opened the form, the filter options which include several list boxes and a bunch of check boxes would always show the values that had last been selected when the form was open before. The form worked fine, but that was annoying me. Any previous selections that were only going to be null this time had to actually be deselected to clear them.

I wrote some SQL to null out all the values in the filter table. I didn’t know how to add the SQL to a button, so I cribbed some code for a macro and assigned the macro to the when loading event of the filter form.

The macro worked to clear the values out of the filter table that is the data source for the form, but the form controls on the filter form still present as though certain selections have been made (but the pictures themselves in the image control are as they would be if completely unfiltered). Hitting the button to refresh the filter form does nothing to change the appearance of, for example, the list box selecting a person. It isn’t until I actually change a selection from what the control on the form looks like has been selected to a different selection and then hit refresh, do all of the other controls then go blank.

It would appear that there is something in the form controls themselves that needs to be cleared out or set to null.

Any suggestions ?

[cpb]

clearing the filter values via a button may prove more practical:

This Thread May Be A Good Place To Start

[chetd]

I haven’t spent enough time investigating this to be sure yet, but I think my issue started here.

While I said (indirectly) that the table “tbl_Filter” was the data source for the form “FilterForm”, the control on the form uses a drop down list populated by a query to give you options for your entry on the form.

I think some of my queries were written without giving care to the result set needing a null record as one of the possible choices in the drop down list. The macro I used to run SQL updating and setting the values in tbl_Filter to Null did half of the job I wanted.

Unfortunately, with the values in the table set to null and the options lists in the controls not containing that option, I was leaving the form controls sort of confused as to what to display.

It was late last night when I made some changes that seem to have the form controls clearing out after a simple refresh of the form after the macro runs automatically on opening the form, so now I’m wondering if I can add a simple line at the bottom of that macro to make doing the form refresh automatic and always open with a form completely free of any filters.

I didn’t include that original macro in the first post because it didn’t seem to be part of the problem, so here is that.

Sub	ExecuteClearFilterValuesSQL
	oForm = ThisComponent.Drawpage.Forms.getByName(FilterForm)
	oStatement = oForm.ActiveConnection.createStatement()
	sql = Update tbl_Filter Set AlbumID = null , DEcatID = null , DEsubID = null , PersonID = null , ParUnionID = null , PicID = null , ThingCatID = null , ThingSubID = null, Myself = null , Parent = null , Sibling = null , Grandparent = null , AuntUncle = null , Cousin = null , Spouse = null , Child = null , NieceNephew = null , Grandchild= null
	oStatement.executeUpdate(sql)
End Sub

Now I think that I’m looking for the language that would refresh the form after running that sql at the end of the above macro.

[Ratslinger]

Hello,
Just add to the end:

	oForm.Reload()