AUTHOR'S MAIN SITE   > > > > >   TABLE OF CONTENTS for version 2 Open Office database tutorials.

Open Office 2 Base (database) Tutorials
Introduction to Using Forms

(With a digression covering cloning databases.)

You may find that the database being shipped with OpenOffice (ver.2) delights you as much as it has me. This page, and the others linked to it, can help you use it.

Forget any experiences you may have had with Adabas, which came with Star Office, the commercial version of Open Office 1. The Open Office Version 2 database, "Base", aka ooBase, is unrelated. And remember that Open Office, including Base, is free! But don't let that fool you. It's not new. Big organizations, civil and governmental, are adopting it as their standard office suite... and saving million$.

There's more about ooBase in the main index to this material.

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window.

Page contents © TK Boyd, Sheepdog Software ®, 2/06.


What it's all about, Alfie...

In theory.. and it is a good theory... you should interact with the tables in your database via forms. That's not to say that users never cheat, never work directly with a table, but doing so is Not A Good Idea. It is very easy to set up a form which looks very like the ordinary view of your table, if that's what you want. And many other good things become possible when you know how to make forms. And some bad things are avoided.

In this tutorial, we're going to look again at the simple mailing addresses database that we set up in the introductory tutorial "A simple table of names and addresses". In that tutorial, we created a database called FDB004. You could just return to that and extend it, I would suggest you set up a new database, or clone the old one. We're going to cover a lot of ground in respect of what you can do via forms. Once the "how" is out of the way in the course of this (tedious?) tutorial, the stage will be set for more interesting future tutorials illustrating why we would want some of the functions.

Along the way to setting up some forms, we're also going to look at cloning a database.

To clone a database

Why would you want to?

To create a backup copy. Or perhaps to use an existing database as a starting point, without risking the integrity of the existing database.

The bad news: You can't just copy the old database! Well, you can... that will capture the essence of the old database... but the new one will need to be "registered" with Open Office.

Using ordinary file management tools.... That's the easy and obvious part done. Now....

Start up Base.

Use the "Open existing database" option, making use of the "Open" button, with which you can browse your backing store. Double click on FDB004.odb when you've drilled down to it.

Already you can look at a table, but you also need to "register" the database with Open Office. I'm not absolutely certain when or why or where you will have problems if you don't... but I'm reasonably sure you will!

To register the database, click on ooBase's menu item Tools, and then on Options

One of the top level sections is OpenOffice.org Base. In front of that, there may be a + sign. If there is, click it. Once you've done that you will see subsections. Click on Databases. The window that opens has a New button. Click it. Browse to the database you are trying to register, assign a sensible name, e.g. FDB007, click Okay. (ooBase will protect you from giving the same name to two databases.) Done! Registered!

(Just before we move on: Deleting files from the list of registered databases does not remove the underlying files on your hard disc. Nor does removing a file take that database off the list of registered databases.)

(Another aside: You may sometimes encounter weird "File cannot be deleted" events. Be sure ooBase is shut down when copying, moving, renaming, deleting database files, and it may pay also to exit the Quickstarter, which will reveal itself in the System Tray (lower right) if it is active.)

There is no "run time" version of ooBase. If you want to access the data on a different computer, install all of ooBase.

Onward!

We now have a copy of the addresses database. Let's see how forms work....

Start ooBase; open FDB007, select "Forms" in the "Database" pane of the program manager. Double-click on the task "Use Wizard to create form...." (Note that you can check which step you are on in what follows if you look in the left hand pane of the wizard's window.)

Step 1: Use the >> button to move all of the "Available Fields" to the "Fields in Form" list. Then select the field called ID, and move it back to the Available list with the < button. (The "ID" field has its uses, but you don't need to see it, and can't edit it. Yes: You could have populated the "Fields in Form" list differently.) Click Next.

Step 2: Skip over Step 2 by clicking Next. We don't need sub-forms. Not adding a sub-form will cause steps 3 & 4 to be skipped, too.

Step 5: Select for Arrangement of the main form the layout that looks like a spreadsheet... the third option, the one that is the simplest... just a rectangle of contiguous rectangles. You'll see "Data Sheet" as a description of the layout when you have the right one selected. Click Next.

Step 6: Select "Form is to display all data", but do not tick any of the "Do not allow..." boxes. Do take a moment, though to imagine the different circumstances which would benefit from one or another of the alternatives. Remember that you can have many tables available for acting on a single form. You can even have several like the one we are creating, differing only in their modify/ delete rights. Having the multiple forms would meet the needs of a situation where there were several users of a database, users with different responsibilities and authorities. Click Next.

Step 7: Just go with the default, unless you really, really want to "play" with the options. Click Next.

Step 8: Call this the "Basic Table Form", or "Data Entry Form". (Not Basic Table View Form, as "view" has a special meaning in database work.) Leave the "Work with the form" selection unchanged.

Click "Finish"!

Give the program a moment, and a window should come up with your table displayed looking a bit like a spreadsheet, much as it looked when you accessed the table directly.

Note that you are looking at an ooWriter document. Cool. Note also the "Read Only" in the title bar. This refers to the layout of the form. (We'll fix the size and position of the table and its window in a moment.) As long as you proceeded as advised above, you should find that you can change, add or delete records in the database. A record is saved to disc as soon as you move out of that record. If you try to close the form with unsaved information, you will be asked if you intended to discard the last record edit, and given a chance to save it.

To delete one or more records: Select it/ them by clicking in the empty rectangle(s) just to the left of the left-most field. Then either use the red X in the toolbar at the bottom of the window, or right click over one of the empty rectangle(s) just to the left of the left-most field in the selection, and click "Delete Row(s)", the only option in the right-click menu.

Note that across the bottom of the spreadsheet-like table of data there are "VCR" style controls which let you move around the database.

If you change the number in the "Record [number] of [number]" edit box, as soon as you press enter, you will go to that record.

Now we'll change the size of the table, and the initial state of the window in which it opens.

Close the ooWriter window displaying the form. In the ooBase project manager window, right-click on your form's name, "Basic Table Form", if you took my suggestion. You should get a number of choices: Copy, Delete, Rename, Edit and Open. If you only get one or two of those, then be sure you haven't got the form open already. If that doesn't prove to be the case, close ooBase, exit the Quickstarter, reopen ooBase. If that doesn't fix things, close all open applications, re-start Windows, try again. Isn't necessary often, but the need used to arise once in a while. I had the "edit not available" problem when I was doing some things wrong.

Once "Edit" is an option, select it.

You'll see your form again, but now you can do things to it, and the things on it. Click on a corner of the grid which displays your data. The grid should acquire 8 small green squares, drag handles. (If you don't get them on your first try, click again in a slightly different place.) You can use them to re-size the grid. You can re-position the grid.

Once you have the grid at a sensible size, in a sensible place, adjust the size and position of the window that it is in. Save the form. (File | Save, or use the icon.) Close the window with the form in it. Once again, double-click on the form's name in the ooBase project manager window to re-open it, and it should come up in the right place, with the data sheet as you wanted it.

Re-arranging column order....

It is possible, but sometimes not easy, to rearrange the order of fields in a table. But it is not necessary. If you use forms to work with your data, rearranging the order the fields are listed is really easy.

Open the form for edit. Drag one of the column headings left or right. You should see an arrow before you release the mouse button, and when you release the mouse button, the column you were dragging should appear in the place you asked for it to occupy. Easy!

Getting clever...

Just so that you know that you can, not that you're likely to need to, re-open the form for editing, right click on the grid, and select "Control" from the menu. The resulting properties editor has two tabs... you should be on the "general" tab. Within that you can alter both the row height and the font... of the data you are displaying. I couldn't find a way to change the font of the column headings, and, annoyingly, when you go to edit a datum, it temporarily reverts to the small font!

Once you have opened a form for editing, you can flip back and forth between editing and testing with the button on the bottom of the window which features an orange set-square under a horizontal white ruler, and a green pencil. It's tool tip says "Design Mode On/Off". If you can't find it, use the window's main menu View | Toolbars item to be sure that the "Form Design" toolbar is ticked for display.

Play with the form a little bit, to get the idea of what you can do, but don't try to make it perfect.

Note the icon in the bottom of the window, near the Design Mode On/Off, which gives you access to the form's properties. (The grid must be selected to enable the "Form" (properties) icon. The second tab, "Data" has some useful items on it. (The form's properties are also available from the same right click menu that gave us access to the grid's properties.)

Remember that the form is "just" a ooWriter document, albeit with some fancy "stuff" on it... In the design mode, don't be shy about putting some ordinary stuff on the form, outside of the table of data, for example a title.

Lets' leave the topic of the form's design for a bit, and look at using features of the form. Take yourself out of design mode, if that's what you're in. The grid should fill with data.

Generally speaking, I prefer to work with the main menu bar of any application. With ooBase, to work with the data in the grid on your form, you do most things with the grid's toolbar buttons, which I sometimes refer to as icons. They are across the bottom of the window. When the form is not in design mode. The toolbar they are on is called the "form navigation bar". Well... the toolbar starts there. You can move it. (Put pointer on left end, where you get four headed arrow. Drag. One advantage of dragging a toolbar off the edge of the window is tat the toolbar's name appears in a title bar. "Stuck to the edge of the window" is called "Docked". If you lose a toolbar, use the main menu item "View", select the Toolbars option, the toolbar that is missing should be unticked. (If ticked, look again... its there somewhere!) Click on it. The menu window should close and the toolbar should reappear.) I'm not going to explain all of the buttons on each toolbar... experiment, but be sure to try the following, at least....

