Adding a Search Control to a Form

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 02:54, 17 October 2007 (edit)
Lorinda (Talk | contribs)
(Add An Alias to the '''All Sightings''' Query - continue article)
← Previous diff
Current revision (18:03, 12 January 2009) (edit) (undo)
( | contribs)
(minor typos)
 
(13 intermediate revisions not shown.)
Line 11: Line 11:
#Close the Query #Close the Query
-==Adding the Control to the Form==+<br>
-#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)+==Working With the Form==
 +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===
 +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 {{Menu|Column...}} from the contextual menu. #Right-click or control-click on the '''State''' column heading and choose {{Menu|Column...}} from the contextual menu.
-#In the {{Section|Tab} of the {{Window|Properties: Text Box}} window, replace '''StateOrProvince''' with '''State'''+#In the {{Section|General}} section of the {{Window|Properties: Text Box}} window, replace '''StateOrProvince''' with '''State'''
#Close the {{Window|Properties: Text Box}} window. #Close the {{Window|Properties: Text Box}} window.
-#:'''Note''' the steps above 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.+ 
-#If the Form Controls toolbar is not visible, go to the {{Menu|View}} menu and choose {{Menu|Toolbars}} an then {{Menu|Form Controls}}.+===Create the Macro===
-#Click on the Combo Box form control on the toolbar+#With the '''All Sightings''' form open, go to the {{Menu|Tools}} menu, choose {{Menu|Macros}} then {{Menu|Organize Macros}} and finally {{Menu|NeoOffice Basic...}}. The {{Window|NeoOffice Basic Macros}} window opens.
-#On the form, drag to create a Combo Box in the space between the title and the table.+#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
 +#If there is no module in the {{prefName|Standard}} folder, click on the {{Button|New}} 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
 +#In the {{prefName|Macro Name}}, type <tt>SearchByState</tt>
 +#Click the {{Button|New}} button. The {{Window|AllSightings.Standard NeoOffice Basic}} window opens
 +#After the existing End Sub entry, paste the following code:
 +#:{{preBox|<nowiki>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</nowiki>}}
 +#Click on the {{Button|Save}} icon (the hard drive) on the toolbar
 +#Close the {{Window|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===
 +#If the Form Controls toolbar is not visible, go to the {{Menu|View}} menu and choose {{Menu|Toolbars}} and then {{Menu|Form Controls}}.
 +#Click on the {{Button|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 {{Button|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 {{Window|Properties: Label Field}} window opens.
 +#In the {{Section|General}} tab, in the {{prefName|Label}} field, type <tt>Search by State:</tt>.
 +#Double click the combo box you created. The {{Window|Properties: Combo Box}}
 +#In the {{Section|General}} tab, in the {{prefName|Name}} field type <tt>StateSearchComboBox</tt>
 +#In the {{Section|Data}} tab, make the following changes:
 +##Make sure the {{prefName|Data Field}} field is empty
 +##Set the {{prefName|Type of List Contents}} to Sql
 +##In the {{prefName|List Content}} enter the following statement:
 +##:{{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.
 +#Make sure that the {{prefName|Text Modified}} event is highlighted
 +#Click the {{Button|Macro}} button. The {{Window|Macro Selector}} window opens.
 +#In the {{Section|Library}} section, click on the flippy triangle next to {{prefName|All Sightings}}
 +#Click on the flippy triangle next to the {{prefName|Standard}} folder under {{prefName|All Sightings}}.
 +#Click on the {{prefName|Search Macros}} folder. A list of macros appears in the {{Section|Macro Name}} section.
 +#In the {{Section|Macro Name}} list, select {{prefName|SearchByState}}
 +#Click {{Button|OK}} in the {{Window|Macro Selector}} window and again in the {{Window|Assign Action}} window.
 +#Close the {{Window|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.
 +#To clear filtering, delete the entry in the combo box.
 + 
 +==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.
 +{{botlangbarEN|[[Fr:Ajouter un Contrôle de Tri à un Formulaire|Français]]}}

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