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.
Common
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 |