Things you can do....

Insert a new record. Beware: If you have the shift key down when you hit the space bar, "funny" (but unwelcome!) things happen. New records will be inserted at the bottom of the table, but can be moved by sorting, which we'll come to. The icon is a green right-pointing triangle with a yellow "new" star. Tool tip: "New Record".

Sort the data. For something simple, like sorting it according to the "Last Name" in each record: a) select a cell with a Last Name in it. Click the sort it icon: And A above a Z, with a red arrow beside it. Tool tip: Sort Ascending. (Note: You cannot sort if you have selected the whole of a column. Select just one field in one record.) (Also note: You are sorting the records as displayed on your form. The underlying table is left untouched.)

For a complex sort, say Last Name first, and sort the Smiths out by first name, e.g. Adam first, Zara last, click on the sorting icon that consists just of an A above a Z.

Once you have done a sort, or a filter (we'll come to filters in a moment), if you add records, they will be at the bottom, not in their proper place. Click the "Refresh" icon- the blue semi-circular arrow. (The Refresh button is even more important in a multi-user environment.) The data will only re-sort if you have not clicked the "Remove Filter/Sort" icon: The funnel with the red X on it. (When no filter or sort has been established, the red X is gray.) Removing a sort is generally pointless. Re-sorting, with different criteria might make sense. Removing a sort (pointless) will have the possibly unwelcome side effect of removing any stored filter rule, even if the rule is not currently in action.

Filters. These can be a little scary at first. To see one in action, first enter at least ten records into the database, making sure that you have two and only two people called Brown. Also have someone called Browning.

Select the LastName field of one of you Browns. Click on the "AutoFilter" button. It is a funnel with a diagonal blue line and an arc of blue stars. (A "magic" wand). Help! All but two of your records are "gone"! No they aren't. ooBase is filtering what you see, showing only the record which match the one you had selected when you clicked "AutoFilter". It isn't so much the filtering that is automatic, as the setting up of the filtering rules. After AutoFilter creates some rules, it applies them, true.

The button with a simple funnel on it is not like most of the others. It isn't a "click here to do...." button. It is an "on/ off" button. (The "Design Mode On/Off" button on the form design toolbar was another.) After using the AutoFilter button, you will see the "Filter On/Off button has acquired a background, filtering will be turned off again, and your "missing" records return. Whew.

Your filter rule is still in the system. Click the simple funnel button and it will be re-applied.

If you click on the button with the funnel and red X, the filtering is turned off, and your filtering (and sorting) rules are erased.

Remember the complex sorts that were possible? Something similar is possible for filters, but I can't seem to get into the right dialog unless the form is in design mode... although I think (from the help file) that i should be able to get in. Anyway... with the form in design mode, right click on the data table, select "Form", go to the data tab, click the "..." to the right of the "Filter" edit box. You may well have "LastName" like 'Brown' in that at the moment. If you make it "LastName" like 'Brown*', your filter will show not only the line for Mr Brown, but also for Mr Browning, Brownlow, etc, etc.

The button with binoculars on it lets you search your data for specific records. Note that there is another button with binoculars on the windows main toolbar. This is for finding things in the other parts of the form, which may be mostly empty at the moment, but this won't always be the case.

In conclusion...

So! There you have it. You've seen how to create a Data Sheet style form for accessing the data in a table. (The other layouts allow you to see all of a big record easily, at the price of only being able to see one record's data at a time. See "Part Two" of "A Simple Form" if you want help.)

You're "an expert" (well, becoming one!) on the reasons for using a form, how to set one up, how to adapt it to your precise needs. In particular, you can present as many or as few of the records fields as you need, and in any order you prefer.

Along the way, you learned about cloning a database.

Good Job!

Editorial Philosophy

I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....

I am trying to present this material in a format which makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.

Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE

If you liked this ooBase tutorial, see the main index for information other help from the same author.

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.


Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org

One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .