Hi all,
A couple of questions:
1- I have never written a macro. Can anyone recommend a source (website or youtube) where I can start learning how to write a macro (I have plenty of time as the pandemic has me out of work for quite a while).
2- In the meantime, I have a report that uses a query where the user enters an order number and a report is generated from that number. Can a macro attached to a button send the current order ID to my report?
I am using MySQL 8.1 and LibreOffice 7.1.2.2 on W10 and a big thank you to anyone who can help!!
Hello,
It is good you have plenty of time as macros will require a lot. Although not the most current, for one place with many links, see this post → To learn LibreOffice Base are there introductions or tutorials?. The Pitonyak documents are the most all encompassing but not what one would call a learning tool especially for beginners.
As for your second question, yes. Take a look at the edited section of my answer in this post → Base: Print Button. The sample there has three forms. One for data entry, one for entry and printing and one for printing. The two for printing use rather small macros to accomplish just what you ask for.
It is good you have plenty of time
Sorry did not mean to be cold. I do hope you are OK and sorry to hear you are out of work - hoping this is temporary. I do pray every day for all Covid-19 victims/family/caregivers.
Hi Ratslinger.
I did not see anything cold about your response. I am still employed however my employer has closed until government regulations allow him to re-open. He has no worries about being closed long-term either so once things stabilize, I head back.
Returning back to the original post, I am having difficulties accessing the ID field on a subform with a macro. I will continue to try and try. If I give up I may post back here again soon.
Again, Thanks!
I want to be considerate in this time when so many are out of work or sick from this. Just hope all is OK.
As for your situation, first you get the main form:
oForm = ThisComponent.Drawpage.Forms.getByName("YOUR-FORM-NAME")
Then get sub form
oSubForm = oForm.getByName("YOUR-SUB-FORM-NAME")
Then use oSubForm to get your control. If in a table control you will then need to get the column and get the data from there.
Hope that helps.
The line with the asterisks is giving me a “NoSuchElementException” error. (This is the field on my filter table)
Sub PrintCurrent,
Dim oForm As Object
Dim oColumn As Object
Dim iField As Integer
Dim oSubForm As Object
Dim oController As Object
Dim oReportDoc As Object
oForm = ThisComponent.Drawpage.Forms.getByName(“MainForm”)
oSubForm = oForm.getByName(“Order”)
iField = oForm.getByName(“order_nbr”).Text
if oForm.isNew() Then
MsgBox “New Record - Cannot Print”
Exit Sub
End If
oSubForm = oForm.getByName(“PrintForm”)
oColumn = oSubForm.Columns.getByName(“order_id”) ****************
oColumn.updateInt(iField)
oSubForm.updateRow()
oController = ThisDatabaseDocument.currentController
(ran out of characters to post full macro)
OK a number of problems. If you need more space either use multiple comments or edit the question and post there noting it is an edit. Now from the code I have no idea what to tell you. You don’t tell me what “order_id” is. Is it the field name of the DB table? Is it the name of a column in a grid control?
Edit:
Went back to the sample referred to. The sub form “PrintForm” is simply the filter table. In the example it has two fields - ID (bool) and Sel_Inv (Int). So I would expect that in the filter table you replaced Sel_Inv with order_id because that is what your code is stating.
Let me start over.
REM ***** BASIC *****
Option Explicit
Sub OpenReport
Dim oController As Object
Dim oReportDoc As Object
oController = ThisDatabaseDocument.currentController
if not oController.isconnected then oController.connect
oReportDoc = Thisdatabasedocument.reportdocuments.getbyname(“facture-client”).open
End Sub
Sub PrintCurrent
Dim oForm As Object
Dim oColumn As Object
Dim iField As Integer
Dim oSubForm As Object
Dim oController As Object
Dim oReportDoc As Object
oForm = ThisComponent.Drawpage.Forms.getByName(“MainForm”)
oSubForm = oForm.getByName(“Order”)
iField = osubForm.getByName(“order_id”).Text
if oForm.isNew() Then
MsgBox “New Record - Cannot Print”
Exit Sub
End If
oSubForm = oForm.getByName(“PrintForm”)
oColumn = oForm.Columns.getByName(“order_id”)
oColumn.updateInt(iField)
oSubForm.updateRow()
oController = ThisDatabaseDocument.currentController
if not oController.isconnected then oController.connect
oReportDoc = Thisdatabasedocument.reportdocuments.getbyname(“facture-client”).open
End Sub
The MainForm contains client info. The subform (Order) includes order info. The filter table is called “print”. The sole field (other than PK) on the filter table is “order_id” and the name of the ID text field is also “order_id”.
The error comes from this line
oColumn = oForm.Columns.getByName(“order_id”)
The error seems correct. You have:
oSubForm = oForm.getByName("PrintForm")
Good so far. Then you go to access the wanted field:
oColumn = oForm.Columns.getByName("order_id")
Not good and different from the first list:
oColumn = oSubForm.Columns.getByName("order_id")
which seems to be what is needed. You’ve mixed up oForm and oSubForm.
Thank you Ratslinger. And thanks for your patience.
I have another db where the ‘order_id’ is on a sub-sub-form. Would I simply add a
oSubform = oform.getByName(“subsubform name”)?
No, you are not following the sequence - need one to obtain another. So you start with:
oForm = ThisComponent.Drawpage.Forms.getByName("MainForm")
You now use oForm to obtain controls on that form or a subform:
oSubForm = oForm.getByName("PrintForm")
and now you can use oSubform to get those controls or a sub sub form:
oSubSubForm = oSubForm.getByName("subsubForm_name")
Now you have access to the sub sub form controls with oSubSubForm
Thank you!
After about an hour of trying I was ready to give up and then I saw two things I missed. I needed to dim subsubform as object and bring down the PrintForm one layer in the form and also change the macro to reflect it was on an additional subform as well (the button was working before this last step but the table wasn’t updating with the current record displayed).
I have several buttons and forms left to configure but I managed to find errors that I didn’t know existed. Again, thank you Ratslinger for the assistance. I learned a few things today in the macro.
Jody
Good luck jodybingo. Lets hope it is very quick.