[SOLVED] Goto a specific record

Creating and using forms
Post Reply
lsemmens
Posts: 49
Joined: Thu May 22, 2014 11:17 am

[SOLVED] Goto a specific record

Post by lsemmens »

Further from my other Lookup Query, so ably solved by Villery (thanks again) I now have an issue where I cannot seem to find a suitable command and syntax to move to a specific record.

I've tried all sorts, the most promising being, (obviously SQL) I have an input box that allows me to enter the search parameter but, try as I might I cannot convince anything to take me to a specific record.

eg. If I enter "001a" I would hope to find the first record that has a reference to "001a" (especially in one particular field). The SQL statement
SELECT MA_SG_REF FROM MASTER WHERE MA_SG_REF = Data
would seem to be correct where Data is a variable that reflects what was parsed to the input box
Data = InputBox("Record Number",0,"1")
I get an error telling me that it cannot find MASTER - which is one of the Tables in the form.

The full sub is and it always fails at the SQL statement As far as I can establish the Database is recognised by the other parameters in the same sub:

Code: Select all

Sub Find2
  Dim Data  as Variant
  Dim oForm     'Reference to the form containing the primary record.
  Dim oSubForm  'Reference to the subForm 
  Dim i
  Dim n
  Dim sFormName 'Name of the SubForm
  dim oDoc as object
  dim oSearch as object
  dim oCursor as object
  dim dispatcher as object
	Dim DatabaseContext As Object
	Dim DataSource As Object
	Dim Connection As Object
	Dim InteractionHandler as Object
	Dim Statement As Object
	Dim ResultSet As Object
 
  DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
  DataSource = DatabaseContext.getByName("StampMaster"
  InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
  Connection = DataSource.ConnectWithCompletion(InteractionHandler)
   
Data = InputBox("SG number",0,"1")
If Data = 0 goto Fin
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("SELECT ""SM_SG_REF"" FROM ""MASTER ""WHERE"" SM_SG_REF = ""Data")

if isnull(ResultSet) then
    msgbox "Not Found", 64, "Title"

else
    oCursor = thisComponent.CurrentController.ViewCursor
    oCursor.gotoRange(ResultSet, false)
    msgbox "Found", 64, "Title"
end if
Fin:

End Sub
Last edited by robleyd on Mon Jun 10, 2019 5:20 am, edited 2 times in total.
Reason: Add green tick
LibreOfice Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.6 HQSLDB on Linux Mint 19 Tara - Cinnamon 3.8.9 (Gtk 3.22.30-1ubuntu3)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Goto a specific record

Post by Villeroy »

Forget macros. Nightmares don't help anybody.
----------------
Create a helping table with one row and 2 columns for now.
menu:Tools>SQL...

Code: Select all

CREATE TABLE "Filter"(TXT VARCHAR_IGNORECASE(20), ID TINYINT PRIMARY KEY);
INSERT INTO "Filter" VALUES(NULL, 0);
menu:View>Refresh Tables. Now you see a new table with one row.
----------
Create a query:

Code: Select all

SELECT "Data".* FROM "Data", "Filter" WHERE "Filter"."ID"=0 AND ("Data"."text_column" LIKE '%' || "Filter"."TXT" || '%' OR "Filter"."TXT" IS NULL);
Replace "Data" with the name of your table and "text_column" with the name of your text column.
This query selects all columns and rows from your data table where the text entered in row #0 of the filter table is part of the text column. It selects the entire set if the search string is Null.
------------
Create a new form with one text box. Bind the form to the following: SELECT * FROM "Filter" WHERE "ID"=0 which selects only the distinct row #0 with the right text value.
Form property "Allow modification" = Yes, allow deletion, insertion, show navigation toolbar = No. It important to allow modification only for this form.
The form's text box is bound to TXT. The only thing you can do with this form is changing the text in row #0 or the filter table.
---------------
Create a another form with a grid control bound to the above query. It may be a subform or just another main form, does not matter in this case.
Add a refresh button to the grid. Make sure it belongs to the second form, not the filter form.
---------------
Now you can enter a search string into the text box, push the refresh button and the matching rows appear in the grid.
---------------
You may notice that the resulting record set is read-only because the underlying query includes more than one table. If this is a problem, it is a solvable one.
---------------
You may use a combination of save button and my AutoRefresh macro as shown in your previous topic about list boxes, so you can type the search string, hit Enter and see the result.
--------------
You find dozends (if not hundreds) topics in the Base forum and in the Base examples forum dealing with "power filtering" which is the term we introduced on this forum for this method of filtering by means of a helper table.
---------------
And of course you can filter a Base form with a built-in feature. Hit the form filter button which enters a special filtering mode with an extra toolbar, enter LIKE %blah% and finish this mode by hitting the first button on that toolbar.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
lsemmens
Posts: 49
Joined: Thu May 22, 2014 11:17 am

Re: Goto a specific record

Post by lsemmens »

Thanks for that. Am I correct in that Filter only returns the filtered result set? I still need to be able to move to other "unfiltered" records afterwards and/or modify records too.

i.e. simply: find to a specific record - edit said record and move to the next record.

off to play with it now.
LibreOfice Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.6 HQSLDB on Linux Mint 19 Tara - Cinnamon 3.8.9 (Gtk 3.22.30-1ubuntu3)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Goto a specific record

Post by Villeroy »

What does the "next" record have in common with its predecessor? Are they perhaps sorted by date? Then create a sub-subform where you display the records with the same date.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
lsemmens
Posts: 49
Joined: Thu May 22, 2014 11:17 am

Re: Goto a specific record

Post by lsemmens »

Background. I am developing a database of Postage Stamps. Sorted by Country, then by a Catalogue code. So some may have commonalities and others may not, other than being issued in the same country for the same reason i.e. postage. The database gets quite complex with lookups for things like size, colour, subject, designer, printer, variants and so forth. The master Table is Country Info linked to a Slave Table for stamp info which is further linked to another table which keeps track of all the copies of said stamp in my collection. The Slave table is where all the work happens and this is where I am currently at, trying to find a method whereby I can return to particular record easily if, say I need to add a copy to my collection. Even without extensive Data in the database the size of the database is about 1.6Mb. (It's currently sitting at 3.2Mb).

Originally I started about 10 years ago developing it in M$ Accsux and it was working quite well until I struck the 2Gb limit, which I then worked around. But life got in the way and now I have time to start over.
IIRC the command that did all the work for me back then was "DO CMD GOTO RECORD mRecNo"

I'm currently playing with a form to select the record from which I hope to open the master form at the appropriate record Based on information from this thread
LibreOfice Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.6 HQSLDB on Linux Mint 19 Tara - Cinnamon 3.8.9 (Gtk 3.22.30-1ubuntu3)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Goto a specific record

Post by Villeroy »

http://forum.openoffice.org/en/forum/do ... hp?id=6442 is a database of mine with some 10,000 street names and a "power filter" matching the start of a given search string. If you open the form, remove any power filter, focus the table grid and hit the binocular icon on the navigation toolbar, you get the "goto first matching record" feature. Enter "zit", search in the name field only and choose "begins with". The search takes a lot of time until the cursor reaches the location named "Zitadelle".
It is far more efficient to apply a filter on "zit" and get all the records where the name starts with "zit". This works almost instantly. This is not exactly what you were looking for, however if your "next records" have something in common with the first matching record, a sub-sub-form could show all the "next" records belonging to the matching one regardless of position and sort order. In my "BerlinStreets.odb" such a sub-subform could show all other streets sharing the same PLZ (german postal code).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Goto a specific record

Post by UnklDonald418 »

Also, look at this example of filtering data using list boxes.
[Example #3] Filter/Search with Forms (leveraging SubForms)

The file LIKE_user_input_filter.odb demonstrates using up to 4 columns/list boxes to filter the data. I have successfully used that approach filtering with 7 columns so filtering by country, size, colour, subject, designer, printer etc should be fairly straightforward.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
lsemmens
Posts: 49
Joined: Thu May 22, 2014 11:17 am

Re: Goto a specific record

Post by lsemmens »

Thank you both for your expertise and assistance. I will be investigating, however, life might prevent me returning to this for a day or so.
LibreOfice Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.6 HQSLDB on Linux Mint 19 Tara - Cinnamon 3.8.9 (Gtk 3.22.30-1ubuntu3)
lsemmens
Posts: 49
Joined: Thu May 22, 2014 11:17 am

Re: Goto a specific record

Post by lsemmens »

I shall now mark this as SOLVED, you both put me on the right track with the end result being better than I initially intended.

In the end I used a couple of grids to select the appropriate records and then forms and sub forms on the same page.
LibreOfice Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.6 HQSLDB on Linux Mint 19 Tara - Cinnamon 3.8.9 (Gtk 3.22.30-1ubuntu3)
Post Reply