Re: [libreoffice-users] List box not updating field with bound value

Arrrgh! What a long, sad march through the wilderness this has been. 
Following what seemed like a most reasonable suggestion (which I 
wouldn't have needed had I been less exhausted when I posted 
originally),  I went searching in the OO forum archive (well, using 
Google, to be truthful, which isn't quite the best way, but I'm in a 
hurry) to find out why my list boxes weren't working, and what I could 
do to fix them.

In short, there's a lot of smoke out there, but damn little fire. 
Theorists abound, and some folks definitely found the long way round the 
mountain (and lost ME in the process). I just wanted to build a fire and 
cook some supper. More particularly, I wanted to place a number in an 
integer field in my main table which could be linked to a key in another 
table so that a data field in the record with the matching key can be 
made to appear in the main record, using relational database magic. 
Maybe one can do all sorts of other things with list boxes, but I'm not 
interested. I'm just trying to build a form that uses a table relation 
locate the value I want for my main table record, and put that value's 
key into a field in the main record. This is a basic, routine thing one 
does with relational databases. Because I do it somewhat less than 
daily, I need written instructions to keep me from shooting my foot.

Here's where you won't find out how to do it - the " Combo Box/List Box 
Wizard" article in the LO help file. I read that thing 3 times, and I've 
used list boxes in MS Access for years, but I still don't know what 
they're talking about. For technical writing real people can use, this 
one gets a "D'. My despair increased perceptibly after my encounter with 
this disastrous article.

I'll skip additional recounting of the misery I encountered trying to 
find some straightforward instructions about how to do what I wanted to 
do. Instead, I'll provide some:

GOAL: Using a form which has text boxes linked to fields in your main 
table, you want to be able to select values for some of the fields, 
using a drop down list box. The value selected will be placed in the 
main table indirectly, using a reference to another table which contains 
the possibilities available for the main table field. This reference is, 
of course, a record key field. Using this indirect reference, we keep 
the database "normal", which helps to minimize data entry error (which 
makes data retrieval harder, when it occurs).

DO THIS:

1. CREATE VIEWS. You will using a main table and one or more secondary 
tables, with the latter providing the list contents for the list box 
controls you'll be setting up in your main table. Since table field 
names aren't always human-friendly, and in any case may need to be 
changed in the future, create a view for each table, such that those 
fields which need them have "aliases" to make field labels more 
human-friendly (understandable). Since you're going to use view, and not 
tables, should you need to change these labels in the future, you need 
do it only once - in the view, rather than in each table in which the 
field appears.

2. CREATE A BASIC FORM. Use the form wizard to quickly make a form 
containing the fields from your main table which you care about. Use any 
of the templates you wish, as long as the result is that the desired 
record fields for a single record appear on the form. (This is the quick 
way to get things going.)

3. LOCATE THE FIELDS OF INTEREST. These would be those that at this 
point are text boxes linked to fields, but which you wish to be list 
boxes linked to main table fields. With EACH of them, execute the 
remaining steps in this list:

4. REMOVE THE TEXT BOX (ONLY). Use Ctrl+L-click to select the box, apart 
from its label, which you're going to keep. Delete the selected box.

5. CHECK TO SEE THAT "AUTOMATIC WIZARD" IS ON. (OK, I'm only 
approximating its name, 'cause I don't really know what it is.) You 
should have the "Form Controls" tool bar visible on the left side of 
your form designer window. (If it isn't, activate it through View > 
Toolbars.) Second icon from the bottom, you'll see a magic wand (I 
promised you relational database magic, right?), or maybe it's just a 
road flare. In any case, it should be depressed. When it is, and you 
create a list box, you'll get some wonderful help, and all your misery 
will cease. Believe me, I know.

6. CREATE A LIST BOX. Seven icons up from the bottom, on the same 
toolbar, is the list box icon. Click it, then move your mouse pointer 
into some blank area of your form. Click and move the mouse diagonally 
down, to make a box. Don't worry about it's shape, as it's not yet 
important. The wizard will appear, and THAT is. Using it, you will 
quickly and painless do 3 critical things:
    a. Pick the table that will provide list box contents.
    b. Pick the field to appear in the list box.
    c. Link [a] the field in the main record that is to hold the key of 
the record in the list box source table with [b] that key in the list 
box source table. (Obviously this is a critical step, so get it right. 
Mess up and you get to start over from the beginning.)

7. RESIZE, AND REPOSITION, THE LIST BOX. You probably want it the same 
height as the other, non-list box fields, and wide enough to hold 
whatever you will be putting into it. You can then move it into the 
place formerly occupied by the text box it replaces.

8. TUNE UP THE LIST BOX. Click on the box to get Properties dialog. Do this:
    a. Change "Dropdown" to yes. (This way, you'll actually get to SEE 
your list. So, why isn't this set by default? I have no idea.)
    b. Change "Line count" to equal the number of items from your 
source table which you want displayed in your list box.
    c. If you want to have the list box look like the text boxes on 
your form, change the "Background Color" to "10% gray", and "Border" to 
"3D look".

Before closing the properties dialog, click the Data tab, and look at 
what you did NOT have to do - all this is set up for you by the Wizard, 
which (unlike me) is likely to get it right. Look at the Events tab too. 
See? No macros either. Relational database magic (well, Base magic...). 
You're still as informed as you ever were about macro programming after 
this exercise, and have saved a ton of time. If you got the results I 
got, your list box just works. (OK, close the property dialog now).

So, my journey into Base continues. It seems rugged, feature rich, and 
just works, if you can figure things out. I really do like it, I'm very 
very grateful to have it access to it.

Yeah...we need to write some more documentation. I've formally begun my 
involvement with that issue. Looks like fun, and of course, a lot of work.

Hope this is useful. I know I'll personally be using it in the future.

Tom Cloyd, MS MA
tc@tomcloyd.com
(435) 272-3332
St. George/Cedar City, Utah

On 06/19/2011 03:07 AM, Tom Cloyd wrote:
> I know this is complicated, but I'll try to be simple and clear.
>
> I'm building a form that displays a single record at a time from a 
> table, as a set of text boxes. The form displays all data in the table 
> by moving successively to each record. Every field except the primary 
> key field can be edited.
>
> I have a list box bound to one of the fields. It displays a field from 
> another table (which has only a key field and a data field - text). 
> That table has only a few records. It's sole purpose is to contain the 
> list to be display in the list box.
>
> My intention is that selecting a value from the list box will cause 
> the field in the main table to be updated by the key of the record 
> containing the selected list item in the list table. That key is field 
> #1 in that table, so my bound value is 1.
>
> The list is displaying perfectly. However, in the form, the field to 
> which the list box is bound displays nothing, ever. It DOES initially 
> have a value, and certainly should after I make a list box selection. 
> It just never shows any value, however. Moreover, looking at the main 
> table's record outside of the form shows that it contains only "0", 
> regardless of which list item I select.
>
> So...
>
> 1. Why is no value ever displayed in the form, even though there IS a 
> value in the record? In MS Access, it would show, I believe.
> 2, Why is the selected list item's key never updated into the master 
> table field. It IS bound, according to the property dialog.
>
> I don't see my error, I'm afraid. Any suggestions would be appreciated.


--
Unsubscribe instructions: E-mail to users+help@global.libreoffice.org
In case of problems unsubscribing, write to postmaster@documentfoundation.org
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted