I an form I would like 1 button per first letter of a record (e.g. button A, button B etc), that when pressed gives only those records where the field Name starts with that letter. The search for A is SELECT “lid-naam” FROM “db-leden” WHERE “lid-naam” = ‘Like “[AÀÁÂÃÄÆÅ]*”’.
But I cannot find any macro that does this (I am a dummy and newbie on LO base).
First step: Test you code in a query. The code you posted never works in in one of the internal database. Don’t know if it works in any databases. " will be set for masking field names and table names. ' will be used for mask text content.
SELECT "lid-naam" FROM "db-leden" WHERE "lid-naam" = Like 'A%'
This will show all lid-naam starting with A.
SELECT "lid-naam" FROM "db-leden" WHERE LEFT("lid-naam",1) IN ('A','À','Á','Â','Ã','Ä','Æ','Å')
This will show all lid-naam starting with the characters in the round brackets. If you set UPPER("lid-name") you will also see the content with lower cased starting characters.
Then you could start with the macro. Put content of the brackets in the tag of the button. So you could read this by macro and have to create only one macro and different buttons with the content of the first character.
SUB FilterFirstCharacter(oEvent AS OBJECT)
oField = oEvent.Source.Model
stFilterContent = oField.Tag
oForm = oField.Parent
oForm.filter = " LEFT(""lid-naam"",1) IN " + stFilterContent
oForm.ApplyFilter = TRUE
oForm.reload()
END SUB
Thanks for the answer, but what do you mean with ‘the tag of the button’, I do not see anywhere in the control property of the button the field ‘tag’? Sorry, very newbie…
Thought it will be called “Tag” in English. Have changed the language here and se it is called “Additional information” (see in the control properties → General, nearly last entry. This will read by oField.Tag.
I haven’t masked the field with double quotes. Have a look at the first post. Corrected it there to get double quotes in a string in macro. (""lid-naam"")
You have to start the macro by the button. It couldn’t be started directly, because it will miss the field, the form and all other objects.
It’s the second entry in "Events" → "Execute action". Press the button with 3 points and search for the procedure “FilterFirstCharacter”, which should be part of you database document. Don’t safe it in MyMacros. If you move the Base file to another system it won’t work.
Just read the error code: It is missing the parantheses, first (, but the second will be missed also.
.
Either add this to the tag
('A','À','Á','Â','Ã','Ä','Æ','Å')
or modify the code of the macro.
You have forgotten the brackets:
('A','À','Á','Â','Ã','Ä','Æ','Å')
or you have to change the macro code:
oForm.filter = " LEFT(""lid-naam"",1) IN (" + stFilterContent + ")"
yes, this works well. Thank you very much for your support, you all.
But I wonder now, after selecting a button, e.g. W I get all the records starting with W in the field lid-naam. How do I get back then to display all records. A different macro? An empty tag? Another kind of tag?
@JonIgartua : Have a look at the navigation bar. A filter has been set. You could click there to see all content and to see only the filtered content.
You could also create a button for setting back to “no Filter”.
SUB FilterDelete(oEvent AS OBJECT)
oForm = oEvent.Source.Model.Parent
oForm.ApplyFilter = False
oForm.reload()
END SUB
OK, that macro also works.
Now I copy/paste the subroutine in the same macro routines and modified the name of the macro and the field name.
Then I made the button in the form Crew and installed such as in the form Leden, but when I activate the macro it gives me an error for Leden?
WHat am I doing wrong or where can I modify the words lid into crew?
Have a look at the query, which should be executed. Seems yo start the filter from a button, which is in the form for “lid-naam”. Might be it should start in a form for “crew-naam”. “crew-naam” is unknown in this form.
Open the form navigator to see in which form you have created the button. Or did you copy the button and pasted it in another form and the whole form as source for the button has also been copied. So move the button in the form for “crew-naam”. You could do this in form navigator. But don’t move too much without saving the form and saving the database file also.
Sure, i did not name the two forms differently. After doing that and cut/paste the right buttons in the right forms, IT WORKED.
I was wondering if I have to learn programming (I am 70) or does there exist a comprehensive tutorial with examples?
Again a great thank you for your help.
https://ask.libreoffice.org/uploads/short-url/1fPpS0BKAgRURxNdARoWvKDDv7l.odb (no macros)
Have a look at “Filter Form 2”.
1 Like
Hello, I am seeking a macro that can close the form I am working in (i will then use this in a button on all my forms). I intended something found on the net, but none of them works. See beneath:
Sub FormulierSluiten (oEv as object)
dim sFormName As String
dim sTitle As String
dim iStart As Integer
Dim iLength As Integer
Dim iEnd As Integer
sTitle = ThisComponent.Title
iStart = Instr(sTitle,":") + 2
iEnd = Instr(sTitle,"(") - 1
iLength = iEnd - iStart
sFormName = Mid(sTitle, iStart, iLength)
ThisDatabaseDocument.FormDocuments.getbyname(sFormName).close
End Sub
Sub Snippet( Optional oInitialTarget )
Dim oCurrentController As Object
Dim oActiveConnection As Object
oCurrentController = oInitialTarget.CurrentController
oActiveConnection = oCurrentController.ActiveConnection
oActiveConnection.close()
oInitialTarget.close( True )
End Sub
Add a push button, form or subform does not matter.
Action: “Open document or URL”
URL: .uno:CloseDoc
1 Like