Adding a Search Control to a Form

From NeoWiki

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.

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.

1. 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)
2. In the Alias row of the StateOrProvince column, type State
3. Click the Save icon on the toolbar, or hit Command-S
4. 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.

1. Right-click or control-click on the State column heading and choose Column... from the contextual menu.
2. In the General section of the Properties: Text Box window, replace StateOrProvince with State
3. Close the Properties: Text Box window.

Create the Macro

1. 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.
2. Under the Macro from section, click on the flippy triangle next to All Sightings
3. Click on the flippy triangle next to the folder named Standard under the All Sightings entry
4. If there is no module in the Standard folder, click on the New button.

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

5. Click on the Search Macros module
6. In the Macro Name, type SearchByState
7. Click the New button. The AllSightings.Standard NeoOffice Basic window opens
8. After the existing End Sub entry, paste the following code:

   Sub SearchByState dim oFilter as object dim oFormCtl as object oFormCtl = ThisComponent.Drawpage.Forms.getByName("Standard") oFilter = oFormCtl.getByName("StateSearchListBox") if oFilter.CurrentValue <> "" then oFormCtl.Filter = "State LIKE " + "'"+oFilter.CurrentValue+"'" oFormCtl.ApplyFilter = True else oFormCtl.ApplyFilter = False end if oFormCtl.Reload End Sub

9. Click on the Save icon (the hard drive) on the toolbar
10. Close the AllSightings.Standard NeoOffice Basic window.

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

1. If the Form Controls toolbar is not visible, go to the View menu and choose Toolbars and then Form Controls.
2. Click on the Combo Box button on the toolbar
3. On the form, drag to create a combo box in the space between the title and the table.
4. Click on the Label field button on the toolbar (the ABC button).
5. Drag on the form, near the combo box to create a label field
6. Double click on the label field you just created. The Properties: Label Field window opens.
7. In the General tab, in the Label field, type Search by State:.
8. Double click the combo box you created. The Properties: Combo Box
9. In the General tab, in the Name field type StateSearchComboBox
10. In the Data tab, make the following changes:
    1. Make sure the Data Field field is empty
    2. Set the Type of List Contents to Sql
    3. In the List Content enter the following statement:

       SELECT DISTINCT "State" FROM "All Sightings"

11. In the Events tab, click the ... button to the right of the empty field in the Text modified line. The Assign Action window opens.
12. Make sure that the Text Modified event is highlighted
13. Click the Macro button. The Macro Selector window opens.
14. In the Library section, click on the flippy triangle next to All Sightings
15. Click on the flippy triangle next to the Standard folder under All Sightings.
16. Click on the Search Macros folder. A list of macros appears in the Macro Name section.
17. In the Macro Name list, select SearchByState
18. Click OK in the Macro Selector window and again in the Assign Action window.
19. Close the Properties: Combo Box window
20. Turn off Design mode by clicking on the blue drafting triangle on the form controls toolbar
21. Set the newly created combo box to one of the State options, and verify that the form filters correctly.
22. To clear filtering, delete the entry in the combo box.

External Links

oooforum.org thread on this subject This thread is the source of the macro used above.