OpenOffice.org Help TNT: Tips'n'Tricks that somehow didn't make it to the help (yet).

In the previous two blogs, first you've learned about Form Controls, where you created a Push Button http://blogs.sun.com/oootnt/entry/exploring_hidden_secrets_push_buttons, and then you utilized the built-in wizards to click your way to a DVD Collection database:  http://blogs.sun.com/oootnt/entry/exploring_hidden_secrets_creating_a.

May be you followed the steps using your own copy of OpenOffice.org or StarOffice. Then you might have entered some data records to your new DVD collection database.

In this blog, you can see how to create a Writer text document with some database related form controls.

The Writer document is designed to show your database records. Why not just use the ready made Base form? Well, one advantage of a Writer document over a Base form is the full control about layout and formatting, using the well known Writer menus and icons. Another advantage or disadvantage (this depends on your use cases) is the fact that the Base form is always an integrated part of the complete Base file, while the Writer document is an external document that just opens a link to the connected database.

This is the mydvds.odb Base file to start from. We already did enter some data records.

1. Choose File - New - Text Document to open a new Writer document.

2. Enter a heading and some other text as you like.

3. Choose View - Toolbars - Form Controls to open the Form Controls toolbar.

4. On the Form Controls toolbar, click the More Control icon to open the More Controls toolbar.

5. On the More Controls toolbar, click the Table Control icon.

6. Drag a rectangle of the size and position of the new table where your database records will be shown.

   The Table Element Wizard opens.

In the Table Element Wizard, select your data source and the Base table to be used.

Click Next for the next page of the wizard.

Double-click the fields that should be shown as columns in the table element. Then click Finish.

Remember that you can click the Design Mode On/Off icon on the Form Controls toolbar to switch from design mode to data entry mode and back.

Found issues: The ReleaseYear field obviously accepts date values in the default date format only. I did enter years like 1999, 2000, 2008, but they got converted to some unwanted values. Or my data did not show up at all, because I clicked the "Next Record" button instead of the "New Record" button when I did enter the records (?). I don't know and this blog is not a bug hunting blog, so let's pretend there are years in the ReleaseYear fields.

In design mode you can control the properties of the table element. A double-click opens the Control Properties window. But the more interesting window is the Form Properties window. Right-click the table element and choose Form.

On the Data tab page you can see that the Table Element Wizard did fill in the correct values for Data Source, Content Type, and Content.

Now let's add two text boxes to display the database fields of the record that is selected in the table element.

1. Switch to design mode (the Design Mode On/Off icon must be enabled)

2. On the Form Controls toolbar, click the Text Box icon.

3. Drag a rectangle where you want to show the first text box.

4. Double-click the new text box to open its Control Properties window.

On the Data tab page, select the Data Field that should be shown in this text box.

Next, switch from design mode to data entry mode and test your new Writer form document.

Do not forget to save the Writer document.

When you close the Writer form document and open it again, it will be ready in data entry mode.

In the previous blog about "hidden secrets" inside the OpenOffice.org software you've learned how easy it is to create push buttons. In the blog following this one, you will see how to add controls to your documents that display the contents of database fields.

Here and now we could need a database that is more than just a basic example. For users of OpenOffice.org, nice and useful database templates are already installed. You just rub the menu to summon a wizard that is waiting for you for eons to unveil the next hidden secret.

To create a new DVD collection database

1. Choose File - New - Database.

   You see the Database Wizard.

2. 
   Click Next>> to continue to page 2 of the Database Wizard.

3. 
   Check "Create tables using the table wizard". Then click Finish.

4. Enter a name for the new OpenOffice.org Base file, for example mydvds, and save the file.

   You see the opened Base file, but wait, something happens automatically:

5. The Table Wizard opens.

6. 
   Click the Personal radio button. Select DVD-Collection from the list box. Then click the >> button to move all available fields to the right. This instructs the wizard to add all available fields to the new table.

7. Click Next> twice to continue to the "Set primary key" page.

8. 
   Check the "Auto value" box. Then click Next>.

   In OpenOffice.org Base, every table needs a primary key. A primary key is a field with unique contents for every data record. "Auto value" inserts a unique value into that field for every new record.

9. On the final page of the Table Wizard, click "Create a form based on this table".

   
   

10. Click Finish. Next you see the Form Wizard.

    
    

11. Click the >> button to move all available fields to the right. Then select the ID field and click the < button to move that field back to the left.

    The ID field is the automatically filled primary key field. This field must be present in the table, but it needs not be shown in a form.

12. Click Next> twice to advance to the "Arrange controls" page.

    
    

13. Click the second of the four big icons under "Arrangement of the main form".

    The reason is that I personally prefer this choice from the four arrangements.

14. The remaining pages of the Form Wizard can be used for some fine-tuning, if you want so. I'm sorry for the choice of colors on the "Apply styles" page. Click Finish any time to create the form.

The ready-made DVD-Collection form looks like this now:

You see two columns with all selected fields in alphabetical order of their labels.

The form is saved automatically to the new Base file mydvds.

