[Solved] Change form image from listbox content

Creating and using forms
Post Reply
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

[Solved] Change form image from listbox content

Post by Maax555 »

Villeroy wrote:Works perfectly well without coding. Just keep the file names in a VARCHAR field and bind a picture control to that field.
I want an image on a form to change according to the content of a listbox. Basically a customer logo will be shown when a customer is chosen from a listbox on the form.
I have a table with CustomerID, CustomerName and Logo fields. I have a form for entering the details. I have added a Logo (image field) to my main table.

I have added the field into the form by choosing add field and bringing in the customer logo field from main form.
I cant see a way to link this to the customer listbox. Should the image control be on a subform?

thanks
Last edited by Maax555 on Mon Aug 06, 2018 5:26 pm, edited 1 time in total.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Loading images onto forms

Post by Villeroy »

http://forum.openoffice.org/en/forum/do ... hp?id=3751

Just like any other control for data entry, a listbox enters some value into some table's field.
If you want to enter arbitrary filter criteria into a listbox, combo, text, date, time, numeric control and then filter some other record set by these entries, you either have to follow its "nature" and bind it to some table's field OR you can use the built-in form filter. The built-in form filter switches your form into another mode where you can enter arbitrary filter criteria and when you leave that mode, you get a filtered form.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Loading images onto forms

Post by Maax555 »

Villeroy wrote:http://forum.openoffice.org/en/forum/do ... hp?id=3751

Just like any other control for data entry, a listbox enters some value into some table's field.
If you want to enter arbitrary filter criteria into a listbox, combo, text, date, time, numeric control and then filter some other record set by these entries, you either have to follow its "nature" and bind it to some table's field OR you can use the built-in form filter. The built-in form filter switches your form into another mode where you can enter arbitrary filter criteria and when you leave that mode, you get a filtered form.
Thanks, i had actually downloaded the example you mentioned but was not sure if this was the best way to achieve my goal.
Also will require an automatic refresh as it makes no sense for me to have an extra process of pressing a button just to update a graphic which is there for aesthetics only. I will study the example and try to make apply it to my DB.

thanks again.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Change form image from listbox content

Post by Villeroy »

You already have 2 refresh solutions at least. However, one additional [OK] button does not harm. The "best" solution is always the one without macro code until you are sure that you need to click that nasty button too often.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Change form image from listbox content

Post by Maax555 »

:-) Still struggling to implement the above example into my existing form.
So far I have a Customer form where i can add the image i require. This looks to be working ok as when i go back into same form and scroll through the records the images update accordingly. Do I need to have the image control and refresh on a subform and have the content type set to SQL command with "SELECT * FROM "CustomerImageFilter" WHERE "CustomerID"=0" as the content?
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Change form image from listbox content

Post by Villeroy »

Filter table taking a date range, 2 integers and a text with max. 20 characters:
Tools>SQL...

Code: Select all

CREATE TABLE "Filter"(D1 DATE, D2 DATE, INT1 INTEGER, INT2 INTEGER, TXT VARCHAR(20), FID TINYINT PRIMARY KEY);
INSERT INTO "Filter" VALUES(NULL,NULL,NULL,NULL, NULL,0);
[Execute], [Close]
menu:View>Refresh Tables.
Now you should see a new table "Filter" with one record with primary FID=0 and 5 blank criteria values.

Create a new form linked to source type SQL with source: SELECT * FROM "Filter" WHERE "FID"=0
The following form properties are important too:
Allow modification: YES (we want to edit the distinct record)
Allow insertion: NO (otherwise the user can tab into a new record below the distinct record which might have happened to you)
Allow deletion: NO (we want to keep the distinct record)
Navigation toolbar: NO (we only have one distinct record)

Let's start with one list box for a single integer value and disregard the other 4 criteria fields.
Linked field: INT1
Source type: SQL
Source: SELECT "SomeText" AS "Visible", "PrimaryKey" FROM "CustomerTable" ORDER BY "Visible" (assuming a "CustomerTable" with some text and primary key)
Add 2 push buttons with actions "Save record" and "Undo data entry"
Now you should be able to enter a listbox value at record #0, field "INT1" of your filter table.

Add a subform with no master/slave field:
Source type: SQL
Source: SELECT * FROM "YourTable","Filter" WHERE ("YourTable"."CustomerID"="Filter"."INT1" OR "Filter"."INT1" IS NULL) AND "Filter"."FID"=0
This selects all the records of your table where some aleged "CustomerID" in "YourTable" equals the filter INT1 at the record with FID #0. In case of a missing value (when "Filter"."INT1" IS NULL) the expression returns True anyway and all records will be returned.
Add one refresh button or use the one on the navigation tool bar.

Test the form. Since the fitered subform's refresh button takes the focus away from the filtering parent form, you may omit the save button. Taking away the focus stores a modified record anyway.

You will notice that your data form is read-only. This is because the source involves 2 merged tables, "YourTable" and "Filter". Record sets are writable if they include all mandatory fields, including the primary key, of a single table. If you need to edit the filtered data, edit the data source of the subform:
SELECT * FROM "YourTable" WHERE ("YourTable"."CustomerID"= :paramCustomerID OR :paramCustomerID IS NULL)
Same as above but instead of referring to the filter record, it introduce a parameter :paramCustomerID (leading colon, no quotes, no spaces in the name).
We can substitute this variable with a parent form's value:
Master field: INT1
Slave field: parameterCustomerID (name without leading colon)

---------------------
For the next form with filter criteria just add a record with FID=1 to the filter table and use the fields you need or append new fields or use another filter table if you like.
The simple concept is that you first fill out a form with all filter criteria before you hit a push button to refresh the filtered result set. Any auto-refresh macro may be disturbing when you have multiple criteria and a large table (many thousands of rows) since every refresh may take a second or two.

--------------------
P.S.2: Why is this so complicated? Because it is not a built-in feature. When I saw all the awful variants of macro solutions some 10 years ago, I realized that the given tool set of form controls, listboxes and subforms provide everything you need but you have to provide some data storage because subforms only work with parent form values that are stored in some table. Simply give to your criteria values a home in a table and they will serve as filter criteria. If you do it right, this is a fool proof concept that does not require any macro coding.
Last edited by Villeroy on Fri Jul 27, 2018 7:56 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Change form image from listbox content

Post by Villeroy »

Whenever I need to filter by one criterion, I do the following:
Focus the (sub-) form in question.
Hit the form filter button on the navigation toolbar. A "filter navigator" and an additional toolbar pops up.
Select the criterion from a list box or enter it into some other control (date, time, number, text, expression LIKE *office* with wildcards).
You can use the filter navigator to compose complex sets of filter criteria. Contrary to the above filter form, this built-in but awkward tool set allows you to compose sets of criteria freely.
Switch back to normal data mode and enjoy the filtered form. On the navigation bar you can turn a set filter on and off and you can delete a filter.

In this context you may apprechiate an additional feature for repetetive values in some field. You can set data property "Filter proposal" for a text box and some other types of form controls. If you do so, the box will be shown as a listbox filled with unique values to select from.

The filter with the "magic stick" icon on the navigation toolbar is self explaining. Select some value and push this button. Select some additional value and push it again.
-----------------
The very last button on the navigation toolbar shows a tabular copy of your current form in a docked window. The filter button in that window lets you enter filter criteria in a dialog analog to Calc's standard filter. <Some Field> <Operator> <Criterion> from combo box.
Last edited by Villeroy on Fri Jul 27, 2018 7:31 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Change form image from listbox content

Post by Villeroy »

While you have opened the form for editing (editing the form design), you can toggle between design mode and data entry mode for testing.
Any applied sort order or filter is stored with the form design. See form properties "Filter" and "Sort". The user can toggle this on and off and the user can delete these preconfigured settings. But they will be active with every reload of the form since the user can not edit the form's model. This may be intended or not. Watch out.

This means, you can configure fixed filters and sort orders in the data source, for instance records of the past 100 days with latest records on top: SELECT ... WHERE DateDiff('days',"DateField", CURRENT_DATE) <= 100 ORDER BY "DateField" DESC. This SQL can not be overridden without editing the form model in design mode.
You can configure a filter form where the user can choose among given preconfigured options. This requires an additional table to store the filter criteria and the above outlined form/subform construct.
You can configure preset filter and sort criteria that can be overridden by the user.
Finally, the user may apply arbitrary filter/sort criteria as outlined in my second post (built-in form filter, dialog based filter, magic stick).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Change form image from listbox content

Post by Maax555 »

Apologies for delay as i have not had a chance to return to this. Hope to go over you extensive advice this afternoon. Thanks again.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Change form image from listbox content

Post by Maax555 »

Villeroy wrote:Filter table taking a date range, 2 integers and a text with max. 20 characters:
Tools>SQL...

Code: Select all

CREATE TABLE "Filter"(D1 DATE, D2 DATE, INT1 INTEGER, INT2 INTEGER, TXT VARCHAR(20), FID TINYINT PRIMARY KEY);
INSERT INTO "Filter" VALUES(NULL,NULL,NULL,NULL, NULL,0);
[Execute], [Close]
menu:View>Refresh Tables.
Now you should see a new table "Filter" with one record with primary FID=0 and 5 blank criteria values.

