OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

howto: search / query on form
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
dbojan
General User
General User


Joined: 04 Apr 2007
Posts: 8

PostPosted: Fri Sep 28, 2007 6:00 am    Post subject: howto: search / query on form Reply with quote

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.
Back to top
View user's profile Send private message Visit poster's website
kabing
OOo Enthusiast
OOo Enthusiast


Joined: 25 Apr 2007
Posts: 179

PostPosted: Sat Sep 29, 2007 6:32 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
brian4m
OOo Enthusiast
OOo Enthusiast


Joined: 13 Feb 2006
Posts: 162
Location: Bath, England

PostPosted: Sat Sep 29, 2007 7:20 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
FalynxFX
Newbie
Newbie


Joined: 02 Oct 2007
Posts: 1

PostPosted: Tue Oct 02, 2007 10:59 am    Post subject: Reply with quote

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
Code:
Sub search2
dim oFilter as object
dim oFormCtl as object

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

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

oFormCtl.Reload
End Sub


save, and close the macro window

4. Back on the "Assets" form, right click on the listbox and select "control"
under the "general" tab change "dropdown" to "no"
under the "data" tab, delete anything that is in the "data field"
change "type of list contents" to "query"
change "list content" to "query_assets" (this is the name of the query we made)
under the "events" tab for "changed" click the "..." button. click "macro" on the right hand side.
(borrowing this from dbojan (thanks!))
Expand "My Macros" / "Standard" / "Module1". Click on Module1.
Select "search2" 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.

