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
![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
