Page 1 of 1

[SOLVED] Goto a specific record

PostPosted: Fri Jun 07, 2019 5:04 pm
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
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   Expand viewCollapse view
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("")
  DataSource = DatabaseContext.getByName("StampMaster"
  InteractionHandler = createUnoService("")
  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"

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

End Sub

Re: Goto a specific record

PostPosted: Fri Jun 07, 2019 5:51 pm
by Villeroy
Forget macros. Nightmares don't help anybody.
Create a helping table with one row and 2 columns for now.
Code: Select all   Expand viewCollapse view

menu:View>Refresh Tables. Now you see a new table with one row.
Create a query:
Code: Select all   Expand viewCollapse view
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.

Re: Goto a specific record

PostPosted: Sat Jun 08, 2019 3:49 am
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.

Re: Goto a specific record

PostPosted: Sat Jun 08, 2019 1:32 pm
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.

Re: Goto a specific record

PostPosted: Sat Jun 08, 2019 1:50 pm
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

Re: Goto a specific record

PostPosted: Sat Jun 08, 2019 6:11 pm
by Villeroy ... 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).

Re: Goto a specific record

PostPosted: Sat Jun 08, 2019 6:54 pm
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.

Re: Goto a specific record

PostPosted: Sun Jun 09, 2019 1:54 pm
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.

Re: Goto a specific record

PostPosted: Mon Jun 10, 2019 5:15 am
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.