5. your list box should contain "ford" and "porsche" (the results of the query, and clicking on one of them will update the form

thanks dbojan! I hope this helps.
Back to top
View user's profile Send private message
dbojan
General User
General User


Joined: 04 Apr 2007
Posts: 8

PostPosted: Wed Oct 03, 2007 12:42 am    Post subject: Reply with quote

nice Smile

Is that what you were loking for kabing?

@ brian4m what do you mean validate? Can you explain a bit more?
Back to top
View user's profile Send private message Visit poster's website
kabing
OOo Enthusiast
OOo Enthusiast


Joined: 25 Apr 2007
Posts: 179

PostPosted: Wed Oct 03, 2007 6:13 am    Post subject: Reply with quote

dbojan wrote:

Is that what you were loking for kabing?


I think so; I haven't had a chance to try it out on the database I'm working with. I'll post back one way or the other when I have tried it, but that may be several days.

kabing

And thanks to FalynxFX for the steps!
Back to top
View user's profile Send private message
brian4m
OOo Enthusiast
OOo Enthusiast


Joined: 13 Feb 2006
Posts: 162
Location: Bath, England

PostPosted: Sat Oct 06, 2007 10:42 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
kabing
OOo Enthusiast
OOo Enthusiast


Joined: 25 Apr 2007
Posts: 179

PostPosted: Wed Oct 17, 2007 10:43 am    Post subject: Reply with quote

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:

Code:
SELECT DISTINCT "State" FROM "Locations"


-I also had to change the name of the Form from "MainForm" to "Standard".
-Finally, my field name was "StateOrProvince", but I found that when the Macro ran it was interpreted as "State" OR "Province" which provoked an SQL error. I had to create an alias ("State") for the "StateORProvince" field to get it to work properly.

Here's my final version of the macro:

Code:
Sub SearchByStateOrProvince

dim oFilter as object
dim oFormCtl as object

oFormCtl = ThisComponent.Drawpage.Forms.getByName("Standard")
oFilter = oFormCtl.getByName("StateSearchListBox")

if oFilter.CurrentValue <> "" then
oFormCtl.Filter = "State LIKE " + "'"+oFilter.CurrentValue+"'"
oFormCtl.ApplyFilter = True
else
oFormCtl.ApplyFilter = False
end if

oFormCtl.Reload
End Sub


kabing
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Sun Feb 24, 2008 7:20 am    Post subject: Reply with quote

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.

Code:
Sub Search
dim oFilter as object
dim oFormCtl as object

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

if oFilter.Text <> "" then
oFormCtl.Filter = " UPPER(Field_Name) LIKE " + "UPPER('%"+oFilter.Text+"%')"
oFormCtl.ApplyFilter = True
else
oFormCtl.ApplyFilter = False
end if

oFormCtl.Reload

End Sub
Back to top
View user's profile Send private message
foridea
Newbie
Newbie


Joined: 19 Feb 2008
Posts: 1

PostPosted: Tue Feb 26, 2008 8:20 am    Post subject: Reply with quote

kabing wrote:
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:

Code:
SELECT DISTINCT "State" FROM "Locations"


-I also had to change the name of the Form from "MainForm" to "Standard".
-Finally, my field name was "StateOrProvince", but I found that when the Macro ran it was interpreted as "State" OR "Province" which provoked an SQL error. I had to create an alias ("State") for the "StateORProvince" field to get it to work properly.

Here's my final version of the macro:

Code:
Sub SearchByStateOrProvince

dim oFilter as object
dim oFormCtl as object

oFormCtl = ThisComponent.Drawpage.Forms.getByName("Standard")
oFilter = oFormCtl.getByName("StateSearchListBox")

if oFilter.CurrentValue <> "" then
oFormCtl.Filter = "State LIKE " + "'"+oFilter.CurrentValue+"'"
oFormCtl.ApplyFilter = True
else
oFormCtl.ApplyFilter = False
end if

oFormCtl.Reload
End Sub


kabing


thanks the knowledge became useful
_________________
http://www.ideatasarim.com
Back to top
View user's profile Send private message Visit poster's website
Robby
OOo Advocate
OOo Advocate


Joined: 21 Sep 2004
Posts: 249

PostPosted: Fri May 23, 2008 4:17 am    Post subject: Reply with quote

xxx

Last edited by Robby on Sun Dec 11, 2011 2:01 am; edited 1 time in total
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Fri May 23, 2008 4:35 am    Post subject: Reply with quote

I think this line in your macro is wrong.
Code:
oFormCtl.Filter = oFilter.CurrentValue

The line needs to take the shape more like:
Code:
oFormCtl.Filter = " UPPER(Field_Name) LIKE " + "UPPER('%"+oFilter.CurrentValue+"%')"

For using the value from the combobox try this: (Note: I havn't tested it)

Code:
Sub search1
dim oFilter as object
dim oFormCtl as object

oFormCtl = ThisComponent.Drawpage.Forms.getByName("Cliƫnten")
oFilter = oFormCtl.getByName("search1")

if oFilter.CurrentValue <> "" then
oFormCtl.Filter = " UPPER(Field_Name) LIKE " + "UPPER('%"+oFilter.CurrentValue+"%')"
oFormCtl.ApplyFilter = True
else
oFormCtl.ApplyFilter = False
end if

oFormCtl.Reload
End Sub


Note: change the "Field_Name" to what field you are searching on.


Cheers

Voo
Back to top
View user's profile Send private message
Robby
OOo Advocate
OOo Advocate


Joined: 21 Sep 2004
Posts: 249

PostPosted: Fri May 23, 2008 5:19 am    Post subject: Reply with quote

xxx

Last edited by Robby on Sun Dec 11, 2011 2:01 am; edited 1 time in total
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Fri May 23, 2008 6:00 am    Post subject: Reply with quote

Quote:
BTW I think the UPPER statement is useless when I use a Combobox because one could only select an item from the list

You're right, but it still works with that in, so hey.
EDIT: Don't forget you could also have a typo in your list with a different capital and it won't find those ones without "UPPER"

Quote:
I've followed your suggestion and still getting the runtime error


I just tested it out and it works for me!?! What does the exact error message say?

Quote:
Other suggestions?

I guess the usual things, check you have registered your database in tools>options>openorg.base
Which event are you using to fire the script? Are you using a "before record change" or "before record action" events cause they will give you an error unless you give them special treatment (see "forms and dialogs" by C.Benitez P18)
What are you doing with the "bound field" of the combobox? check which field in your form you have it going to.
Put in your code:
Code:
msgbox oFilter.CurrentValue
....just after you define "oFilter" to see what value it actually has. A message box should pop up with the current value in it. You can then move this down line by line to see which command is causing the error. Maybe the sub dosen't even get to running stage. This will tell you that.

Cheers

Voo
Back to top
View user's profile Send private message
Robby
OOo Advocate
OOo Advocate


Joined: 21 Sep 2004
Posts: 249

PostPosted: Fri May 23, 2008 6:40 am    Post subject: Reply with quote

xxx

Last edited by Robby on Sun Dec 11, 2011 2:01 am; edited 1 time in total
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group