The form shows "(read-only)" in the title bar. That is a sign that you can enter the data records now. If you rather want to edit the form layout, you can do so from the Base file that is still open in the background.

• Right-click the DVD-Collection entry and choose Edit if you want to edit the form layout.
• You can change the form properties, for example, the size and position of the fields, or the background color.
• In the Base file, double-click the name of your form, or click Open, to open the form in data input mode.
• Enter some data records. The new or changed data is stored automatically once you go to another data record.

Now we have a database ready for the next blog. In the next blog we will learn how to create form controls inside a Writer document, and this time the form controls will be connected to some database fields. That's the plan, don't know yet if that's possible at all - stay tuned to this blog.

Many hidden treasures are slumbering beyond the menus of OOo. If you only scan the menu bar for those features you're searching for, you'll find at most 10% of them.

Well, you can argue the exact percentage, but I'm quite sure that you will miss a lot if you never explore beyond the obvious menu commands.

• Some dialogs and wizards will only show up on some actions, as with drag-and-drop from Calc to Base.

• Some cool features are only part of context menus, like the Arrange, Align, Flip, and Convert commands in Impress.

• And some useful things can be inserted into your documents, but they never show up as commands in the Insert menu. Such are the Push Buttons and other Form Controls.

Let's take an intuitive beginner's approach to explore the power of Form Controls. Or to scratch the surface of Push Buttons.

To insert an action button to open another document

You want to add a new button to your document. When you click that button, OOo will start some action, for example, open a certain document in addition to the current document. I chose this example because this is a built-in action, so it's an easy first start.

1. Open a new Writer text document. Optionally type some text.

2. Choose View - Toolbars - Form Controls.

   You see the Form Controls toolbar. As with most other toolbars, you can drag the toolbar to other places, dock and undock it to the document borders, and reshape it to show more rows or more columns of icons. And you can click the down arrow in the title bar to open a menu. The Visible Buttons command shows a nice overview of the available icons with their names.

3. 
   On the Form Controls toolbar, click the Push Button icon. (Do not click the Push Button command in the Visible Buttons menu: this would hide the icon from the toolbar!)

4. Drag a rectangle of the position and size of the push button, then release the mouse button.

5. Right-click the new push button and choose Control from the context menu. This opens one of the two main properties windows.

6. 
   Click the Action list button and select "Open document/web page" from the list.

7. The URL text field is now enabled. Click the … button next to the URL text field to select the document that should be opened when a user clicks the button.

That's it. You have created your first action push button. However, we are not finished yet.

• Currently the button is labeled "Button". Select the text in the Label text field and enter a more descriptive text, like "Open Memo Doc".

Currently the document is set to design mode. When you click the button, it gets eight handles to resize the button, and you can change the button's properties. In order to use the push button, you must leave the design mode. There are two ways to leave the design mode:

• Click the Design Mode On/Off icon on the Form Controls toolbar. You can immediately test the actions of your form controls. Don't forget to save your document before you test the actions. May be the action is defined to quit the current document.

• Save the document and open it again. By default a document with form controls is opened in "action mode", not in design mode. You can change this, of course.

To create an action button to close the current document

Now you know how to create a working push button. However, not all actions are listed in the Actions list box. If you want to define the action to close the current document, you would need a macro that does just that. Then you assign that macro to the push button.

Intuitively, you might want to use the built-in macro recorder of OpenOffice.org. But that is not a good idea when you try to record the "Close this document" action, because that also closes the macro recorder.

So the next choice is to search the web for "OpenOffice.org macro to close the current document". Some minutes later, you find this command:

StarDesktop.CurrentComponent.CurrentController.Frame.close( true )

1. In your web browser or wherever you found the macro, select the macro and copy it to the clipboard.

2. Open the Writer document where you want to create a Close push button.

3. Choose Tools - Macros - Organize Macros - OpenOffice.org Basic.

   This opens the Macros dialog.

4. Click Edit.

   This opens the Basic IDE (Integrated Development Environment).

5. Paste the macro command between the Sub and End Sub statements as follows. Rename the Sub Main to another meaningful name like Sub close_current:

6. 
   Close the Basic IDE. OOo saves your macro automatically.

Next, you create a push button as outlined above in steps 1 to 5.

1. Right-click the button and choose Control to open the Properties window.

2. Click the Events tab on top of the Properties window.

   You see the Events tab page, where you can assign macros to all possible button events.

3. 
   Normally, a button responds to a mouse click when you release the mouse button. So you assign the close_current macro to that event. Click the … icon next to "Mouse button released".

4. You see the Assign Action dialog. Click the Macro button on top right.

5. You see the Macro Selector dialog. Click to open the path "My Macros - Standard - Module1". Now you can select the close_current macro and click OK.

Almost finished. You have written the close_current macro in the Basic IDE, then you have assigned the macro to the "release mouse button" event of your own push button. Not bad for a beginner!

Again, you may want to change the Label of the button to some meaningful text like "Close this doc".

And do not forget to save the document before you try out the Close button in action mode.