2つのサブフォーム間でデータの連携

質問

今、ある単票フォームに、2つのサブフォームを配置し、そのサブフォーム間で金額の連携をしたいと考えています。

【親フォームA】
・ID1、長整数型(主きー)
・受付日時
・発注金額総合計 <== ※Bが更新されたら、ここを更新したい

【サブフォームB】
・ID2、長整数型(主キー)
・ID1(外部キー)
・発注日
・発注合計金額 <===　※Cが更新されたら、ここを更新したい ==> Aの更新へ↑

【サブフォームC】
・ID3、長整数型(主キー)
・ID2(外部キー)
・発注項目名
・単価
・数量 ==>ここを更新する→Bへ↑
・単位 ==>ここを行進する→Bへ↑
・摘要

サブフォームB、Cは、いずれも帳票型です。
それぞれ、テーブルA, B, Cがレコードソースで
テーブル構成は、上の項目の構成と同じです。
A-ID1: B-ID1 = 1：多
B-ID2: C-ID2 = 1：多

この時、Cで単価と数量を入力したら、Bの発注合計金額の値を更新したいです。
それと同時に、親Form Aの「発注金額総合計」の値を、Bの全てのレコードの「発注合計金額」のSumで更新したいです。

よろしくお願いします。

回答No.2

Anzu4699

また長文失礼いたします、必要な部分だけ見ていただけると助かります

■補足回答
＞フィールドの更新後処理では、SQLで更新クエリーを実行すると
＞、フォームのレコードソースの値は既に更新されているのでしょうか？

テーブルのレコードとしてはまだ更新されていません

レコードソースの連結項目であっても、
カレントレコードが「移動」されるか「閉じられる」か「コミット(保存)」された際に
初めてデータベース(テーブルレコード)に反映されます

従ってフィールドの更新後処理では、実際のレコードはまだ更新されていません
レコードとしては、まだ編集中という位置づけになります

「acCmeSaveRecord」は保存の意味合いになるのであるいみ性解です

※編集中のレコードは、キーボードのＥＳＣを２度連続押下すると元に戻す機能を持っています

■余談：データベースが外部(SQLやOracle)の場合、以下のような現象が発生します
(1)ＰＣ－Ａでテーブルレコード修正中(編集中)のとき、他のＰＣからそのレコード参照すると前の値が参照されます
(2)ＰＣ－Ａでテーブルレコード修正中(編集中)のとき、他のＰＣでも同じレコードを修正しようとすると
___最後に更新しようとするＰＣに警告メッセージが出てきて反映できません（ココ裏覚えですがでたはず）
___その場合、レコードロックなどの仕組みを作る必要になります（仕組みは物凄い情報量になるので省きます）

■大昔(2008年ごろ)、自分が教わった事
テーブルを実際開いてみてください、テーブルのレコード内の(どれでも良いです)項目値を
入力すると左側に鉛筆マークがでてきます。

この鉛筆マークは編集中と意味で、まだレコードには実際に反映されていません

キーボードのＥｓｃボタンを２回チャチャと押すと編集中のレコードは元に戻ります
Ｅｓｃボタンを押さずにレコードを移動またはテーブルを閉じると反映されます
※ＶＢやＲＰＧ４００の言語で言うと、コミットされるというわけです

これがAccessではフォームでも連結項目の場合、適用されるというわけです
解らなければ、そういうものだと理解しときや

と教わった、やばいなぁ走馬灯のように思い出す自分・・・なんか涙出てくる

以上でした (さぁ定時だぁパチンコ行くぞ～ｗ)

回答No.1

Anzu4699

■前提条件
(1)開発言語(アプリ)はAccessと判断
(2)親フォーム及びサブフォームＢの更新したい項目はテーブルの項目で連結項目と判断
(3)データベースはAccessと判断

説明(3a).データベースがSQLServerやOracleServerの場合、集計クエリを用いた更新クエリの作成が可能

説明(3b).データベースがAccessの場合、集計クエリを用いた更新クエリの作成が不可能
※不可能理由：ACCESSのデータベースエンジンにある限界の一つで実行すると、
「更新可能なクエリであることが必要です。」というエラーがでます。

■準備 (サンプルコードにてスペル間違いがあるかも)

(1)テーブルＡの「発注金額総合計」を更新するＳＱＬを用意

--- 以下、詳細 ---
(1A).集計＋テーブル作成ＳＱＬを用意 (Sum関数利用)
＜サンプルコード：ＳＱＬ名＝Q_TBL_B_SumTBL＞
SELECT T_TBL_B.ID1, Sum(T_TBL_B.発注合計金額) AS 合計金額 INTO T_TBL_B_SumTBL
FROM T_TBL_B GROUP BY T_TBL_B.ID1 HAVING T_TBL_B.ID1 = ★引数★
※テーブル作成する理由は、上記前提の説明(3b)の問題点の代替案

