Copy field table in field of other table

base
macro

asked 2021-01-05 18:53:18 +0100

Primus
3 ●2 ●2

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.

edit retag flag offensive close merge delete

Comments

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 ( 2021-01-05 20:12:02 +0100 )edit

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.

Ratslinger ( 2021-01-06 18:53:17 +0100 )edit

add a comment

2 Answers

Sort by » oldest newest most voted

answered 2021-01-12 02:44:52 +0100

Ratslinger
25660 ●9 ●35 ●134

updated 2021-01-12 02:48:18 +0100

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.

edit flag offensive delete link

Comments

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.

Primus ( 2021-01-12 10:46:04 +0100 )edit

@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.

Ratslinger ( 2021-01-12 18:53:21 +0100 )edit

add a comment

answered 2021-01-06 10:54:09 +0100

Primus
3 ●2 ●2

updated 2021-01-11 10:24:47 +0100

C:\fakepath\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.

edit flag offensive delete link

Comments

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.

Ratslinger ( 2021-01-06 18:06:57 +0100 )edit

Ok, I made an error for anwser...

Primus ( 2021-01-10 11:29:59 +0100 )edit

@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.

Ratslinger ( 2021-01-10 18:10:18 +0100 )edit

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

Primus ( 2021-01-11 10:27:39 +0100 )edit

add a comment

Copy field table in field of other table

Comments

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

Copy field table in field of other table edit

Comments

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

Copy field table in field of other table