howto: search / query on form

[dbojan]

CREATING A SAMPLE DATABASE

start openoffice.org program
file /new /database
create new database;
next
open database for editing;
next
enter name: testDatabase;
save

in "Database" column left, select "Tables", then in "tasks" select "Use wizard to create table"

Category: Business
sample tables: assets

from "Available fields" double click "AssetID"
scroll a bit and double click "Make" and "Model".
"Next"
"Next"

select "Use an existing field as primary key"
in "Fieldname" choose "AssetID".
"Next"

"Insert data immediately"
Finish.


CREATING TABLE IN DATABASE

You will have excell like sheet.
for "AssetID" enter "1" without quotes. Press tab.
"Make" = ford, "Model" = escort
Press enter. Repeat entering values in next row:
2, porshe, 911.
Close window.

Now you have a working database.


CREATING A FORM TO WORK WITH DATABASE:

in Database column left, select "Forms", then in "Tasks" select "Use wizard to create Form"
Click on ">>" button to add all fields to form.
"Next"
"Next"
"Next"
(click next few times)
Finish
Close assets window.


CREATING SEARCH CONTROL

In "Forms" right click on "Assets", select "edit".

In Assets window in top window menu click on "View" / "Toolbars"/ "Form controls".
in "Form con.." window click on squared "[abc]" control.

Draw a square on the orange background by holding, then releasing left mouse button.
You can use green dots to resize text control.


CREATING MACRO

Menu: "Tools" / "Macros" / "Organize Macros" / "Openoffice.org Basic"

Macro name enter: "search1" without quotes.
Click on "New" button.

You will have new editing window.

Between "Sub search1" and "End Sub" line, add lines so it looks like this
(You can use copy c and copy v for copy and paste)

----

Sub search1
dim oFilter as object
dim oFormCtl as object

oFormCtl = ThisComponent.Drawpage.Forms.getByName("MainForm")
oFilter = oFormCtl.getByName("TextBox")

if oFilter.Text <> "" then
oFormCtl.Filter = "Make LIKE " + "'%"+oFilter.Text+"%'" + " OR Model LIKE " + "'%"+oFilter.Text+"%'"
oFormCtl.ApplyFilter = True
else
oFormCtl.ApplyFilter = False
end if

oFormCtl.Reload
End Sub

-----

(Notice the use of column names "Make" and "Model" in the macro)
("Make LIKE " + "'%"+oFilter.Text+"%'" is one query, using + sign and " OR " to add more fields to query)
(Search is case sensitive)
(TextBox and MainForm are names of textbox search control, and name of the Form)

Click on save icon in the top.
Close Basic Window.


ASSIGNING MACRO TO CONTROL

Right click on textbox control you just drew and select "Control" from menu.

in "Events" tab click on "..." button near "Text modified"
(You can scroll new window if you want.)
Click on "Macro" button.

Expand "My Macros" / "Standard" / "Module1". Click on Module1.
Select "search1" macro in the right pane, and then click on "OK" button.
OK
OK

Close Control properties.

Click on blue floppy disk icon in the top to save changes. (In assets window)

Close assets window. save changes.

Click on blue floppy disk icon in the top to save changes. (In Open office window)


In Forms, double click on Assets to test it.
Click on text control you created and enter search words.
If you want to go back delete words from search box using backspace.

[kabing]

Thanks so much for this howto!

Could this technique be modified to use with list boxes and/or check boxes?

If so, how? I'm particularly interested in the specifics of the macros for these modifications, as I'm pretty clueless when it comes to macros.

kabing

[brian4m]

I also would like to see this developed as the format, step by step, is easily followed.

I have been trying to get at data in a Base form so that I can validate it. I have read many entries in this forum and the API/macros forum and tried them with no success so far and the occasional crashes.

My area of interest is to find out how to get the four data fields, validate them, put out a suitable message if some of the data is invalid then write the correct data to the Base table. The annoying thing is that I can do all of that using a basic macro but not in the Base form.

Brian

[FalynxFX]

I might be able to help you kabing. I was looking for the exact thing you are and came across this thread. So here is what I did. After doing everything dbojan listed to set up the database...
1. In the main OOo Base window under database, select "Queries" > "use wizard..."
from "available fields" select "make" and ">" to move it to the query (only add make to the query);
Next
sort by, "Assets.Make", ascending.
Next
Next
Next
Finish
you should see the results of the query (should be "ford" and "porsche")

2. In the main OOo Base window under database, select "forms"
right click and edit "assets"
from the "form controls" toolbar select the "List Box" and make one on the form by left clicking and dragging to the size you want.
in the list box wizard select "Assets"
Next
select "Make"
Next
select "Make" in the left and right hand panes
Finish


3. In the assets form "Tools" / "Macros" / "Organize Macros" / "Openoffice.org Basic"
under "macro name" type "search2"
click "new" button
edit the "Sub search2" to "End Sub" section to the following

[dbojan]

nice

Is that what you were loking for kabing?

@ brian4m what do you mean validate? Can you explain a bit more?

[kabing]

[brian4m]

Validate.

The aim is to ensure that only valid data is committed to the database and stop the SQL errors that can arise when fields are of the wrong type or too long.
Eg. I have a field for the first name. In the database to be converted to Base the longest first name is 23 characters so I have allowed a bit more and want to test that it is no longer than 30.
I have not yet cracked how to apply List Boxes in sub-forms. So another example is to ensure the Male/Female field is only one character and is M or F.

Brian

[kabing]

I finally got a chance to test the process and macro that FalynxFX provided.

I couldn't get it to re-filter when I changed the value in the list box. But when I changed over to a combo box (which I preferred anyway), it worked just fine.

I did do a few things differently in the end, too:

-I left the dropdown setting in the General tab of the combo box properties window set to "Yes"
-Rather than using a Query for the list contents, I used an SQL statement. Specifically:

[Voobase]

For anyone wishing to use this Search Macro without it being Case Sensitive, simply put UPPER before the field_name on both sides of "LIKE" in the SQL statement. As both the field_name data and text box search data are capitilized, the search is no longer case sensitive. Similarly you could use LOWER.

[foridea]

[Robby]

xxx

[Voobase]

I think this line in your macro is wrong.

[Robby]

xxx

[Voobase]

[Robby]

xxx