Copy field table in field of other table

[Primus]

Hello.

My base had 4 tables :

T_CUSTOMERS
CUS_ID
CUS_LAST_NAME
CUS_FIRST_NAME

T_INVOICES
INV_ID
INV_DATE
CUS_ID
INV_VAT_RATE

T_INVOICES_SUB
INV_ID
PRO_ID
INV_UPET
INV_QTY

T_PRODUCTS
PRO_ID
PRO_LABEL
PRO_UPET

I have a form called F_INVOICES. Within, the main form is frmInvoices and the subform is frmInvoicesSub. In this subform, I have a griddata called grdInvoicesSub (its table is T_INVOICES_SUB).

I have a listbox called lstProId for the column PRO_ID of the T_PRODUCTS table for select a product, with this in SQL mode :

SELECT “PRO_LABEL” || ’ ’ ||
“PRO_UPET” , “PRO_ID” FROM
“T_PRODUCTS”

I’m seaching a macro for after selection of product and update of the lstProId listbox, I would like the PRO_UPET field of T_PRODUCTS to be copied in the INV_UPET field of the T_INVOICES_SUB field.

Important : PRO_UPET and INV_UPET must remain independent ; a subsequent change of the price in T_PRODUCTS table must not be reflected in the invoices already entered in T_INVOICES_SUB table. A JOIN type request is therefore not adapted.

Thanks.

[Ratslinger]

Hello,

It is too difficult to give a simple answer with what you have presented. An SQL statement in a macro is easy enough but you need to get access to the data. This is via names used in the forms, sub forms, tables, and controls. There is also the method of processing. Based upon your statements this most likely needs to be done only on new records - freezing the price (this is not completely clear in the question). So then what happens when there is a problem with the price/record/quantity entered? Need another process?

There needs to be more answers to proceed and a sample (added to Question and not an Answer) to insure naming is correct and all works OK. There is a lot more to your question than simply updating a field.

[Ratslinger]

Hello,

If you do not want to deal with learning LO API, try → Access2Base. It is installed in LO. I do not use it (except for rare occasion of answering a simple question) since it is just another level of interpretation. It is also not as effective as the API.

[Primus]

mdlBilling.odbSo far, I select a product in the T_PRODUCTS table. Thanks to the SQL statement, its price is displayed in the list box. I enter this price in the INV_UPET column and then the subtotal is calculated by multiplying this value by the quantity (but that is not the problem). What I’m trying to do is that this amount (PRO_UPET) is automatically inserted into INV_UPET when I select the product in the listbox.

In MS Access, things are simple:

Sub lstProId_AfterUpdate()
Me.INV_UPET.Value = Me.lstProId.Column(2)

End Sub

But with Base, it’s not easy.

[Ratslinger]

Please do NOT use an answer for additional information. Answers are for answering the original question which this does not. Use either a comment or edit your question and place there noting the edit. Then delete this.

Also, as stated in my comment:

There needs to be more answers to proceed and a sample (added to Question and not an Answer) to insure naming is correct and all works OK.

You have not provided anything asked for. Please read the comment again.

[Primus]

Ok, I made an error for anwser…

[Ratslinger]

@Primus,

Have asked for information twice previously and now a third time (see original comment and comment above). Cannot work without this. Now looking again at the question you have also not provided basic information like specific LO version, OS or what database you are using. Insure in the edited question a sample is provided to work with as I would only be guessing on the construction of what you have. What is already provided is far from enough.

[Primus]

I linked a database file in my first answer. My version is LO 7.0.4.2, Linux Mint 20.1.

[Ratslinger]

Hello,

Will base this answer on the information received.

There are a few issues. There is an open bug report tdf#135641 which affects a list box in a table control. Because of this it is necessary to reposition the cursor location in the table to another field in order to access the selected value. This is not the case when using a list box control. Also the selection is a concatenated value. Getting the needed information could be done with string extraction (as in the coded example) or by accessing the ID for the table in the selected value and executing an SQL statement to retrieve the amount.

Here is your example back with added commented code: mdlBilling.odb

Code is attached to the Item status changed event of the list box in the table control. BTW, also changed the UPET table column to a numeric field.

[Primus]

I really thank you for that answer. When I see the complexity of the code in relation to MS Access, I certainly would not have succeeded. Thanks again.

[Ratslinger]

@Primus,

Complexity is relative to experience using it. VBA is really no different to someone who has never used it before. Your situation was further complicated by multiple issues.

Glad you have something which works for you.

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.