Updating a column table in a form with a join query as a RecordSet (1 Viewer)

[pacctono]

Hello,

I have a form that its a record set is a join query with four tables and I would like to update one column of the main table. The RecordSet query is:

<code>
SELECT t.au_control, t.fe_fecha, t.tx_operacion, t.tx_documento_contable, t.tx_referencia, t.tx_descripcion, t.mo_ingreso, t.mo_egreso, t.mo_saldo,
t.tx_cuenta, b.tipo_cuenta, b.banco_cuenta, e.periodo, e.asiento_id, t.bo_conciliado, op.bo_comision,
IIf(0=Nz([op].[bo_comision],0) And 0=InStr(1,UCase([t].[tx_descripcion]),'COM'),False,True) AS booComision
FROM ((ba_banesco AS t LEFT JOIN diarios_enc_2020 AS e ON t.tx_referencia = e.banco_refer)
INNER JOIN bancos AS b ON t.tx_cuenta = b.cuenta)
LEFT JOIN ba_operaciones AS op ON (t.tx_banco = op.tx_banco) AND (t.tx_operacion = op.tx_operacion);
</code>

I am using the table "diarios..." that is not in my subsystem. I would like to change only the 'bo_conciliado' column in the t table (ba_banesco) but the form does not allow me.

I think I can create a temporal table with all the columns in the join and use it as the RecordSet.

Can anybody suggest me any solution using the joins???

 

[Pat Hartman]

Joins, even four tables/queries, don't make a query not updateable. However, if any one of the joined tables/queries is not updateable, then nothing in the query will be updateable.

Other common issues.
1. aggregation. I don't see any aggregation in the posted query but I also can't tell if any of the objects you are joining to are queries and those might aggregate data.
2. How the tables are related also influences whether or not the join will be updateable. Cartesian products are not usually updateable.

Please post a picture of your relationship window and expand the tables enough so that we can identify the PK's and join fields. Or, post the database and tell us which query to look at.

 

[pacctono]

Joins, even four tables/queries, don't make a query not updateable. However, if any one of the joined tables/queries is not updateable, then nothing in the query will be updateable.

Other common issues.
1. aggregation. I don't see any aggregation in the posted query but I also can't tell if any of the objects you are joining to are queries and those might aggregate data.
2. How the tables are related also influences whether or not the join will be updateable. Cartesian products are not usually updateable.

Please post a picture of your relationship window and expand the tables enough so that we can identify the PK's and join fields. Or, post the database and tell us which query to look at.

Here is the picture, I think you talk about. By the way, I meant RecordSource, no RecordSet

 

[Pat Hartman]

tables t and b do not have primary keys. ALL tables need a PK. If there is no natural key, then use an autonumber.

The join be tween tables t and e and between t and b are data field to data field. these will not be updateable.

Start by fixing the relationships.

Relationships will always be from a PK to a data field in a 1-m relationship and from a PK to a PK in a 1-1 relationship. The joins in a query should mimic the relationships.

Joins will work on any data field to any data field provided the data types are the same so if you have a customer table and a vendor table, you can join on CustomerName to VendorName. In reality it doesn't make any sense but if you are looking for customers who are also vendors, it does make sense. Or you could join CustomerName to PetName in the pet table. This one is unlikely to be of any value whatsoever but you can do it. Relationships are for life and they have consequences. Joins are temporary. Its like the difference between your wife and your mistress.

 

[pacctono]

tables t and b do not have primary keys. ALL tables need a PK. If there is no natural key, then use an autonumber.

The join be tween tables t and e and between t and b are data field to data field. these will not be updateable.

Start by fixing the relationships.

Relationships will always be from a PK to a data field in a 1-m relationship and from a PK to a PK in a 1-1 relationship. The joins in a query should mimic the relationships.

Joins will work on any data field to any data field provided the data types are the same so if you have a customer table and a vendor table, you can join on CustomerName to VendorName. In reality it doesn't make any sense but if you are looking for customers who are also vendors, it does make sense. Or you could join CustomerName to PetName in the pet table. This one is unlikely to be of any value whatsoever but you can do it. Relationships are for life and they have consequences. Joins are temporary. Its like the difference between your wife and your mistress.

I forgot to answer you. Thanks for your help. I was very useful.

 

[Pat Hartman]

You're welcome