Page 1 of 1

Listbox functionality in table control

PostPosted: Sun Sep 01, 2019 5:55 am
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.

Re: Listbox functionality in table control

PostPosted: Sun Sep 01, 2019 9:40 am
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.

Re: Listbox functionality in table control

PostPosted: Sun Sep 01, 2019 9:50 am
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.

Re: Listbox functionality in table control

PostPosted: Sun Sep 01, 2019 10:07 am
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.

Re: Listbox functionality in table control

PostPosted: Sun Sep 01, 2019 10:45 am
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

Re: Listbox functionality in table control

PostPosted: Sun Sep 01, 2019 11:54 am
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?

Re: Listbox functionality in table control

PostPosted: Sun Sep 01, 2019 12:31 pm
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).

Re: Listbox functionality in table control

PostPosted: Mon Sep 02, 2019 6:14 am
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.

Re: Listbox functionality in table control

PostPosted: Mon Sep 02, 2019 7:36 am
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.

Re: Listbox functionality in table control

PostPosted: Mon Sep 02, 2019 9:21 am
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

Re: Listbox functionality in table control

PostPosted: Mon Sep 02, 2019 9:59 pm
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.

Re: Listbox functionality in table control

PostPosted: Tue Sep 03, 2019 9:15 am
by gkick
Thanks will give it a shot

Re: Listbox functionality in table control

PostPosted: Tue Sep 03, 2019 11:55 am
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.

Re: Listbox functionality in table control

PostPosted: Thu Sep 05, 2019 6:45 am
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.

Re: Listbox functionality in table control

PostPosted: Thu Sep 05, 2019 6:38 pm
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.

Re: Listbox functionality in table control

PostPosted: Fri Sep 06, 2019 7:12 am
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.