Ask Your Question

LOBA: Find the record number from an ID (or other query search)

asked 2017-01-05 21:10:34 +0200

EasyTrieve gravatar image

In a form, I can see how to select a record using the LOBA (LibreOffice BAsic) absolute method, like this:


It causes the form to move to the given record.

But how is the best way to get a record# from something like a ...WHERE ID=x search?

Here's what I know so far:

1) The ROW_NUMBER() function, can be used like this:


Unfortunately, my version of LO, the latest,, does not seem to support this HSQL function.

2) I imagine I could run a query of all ID's, load the results into an array, search the array for the ID, and use array index+1 as the row number. But this seems slow, awkward, and ignores the index.

3) I'm looking for something like findFirst() in Access. I did find findFirst() in LOBA, but it appears to be for text searches.

For example, say you have a simple table of cities, with two fields, ID and City, and you have a simple form, which shows only the city and nothing else. Then using only Basic, how can I cause the form to display a given city if I know the city's ID?

edit retag flag offensive close merge delete


EDIT: For 3) above, I meant FindFirst in Access, i.e. capitalized and without quotes. Not sure why I can sometimes edit my own question, and sometimes not. And at the moment, I can't even edit my comments. Strange.

EasyTrieve gravatar imageEasyTrieve ( 2017-01-06 22:04:39 +0200 )edit

Just a note: the proper name is StarBasic, no need to add another strange abbreviation that would be completely unsearchable.

Mike Kaganski gravatar imageMike Kaganski ( 2017-02-28 19:42:52 +0200 )edit

@Mike Kaganski, I see your point, but just look at the LO menu, where they call it "LibreOiffice Basic": Tools > Macros > Organize macros > LibreOffice Basic

EasyTrieve gravatar imageEasyTrieve ( 2017-02-28 22:34:33 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2017-02-28 18:25:31 +0200

EasyTrieve gravatar image

updated 2017-02-28 18:28:17 +0200

I finally found a solutionand would have posted it here sooner, but for the fact that would crash when I tried to add this answer, till now. (Think the bug in is now finally fixed.)

LO is missing the native function to do this, but there is a workaround if the dataset is not too big. Here's a working example that uses it. It goes like this:

Assuming once I have the ID I'm seeking, I SQL a simple table of just ID, that I can quickly search. This table needs to be sorted as in the underlying dataset (table or query). Then I loop thru it and once I find the ID I'm seeking execute oResults.getRow() to get the row number. Then I can use that row# to move to that record in the underlying data set with Absolute(row#).

As noted in the code, this solution uses a loop rather than a normal fast, indexed lookup function. (Perhaps someday LO can add a findFirst type of function to support this procedure using indexes.)

Again, see the answer to this other related question

edit flag offensive delete link more

answered 2017-01-06 12:27:50 +0200

peterwt gravatar image

updated 2017-01-10 17:30:56 +0200

You don't need to use a Basic routine.
Create a Form showing the ID and City. Make the Form source a parameter query where the parameter required is the ID. When this Form is opened it will prompt for the ID and when entered will show the data for only that ID.

EDITED 9/1/2017

@Ratslinger There is a small problen in the database you uploaded. In the macro DisplayUsingSQL the first record is not displayed. As you have moved to the first record before the "While" line this moves to the second record before displaying the records. Inserting "oResult.previous" before the While then displays all the records.

@EasyTrieve I have uploaded a database example which does as you asked. The Form source is the Table. The Macro, run on clicking the button, first saves the entered ID in a variable. The Query has no WHWER clause so the result set contains the complete Table. The result set is searched for a match on the ID Field using the entered ID. On a match the Row is found and used to move the Form to that Record. If there is no match a message is displayed.

Your reference to the MS Access FindFirst() is part of the DAO in VBA. It is one of the rich features of Access which are not replicated in LO Base.


EDITED 10/01/2017

@EasyTrieve There is a better and simpler way using a Form Filter. Attach the macro below to the button in the Form in my sample.

Sub Findit(oEvent)
Dim inp As Integer
oForm = oEvent.Source.Model.Parent
inp = oForm.getByName("Input").Text
sFilter="""ID"" = " & inp
End Sub
edit flag offensive delete link more


@peterwt, thanks very much, but hold on a bit, I've got something that's more to what I was looking for working. There is a much better way to do this I think. ..I need a little more time to work out a smaller detail of it, and will share it with you when I get it complete. Thanks.

EasyTrieve gravatar imageEasyTrieve ( 2017-01-09 18:59:42 +0200 )edit

answered 2020-08-28 11:07:52 +0200

BlueBike gravatar image

Why don' t you use acces2base ? Access2Base is a LibreOffice/OpenOffice Basic library of macros for (business or personal) application developers and advanced users. Their syntax and their meaning are directly inspired by MSAccess.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-01-05 21:10:34 +0200

Seen: 898 times

Last updated: Aug 28 '20