OO-Snippets: filter data from combo-box
Commons
| Keywords | Combo Box, Filter, Find, Form, Event, Dynamic, Persistant |
|---|---|
| Language | OOBasic |
| Application | Office |
| Authors | Sasa Kelecevic Oliver Brinzing Tom Schindl |
| Supported Versions | |
| Supported OS | |
| Question | How to filter data in a form by selecting value in combo box. |
| Answer | In oOO1.1 you can do this only with macro.This small wizard insert a macro in document library and assign macro to combo box event - Item status changed. Install In Basic IDE create new library ( ComboFilter ). Insert module ( Module1 ). Copy and paste code below in module. Use Open the form in Design Mode. Link the form with a database. On form create combo box with ComboBoxWizard.Follow the directions in the wizard dialog boxes. Run a small wizard Tools | Macros | Macro - ComboFilter - Module 1 ? Start_Combo_Wizard. If you want to see the code open document library ? ComboName_Module - ComboNameFilter. Thanks to Oliver Brinzing. |
Code-Snippet-Listing (snippet-source)
'**************** Filter form with ComboBox ************************ Dim sTableName , sDot , sFieldName , sLike ,sCriteria , sEnd as string Dim sSource , sCode , sMacro , sModuleName as string Dim oControl , oForm , oLib as object Dim oEvents(0) as New com.sun.star.script.ScriptEventDescriptor Dim iPos as integer Sub Start_Combo_Wizard REM Save ThisComponent.Store REM define control , form oControl = thiscomponent.getCurrentSelection.getByIndex(0).getControl oForm = oControl.getParent REM table name and field field mane sSource = oControl.ListSource sSource = Mid(sSource,17,) iPos = InStr(sSource,"FROM") sField = Mid(sSource,1,iPos - 1,) sField = Mid(Trim(sField),2,) sField = Mid(sField,1,Len(sField)-1) sTable = Right(sSource,Len(sSource)-iPos - 3) sTable = Mid(Trim(sTable),2,) sTable = Mid(sTable,1,Len(sTable)-1) REM filter sTableName = "chr(34) &" & chr(34) & sTable & chr(34) & " & chr(34)" sDot = " & " & """.""" & " & " sFieldName = "chr(34) &" & chr(34) & sField & chr(34) & " & chr(34)" sLike = " & " & chr(34) & " LIKE '" & chr(34) & " & " sCriteria = " oControl.text & " sEnd = chr(34) & "'" & chr(34) call InsertModule End Sub REM insert new module in document library Sub InsertModule oLib = ThisComponent.BasicLibraries.getByName("Standard") sModuleName = oControl.Name & "_Module" call BasicCode If oLib.hasByName(sModuleName) Then iReturn = MsgBox("The module already exists. Overwrite?",4 + 256,"" ) If iReturn = 6 Then oLib.removeByName(sModuleName) oLib.insertByName(sModuleName,sCode) ElseIf iReturn = 7 Then Exit Sub End If Else oLib.insertByName(sModuleName,sCode) End If REM Save ThisComponent.Store call ComboBoxEvent End Sub REM event and macro Sub ComboBoxEvent sMacro = "document:Standard." & sModuleName & "." & oControl.Name & "Filter" oEvents(0).ListenerType = "XItemListener" oEvents(0).EventMethod = "itemStateChanged" oEvents(0).AddListenerParam = "" oEvents(0).ScriptType = "StarBasic" oEvents(0).ScriptCode = sMacro For I = 0 to oForm.Count-1 If oControl.Name = oForm.getByIndex(I).Name Then Exit For EndIf Next I oForm.revokeScriptEvents(I) oForm.registerScriptEvents(I, oEvents()) REM Save ThisComponent.Store End sub REM code in inserted module Sub BasicCode newline = chr(10) sCode = sCode & " Sub " & oControl.Name & "Filter(oEvent)" & newline sCode = sCode & " oControl = oEvent.Source.Model" & newline & newline sCode = sCode & " If oControl.Name = " & chr(34) & oControl.Name & chr(34) & " Then" & newline sCode = sCode & " oForm = oControl.Parent" & newline sFormFilter = sTableName & sDot & sFieldName & sLike & sCriteria & sEnd sCode = sCode & " sFormFilter = " & sFormFilter sCode = sCode & newline sCode = sCode & " oForm.Filter = " & "sFormFilter" & newline sCode = sCode & " oForm.ApplyFilter = TRUE" & newline sCode = sCode & " oForm.ReLoad" & newline sCode = sCode & " Else " & newline sCode = sCode & " ' nothing " & newline sCode = sCode & " End If " & newline & newline sCode = sCode & " End Sub" & newline End Sub |
Changelog
| Date | User | Modification |
|---|---|---|
| 2004-06-22 | tomsontom | Modified to match new snippet-DTD |
| 0000-00-00 | sasa | Initial version |
