#Open the '''All Sightings''' Form for editing (In the main database document, click on {{prefName|Forms}} in the left hand column, then right-click or control-click on the '''All Sightings''' Query and choose {{Menu|Edit}} from the contextual menu)

+

Open the '''All Sightings''' Form for editing (In the main database document, click on {{prefName|Forms}} in the left hand column, then right-click or control-click on the '''All Sightings''' Query and choose {{Menu|Edit}} from the contextual menu)

===Correct the '''State''' Column in the Table===

===Correct the '''State''' Column in the Table===

Line 80:

Line 80:

#Set the newly created combo box to one of the State options, and verify that the form filters correctly.

#Set the newly created combo box to one of the State options, and verify that the form filters correctly.

#To clear filtering, delete the entry in the combo box.

#To clear filtering, delete the entry in the combo box.

-

==External Links==

==External Links==

[http://www.oooforum.org/forum/viewtopic.phtml?t=63240&sid=3b40ebbf8ab6c6de3f3960e0d401a617 oooforum.org thread on this subject] This thread is the source of the macro used above.

[http://www.oooforum.org/forum/viewtopic.phtml?t=63240&sid=3b40ebbf8ab6c6de3f3960e0d401a617 oooforum.org thread on this subject] This thread is the source of the macro used above.

Revision as of 12:36, 2 June 2008

In addition to using form-based filters, you can also create a control on a form that will allow the user to quickly filter the data according to the control setting. As an example of this, we will create a combo box on our All Sightings form to filter by the StateOrProvince field. The process for creating a search control with a text box or a list box is very similar.

Contents

Add An Alias to the All Sightings Query

In the process of testing this approach, I discovered that the field name StateOrProvince was problematic. The macro described later in this article read this name as two field names separated by the logical operator "OR", and threw an SQL error. Fortunately, this issue was easily solved by creating an alias for the field in question.

Open the All Sightings Query for editing (In the main database document, click on Queries in the left hand column, then right-click or control-click on the All Sightings Query and choose Edit from the contextual menu)

In the Alias row of the StateOrProvince column, type State

Click the Save icon on the toolbar, or hit Command-S

Close the Query

Working With the Form

Open the All Sightings Form for editing (In the main database document, click on Forms in the left hand column, then right-click or control-click on the All Sightings Query and choose Edit from the contextual menu)

Correct the State Column in the Table

The steps in this section are not normally part of creating a search control. They are necessary here because we created the Alias for the StateOrProvince field after the form was built.

Right-click or control-click on the State column heading and choose Column... from the contextual menu.

In the General section of the Properties: Text Box window, replace StateOrProvince with State

Close the Properties: Text Box window.

Create the Macro

With the All Sightings form open, go to the Tools menu, choose Macros then Organize Macros and finally NeoOffice Basic.... The NeoOffice Basic Macros window opens.

Under the Macro from section, click on the flippy triangle next to All Sightings

Click on the flippy triangle next to the folder named Standard under the All Sightings entry

If no module in the Standard folder, click on the New button.

In the New Modules Window, type a SearchMacros in the Name field and click OK button.

Click on the Search Macros module

In the Macro Name, type SearchByState

Click the New button. The AllSightings.Standard NeoOffice Basic window opens

Note Embedding the macro directly in the form has the advantage that if you move or copy the database file to another computer, the macro will still function. The disadvantage, however, is that you will be ask whether or not you want to enable macros in the form any time you open the form.

Add the Control to the Form

If the Form Controls toolbar is not visible, go to the View menu and choose Toolbars and then Form Controls.

Click on the Combo Box button on the toolbar

On the form, drag to create a combo box in the space between the title and the table.

Click on the Label field button on the toolbar (the ABC button).

Drag on the form, near the combo box to create a label field

Double click on the label field you just created. The Properties: Label Field window opens.

In the General tab, in the Label field, type Search by State:.

Double click the combo box you created. The Properties: Combo Box

In the General tab, in the Name field type <StateSearchComboBox

In the Data tab, make the following changes:

Make sure the Data Field field is empty

Set the Type of List Contents to Sql

In the List Content enter the following statement:

SELECT DISTINCT "State" FROM "AllSightingsList"

In the Events tab, click the ... button to the right of the empty field in the Text modified line. The Assign Action window opens.

Make sure that the Text Modified event is highlighted

Click the Macro button. The Macro Selector window opens.

In the Library section, click on the flippy triangle next to All Sightings

Click on the flippy triangle next to the Standard folder under All Sightings.

Click on the Search Macros folder. A list of macros appears in the Macro Name section.

In the Macro Name list, select SearchByState

Click OK in the Macro Selector window and again in the Assign Action window.

Close the Properties: Combo Box window

Turn off Design mode by clicking on the blue drafting triangle on the form controls toolbar

Set the newly created combo box to one of the State options, and verify that the form filters correctly.