[base]: Macro - form: save, refresh, copy, paste

[Martin-PL]

Hello!
I need help with macro in LibreOffice Base (ver. 5.3).
I have a form called ‘Form1’ in which are MainForm (from table) and SubForm (from query). I need a macro that works after lost focus from textbox called ‘Text1’ and:

1. save recordord,
2. refresh form,
3. copy text from textbox called ‘Text2’ (in SubForm),
4. paste this text (from ‘Text2’) to textbox ‘Text1’ (in MainForm),
5. save and refresh again.

Recording macros don’t work. I couldn’t write this by myself. Could anyone help me?

ORDO-D.odb - see table: Dokumenty, queries: ipws and znaki, form: Dokumenty

[Ratslinger]

Why do you need to save & refresh before & after moving the text? What are you saving, there are two forms (sub & main)? Do you know anything about writing macros so you can maintain if necessary? Have you looked at this document - OOME click here or the LO documents on macros Chapter 9 - click here?

[Martin-PL]

When I save the main form and refresh, the sub form gets new data (concatenation of some values from main form).
I’ve read this document’s but I couldn’t do what I need… It’s too hard for me And the examples of marcros don’t work in my LO.

[Ratslinger]

Unfortunately this is the easy stuff. Don’t understand. Sub form is concatenation of data from main form which you want to AGAIN move into main form? Sounds redundant! What examples of what macros don’t work for you?

[Martin-PL]

In table i have three columns to describe type of record (client, topic, year, ex: Client1, Topic1, 2017). Query concatenate this columns and returns one-cell code-of-type, ex: “Client1-Topic1-2017”. Macro and subform help me count records in every code-of-type and define code-of-type-and-record, ex: “Client1-Topic1-2017-1”, next record in the same type: “Client1-Topic1-2017-2”, next record in another type: “Client2-Topic2-2017-1” etc. Now it works! If You want, I can send You my database

[Martin-PL]

I don’t know why macros don’t work. Everytime I have messages of error.

[Ratslinger]

I’ll read your comment a few more times but it seems like a straight forward query to get counts. If just counts wanted no concatenated fields necessary. Not sure why you are creating this grouping of fields. Have raised your karma. Please edit your original question (lower right of question edit and post .odb there. Please no personal or confidential info!

[Ratslinger]

Is it that you want blank fields to contain a 1? That is all I see the macro doing. Are you using the queries for reasons besides the macro?

[Martin-PL]

When you add new record by form, macro is very useful. Ex: you know Client name (Klient = FARA), you know topic (JRWA = 110), you know year (Rok = 2017), but you don’t know how many documents was added in this cathegory (FARA.110.2017). Macro copy the max number of document +1 from subform (from query ipws) and paste it in blank txtNumer in main form.
For me it’s very usefull. I have dozens of documents in all cathegories for different clients.

[Ratslinger]

Just to give you an idea of where to start here is some code to move text:

Sub ShowText()
	Dim oForm As Object
	Dim oField As Object
	oForm = ThisComponent.Drawpage.Forms.getByName("Your_Form")            'Get Form'
    oSubForm = oForm.getByName("Your_Sub_Form")                        'Get SubForm'
	Doc = ThisComponent
	DocCtl = Doc.getCurrentController()
	oGetField = oSubForm.getByName("Text2")
	oPutField = oForm.getByName("Text1")
    answer = oGetField.Text                                         'Get wanted text'
'Get the VIEW of the control'
   	CtlView = DocCtl.GetControl(oPutField)
	CtlView.Text = answer                                            'Insert text'
	oPutField.commit()                         'need if going to update record'
End Sub

[Ratslinger]

If this answers your question please click on the (upper left area of my answer).

[Martin-PL]

It works! Than You very much!

I completed the code:

    Sub ShowText()
    Dim oForm As Object
    Dim oField As Object


rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

    
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:RecSave", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:RecRefresh", "", 0, Array())
    
    
    oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")            'Get Form'
    oSubForm = oForm.getByName("SubForm")                        'Get SubForm'
    Doc = ThisComponent
    DocCtl = Doc.getCurrentController()
    oGetField = oSubForm.getByName("fmtNowy_numer")
    oPutField = oForm.getByName("fmtNumer")
    answer = oGetField.Text                                         'Get wanted text'
'Get the VIEW of the control'
    CtlView = DocCtl.GetControl(oPutField)
    CtlView.Text = answer                                            'Insert text'
    oPutField.commit()                         'need if going to update record'
  
 
 rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:RecSave", "", 0, Array())

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:RecRefresh", "", 0, Array())
  
    
End Sub