Hi :)
So, you have managed to solve the problem and got it all working? I think the
word is "triumphal" rather than "sad"?
Regards and apols from
Tom :)
________________________________
From: Tom Cloyd<tc@tomcloyd.com>
To: users@global.libreoffice.org
Sent: Tue, 21 June, 2011 9:59:44
Subject: 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