Create a new form linked to source type SQL with source: SELECT * FROM "Filter" WHERE "FID"=0
The following form properties are important too:
Allow modification: YES (we want to edit the distinct record)
Allow insertion: NO (otherwise the user can tab into a new record below the distinct record which might have happened to you)
Allow deletion: NO (we want to keep the distinct record)
Navigation toolbar: NO (we only have one distinct record)
So please bear with me as I am used to creating forms with wizard from tables but not modifying the source type.
So i created a form using the wizard and filter table and brought in all the fields for now as can delete if not required. I made the following changes below, am I correct so far?
FORM1.JPG
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Change form image from listbox content

Post by Maax555 »

I have read through the details kindly posted by Villeroy above, i cant help feel that this is the answer to another question?
I cant see any relation to bringing in an image based on customer listbox in the above.
If this does indeed relate to updating an image then it looks massively complex for something that seems so basic?
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Change form image from listbox content

Post by UnklDonald418 »

Actually, it's not that complicated. Try this demonstration database. Due to the embedded images it is too large to post directly on the forum.
http://www.mediafire.com/file/5i6hbbd9d ... s.odb/file

On the FilterTest form document when you select a name using the List Box control it stores the ID for the selected name in the INT1 field of the Filter table. The Filter table that Villeroy gave you can be used for multiple purposes, but here we only need the INT1 field.
Press the Diplay Selection button and the SubForm is refreshed, displaying the data from the CUSTWIMG table whose ID field matches the one stored in the INT1 field of the Filter table.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Change form image from listbox content

Post by Villeroy »

One more thing regarding the built-in form filter which is not too bad:
Open the CUST_Entry form for editng.
Get the data properties of the last name text field and turn on "filter suggesion"
Do the same with the first name text box.
Now you have 2 filtering list boxes for free when you turn on the form filter mode.
-------------
A regular listbox control in normal operation mode does not take any filter arguments. Its only purpose is writing one value into its linked field.
However, values stored in main forms do filter records in subforms. If this is so, you only need to write a listbox value to some linked field in an arbitrary dummy table and show the filtered data in a subform. The filter table just stores the listbox value, so the subform can work. The restrictions (no new record, no deletion) prevent that any user deletes the filter record or tabs from the single filter record into the next record which would be a new record with absolutely no matching subform records. A nice side effect of storing filter criteria: other forms and reports can refer to the same filter criteria.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Change form image from listbox content

Post by Maax555 »

Firstly many thanks for the continued support, I am sure it must get a little frustrating at time when guys like me can't grasp things :-(
If it was not for the likes of you guys supporting on these forums then myself and many others would have probably given up and dismissed OO/LO long ago.
So looking at the example Demo30 i can see how this works. Two issues for me are,
1) The INT1 listbox is on a mainform with other fields on SUb. I already have all my fields on mainform. I am unsure if i need to move some fields to to a subform and if other dependant fields will still function correctly?
2) This uses a refresh button to update the image, as this is purely an aesthetics thing on the form I did not want to add an additional button which is only changing an image.
I do have a refresh Macro already running from the Customer listbox so that may well work for the image refresh also?
I will experiment and see what I can achieve.

Thanks again guys.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Change form image from listbox content

Post by Villeroy »

You can drag&drop elements on the form navigator.

First make it work without macro. Then you may decide if the refresh is one click too many or not.
1) Right-click the main form in navigator and add a hidden control, name it AutoRefresh and type in the subform name as value (case sensitive). A hidden control is a place for configuration data of macro coders.
2) Install download/file.php?id=35500
3) Point the form's "After Record Action" event to the macro.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Change form image from listbox content

Post by UnklDonald418 »

I already have all my fields on mainform. I am unsure if i need to move some fields to to a subform and if other dependant fields will still function correctly?
The Form Navigator allows you to make the needed changes without changing any of the controls on MainForm.
Open the Form Navigator and at the very top of the Navigator dialog right click on Form . Select Form to add a new Form to the list. While not entirely necessary, from a maintanence standpoint it would be better if you rename is to something like FilterForm.
Still on the Form Navigator drag your MainForm onto the new FilterForm. Technically it becomes a subform, but since MainForm is just a name, you can leave it.
Right click on FilterForm and select Properties to open the Form Properties dialog. On the Data tab select Filter as the Content type and edit the other properties as needed to ensure the integrity of table Filter.
On the Form Navigator select MainForm to change to the Form Properties dialog for MainForm and on the Data tab of the Form Properties dialog, set the Link master fields and Link slave fields.
Select FilterForm again and now add the ListBox control to your form document.
You can use a refresh macro to replace the button, probably on the Changed event on the list box control. The macro will need to refresh MainForm.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Change form image from listbox content

