Listbox functionality in table control

Creating and using forms
Post Reply
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Listbox functionality in table control

Post by gkick »

Hi all,

Need some guidance on listboxes. Happen to have a number of forms all of which make use of listboxes associated with lookup or reference tables aka Title, Country, Status etc.
As there are many, creating multiple forms for maintenance is not very practical.
Have been searching the forum for updateable listboxes and have come across a couple of great sample dbs which are very close to my requirement.
One uses some hidden control, a default button and if the user hits enter or presses the button the new entry is stored in the underlying table and the listbox shows a blank.

Is it somehow possible to modify this functionality to let the user overtype the existing entry and upon pressing enter or tab move to the nextcontrol while the previous listbox actually shows the freshly created item being part of the current record.
A button is no good because most of the listboxes are part of table controls.
I hope the narrative makes sense, thank you.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Listbox functionality in table control

Post by Villeroy »

viewtopic.php?f=21&t=88831 is a macro which refreshes any specified form control(s) after a record has been modified, deleted or inserted.
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
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Listbox functionality in table control

Post by RoryOF »

I cannot answer for LibreOffice, but this may be worth trying: in /OpenOffice /Tools /Macros /OpenOffice Basic, in the Tools library is a collection of Listbox macros which may be of assistance. A similar path may be used in LibreOffice.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Listbox functionality in table control

Post by gkick »

Thank you Villeroy, however I think it needs more then a refresh like in your stampexample which moves to a new row after updating the source table with a new record. And I do not know how to reference the listbox as tablecontrols do not know what on them ???
@RoryOF thanks, will have a look.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Listbox functionality in table control

Post by Villeroy »

It refreshes any form, combo box and list box including boxes embedded in table controls.
Refreshing this form's subform "Subform" and a parent form's list box "lstClients" within table control "TableControl": Subform;../TableConrol/lstClients
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
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Listbox functionality in table control

Post by gkick »

Thanks, so all I need is the macro, no need to select the new entry afterwards as its saved with PK and stays resident on the tablecontrol as part of the tables current record?
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Listbox functionality in table control

Post by Villeroy »

Whenever you save/delete/insert a form's record, with or without movement, the macro will be triggered if the form is bound to the form's event "After record action" . The triggered macro refreshes the forms and controls specified in a hidden control named "AutoRefresh". Multiple elements are semicolon separated.
You need a hidden control "AutoRefresh" and you have to specify the form/list/combo box correctly by its case sensitive, hierarchical path name where ../ refers to the parent form. The parent may be the abstract mother of all forms displayed in the forms navigator as the "Forms" node. From the view point of a top level form ../Other_Form refers to another top level form named "Other_Form" (from the calling form one level up to the "Forms" container and then down to element "Other_Form")
If the macro throws any error messages, you either called it by an inadequate event or the calling form lacks the hidden "AutoRefresh" control or the specified element does not exist or the specified element is not refreshable (not a form nor list nor combo box).
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
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Listbox functionality in table control

Post by gkick »

@Villeroy thanks for the explanations,
managed to create a dummy form with a couple of listboxes, added the macro, added a hidden control, whats missing are the refs.
However I did not find any hidden control in the movies 5.db on any form under navigator to look up the example of refs.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Listbox functionality in table control

Post by gkick »

Got it, looked up the values in the stamp db, assigned the macro to the right event, values in line with the hierarchy of the navigator, no error msg and can not overtype existing entry, just looping through the source table instead.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Listbox functionality in table control

Post by gkick »

Ok, just process of elimination, I think its the last option you mentioned, the listbox is not updateable because it stores the pk of the lookup table in the tblContacts.
Changed it to a combo, can overtype, new item is added but on lost focus the combo displays the value of the pk,
removed the pk from the sql source, same thing because the tblContacts still has the fk
changed record source to table, same thing as the combo has no bound column property,
removed the profession field integer from contacts and added new text field instead, no go
Apart from that dont think its a good idea to change all the fields to varchar. Is there another way such as perhaps a separate combo or listbox at the top of the form and change the record source property to the whatever selected listbox of the sub? Apart from that what are the implications of performance dealing with a dozen lookups? Thanks for your time and help anyway

cheers
GK
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Listbox functionality in table control

Post by UnklDonald418 »

I believe I know the source of your problem. You said
listbox is not updateable because it stores the pk of the lookup table in the tblContacts
The purpose of a listbox is to store a primary key value from one table in a foreign key field of a different table, so I don't think that is the problem.
It appears you are using a query as the data source for the table control. For the listbox selection to be updateable in tblContacts, the query must include the primary key field from tblContacts and there must also be a column in the table for that field. It can be a hidden column, but it must exist so that the database engine knows which row in tblContacts is to be updated.
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
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Listbox functionality in table control

Post by gkick »

Thanks will give it a shot
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Listbox functionality in table control

Post by Villeroy »

I think, UnklDonald418 hit the nail. Forms are editable if the underlying row set comes from a single table including the primary key. Exception: dBase tables don't require a primary key.
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
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Listbox functionality in table control

Post by gkick »

Noops, changed the datasource and the listbox does not budge, however if I use a combo instead, its editable and just overwrites the existing value. Never mind, that one goes on the backburner. Thanks anyway.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Listbox functionality in table control

Post by UnklDonald418 »

There must be something else going on there because if you can store a Combobox selection/entry in a table you should also be able to store a Listbox selection in that same table.
A Combobox stores a string in a CHAR or VARCHAR field and it allows the editing or adding new entries to the list.
A Listbox stores a numeric value (Boundfield) in an INTEGER field and it does NOT allow editing or adding new entries to the list. A separate form is required to edit the contents of a table referenced in a Listbox query.
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
gkick
Posts: 324
Joined: Wed Aug 07, 2019 5:24 pm
Location: Chile

Re: Listbox functionality in table control

Post by gkick »

Thank you, that's it! There is no other form, only 6 table controls with each having list boxes. Might experiment a little more next week adding another invisible form.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Post Reply