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
t...@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+h...@global.libreoffice.org
In case of problems unsubscribing, write to postmas...@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