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

[Solved]: add a find record button/text box

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
gmorgan
Power User
Power User


Joined: 11 Jun 2008
Posts: 54
Location: Zimbabwe

PostPosted: Tue Jun 17, 2008 3:38 pm    Post subject: [Solved]: add a find record button/text box Reply with quote

I would like to run my form in full screen but need the "find record" option from the navigator toolbar.
Is there some way of assigning a button to open the default find record dialogue box, or a more elegant way to search through a surnames field in a table (given also that there may be a number of people in the field with the same surname).


Last edited by gmorgan on Wed Jun 25, 2008 7:26 am; edited 1 time in total
Back to top
View user's profile Send private message
gmorgan
Power User
Power User


Joined: 11 Jun 2008
Posts: 54
Location: Zimbabwe

PostPosted: Sat Jun 21, 2008 3:37 am    Post subject: Now to search to next matching record Reply with quote

I have managed to achieve the search using a textbox with the applied macro
[code]
Sub search1
dim oFilter as object
dim oFormCtl as object

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

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

oFormCtl.Reload
End Sub
[/code]

Many thanks to whoever did this coding, I've forgotten where in the forums I found it.

How do I make the textbox search to the next surname - eg two or more instances of the same surname in the table data field "Surname", like the find function from the navigation toolbar?
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Sat Jun 21, 2008 7:05 am    Post subject: Reply with quote

Hello

I think this link can help you
http://www.oooforum.org/forum/viewtopic.phtml?t=71766&postdays=0&postorder=asc&start=0

If you are working with the dummy table I think it will not help you

The problem of the toolbar in fullscreen can maybe not changed. If you transfer to OOo2.41 maybe it is over.I did not try it. If the toolbar is your only problem think twice before changing. I do use 2.41 I'm satisfied but you can't know you find a problem. I did have the problem with toolbars in OOo2.3 as you tell over the full screen

I hope it helps you
Romke
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Sat Jun 21, 2008 8:35 am    Post subject: Reply with quote

This is also an easy way to filter to a record by using a combobox in a "dummy" mainform and having your "surnames" table in a subform. If there was more than one surname of the same type then all of those records would display in the subform. You would then just need to use your navigation buttons to find the correct one or else have them displayed in a "table grid" to select from.

http://www.oooforum.org/forum/viewtopic.phtml?t=72134

Voo
Back to top
View user's profile Send private message
gmorgan
Power User
Power User


Joined: 11 Jun 2008
Posts: 54
Location: Zimbabwe

PostPosted: Sat Jun 21, 2008 1:47 pm    Post subject: thanks Reply with quote

Firstly, Voo, thank you I have used that post and it was the way I set up one of my forms in the database. The form I want this search altered is a single simple form and table.
Ramke, your help has been invaluable. My search textbox is working well, just without the ability to carry on searching from the record it finds (ie looking for another instance of the surname) and I haven't had the time to look throught your link yet.
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Mon Jun 23, 2008 2:46 am    Post subject: Reply with quote

Quote:
How do I make the textbox search to the next surname - eg two or more instances of the same surname in the table data field "Surname", like the find function from the navigation toolbar?


If you draw a "Navigator" bar onto your form you should be able to use the "next" arrow to go to the next instance of that surname. The macro you are using is a filter, so if you have surnames that are identical then they are all retrieved and you just need to arrow to them.

The problem I have always found with using a filter is finding a way to remove the filter after you have finished with it which makes sense to the user. Also, sometimes the form remembers the filter and it dosen't show it the filter bar making filter use a bit messy. If you want an example of how i've dealt with filter (search) box's have a look at this example I did for someone....
http://www.oooforum.org/forum/viewtopic.phtml?t=72062
The filters are towards the end of the macro (which you will need to load separately). You will need to look at the form and control properties to see what events fire the various macro's. I don't think I documented the search box in the example. It was a secondary thing..

Otherwise, the way RPG has suggested is quite good because you go to the actual record. You can then navigate onwards, without having to remove the filter and reload the form (as a filter macro would require).

Here is the macro's from the searchbox in the example I was talking about...

Code:
Rem... Actually run from "when loading" and "when unloading" events of mainform.
   Rem... Most benefit actually occurs with "when unloading", so next time you use your
   Rem... form the filter is not still lingering.
Sub Clear_When_Loading_Main (oEv as object)

oEv.source.Filter = ""
oEv.source.ApplyFilter = false

end sub

   Rem To make a "return" from search box do the search. (same as clicking "search")
   Rem... driven off the "key pressed" event of the text box
Sub Key_Pressed_Main (oEv as object)

   dim oForm as object

   oForm = oEv.source.model.parent

      if(oEv.KeyCode=1280) then
         searchbutton_Main   
      end  if
End sub


   Rem... driven off the "text modified" event of the text box.
Sub searchbox_Main

