Example or starting point for macro to filter table for form

[SonomaCharles]

My users use a form to edit a table. They currently have no way to navigate to a particular row other than creating a filter (not very non-techie-friendly) or scrolling. They mostly scroll. There are only about 150 records so it is not horrible to scroll to a particular record, but it is obviously less than ideal.

I would like to give them a text field with a macro behind it such that the user could key in, for example, ‘st’ and the macro would initiate a filter on Last_Name LIKE ‘%st%’.

I am not at all experienced with databases but I am an experienced coder so I am comfortable taking on macros and Basic. I see how to attach a macro to the text box and process the text each time it changes.

What I am not seeing is how to set the filter to Last_Name LIKE some value from within a Basic macro. Can someone give me a pointer?

Thanks much.
Charles

[SonomaCharles]

Based on MS Access posts on the Web I have been trying a button (just for purposes of testing setting a filter) that executes variations of

Sub Main
DoCmd.SetFilter WhereCondition:= [Last_Name] LIKE ‘%st%’
End Sub

But no matter what I have tried I get errors on the token following LIKE.

CM

[Wanderer]

@SonomaCharles

Based on MS Access posts

IMHO not the best fitting place. I’d suggest to search for “LibreOffice Base power filter” and consider also OpenOffice, as this is an old topic. The idea is to use a filter table in your form and sub-forms to show your data. You only need a macro, if you wish to eliminate the refresh-button.
.
The topic should also be covered in the Guide for Base, but I only read the german version.
If you prefer websites/forum you may start here:
https://forum.openoffice.org/en/forum/viewtopic.php?t=110457

Some (old) remarks to use like with this:

https://forum.openoffice.org/en/forum/viewtopic.php?t=108367

[RobertG]

Something like this:

SUB SetFilter(oEvent AS OBJECT)
 oField = oEvent.Source.Model
 oForm = oField.Parent
 stFilter = oField.Text
 oForm.filter = " UPPER(""Last_Name"") LIKE '%'||'" + UCase(stFilter) + "'||'%'"
 oForm.ApplyFilter = TRUE
 oForm.reload()
End Sub

Place the form control for text in the form for the filter. Link this procedure to event “leaving focus”.

[SonomaCharles]

@Wanderer Thank you, and thank you for your patience with me and my monolingual countrymen.

I apologize. I have ZERO experience with database programming and no knowledge of the “genealogy” of LibreOffice. I often hear it described as “a free replacement for MS Office” and so I thought MS Office posts might be relevant.

I do read the documentation. Again and again. I find it difficult to use because I am used to more a more formal style: syntax diagrams, formal specifications, and so forth. There is nothing wrong with the LibreOffice documentation, but it is written in a much more “suppose you wanted to do this; here is what happens if you do that” style than I am used to.

Thank you again for your help.
Charles

[SonomaCharles]

@RobertG Thanks! Works perfectly!

CM