Access/Update Mail Merge Field in Writer form

[dreamquartz]

Hello All,

Need to be able to access/update Mail Merge Fields in a Writer document where the is no form.

I am trying to modify a simple Basic Macro, we use for opening forms with Filtered info, but I get stuck on the form issue.

Code: Select all

REM  *****  BASIC  *****
Const cDoc = "!fUpdateProjectInfo"
Const cForm = "fProjectNumber"
Const cHere = "FKProjectNumberID"
Const cThere = "ProjectNumber"

sub fUpdateProjectInfo(ev)
GlobalScope.BasicLibraries.loadLibrary("Tools")
sPath = DirectoryNameoutofPath(ThisComponent.URL, "/")
sPath = sPath &"/"& cDoc &".odt"
thisForm = ev.Source.Model.Parent
i = thisForm.findColumn(cHere)
var = thisForm.getString(i)
doc = StarDesktop.loadComponentFromURL(sPath, "_default", 0, Array())
form = doc.DrawPage.Forms.getByName(cForm)
form.Filter = ""& cThere &" = "& var &""
form.reload()
End Sub

As is shown above, there is the Constant cForm, This macro runs without a problem, because the form does exist.
Now I am trying to use a modified version of this macro where there is no cForm.

Using the Mail Merge function on a pre-formatted background in Writer, the ResultSet of Queries are easily placed. The forms are always forwarded in pdf-format.
When creating these type of forms, there are no forms needed. Using the 'Form Navigator' no forms are shown.

Now my question is how use access/update cThere if there are no forms.

Any suggestions are highly appreciated.

Dream

09/30/2026; moved to this forum, removed duplicate request; dropped status {CLOSED]

[Bidouille]

Save our time and provide a sample document.

NB: This question should have been posted in the forum dedicated to macros

[dreamquartz]

Double request (as above) removed

[DiGro]

Maybe a click on F9 ?

[dreamquartz]

As requested by Bidouille;
Attached you will find a very basic layout, using the following SQL Query:

Code: Select all

SELECT 
	 COALESCE ( "tPerson_1"."Surname" || ', ', '' ) ||
		 COALESCE ( "tPerson_1"."GivenName" || ' ', '' ) ||
		 COALESCE ( '(' || "tPerson_1"."Nickname" || ') ', '' ) ||
		 COALESCE ( "tPerson_1"."MiddleInitial(s)", '' ) ||
		 COALESCE ( ' ' || "tPerson_1"."Suffix", '' ) "Participant"
 FROM
	 "tPerson" "tPerson_1"

There can be a max of 12 Participants. The Numbering is added for every next Participant, including all the codes that are followed.
The Codes are manually added for every Participant, and will show just like the Numbering for every Participant.

The <Participant> filed is added to the form, using: Insert->Field->More Fields (Ctrl+F2); Selecting the DataBase and the Query, holding the SQL as per listed above.

I would like to be able to use the principle of opening the Form with a PushButton, using the same SQL Query.
The struggle I have with this, is that I cannot show all the Participants in the same form that way.

Opening the form, and using the the source via Ctrl+Shift+F4 works as required.

I use the Macro, listed initially above, for other forms, but none can show it as per the source principle.

Any suggestions are appreciated,

Dream

DataBase vs MailMerge.odt

(96.89 KiB) Downloaded 23 times

[Bidouille]

Why would you want to use a macro?
Your document looks strangely similar to what you can achieve with a sheet of labels:

[dreamquartz]

@Bidouille
You are absolutely right.
I am using the records indeed that way, but there is a significant difference between the form I use, and the Labels you mention.
I am using the document where I pull the info from a Query, located in the DataBase, by using the standard steps, as described above, and after every line I use 'Next record'.

As a stand alone form it works great, but because it is actually a pre-printed template form (area is a pdf-background) the Resultset of the Query must be placed in specific locations on the form.
For the simplicity of purposes and proprietary layout, I reduced the form significantly.
This means that the use of records, represented via a 'Text Box' can not be used, due to the fact they are not transparent.
I have not found a way to make Text Boxes, Combo Boxes, Table Controls, and the reset transparent.
If I could, it would solve the problem I have.

Having said that, I would like to be able therefore to fill out this form from within any other form, by with the use of a PushButton, there where it is useful for the User.
The Macro above indicates that for e.g. the ProjectNumber.
When the ProjectNumber is available on a Form, an other Form, based on the ProjectNumber can be easily created, but none of them can be with the pre-formatted layout.

When looking @ the example provided, I can create a form, add info, use Text Boxes, but I can not get the ResultSet I need.

Hope the explanation helps a little to explain the issue I face,

Dream

[Bidouille]

Remember that an ODT document is a ZIP archive. The XML content is inside it.
It would probably be easier to edit it by adding the field directly.

[F3K Total]

Why not using a report?
See attached file. To open the report you can doubleclick on it or use the small macro inside for opening.
EDIT 2026-11-05 9:15 UTC: The .odb file was made using Libreoffice 26.2.6, does not run with ApacheOpenOffice, sorry.