| View previous topic :: View next topic |
| Author |
Message |
Billyray
OOo Enthusiast
Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio
|
 Posted: Sat Mar 10, 2007 1:04 pm Post subject: Someone check MSA-Base Faq please! |
 |
|
| I'm trying to include the Findfirst function that is coded on the wiki, in OOo-Base 2.0.2 on Ubuntu Linux. The code as shown (with the appropriate changes to specific names) will not compile. Shouldn't a While end with a Wend? Should a Do have some sort of conclusion. I can't tell what it's trying to do, to fix it myself, and it's too different from MS Access VBA to understand either. Thanks for any help. |
|
| Back to top |
|
 |
hol.sten
Super User
Joined: 14 Nov 2004
Posts: 3531
Location: Hamburg, Germany
|
| Posted: Sat Mar 10, 2007 2:59 pm Post subject: Re: Someone check MSA-Base Faq please! |
|
|
| Billyray wrote: | | The code as shown (with the appropriate changes to specific names) will not compile. |
Can you post it here?
| Billyray wrote: | | Shouldn't a While end with a Wend? Should a Do have some sort of conclusion. |
This works: | Code: | do while i < 100
do-something
i = i + 1
loop |
And this works: | Code: | do
do-something
i = i + 1
loop while i < 100 |
And this works: | Code: | while i < 100
do-something
i = i + 1
wend |
Regards
hol.sten |
|
| Back to top |
|
|
Billyray
OOo Enthusiast
Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio
|
| Posted: Sat Mar 10, 2007 3:57 pm Post subject: |
|
|
Ok, I found the prob. The line should read LOOP WHILE etc. but the code box is missing the LOOP part of the statement. With the loop in place the DO works too.
So now I have the search function working on my form... Yay. But I am searching by last name, and I will need to see the first name too, to make sure I have the right last name in cases of more than one. Can I put both names in the combo box somehow and search based on that?? I would still try to adapt the function from the MSA-FAQ code.
Thanks!  |
|
| Back to top |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Sat Mar 10, 2007 4:51 pm Post subject: |
|
|
| Billyray wrote: | Ok, I found the prob. The line should read LOOP WHILE etc. but the code box is missing the LOOP part of the statement. With the loop in place the DO works too.
|
oops, I'll update the wiki page ASAP.
| Quote: |
So now I have the search function working on my form... Yay. But I am searching by last name, and I will need to see the first name too, to make sure I have the right last name in cases of more than one. Can I put both names in the combo box somehow and search based on that?? I would still try to adapt the function from the MSA-FAQ code.
|
Well, without seeing exactly what you are adapting this comes to mind.
You could populate your list box with a query that concatenated the first and last name field then split them apart for the search.
The ooBasic function findfirst coule be enahnced to take two sequences as input. One with a sequence of column names or numbers and the other with the search criteria for each of the fields.
The other option would be to create not just findfirst but also findnext and approach it that way.
Adapting the findfirst for sequences would be the fastes road. I'll see about doing just that and posting the results here or on the wiki.
EDIT - before I do that however another thought.
I have another function in my toolbox that I use on occaision
| Code: |
function findFirstFltr( oRS as variant, sCriteria as string ) as variant
oRS.setPropertyValue("Filter", sCriteria)
oRS.setPropertyValue("ApplyFilter", True)
' To filter data execute again
oRS.execute()
if not oRS.isBeforeFirst then
'no records returned
findFirstFltr = NULL
else
'move to first record
oRS.Next
findFirstFltrt = oRS.getBookMark
endif
end function
|
I use this to set more complicated search criteria. I leave the action of removing the filter for empty recordset to the calling routing as one should keep a copy of the current bookmark prior to the call to this routine, not inside the routine.
Depending on what you are needing to accomplish it might be a better way to go.
Drew |
|
| Back to top |
|
|
Billyray
OOo Enthusiast
Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio
|
| Posted: Sat Mar 10, 2007 5:30 pm Post subject: |
|
|
Wow, thanks Drew, this is very helpful!
Here's a thought; What if I concatenated the Last Name, the First Name and the CustomerID, all together with some spaces to keep it legible in a query, then split out the CustomerID and search for the CustomerID using the Find First since that will be a unique record no matter what? Do you think that is doable? The CustomerID is also the primary key.
I'm just not sure how to create a query like that, or how to get it into the property box of the combobox, or exactly how I would split out the CustomerID to call the event subroutine. 
A thousand Thanks for your help already! |
|
| Back to top |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Sat Mar 10, 2007 5:40 pm Post subject: |
|
|
Well Yeah, searching for a unique identifer is always a good thing... 
Creating a query that concatenates valuues is quite simple, and as for using this to populate your list box that is also.
My dearest, whom I asked to let me check something on the Ubuntu machine - so could she get off for a little bit - just informed me that she is getting of her computer..AND so am I LOL...WE are going out for a bit, Karaoke I think...Gotta love her..so can't do a long example.
If you search the Base forum for the word concat I think you will find what you need. Otherwise I will check back in the morning and will take you through doing it that way, it is pretty simple actualy - once you see it done. |
|
| Back to top |
|
|
Billyray
OOo Enthusiast
Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio
|
| Posted: Sun Mar 11, 2007 12:21 pm Post subject: |
|
|
hey Drew, THANKS for pointing me in the right direction with the "concat!" I've adapted the code from the MSA FAQ using the idea I mentioned above. I've separated the two code blocks, putting the FindFirst function in the My Macro/Standard area, and putting the Find_Combo_AfterUpdate in the Customer Form/Standard area. I haven't changed the FindFirst function at all (except for the correction mentioned above) and am attaching the changes I made to the Find_Combo_AfterUpdate below:
| Code: |
Sub Find_Combo_AfterUpdate( OEv as Object)
' Oev is the event object automatically sent
' to sub procedure call
' Oev will have a property Source
' that is the control that triggered
' the call to this procedure
Dim oForm as variant ' The dataform obejct
Dim oComboBox as variant ' The combobox control
Dim oBkMark as variant
Dim iID as double
oForm = oEv.Source.Model.Parent
oComboBox = oForm.getByName( "LnameSearch" )
iID = Val( Mid( oComboBox.Text, InStr( oComboBox.Text, "#") + 1, (Len( oComboBox.Text))-(InStr( oComboBox.Text, "#"))))
oBkMark = FindFirst( oForm.CreateResultSet(), "CustomersID", iID )
' oForm.CreateResultSet() is equivilant here to me.recordsetclone
if not IsEmpty( oBkMark ) then
oForm.moveToBookmark( oBkMark )
else
msgBox( oComboBox.Text & " not found" )
end if
End Sub
|
Here's the SQL in the combobox:
| Code: |
SELECT CONCAT(`Last Name`,', ',`First Name MI`,' ID#',`CustomersID`) from Customers order by `Last Name`
|
The good news is that now I can be sure of getting exactly the right customer's record (not surprisingly there are quite a few Smiths). And I can see the first name to make sure I select the right one.
The not so good news, is that it does take some time to do a findfirst search on the CustomersID field, which I think must be out of order since I need the Last Name sort for the combo box. On a recordset of 1300 records, it takes about 3-5 seconds for a search to be found. Maybe I need a little "please Wait" notice during this time. 
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003. |
|
| Back to top |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Sun Mar 11, 2007 2:09 pm Post subject: |
|
|
Please wait...notice..now there should be a faster way, don't you think.
When I put up the findfirst routine I was trying to show how someone could replicate a VBA command using ooBasic. I would not say it would be my choice for performing the action in Base.
Here is a copy of your routine with a different approach - using a filter.
| Code: | Sub Find_Combo_AfterUpdate( OEv as Object)
' Oev is the event object automatically sent
' to sub procedure call
' Oev will have a property Source
' that is the control that triggered
' the call to this procedure
Dim oForm as variant ' The dataform obejct
Dim oComboBox as variant ' The combobox control
Dim oBkMark as variant
Dim iID as double
oForm = oEv.Source.Model.Parent
oComboBox = oEv.Source ' oForm.getByName( "LnameSearch" )
iID = Val( Mid( oComboBox.Text, InStr( oComboBox.Text, "#") + 1, (Len( oComboBox.Text))-(InStr( oComboBox.Text, "#"))))
' oBkMark = FindFirst( oForm.CreateResultSet(), "CustomersID", iID )
' oForm.CreateResultSet() is equivilant here to me.recordsetclone
' if not IsEmpty( oBkMark ) then
' oForm.moveToBookmark( oBkMark )
' else
' msgBox( oComboBox.Text & " not found" )
'end if
oForm.setPropertyValue("Filter", "`CustomersID` = " & str(iID) )
oForm.setPropertyValue("ApplyFilter", True)
oForm.reload
End Sub |
The first thing to notice is that I removed the call to the findfirst function completely. Instead using the filter setting on the result set.
When a filter is set is actualy appending a WHERE clause to the SQL statement that loads the result set. So let's say your form is based on the table "Customers", what is actually happening is that Base created the SQL command
SELECT * FROM `Customers`
and that is the actual command on the dataaform object. When I apply the filter with the line
oForm.setPropertyValue("Filter", "`CustomersID` = " & str(iID) )
I am altering the command to be ( assuming the combo box had the record for iID = 3 )
SELECT * FROM "Customers"
WHERE "CustomerID" = 3
The reload function then re-executes the updated query.
I am confident that using this approach should give you enough performance boost that no 'Please wait....' notice will be required.
It does change how you think about working with your form a little however. The main difference being that using the FindFirst function always leave the dateform resultset fully populated with your tables records, so even after the user has selected a record via the combobox they can still browse the record set with the data navigator buttons. Not so here, when the filter is applied the dataform result set will have exactly one record. However if look closely you see that there is also a button on the data navigator tool bar for 'Apply filter', after this routine runs the button will be selected, all your user needs to do to remove the filter is click the button on the toolbar. Also, the filter will remain in place, even if it is not being used now, so clicking again will change the result set back to the record last selected in the combo box.
Try this routine and if you would not mind let me know if it is faster then the FindFirst routine. I would be very surprised if it is not, although I have not tested the two approaches against an Access database as it appears you are using. I know it is faster for a native Base ( embedded HSQLdb ) file.
Drew |
|
| Back to top |
|
|
Billyray
OOo Enthusiast
Joined: 06 Mar 2007
Posts: 144
Location: Lake Erie's Shore in Ohio
|
| Posted: Sun Mar 11, 2007 2:52 pm Post subject: |
|
|
I tried the filter idea, and it is indeed faster! I think it is preferable in this situation, and like you said, the filter is easy enough to remove. I'm glad to have the bookmark idea too though, as there are situations where the bookmark would be better.
I should mention, in case you haven't noticed my new sig, that I am using MySql which is connected through JDBC on my desktop. I converted my Access database to MySql, as it was my understanding the OOo Base could not modify Access mdb. files.
I am quite satisfied with the Name search now, so I have a few more search boxes to design, similar to it, and then I have a table control which lists the Work Orders that this customer has on file. I need to be able to click on the Work Order number, and have the Orders Form open to that particular Work Order. I have the Work Order form design finished, so it's just a matter of getting the customer form adjusted to select it. I am thinking your filter idea may be just the thing to use too! 
_________________
Billyray
using:
Linux distro: Ubuntu 10.04 LTS, Lucid Lynx, OOo Base 3.3, connected to MySql database using Java jdbc (note: ONLY sun-java-6-1.6.0_22 jre works right), and converted from MS Access 2003. |
|
| Back to top |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Sun Mar 11, 2007 10:18 pm Post subject: |
|
|
| Quote: | | I should mention, in case you haven't noticed my new sig, that I am using MySql which is connected through JDBC on my desktop. I converted my Access database to MySql, as it was my understanding the OOo Base could not modify Access mdb. files. |
Nope, just goes to show how observant or not I can be at times. In 20 plus years of developing software I can no longer enumerate the number of hours I have spent chasing down an anomaly only to find I missed something that was staring me in the face.
I wouldn't agree on the ability to modify mdb files necessarily however. Granted, you can't change forms, reports or even query definitions. From what I can tell however, reading and writing the tables in a vanilla flavor MDB file works well, on a MSWindows desktop.
There are issues doing so on Ubuntu, even with the data tables, or so I hear. I' don't know what the situation is with Solaris or Novell's *nix?
Using MySQL with Base had a few issues also, when last I was talking on the forum - something I was away from for 5 months and two releases ago - during which time I did not have use of OOo.
Your post has helped to kick start me back into thinking Base development.
Now wouldn't it be nice if the Combo and List box controls allowed multiple columns - then we will be able to simply code this
| Code: |
Sub Find_Combo_AfterUpdate_later( OEv as Object)
Dim oForm as variant ' The dataform obejct
Dim oComboBox as variant ' The combobox control
oForm = oEv.Source.Model.Parent
oComboBox = oEv.Source
oForm.setPropertyValue( "Filter", _
" ""StudentID"" = " & _
oComboBox.Columns(2).Text )
oForm.setPropertyValue("ApplyFilter", True)
oForm.reload
End Sub
|
or something similar. |
|
| Back to top |
|
|
|
|
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
|
