Combo Box that updates list table

[oldnilene]

I have yet to completely comprehend the functions of a combobox. I would like to have a box that has 3 functions: displays the names stored on a list table, add names to this list table when a new name is entered in the box, and updates my destination table. Can a combo box do this? As far as I can tell a combo box functions a lot like a list box but selects a name from your list as you type it in. Can anyone clarify for me?

[Safway]

Here is what someone else said somewhere else. I wrote it down because I had the same question as you.

List boxes map numeric values to strings, so you can pick a string in order to write a number into the form's data source. Before you can add something else than the listbox has to offer, you need to edit another table and refresh your form in order to pick the new item from the list.

When using a combo box you do not want a many-to-many relation, since you want to put arbitrary text values directly into the main table. A combo box accepts any string while offering some auto-completion functionality from other given strings. There is no mapping between two fields since it is impossible to map a brand new item to any existing ID.

[MusX]

if you have some sql knowledge you could write a trigger to insert new name to listbox's source table on inserting row on main table

[Villeroy]

Right, a list box lets the user pick (or type) some name and the box selects the corresponding foreign key into the linked field.
First the other table's primary key has to be stored before this primary key can be used as another table's foreign key. It would be a violation of referential integrity otherwise.

Like all forms, subforms and form controls in this very, very simplistic tool set, the list box offers stored values only. You can add some new entry in the other table, store it to disk, switch back to the other rows set and refresh the list box only. For this purpose the form's navigation toolbar has 2 refresh buttons. The second one refreshes is enabled when a list control is focussed and it allows to refresh only this control without changing the current row. Reloading the entire form refreshes the list control as well but may take some time and selects the first record, throwing you off the current work flow.
And yes, a combo box is a far simplier control which lets you type any value you like, offering some suggestions from a list. This has nothing to do with edits across table boundaries (well, the suggestions may come from another table but combos disregard referential integrity).

Quite often it would be not suffice to store a missing name on the fly. A person is usually identified by a surname, forname and birthday. Simply adding a missing name to the referenced table would break any table of persons where all of these attributes are mandatory (not null).

Leaving behind the misleading and almost useless form design wizard, you may notice that a Writer document (or any other ODF doc) can take as many forms with subforms as you need.

http://user.services.openoffice.org/en/ ... hp?id=2879 [contains a form for the movies a subform for the related genres of the selected movie and a third input form where you can enter a new genre name, store it to disk, switch back to the subform, refresh the list box control within the table control and assign the newly added genre.
In this very, very simplistic database tool set you are better off when you fill the lists before mapping list items to each other (genres to movies, persons to clubs, articles to invoices and vice versa...)

[Igor]

Villeroy, your example is exactly what I am looking for: how to refetch newly added data into the list box within one and the same form. Unfortunately I couldn't find any script ("macro") that drives the buttons in the movies_3 Base document. Can you please show me the listing. Thanks anyway, now at least do I know it is possible.

[Villeroy]

Villeroy, your example is exactly what I am looking for: how to refetch newly added data into the list box within one and the same form. Unfortunately I couldn't find any script ("macro") that drives the buttons in the movies_3 Base document. Can you please show me the listing. Thanks anyway, now at least do I know it is possible.

As already stated, you are better off when you fill out your lists before you edit the relations between your lists.
In my example file, the tiny 3rd form to add new genres needs a macro to refresh the other form's list content. I don't know the macro code but I think this is exactly the reason why the user interface for a form (toolbar "Form Design") has a second refresh button. The second refresh button refreshes a selected list box or combo box leaving alone the rest of the currently edited record. I use to use this button in the rare cases when I had to enter a new item while editing the relation.
Please notice that version 3.2 is broken in a way that it does not support the tiny extra form in my example file.
http://www.openoffice.org/issues/show_bug.cgi?id=109549
Forms limited to "add data only" raise some error "No data availlable ..."

[Igor]

Thanks for the quick answer. The form I am working on fits perfectly into the use scheme you laid in your example, including the buttons, because it is the natural way for people working with data. The new film is the first one of its genre the user is entering into her base, so she doesn't have this genre yet. Why distracting her with closing the form, opening the table, entering the genre, reopening the form and applying the genre? Then again, for every button on the navigation bar there is or there should be a method or property of RowSet service that supplies the same funcionality. It is fairly reasonable to assume that everything you can do with GUI can be done with API as well. Until now I was able to find applicable methods and properties, but not this one. It doesn't seem to be documented in Developers Guide either, or maybe I am not able to find it there. I tried with reloading the form, the document, refreshing the Tables collection, but nothing works. Examining the form in Basic IDE I noticed its ResultSetType already is SCROLL_SENSITIVE, so it should "see" the changes made to the bound table.

[Villeroy]

Why distracting her with closing the form, opening the table, entering the genre, reopening the form and applying the genre?

This is impractical indeed. There is no reason to close the form assuming the every computer user is able to handle more than one window.
Reloading the entire form resets the row set cursor to the first row. You would lose the record position you were working on.
This is why there is a second reload button on the toolbar. Focus the box and hit the second one. It keeps the entire form alive just updating the focussed box.

Edit: Therefore the button in my example document labeled "Refesh Subform" is not really useful. I would not implement it like this. This is one of my first example files derived from Movies_2.odb by Drew Jensen.

It is extremely easy to find all the properties and methods if you are familiar with basic ("Basic" not necessarily, there are better languages) programming concepts like events, structs, methods and properties in an object oriented API.
http://extensions.services.openoffice.org/en/node/201
Just install the MRI extension and assign a form event "after record change" to a test routine and inspect the passed event struct.

I assign MRILib.Module1.Mri direictly to the "after record change" of the "Genres" form in my example file, browse through the object hierarchy, drag up the bottom border of the MRI window and get the following Basic code recorded by MRI (other programming languages are supported as well):

Code: Select all

Sub Snippet( Optional oInitialTarget )
REM we get oInitialTarget is the event struct with only one element Source
  Dim oSource As Object
  Dim oParent As Object
  Dim oObj1 As Object
  Dim oObj2 As Object
  Dim oObj3 As Object
  Dim oObj4 As Object

REM Source is the calling obj (the form)
  oSource = oInitialTarget.Source
REM its parent is the Forms container you see in the form navigator tool:
  oParent = oSource.Parent

REM the container has one element MainForm
  oObj1 = oParent.getByName( "MainForm" )
REM with all the controls and an element SubForm
  oObj2 = oObj1.getByName( "SubForm" )
REM SubForm has a grid control
  oObj3 = oObj2.getByName( "SubForm_Grid" )
REM the grid has 2 elements, one of which is the list box named "GenreID"
  oObj4 = oObj3.getByName( "GenreID" )

REM finally we can find method refresh() for this control
  oObj4.refresh()
End Sub

I did not write a single line of code just added the REM comments.

[Villeroy]

Adding screenshots.

Form desgin mode with form navigator, form "Genres" selected and its properties with MRI assignment.

After turning off design mode, double-clicking the "Source" entry and dragging up MRI's bottom sub-window.

The recorded macro can be copied into the Base document. After replacing the event call to MRI with a call to the recorded snippet and reloading the form you can enter new genres which appear in the upper-right genres selection.

[Igor]

Thank you very much Villeroy, you solved my problem. Not that I really need butons. Actually I am using only one to trigger a dialog for adding new record. Everything else goes on "automatically", from the users point of view of course, now including the list boxes refreshment thanks to you. I was using MRI before, but never paid attention to generated code. I guess this is the "when nothing else helps, read the manual" case. Thank you once again. As a sign of gratitude I'll do my best to solve my future problems myself.