Update records between subforms through a lookup field

[cuadradoja]

My situation is the following:

I have two forms from different tables and at the same time, each form contains a different subform; that is, subform 1 and subform 2. Also, each subform is made up of different fields, among which, in the first subform there's an Input type field called cod_num1 and in the second subform, another lookup type field called cod_num2. My question is, how can I update a record from subform 1 from subform 2, based on the value selected in the cod_num2 field?

I've tried it several times from the same field applying in Lookup/After browse, but so far I haven't had any favorable results. I really need some help...

[kev1n]

If subform 2 is on another form than subform 1, they are not open at the same time. Right?
Then you will probably need to run an Update query after selecting a value feom the 2nd subform.

[cuadradoja]

That's precisely what I've tried to do, but when I put the condition in AS, the field that I want to be updated in subform 2 does not. Would it be too much to ask you to give me some concrete idea to be able to achieve it?

[kev1n]

Could you post the code you tried? And how are the to subforms related to each other?

Including screenshots or a mockup of your form/design would be an valuable addition to help clarify and illustrate the matter at hand.

[cuadradoja]

First of all, in the following image I show the 'Preselección' form, where I select in [pre_sdr_spn] the value I want. This value comes from the same table where the field I want to update is located: [sdr_pro_des], which is of type checkbox.

Preselección Form.jpg

The code I used in the after browse to get it to update the field [sdr_pro_des] in the other table was as follows:

$sql = "UPDATE sdr_pro
SET sdr_pro_des = '1'
WHERE sdr_pro_spn = pre_sdr_spn ";

nuRunQuery($sql);

The issue is that when I save, the update of field [sdr_pro_des] is not done, as shown in the image below:

SDR Form.jpg

The curious thing is that when I change the condition in WHERE, with another field that belongs to the same table of the 'Request Request [SDR]' form, then if it updates correctly. In truth, I don't know what I'm doing wrong in the WHERE condition and I don't know if this information is enough for you to help me with this...

[kev1n]

Does pre_sdr_spn belong to the table sdr_pro?

The statement is updating the value in the column "sdr_pro_des" of the "sdr_pro" table, setting it to the value '1' for any row in the table where the value in the column "sdr_pro_spn" is equal to the value in the column "pre_sdr_spn" (of the table sdr_pro)

[cuadradoja]

Regarding the first question, the field pre_sdr_spn as such does not belong to the sdr_pro table, but the values ​​it shows do come from that table.

With the other question, I would say that the statement is updating the value in the column "sdr_pro_des" of the "sdr_pro" table, setting it to the value '1' for any row in the table where the value in the column "sdr_pro_spn" is equal to the value in the column "pre_sdr_spn" (of the table pre_sdr).

pre_sdr is the table of the first form where the required value is selected, i.e. the field: pre_sdr_spn.

[kev1n]

Try running the SQL in phpMyAdmin and most likely you will see errors since pre_sdr_spn does not belong to the table sdr_pro.

Shouldn't pre_sdr_spn be replaced with the value you're picking?