(1B).(1A)で作成したテーブルを用いて、テーブルＡの「発注金額総合計」を更新
＜サンプルコード：ＳＱＬ名＝Q_TBL_B_Upd＞
UPDATE T_TBL_B_SumTBL LEFT JOIN T_TBL_A ON T_TBL_B_SumTBL.ID1 = T_TBL_A.ID1
SET T_TBL_A.発注金額総合計 = [T_TBL_B_SumTBL]![合計金額];

(1C).(1A)で作成したテーブルオブジェクト自体を削除
＜サンプルコード＞
DoCmd.DeleteObject acTable, "T_TBL_B_SumTBL"

(2).テーブルＢの「発注合計金額」を更新するＳＱＬを用意

--- 以下、詳細 ---
(2A).単価×数量を算出するＳＱＬを用意
＜サンプルコード：ＳＱＬ名＝Q_TBL_C_Cmp＞
SELECT T_TBL_C.ID2, [T_TBL_C]![単価]*[T_TBL_C]![数量] AS 金額計算
FROM T_TBL_C WHERE T_TBL_C.ID2 = ★引数★

(2B).(2A)のＳＱＬを用いて集計＋テーブル作成ＳＱＬを用意 (Sum関数利用)
＜サンプルコード：ＳＱＬ名＝Q_TBL_C_SumTBL＞
SELECT Q_TBL_C_Cmp.ID2, Sum(Q_TBL_C_Cmp.金額計算) AS 合計金額 INTO T_TBL_C_SumTBL
FROM Q_TBL_C_Cmp GROUP BY Q_TBL_C_Cmp.ID2;
※テーブル作成する理由は、上記前提の説明(3b)の問題点の代替案

(2C).(2B)で作成したテーブルを用いて、テーブルＢの「発注合計金額」を更新
＜サンプルコード：ＳＱＬ名＝Q_TBL_B_Upd＞
UPDATE T_TBL_B RIGHT JOIN T_TBL_C_SumTBL ON T_TBL_B.ID2 = T_TBL_C_SumTBL.ID2
SET T_TBL_B.発注合計金額 = [T_TBL_C_SumTBL]![合計金額];

(2D).(2B)で作成したテーブルオブジェクト自体を削除
＜サンプルコード＞
DoCmd.DeleteObject acTable, "T_TBL_C_SumTBL"

■対応回答（未検証につき作成時には微調整が必要）
(1).サブフォームＣの更新時イベントにて、準備(2)のＳＱＬ等を実行後(Docmd.RunSQL関数だったかな)
サブフォームＢのリクエリーを実施

(2).サブフォームＢの更新時イベントにて、準備(1)のＳＱＬ等を実行後(Docmd.RunSQL関数だったかな)
親フォームＡのリクエリーを実施

＜備考A＞
作成時はまずボタンで動作するようにして置き、動作検証後に更新後イベントに
組み込むことをお勧めいたします（きちんと動作しないときの原因調査に役立つ）

＜備考B＞
リクエリー等を頻繁に行うと、ちらつきが気になる人がよくいます
の場合、ちらつき防止用の関数があったと思います（関数名忘れ）ので併用することをお勧めします

＜備考C＞
フォーム更新後イベントに付き、入力しただけでは反映されません
入力即という事であれば、項目の更新後または変更時イベントで行えばよいと思います
が、その場合以下の点に注意
(備考C1).Access特有の編集時における元に戻す(確かEscを2回クリック)機能が使えなくなります
(備考C2).さらに頻繁に動作することになる為、データ量によってはレスポンスが悪くなる可能性有

■まとめ
用は、それぞれのフォームに対し、更新ＳＱＬを実施後、 画面再表示(リクエリー)をかける
あとはその実行するタイミング（キック場所）を調整していけばよいと思います。

前提条件が違っていて、更新したい項目が、非連結項目の場合は、また別の手法になります

実際のサンプルコードを全部作って説明したほうが良いのかもしれませんが
理解を深めるために、ここから先は調べてみてください
※内心＝時間があれば全部のサンプルコードを載せたい所だが、単に自分(Anzu)が時間が無いだけの言い訳でした

以上でした。

お礼

ご回答ありがとうございました。
とりあえず、時間的に余裕がありませんので、また、落ち着いた時に書かれている内容を吟味してみたいと思います。

ありがとうございます。

補足

大変お忙しいなか詳しい説明をくださり、ありがとうございます。

以下、私からのお返事を書かせていただきます。

>■前提条件
(1)開発言語(アプリ)　→　Accessです。
(2)親フォーム及びサブフォームＢの更新したい項目はテーブルの項目で、フォームのレコードソースです。リレーションシップも設定済みです。
(3)データベース　→　Accessです。

>説明(3a).データベースがSQLServerやOracleServerの場合、集計クエリを用いた更新クエリの作成が可能
→先でSQL Serverに移行する事を検討するかも知れません。集計クエリを用いた更新クエリー作成がアクセスでは出来ないという情報、ありがとうございました。

>■準備
>(1)テーブルＡの「発注金額総合計」を更新するＳＱＬを用意
>(1A).集計＋テーブル作成ＳＱＬを用意 (Sum関数利用)
>＜サンプルコード：ＳＱＬ名＝Q_TBL_B_SumTBL＞
>SELECT T_TBL_B.ID1, Sum(T_TBL_B.発注合計金額) AS 合計金額 INTO T_TBL_B_SumTBL
>FROM T_TBL_B GROUP BY T_TBL_B.ID1 HAVING T_TBL_B.ID1 = ★引数★
>※テーブル作成する理由は、上記前提の説明(3b)の問題点の代替案

Group by + Having句の使い方をこちら「http://www.atmarkit.co.jp/ait/articles/0706/21/news128.html」で調べてみました。
処理の順番では、Where句→Group by句→Having句となるので、Group by句でグループ分けしたものに対して、Having句でID1の抽出する値を絞り込んでいるということですね。

>(1B).(1A)で作成したテーブルを用いて、テーブルＡの「発注金額総合計」を更新
>＜サンプルコード：ＳＱＬ名＝Q_TBL_B_Upd＞
>UPDATE T_TBL_B_SumTBL LEFT JOIN T_TBL_A ON T_TBL_B_SumTBL.ID1 = T_TBL_A.ID1
>SET T_TBL_A.発注金額総合計 = [T_TBL_B_SumTBL]![合計金額];

この場合、Update [SUM table b] left join [table a] on ...
とすると、左から右への更新クエリーになって、該当するレコードがない場合には、追加クエリーの役目もするのですね。。。
（http://hatenachips.blog34.fc2.com/blog-entry-153.html）

>■対応回答（未検証につき作成時には微調整が必要）
>(1).サブフォームＣの更新時イベントにて、準備(2)のＳＱＬ等を実行後(Docmd.RunSQL関数だったかな)
>サブフォームＢのリクエリーを実施

>(2).サブフォームＢの更新時イベントにて、準備(1)のＳＱＬ等を実行後(Docmd.RunSQL関数だったかな)
>親フォームＡのリクエリーを実施

→現在、RecordsetCloneを取得して、計算して、フォームの該当フィールドにセットする処理を作ってみましたが、イベントの処理がなかなか難しいです。ヘタすると、イベントがずっと連鎖して動きっぱなしのような画面の動きになってしまいます。その理由は、多分、親、子、孫で「Before_Updateのイベント」に処理を入れているからです。その対策として、値を更新した後に孫のサブフォームのフレームにSetfocusするとしたら、イベントの連鎖更新が止まりました。

スピードに関して言えば、しかし、処理的には上の更新クエリーのほうがずっと速いのでしょうか？

>＜備考A＞
→まずはボタンを作って試してみるというご提案、とても役に立ちそうです。

>＜備考B＞
>リクエリー等を頻繁に行うと、ちらつきが気になる人がよくいます
→ Me.Repaint = False
→ Me.Repaint = True
ですね

＜備考C＞
>フォーム更新後イベントに付き、入力しただけでは反映されません
>入力即という事であれば、項目の更新後または変更時イベントで行えばよいと思います
>が、その場合以下の点に注意
>(備考C1).Access特有の編集時における元に戻す(確かEscを2回クリック)機能が使えなくなります
>(備考C2).さらに頻繁に動作することになる為、データ量によってはレスポンスが悪くなる可能性有

→　フィールドの更新後処理で為した時、RecordsetCloneを使うと、値を変える前の古い値で計算してしまい、うまくいきませんでした。なので、Form_Currentで実行していますが・・・
すると、計算結果が更新出来ない時が出てしまう時があり、そのために、ボタンを作ってしまいました。無理やりRecordsetCloneで値の計算をする時、わざわざRuncommand acCmeSaveRecordを実行してから、RecordsetCloneを作っています。

フィールドの更新後処理では、SQLで更新クエリーを実行すると、フォームのレコードソースの値は既に更新されているのでしょうか？

よろしくお願いします。