Adding a Search Control to a Form

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 16:40, 19 June 2008 (edit)
Lorinda (Talk | contribs)
m (Add the Control to the Form - correct formatting issue)
← Previous diff
Current revision (18:03, 12 January 2009) (edit) (undo)
( | contribs)
(minor typos)
 
(One intermediate revision not shown.)
Line 25: Line 25:
#Under the {{Section|Macro from}} section, click on the flippy triangle next to {{prefName|All Sightings}} #Under the {{Section|Macro from}} section, click on the flippy triangle next to {{prefName|All Sightings}}
#Click on the flippy triangle next to the folder named {{prefName|Standard}} under the {{prefName|All Sightings}} entry #Click on the flippy triangle next to the folder named {{prefName|Standard}} under the {{prefName|All Sightings}} entry
-#If no module in the {{prefName|Standard}} folder, click on the {{Button|New}} button.+#If there is no module in the {{prefName|Standard}} folder, click on the {{Button|New}} button.
-#:In the {{Window|New Modules}} Window, type a <tt>SearchMacros</tt> in the {{prefName|Name}} field and click {{Button|OK}} button.+#:In the {{Window|New Modules}} Window, type <tt>SearchMacros</tt> in the {{prefName|Name}} field and click {{Button|OK}} button.
#Click on the {{prefName|Search Macros}} module #Click on the {{prefName|Search Macros}} module
#In the {{prefName|Macro Name}}, type <tt>SearchByState</tt> #In the {{prefName|Macro Name}}, type <tt>SearchByState</tt>
Line 67: Line 67:
##Set the {{prefName|Type of List Contents}} to Sql ##Set the {{prefName|Type of List Contents}} to Sql
##In the {{prefName|List Content}} enter the following statement: ##In the {{prefName|List Content}} enter the following statement:
-##:{{preBox|SELECT DISTINCT "State" FROM "AllSightingsList"}}+##:{{preBox|SELECT DISTINCT "State" FROM "All Sightings"}}
#In the {{Section|Events}} tab, click the {{Button|...}} button to the right of the empty field in the {{prefName|Text modified}} line. The {{Window|Assign Action}} window opens. #In the {{Section|Events}} tab, click the {{Button|...}} button to the right of the empty field in the {{prefName|Text modified}} line. The {{Window|Assign Action}} window opens.
#Make sure that the {{prefName|Text Modified}} event is highlighted #Make sure that the {{prefName|Text Modified}} event is highlighted

Current revision

This article is part of the To Base and Back Again tutorial series.

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.

  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.

This article in other languages: Français
Personal tools