[Solved] OpenOffice Base Search Form

[furchy152]

Hello!

I am new with Open Office and also to macros...

So I have odb file, where i have 2 tables and 3 forms.

I tried to do search form, where i would like to write Priimek to txt field, and the search option would have to find all matching Priimek in table Vabilo. In table Vabilo there is also SignaturnaStevilka, so when the result from Vabilo is Priimek X, SignaturnaStevilkaY. The program should then go to table Policist and find SignaturnaStevilka that is matching the one from table Vabilo and in result should write Priimek from Policist besides SignaturnaStevilka.

So the complete result should be Vabilo.Priimek, Vabilo.SignaturnaStevilka, Policist.Priimek, Policist.Ime.

Is there anybody who knows how to do that? I tried several options with macros, subforms and so on (from this forum and from Youtube), but i Did not manage to do anything usefull.

Thanks in advance!

Mitja

[UnklDonald418]

See if this does what you want.

Vabljenje oseb na PPv1.odb

(42.28 KiB) Downloaded 371 times

I added Test_Form and a table Filter.
On Test_Form select a name from the ListBox labeled SignaturnaStevilka.
If the selection is different than what was originally displayed the Save Selection push button will be enabled. When you press the button it saves the SignaturnaStevilka value for the selected name in the Filter table.
Press the Refresh Selection Data and a MainForm ( data source Filter table), SubForrm (data source Policist table), SubSubForm (data source Vabilo table) is refreshed displaying all the data related to the selected name.

[furchy152]

Thanks for your quick help, but it does not do what i need it to do...

I made new form Iskanje. It is made for searching in query Poizvedba_vabilo (everything is from table vabilo, except PPriimek and IIme is read out of table Policist).


So in new form Iskanje, it is meant to write (not drop down menu) search key word, and the program searches in Vabilo in column Priimek. Results should write down matching word or partial text from text field and Vabilo.Priimek. The final result has all fields of query Pozvedba_Vabilo...

Sorry my english is not my strong side, so I hope you understand what I mean

[UnklDonald418]

If I understand correctly, you want to do a keyword search. Try this

Vabljenje oseb na PPv3.odb

(42.57 KiB) Downloaded 428 times

I added a modified Filter table,
added a new query Query_PV01 based on your query Poizvedba_Vabilo
and then modified form Iskanje

[furchy152]

Yes!

Thats exactly what I need. Thank you very very much…

If you have time, can you please tell me how you did it? In short… Why there must be table Filter with ID vabila boolean, how do you do that kind of query? So I Will understand and Will be able to do that next time on my own…


Thank you once again!

[Villeroy]

Base tutorials: viewforum.php?f=83
Base examples: viewforum.php?f=100
[Example #1] Filter/Search with Forms (leveraging SubForms)

[UnklDonald418]

First look at Query_PV01. I added a to the WHERE clause of your original query.

Code: Select all

AND ( ( UPPER ( "Vabilo"."Priimek" ) LIKE '%' || UPPER ( :P1 ) || '%' ) OR ( :P1 IS NULL ) )

This makes Query_PV01 what is called a parameter query. If you execute the query you will see a Parameter Input dialog. Type something (or nothing) in the Value box and select OK to see the results of the query based on the LIKE clause. We make use of this parameter query on form Iskanje
Open form Iskanje in the Edit/Design mode.
Select the Form Navigator from Form Design toolbar along the bottom of the screen.
In the Form Navigator window right click on MainForm and select Properties to open the Form Properties dialog.
On the Data tab notice that the Content Type is Table and Content is Filter, which means that MainForm is using the table Filter as its data source.
In the Form Navigator now select the txtPriimek control and the Form Properties dialog is replaced with the Properties: Text Box dialog. The Data field will show SearchText, which is where the text you type in txtPriimek control is stored.
In the Form Navigator now select SubForm to get a Form Properties dialog for the SubForm. For the SubForm we are using Query_PV01 as the data source as you can see in the Content and Content type properties. Look down the list and take note that Link master field is SearchText from Filter table. The Link slave field is P1 which is from Query_PV01. If you select the ellipsis (...) to the right of either of the Link properties you will see a Link fields dialog.
The result is that SubForm will display data selected by Query_PV01. when parameter P1 is replaced by the contents of SearchText
The table Filter in this case only needs 2 fields, FilterID and SearchText, the Integer field could be deleted, but I thought it might come in handy for another form. Either way, it does no harm.
Actually, you could add more fields to table Filter and use them in a similar fashion to generate even more selective results.
I created one form that uses 6 parameters to look up automotive alternator pulleys based various dimensions and properties.
I hope this helps you understand.
Here are some other resources that might help
[Example #1] Filter/Search with Forms (leveraging SubForms)
What's the best way for "users" to search records?

[furchy152]

Thanks!

The explanation Will be very usefull

Finally my databse with forms is completed with your help…

Here is the final version, if someone Will do something similar.