Post by Maax555 »

So trying to follow the above best I can.
Select FilterForm again and now add the ListBox control to your form document.
Unsure what to do with this instruction as I already have a Customer Listbox which i am wanting to use to change selected customer.

I have added link to what i have so far, I can usually follow instructions and know I doing things correctly as well as have an idea of how they are working.
On this occasion I have tried to follow instruction until last part mentioned above but I fear I am not executing the instructions correctly.


https://www.dropbox.com/s/e6rh1bkg50x4d ... 2.odb?dl=0
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Change form image from listbox content

Post by UnklDonald418 »

It appears that form document Partnumber2 is where you are trying to display the logos.
On that form document the Filter table isn’t needed, a simple SubForm will suffice.
I had some issues trying to modify the version you uploaded so it might be easier to start with a working version of Partnumber2, before you attempted to add the Filter table.
Using the Form Navigator add a SubForm to MainForm, name it SubFormLogo.
On the Form Properties dialog for SubFormLogo, set Content to the "Customers" table.
Both the Link Master and Link Slave fields will be "CustomerID"
As a precaution, set Allow additions, Allow modifications, Allow deflections and Navigation Bar all to No.
Now on the form document, if you already have an image control, drag it onto SubFormLogo, otherwise add one.
On the Data tab of the Properties: Image Control dialog select CustomerLogo as the Data field.
Exit the Design Mode and move through your part numbers, when it encounters a part number with an assigned Customer that has a Logo stored in the Customers table, the logo should now display.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Change form image from listbox content

Post by Maax555 »

UnklDonald418 wrote:It appears that form document Partnumber2 is where you are trying to display the logos.
On that form document the Filter table isn’t needed, a simple SubForm will suffice.
I had some issues trying to modify the version you uploaded so it might be easier to start with a working version of Partnumber2, before you attempted to add the Filter table.
Using the Form Navigator add a SubForm to MainForm, name it SubFormLogo.
On the Form Properties dialog for SubFormLogo, set Content to the "Customers" table.
Both the Link Master and Link Slave fields will be "CustomerID"
As a precaution, set Allow additions, Allow modifications, Allow deflections and Navigation Bar all to No.
Now on the form document, if you already have an image control, drag it onto SubFormLogo, otherwise add one.
On the Data tab of the Properties: Image Control dialog select CustomerLogo as the Data field.
Exit the Design Mode and move through your part numbers, when it encounters a part number with an assigned Customer that has a Logo stored in the Customers table, the logo should now display.
The above works :-), many thanks. I thought i had actually already done this without it working but obviously not as your clear instructions made it very easy to enter and understand.

The last minor issue is that I had to add a refresh button to make the logo update if the form. For my better understanding, as the refresh button is on the SubFormLog form I assume when pressed it only refreshes fields on that particular form?
So if i am to look at converting a refresh macro it should only refresh the SubFormLogo form?

thanks again.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Change form image from listbox content

Post by UnklDonald418 »

So if i am to look at converting a refresh macro it should only refresh the SubFormLogo form?
Yes, but you must first save the change made to the list box value.
The following should work when using the Changed event of the listbox control.

Code: Select all

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

Sub RefreshLogo(oEvent as Object)

oControl = oEvent.Source
oModel = oControl.Model
' Save the change to CustomerID on MainForm
oModel.Commit()
' get the SubForm 
oForm = oModel.Parent.getByName("LogoForm")
'reload/refresh SubForm
oForm.reload

End Sub
Can you see there is no need to store the customer logo in the Partnumber table? If a customer changed their logo you would have to locate and change it for every one of their part numbers.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Change form image from listbox content

Post by Maax555 »

Hi,
Yes, but you must first save the change made to the list box value.
The following should work when using the Changed event of the listbox control.
Excellent, worked like a charm. Not only that, because I now know that only the particular subform you need to refresh should be considered in the macro rather than the mainform it was easy to apply your exact same macro to the refresh status section. So now I have removed that refresh button and the whole form is starting to come together.
Can you see there is no need to store the customer logo in the Partnumber table? If a customer changed their logo you would have to locate and change it for every one of their part numbers.
Yes. I have removed this field from PartNumber table. I put it there originally when i was experimenting.

Once again many thanks.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Post Reply