Macro issues: Trying to get data from a table in a form

[ruko81]

Hi everyone,

I have what feels like a basic question, but for some reason I can’t find the answer.

I’m working on a simple database to keep track of my clients, services, quotes and invoices.

I have a form to create new quotes, and within that I have a subform with a table (grid?).
One of the columns in that table is converted to a listbox so I can select a service I can add to my quote.
I am trying to create a Basic macro to fill the rest of the fields with the relevant information based on which service has been selected. (they get copied over from the Services table)

The problem is that I don’t know the correct way I need to access the data in the table on the form. A certain something (coughchatgptcough) gave me these lines:

oForm = ThisComponent.Drawpage.Forms.getByName("Quotes").getByName("QuoteLines")

sServiceID = oForm.getByName("clmDescription").CurrentValue

Quotes = main form
QuoteLines = subform
clmDescription = the collumn I’m trying to get the data from. With that I can get the relevant info from my Services table

I get an error that the component doesn’t exist, so I’m guessing this is not the correct way to point to the table on my subform, but ChatGPT insists that it is.

Anyone who knows the correct way?

[Villeroy]

Why should a Microsoft product produce well written LibreOffice macros? Install the MRI extension, read the docs.

Sub some_form_event(ev)
form = ev.Source
col_index = form.findColumn("column_name")
s = form.getString(col_index)
End Sub

Sub some_control_event(ev)
form = ev.Source.Model.getParent()
col_index = form.findColumn("column_name")
s = form.getString(col_index)
End Sub

[CRDF]

First step is to construct and check a
SELECT
for all data (= columns) you want to fill in this Form.
Check the retrieved data (columns) via print/MsgBox
 
Best if you upload a sample, without real data of course.
And, first of all, install MRI!

@ruko81

One of the columns in that table is converted to a listbox so I can select a service I can add to my quote.

So the event will be something like After update of this Table Control (“grid”) field.
Check this code as I said above.

@ruko81

I am trying to create a Basic macro to fill the rest of the fields with the relevant information based on which service has been selected. (they get copied over from the Services table)

Also check this as normally having the service code you do have the infos via query (SELECT).
No copy (normalization); and no need of code.
Say:

• Main Form: quote ID - pk | date | value $ | […]
• Sub Form = table QUOTE_DETAILS: ID (of this row in this table) |
  quote ID - fk | services within this quote = the ListBox | […]

[CRDF]

Practical example: say in QuoteLines you insert “job” via the ListBox and want to fill in a standard rate for each one.
This rate you must be able to edit according to the Tender convenience.
Same for a “Description” or any other columns of course.

Option Explicit
Sub Preset(Evt As Object)
	'GlobalScope.BasicLibraries.LoadLibrary("MRILib")
	Dim SF As Object, Grid As Object, LBService As Object, NValor As Object, Con As Object, Stmt As Object, Rst As Object
	Dim jid As Integer
	Dim sSQL As String
	On Error GoTo Erro
	REM SET VARIABLES FOR THE CONTROLS:
	LBService = Evt.Source ' the list box
	Grid = LBService.Parent
	SF = Grid.Parent ' the subform
	NValor = Grid.getByName("nValor") ' the numeric control rate of job
	REM CONSTRUCT THE QUERY:
	jid = LBService.SelectedValue ' get job id from the list box
	sSQL = "SELECT ""RATE"" FROM ""SERVICES"" WHERE ""ID"" = " & jid
	REM SET VARIABLES AND RUN THE QUERY:
	Con = SF.ActiveConnection()
	Stmt = Con.createStatement()
	Rst = Stmt.executeQuery(sSQL)
	Rst.next ' position the cursor @ 1st (only) row
	NValor.BoundField.Value = Rst.getDouble(1)
	Exit Sub
	Erro: MsgBox "ERRO " & Err & Chr(10) & "na linha " & erl
End Sub

[skyandrews]

Try

• sServiceID=oForm.columns.getByName(“clmDescription”).value()