Page 1 of 1

[Example] Searching in a form with macros

PostPosted: Thu Apr 08, 2010 12:41 pm
by RPG

For every body just starting with a database there is a problem with searching of the data. There are several methods for doing it but basically they are the same and that is: One form have to do that searching. This means you have to understand a little of SQL. This can you learn in the GUI for queries.

I prefer to use macros. That means a macro init the form so it can search. This is not flexible. Every time you have to change the macro. This makes I have searched for a method what is more flexible. I think I have found that method. I have post on the other forum an example how it can work when you past the fieldname in the tag field of the control. This tag field what you can see on the properties tabpage of the control and there is called "addition information" The first post you can find here. Now I have extend that same method for passing the operator for searching. When you have more then one field for searching then they are connect with "AND", this can not be changed. In the link you can maybe find more information what is not told here. I have done an example with a working example for search. There is more code in that example. The most is working and can maybe used.

Some part I have test for more time but other parts I have test only a little there I don't use it for me own. I have said it is more easy but I think that it can be to difficult for people who are just starting with OOo-base without any experience with making a database ,SQL or basic.

When you want use this method you have to understand some rules.
a) All the controls who are for the filter must have the same name.
b) In the tag field of a control you must pass the information what you need for that filter:Name of field, operator,Formname
c) The control must say something from the field you want filter.
d) The control is not bound to a datafield

Some parts more explained.
First the information in the tag field. This is a comma separated list. And have not to used quotes.
a)There you have to pass the fieldname which you want filter. This cannot be empty.
b) You can give operator for the search function. I think to =,<=,>=,like. If it is empty then the = is used.
c) The formname where you want filter if it is not the same as where the controls. If it is empty then the filter comes where the control is.

More fields
There you can use more then one field all field are connect with: AND. This cannot be changed. Only filled field are use in the query but for date and number field I have that part not test much.

Start first learning
When you start using this sub then start with text fields. This field is the most easy for understanding. When you want filter on numbers or dates then the query is changed a Little.

If the fields are empty then most of the time that field comes not in the filter. But I have it not test for numeric and date field.

I do also use it with listbox and combobox. For people who just start programming don't use there you need maybe more macros or a query for filling the list.

Activating the sub
When I start his sub writing I did use it only for a textbox. Then I used two events.
a) When text is modified
b) When key is realized.
For other it depends what you need and it can be a mouse click or something else. The history made you cannot use a button on this moment for activating the sub.

I knew the code is long but partly it comes I have tried as much as error checking in this code. And for people who are thinking the code is slow for searching. This code does not search it makes only the filter and take a half second.

Searching in a textcontrol
When you use a text control then the best method is to add in the tag field the "like" operator for the query. When I made this sub searching in a text field is always using this : Select & from "table" where "fieldname" like '%search%'.

When I want have it was searching more exact I add in the search control "-" on the beginning or the end. The result is then and then the query changed. to :Select & from "table" where "fieldname" like 'search'
When you want change this, then delete some line of the code where the "%" is inserted in the query.

For me was this enough. But I don't know how it is working for other people.

 Edit: I found some little problems in the code what I have changed 


Re: Searching in a form with an example

PostPosted: Thu Apr 08, 2010 9:51 pm
by Safway
I get an error trying to use your database. See the attached screenshot.

Re: Searching in a form with an example

PostPosted: Thu Apr 08, 2010 11:45 pm
by RPG

I don't know what the reason is for the problem but maybe the next can be a solution for you: make a connection to the hsqldb.jar.

You can do in the folowwing way.

menu --> tools -->options

In the next popup goto --> java --> Class path

In the next popup
add archive and search then the file you need in my case in is:

There I have a linux version it can maybe the same for you. but for window users the start is a little else but they have to search the file.

I hope this is working. It is not clear to me why you need it but I have to use the same for me most of the time.


Re: Searching in a form with an example

PostPosted: Sun Jan 23, 2011 9:27 am
by goodvibes

Thanks very much for your example - I have now got a form with a calculated field working using your method.

Note: It would not work for me with a FormattedField, updating Value or EffectiveValue (Text is not a valid property for a FormattedField), but when I changed the control from FormattedField to Currency (which was what I really needed anyway), it worked fine.

I'm still not totally happy with having to commit a column to the database on order to see the new value in the SubForm_Grid.
This means I have to default all mandatory columns to valid values instead of NULL so that the record can be saved before all the controls have been entered.
However, if one does not do the commit, then the value of the calculated field that has been set by the macro is not seen in the grid and not used when the record is saved.

Does anyone have a way of overcoming this? i.e get a grid to take notice of of a new value without having to commit it.
I've looked for a method but cannot find anything that looks useful except for maybe fireProperyChangeEvent but I cannot see how to use this.

Re: Searching in a form with an example

PostPosted: Sun Jan 23, 2011 8:03 pm
by RPG

I'm glad to hear that some things are working. There I work in this moment with Libre Office 3.3 RC4 I cannot work with the forms I have.
The properties of a formatted text box you need maybe are.

I don't know if you can use it for searching. The formatted value is not stored in the database.

The other question is off topic. When you want have an answer you have to make a new thread but I have no solution.

OOo3.3 give more problems you can also not work with frames. There I use the frames a lot in this example it can not work with OOo3.3


Re: Searching in a form with an example

PostPosted: Mon Oct 15, 2012 6:21 am
by jza
RPG wrote:
OOo3.3 give more problems you can also not work with frames. There I use the frames a lot in this example it can not work with OOo3.3


Wonder if there is a patched version of this that work with 3.4.x?

Great work :bravo:

Re: [Example] Searching in a form with macros

PostPosted: Mon Oct 15, 2012 5:44 pm
by RPG

I don't know in which version of OOo or LibO the bug is repaired but it is working now good again good for me when I use LibO 3.5.4.


Re: [Example] Searching in a form with macros

PostPosted: Tue Jan 22, 2013 8:48 am
by robce1964
WOW ... I found what I needed ... now I try to apply to my case ...
thank you .... thank you ....thank you!
:bravo: :super: