Hi Chris, I ran my "very easy to integrate" library and form by a friend, just to get here opinion about the easy part and got the response - "Your kidding right?"..So between that feed back and another person asking the same question on the forum I decided instead to write what I hope is a concise explanation of how to do this. Now I am back to see if I can't make my "easily integrated utility form" a bit easier for my friend to use.
Setting a filter from a button click! Let's say you have a form, named "frmClients", with three components on it: A text box named "txtBox" A button named "btnApplyFilter" A table grid named "grdRecords" You want to limit the records displayed in the table grid based on the string entered into txtBox. Such that the only records displayed will all have a field that contains the exact string entered. This is referred to as filtering the result set. The filter will be applied when you click on btnApplyFilter. I said your form had three components. What I should have said is that it has three visible components, there is always at least one hidden component also, a dataform control. A dataform control has two main functions. It supplies access to a set of records from a query and it acts as a container for the other controls that will display or manipulate the data in these records. You will use both of those functions to achieve your goal here. Just like the visible controls have names, this dataform control has a name also. If you created your forum using the Form Wizard then it is named "MainForm". If you added it to a form in design mode it is named "Standard". ( nice little bit of confusion there ) Now look at your form again. Of the three controls only one is actually bound to the datasource of the form, grdRecords. But all three controls should be owned by ( conatinaed in ) the dataform control. A table grid control has no way to actually limit the records it displays, rather, it displayes all the records for the dataform control ( result set ) it is bound to. In other words then to achieve what you want you must filter the actual dataform control that the table grid is bound to. This is relatively easy, as a dataform control has two properties specifically for this. .Filter a string property where you enter the filter .ApplyFilter a boolean value that determines if the filter is used or not. Before going on a quick look at what the filter really is. As I said the dataform control has a result set of records supplied by a query. A query can be thought of as another term for a SQL Select statment. An SQL Select statement can be as simple as "SELECT * FROM tblClients". This statement will return all the records for the table "tblClients". You can also limit the records returned by a select statement with the use of a WHERE clause, such as: "SELECT * FROM tblClients WHERE ClientID = 1" Assuming the tblClients uses the field ClientID as a primary key and this fields data is unique then the above statement would return exactly 1 or 0 records, depending on whether a record has the value 1 in the field ClientID. When you set the Filter property of a dataform control you are actually adding the where clause to the query that supplies the records to that dataform control. Since this is the case then, you DO NOT supply the key word "WHERE" when you do so, this is added for you. In other words if the first query ( SQL select statement ) above was used to base the form on then the dataform control would have as its result set supplier: "SELECT * FROM tblClients" If you then set the Filter property to "CleintID = 1" you would have created the SQL statement "SELECT * FROM tblClients WHERE ClientID = 1". Alright then, you have just about all the information you need to setup the action you want on the form. 'Just about all the information', what you need to know now is how and where to respond to the action of clicking on the button. When you look at a Graphical User Interface the act of clicking on a button is referred to as an Event, and where you do the work that is to be performed when this Event happens is referred to as an Event Handler. In Base if you open a property editor for a control you will see that the dialog contains a tab for 'Events' and this tab has a row for each Event that can happen on the control. The row contains a name for the event and a place to enter the name of the Event Handler. This event handler is another name for a function or sub procedure in a code library. A code library is simply a collection of functions or sub procedures written in a particular scripting language and stored together in a single file. In this file these functions or procedures can be further grouped together into modules. One last bit of background information then - regarding events and event handlers. I said above that an controls have a set of Events, each of which has the possibility to call a custom Event Handler. That this Event Handler was a function or sub procedure in a script library. OpenOffice.org is designed such that whenever an Event Handler is called in response to an Event on a control there is a special parameter passed to the Event Handler. This is called an EventStructure, the contents of which can vary depending on the type of Event that caused the Event Handler to be called. One piece of information is always contained within this EventStructure, when working with control, is named "Source". Source is a reference to the actual control that initiated the Event Handler call. Well, now you do have all the background you need to 'just do it'. The first thing I would do then is to create the macros ( functions or sub procedures ) that are going to be needed. The reason I am doing this first is because you must have these scripts written before you can assign them as controls event handler. ( In competitors applications you can create this directly from the control property editor ) With an OpenOfrice.org window open I select: Tools>Macros>Organize Libraries This will open a dialog with 3 tabs. ( Modules, Dialogs, Libraries ) Select the tab "Modules" Every installation of OpenOffice.org has a library named "Standard". Select the Standard Library. Click on the button "New". This opens the New Module dialog where you give the module a name. Enter "ClientsForm" and click OK. ClientsForm is now the selected module, so click on Edit. This opens the Basic Editor, with the ClientsForm loaded and three lines of code automatically added for you: REM ***** BASIC ***** Sub Main End Sub Leave these lines alone. Move to the last line and enter 2 blank lines. Then add the following lines: sub onClickApplyFilter( oEventStructure as variant ) end sub This is a complete sub procedure, ready to be called by an Event on a control. At the moment it doesn't do anything but it does have one important item - a parameter named "oEventStructure". This parameter will be automatically filled in for us when the Event is called from the button click Event. Add three variable declarations to the sub procedure: sub onApplyFilter( oEventStructure as variant ) dim oButton as variant dim oDataForm as variant dim oTextBox as variant end sub Now I can add lines to set these variables to the controls on the form. The first variable oButton is easy to get, remeber the event structure will always have a property "Source" that is the control, so: oButton = oEventStructure.Source The next variable is the dataform control. Remember all our controls are owned by the dataform control, every control has a property "Parent" and this represents this relationship. So to set the oDataForm variable I add this line: oDataForm = oButton.Model.Parent There is one part of that line that has not been mentioned before, ".Model". The reason for adding this is simply this. OpenOffice.org is designed in such a way that for every control you see on screen there are actually 3 parts of it, the part you see, this is referred to as the "View". The settings for the control that you could manipulate at design time in the property editor, this is referred to as the "Model" and finally a piece that you never see referred to as the "Controller". Because we want to get to the "Parent" property, which is set at design time, we must work with the "Model". Finally I want to assign the variable oTextBox. This control is also owned by the dataform, just like the button was, so I can get to it with this line of code: oTextBox = oDataForm.getByName( "txtBox" ) The full sub procedure is now: sub onApplyFilter( oEventStructure as variant ) dim oButton as variant dim oTextBox as variant dim oDataForm as variant oButton = oEventStructure.Source oDataForm = oButton.Model.Parent oTextBox = oDataForm.getByName( "txtBox" ) end sub Now I need to add the lines that will actually set the filter. oDataForm.Filter = "ClientID = " & otextBox.Text oDataForm.ApplyFilter = True oDataForm.Reload There are just two things that I should tell you about those lines. First - since ClientID is assumed to be a numeric value there was no need to single quote the text value. Second - to get the new result set of records I had to Reload the query held by the dataform control, oDataForm.Reload. Now, I am going to make one more change to this sub procedure. What if I want to remove the filter completely. I decided that to do this I would check to see if the Text in txtBox is empty and if so remove the filter, to do this I needed only add a couple of lines of code and then the full sub procedure would look like this: sub onApplyFilter( oEventStructure as variant ) dim oButton as variant dim oTextBox as variant dim oDataForm as variant oButton = oEventStructure.Source oDataForm = oButton.Model.Parent oTextBox = oDataForm.getByName( "txtBox" ) if oTextBox.Text <> "" then oDataForm.Filter = "ClientID = " & oTextBox.Text oDataForm.ApplyFilter = True else oDataForm.ApplyFilter = False end if oDataForm.Reload end sub Now that we have our Event Handler all set all that is left is to tell the form to use it. Open the form, frmClients, for editing. Select the button btnApplyFilter and open the control property editor. Select the Events tab. For a button the event name we want is "When initializing". Select this row and click on the button all the way to the right. This opens the "Assign Action" dialog. This dialog again shows us the same list of events as the Event Tab of the property editor, but with the "When Initializing" event highlighted. ( why this extra step...again, I don't know, I can think of no reason this extra step is here - but perhaps there is one ) On the Assign Action dialog then click on "Macro". This opens the "Macro Selector" dialog. In this dialog from the list on the left side select the Standard library and the ClientsForm module. Then on the right hand list select the macro, onApplyFilter and click on OK. Save your form and save your Base file. That's it. Drew On 6/21/07, Andrew Jensen <[EMAIL PROTECTED]> wrote:
I'm sorry to say it is not Access that is slowing you down :>( Actually I have just the thing for you, almost as if it was made just for your use. It is a small library and 'find client' form designed to be integrated into databases like yours. I have attempted to design it in such a way as to be easy to do this integration. It uses filters to 'drive' the actual work form that calls it. I'll put the link to download it here tonight. If you use it all great, if not I'll point out the code fragment you will want to use in your form. On 6/21/07, Chris Moore <[EMAIL PROTECTED]> wrote: > > What I have is a single table (flat file) that contains client data > about cases that have to have fields updated as progress is made on said > case. There is approximately 12k records, so when searching for a record > to append to or to check the status of, it takes to long to go through > the records using the 'Find Record' function of Base. So what I need is > a way to search the database for individual records and have the records > > displayed in my form on a per record search with more speed than 'Find > Record' provides when searching for a single record. > > I know what your talking about with the select statement. I can also use > a simple query, SELECT * FROM `ABC` WHERE ( ( `DocNo:` = '000-111' ) ), > that does exactly what I need it to do. I just need to figure out how to > dynamically change the query (or filter) criteria, DocNo, using a text > box and button setup that can be added to my form. > > I'm trying to move from Access to OOBase and I need to be able to have > search performance close to that of Access. Right now I'm linked to the > existing Access database (which may be slowing me down) and trying to > come up with a way to move away from a proprietary database. I'm going > to be using Base as the front-end and switching the back-end from > Access to MYSQL once I get the form performing correctly. > > Thanks Andrew > > > Andrew Jensen wrote: > > OK, yes I think I can find the URLs, as most are sitting on my ftp > > server...I'll put a few here - later this evening. > > > > However, given your last message. You realize that when you set a > filter on > > a result set you are actually adding a where clause to the select > > statement. > > After the filter is set and activated then you need to reload the > result > > set, which simply re-issues the SQL statement. > > > > The reason I bring this up is that if your problem is performance on > > searches then your problems may be more with: > > > > A ) The schema ( indexes primarily ) > > B) The joins used in the query > > > > In either of these cases using a filter, IMO, will not offer you much > help. > > Then again I could be wrong and often am. > > > > Anyway, with that in mind - I would make this suggestion. If you could > > > offer > > some details on your database structure and perhaps an example query > > that is > > performing poorly, I might be able to offer some help in a more > general > > way. > > Mean time, when I get some time later today to do so, I'll clean up > the one > > example I have in mind and put the link to it here. > > > > Till then > > > > Drew > > > > On 6/21/07, Chris Moore <[EMAIL PROTECTED]> wrote: > >> > >> Andrew, > >> First of all thank you for your response. > >> > >> I did search around the forums and only found stuff about what I'm > >> having problems with, long search times. I'll do some more searching. > > >> Do you have the URL's to the examples from the forum? If you don't > have > >> them handy don't spend any time on it. > >> > >> I do most of my scripting using Perl. I do very little command line > >> scripting but with some examples I should be able to figure out how > to > >> adjust the scripts for myself. > >> > >> Thanks again, > >> > >> > >> Andrew Jensen wrote: > >> > Hi Chris, > >> > > >> > Well, I can tell you that at the OOoForum.org site there are > numerous > >> > examples of ways to set filters on forms . Using a text box for > entry > >> and a > >> > button to apply the filter is covered specifically, as is the use > of > >> combo > >> > and list boxes. A number of these threads include example database > and > >> > script files that you can download and checkout on your machine. > >> > > >> > Unfortunately there are some difficulties with the site that might > make > >> > your > >> > finding these entries rather difficult, but these are being > addressed. > >> > > >> > In the mean time, there are a number of example files that I can > make > >> > available for you to download. These range from very simple > filtering > >> > techniques to one that is rather advanced. > >> > > >> > My question would be which is most appropriate for you - all use > >> StarBasic > >> > scripts called from user triggered events on the form. > >> > > >> > If you could give me an idea of your comfort level with scripting > >> already I > >> > would be able to suggest which of the examples might be most > >> appropriate. > >> > Let me know here and I will follow up with the links to the example > >> files. > >> > > >> > Drew > >> > > >> > On 6/21/07, Chris Moore <[EMAIL PROTECTED]> wrote: > >> >> > >> >> Is there a way to set up a filter that can work with a text box > and > >> >> button? I'm trying to avoid the find record button and want to be > able > >> >> to have a text entry box on a form that will filter the data for > the > >> >> record entered into the text box when the button is pushed. Is > this > >> the > >> >> right way to go about this or should I be trying to use a query > with > >> the > >> >> text box and button setup? > >> >> > >> >> -- > >> >> Chris Moore > >> >> [EMAIL PROTECTED] > >> >> > >> >> > --------------------------------------------------------------------- > >> >> To unsubscribe, e-mail: [EMAIL PROTECTED] > >> >> For additional commands, e-mail: [EMAIL PROTECTED] > >> >> > >> >> > >> > > >> > >> -- > >> Chris Moore > >> [EMAIL PROTECTED] > >> > >> --------------------------------------------------------------------- > > >> To unsubscribe, e-mail: [EMAIL PROTECTED] > >> For additional commands, e-mail: [EMAIL PROTECTED] > >> > >> > > > > -- > Chris Moore > [EMAIL PROTECTED] > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > >
