Macro not selecting Value of listbox

English
, , ,
#1

I have an AI designed macro that is supposed to read the ID value of a listbox tied to a Reports Table and then opens the report named using the ID.

It gets A Basic Runtime Error Property or Method Not Found. Value

It highlights a line - nID = oListBox.Value
The Bound Value is set to 1, where ID is located.

Can this macro work?

Here is the macro:


Sub OpenReportByID(oEvent As Object)
    Dim oForm As Object
    Dim oListBox As Object
    Dim nID As Long
    Dim sReportName As String
    Dim oConnection As Object
    Dim oStatement As Object
    Dim oResult As Object
    Dim oReportDoc As Object
    
    ' Get the form that triggered the event'
    oForm = oEvent.Source.Model.Parent
    
    ' Get the ListBox control'
    oListBox = oForm.getByName("lstReports") ' Change to your ListBox name'
    
    ' Get the selected ID (bound value)'
    nID = oListBox.Value
    
    If nID > 0 Then
        ' Connect to the database'
        oConnection = ThisDatabaseDocument.CurrentController.ActiveConnection
        If IsNull(oConnection) Then
            ThisDatabaseDocument.CurrentController.connect
            oConnection = ThisDatabaseDocument.CurrentController.ActiveConnection
        End If
        
        ' Create SQL to get the report name'
        oStatement = oConnection.createStatement
        oResult = oStatement.executeQuery("SELECT ""ReportName"" FROM ""Reports Table"" WHERE ""ID"" = " & nID)
        
        If oResult.next Then
            sReportName = oResult.getString(1)
            
            ' Open the report'
            oReportDoc = ThisDatabaseDocument.ReportDocuments.getByName(sReportName)
            oReportDoc.open
        Else
            MsgBox "No report found for this ID."
        End If
    Else
        MsgBox "Please select a valid report."
    End If
End Sub
#2

First of all, to write code for LO, install the MRI extension.
Then do it stepwise.

  1. Check via MRI the properties and methods of ListBox.
  2. Check (print) the retrieved value from the ListBox.

You can assign a data source to the form. Then
oConnection = oForm.ActiveConnection

#3

Compare to the following thread to get the sekected Value:
RobertG also linked the proper part of the documentation.

I can understand the principle, so: Yes, but it may contain more errors like the first, if you don’t look up entries but guess names in AI-mode.
.
Actually I don’t see, why it is necessary to get a database-Id from a listbox, then query the database for the name. I would directly have the selected name (string) as value (bound field). Then call report from this name. (Error possible, if your list is not perfect. But that is the same as now.)
EDIT: See comment below by RobertG to see this implemented.

#4

Development Tools

#5

@fpy Have you used the linked “Development Tools” for Base?

I don’t see Base listed and also no forms for Writer/Calc.

#6

fair. maybe this would be more accurate → Debugging a Basic Program :wink:

#7

:ok: but first of all I would install MRI.
And then proceed writing the code stepwise.

  1. Assign the variables.
  2. Check retrieved values.
#8

:ok: …
3. read LibreOffice: ListBox Service Reference
4. … etc

but,
just probably hopeless for someone posting about his AI hallucinated macro :wink:

#9

I guess that’s the main obstacle for user, who never did coding before. AI can be quite useful, but instead of writing the code you have to invest a lot in checking, testing and debugging.
(I read the “development story” of the WriterAgent-extension recently… GitHub - KeithCu/writeragent: A LibreOffice extension that adds agentic AI features. · GitHub
)

#10

There is no property Value available for a listbox. Take CurrentValue or SelectedValue.

This code will be enough for opening a report by a listbox. CurrentValue should be the name of the report, not an “ID”.

Sub OpenReport(oEvent AS OBJECT)
	DIM oListBox AS OBJECT
	DIM stReport AS STRING
	oListbox = oEvent.Source.Model
	stReport = oListbox.CurrentValue
	ThisDatabaseDocument.ReportDocuments.GetByName(stReport).open()
End Sub

Report_open_by_Listbox.odb (19.7 KB)
Open the form. When choosing the report it will be opened.

2 Likes
#11

as far I recall @fpy was the first one who propagates a LOT of AI ( especially as screenshots ) :wink:

1 Like
#12

who’s writing in French nearby ? :expressionless:

Wer schreibt in der Nähe auf Französisch?

#13

nothing realy specific to AI.
e.g. Recording a Macro

Pareto principle - Wikipedia

1 Like
#14

I think fundamental to stress the importance of having MRI for writing code in LO.
Because of this, I didn’t mention .Value in the answer.
And the importance of a stepwise approach via MRI and print/MsgBox before trying to write a complete routine.

#15

I have tried your macro in your db. It works, but when I try on my db, using ValueList options and sql calling a table, I get an error. I’ll keep trying to figure out what Im doing wrong.
BTW, I tried to install the MRI extension, mentioned in earlier posts by others, but the installer said it did not have a signature and would not install.
Thanks for the help, I’ll keep trying.

#16

From this Site

:grey_question:
No installer.
Download .oxt > LO > add extension.

The source of your ListBox is the DB Reports table, or ValueList?
:thinking:

#17

@TNBilder: Try through SQL something like this in field properties → Data :

SELECT "ReportName",  "ReportName" FROM "Reports Table"

This will show the ReportName in the field and will choose ReportName when bounded field is set to ‘1’
If bounded field is set to ‘0’

SELECT "ReportName" FROM "Reports Table"

would be enough.

#18

Thank You, I got it to work for my db.

#19

Try

  • nID=oListBox.selectedValue()
#20

Yes, seems quite similiar to the comment above (25 days earlier):

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.