dim oFilter as object
dim oForm as object
dim mystring as string

   oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
   oFilter = oForm.getByName("SearchBox")

      if oFilter.Text <> "" then
      
         mystring = oFilter.text
         FrstCHR = LEFT(mystring, 1)
         strLen = LEN(mystring)
            Rem... so you don't accidently have a space at the beginning of search-term, which stuffs the search.
         If FrstCHR = SPACE( 1 ) then
            msgbox "Please don't leave space in start position of search box. It will catch you out later!"
            oFilter.Text = ""
         End if


   Rem... else.. text was modified but the text box is empty, so the contents must have been cleared so remove filter and reload()
      else

      oForm.ApplyFilter = False
      oForm.Reload

      end if
End Sub


   Rem... driven off the "when initiating" event of search button
Sub searchbutton_Main

   dim oFilter as object
   dim oForm as object

      oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
      oFilter = oForm.getByName("SearchBox")
      
      ' msgbox "searchbutton - reload from searchbutton"
      
      oForm.Filter = "UPPER(ID) LIKE " + "UPPER('%"+oFilter.Text+"%')" + " OR UPPER(Asset_Number) LIKE " + "UPPER('%"+oFilter.Text+"%')" + " OR UPPER(Current_Client) LIKE " + "UPPER('%"+oFilter.Text+"%')" + " OR UPPER(Stand_Type_For_Search) LIKE " + "UPPER('%"+oFilter.Text+"%')"
      
      oForm.ApplyFilter = True
      oForm.Reload


End Sub


Cheers

Voo


Last edited by Voobase on Wed Jun 25, 2008 6:10 am; edited 1 time in total
Back to top
View user's profile Send private message
gmorgan
Power User
Power User


Joined: 11 Jun 2008
Posts: 54
Location: Zimbabwe

PostPosted: Tue Jun 24, 2008 1:34 pm    Post subject: Reply with quote

That's exactly what I wanted the search to do! (also including the navigator bar) Thank you.

Is it too much to ask how I would get a message box display "Record Not Found" if the search doesn't find anything, otherwise I find myself sitting waiting wondering the search is still ongoing/crashed or something.

One last question on this: must the text be deleted to clear the filter, or will highlighting and replacing do the same.

Will be marking it as solved - and I am sure it will be useful to others as it was all pretty clear to me and I am just learning how all these things work.

Thanks again
Glenn
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Wed Jun 25, 2008 6:08 am    Post subject: Reply with quote

Hi again,
Quote:

I have left this macro out, what is KeyCode=1280?


1. What that sub is doing is checking each character you type in the text box and is looking for "Enter (return)" being pressed. If that occurs it runs the "Searchbutton_Main sub.

I made a mistake in the Rem comment and it should actually say to run it off the "key pressed" event, not the "button pressed" event. 1280 is the character code for the enter button (return). (I'll edit my post from before and fix it up). You will probably want this feature so you don't have to use the mouse to press the search button all the time.
(edit.. Sorry if this answer does not make sense. I think I might have been responding to a previous draft of your question)

Quote:
must the text be deleted to clear the filter, or will highlighting and replacing do the same.


2. It is the "else" part of the "searchbox_Main" sub that does this. If the "text is modified" event fires and there is nothing in the text box, it puts the filter to "false"and does a reload. You will know if it has done a reload because it will take a few seconds and you will see there are more records to navigate to by the display on the navigator bar.

To answer your question... Yes, the text must be cleared (say highlight then press delete) to clear the filter. If you highlight the text and then replace it with more text, the filter is not cleared, but that is OK because as soon as you press search for the thing you typed in it searches for that instead.

Cheers

Voo
Back to top
View user's profile Send private message
gmorgan
Power User
Power User


Joined: 11 Jun 2008
Posts: 54
Location: Zimbabwe

PostPosted: Wed Jun 25, 2008 7:26 am    Post subject: Reply with quote

all sorted out! will be moving onto other little tweaks, but the database is up and running in my practice.
will mark as solved
Glenn
Back to top
View user's profile Send private message
gmorgan
Power User
Power User


Joined: 11 Jun 2008
Posts: 54
Location: Zimbabwe

PostPosted: Wed Oct 01, 2008 7:14 am    Post subject: including 2 names in my search of 2 name fields Reply with quote

Hi Voo
Sorry to come back to this after so long. Everything we did has been running very smoothly.
The search filter allows me to type in one name to find in either the Surname field or the FirstNames field. eg. I can search for "John" and get results from my search, or I can type in "Smith" and get all the smith's back, but I get no result from my search if I type in "John Smith".

Is there some way of tweaking the code to allow this

oForm.Filter = "UPPER(Surname) LIKE " + "UPPER('%"+oFilter.Text+"%')" + " OR UPPER(FirstNames) LIKE " + "UPPER('%"+oFilter.Text+"%')"

Hope you're still out there!
Glenn
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
Page 1 of 1

 
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