ACCESSで計算結果を格納する方法

質問

いつもおせわになっております。同じ案件名で質問したのですが、説明がつたなすぎたので質問しなおします。

メインのテーブルの構造は、受注先、作業項目、受注名（主キー）、担当者（これはなくそうと思っています）、受注名、注文日、完了日、（見積金額、請求金額、入金金額、外注支払い、粗利益）とあります。
サブのテーブルは、ひとつの案件の中に小分けの案件があるので、
ナンバー（オートナンバー／主キー）受注名、サブタイトル、ページ数、希望金額、取り掛かり日、作成終了日、提出日、締め切り日、支払先１、支払金額１（支払先１への支払い金額です）、支払先２、支払金額２、支払先３、支払い金額３、請求日、請求金額、入金日、入金日、見積もり、調査、報告書（最後の三つは、やったかどうかのチェック用のチェックボックスです）
メインテーブルとサブテーブルはたしか受注名で繋がっています。

メインテーブルの単票フォーム内のサブフォームに、サブテーブルを帳票形式で表示して、個別案件を入力するようにしました。サブフォーム内のレコードの各案件の希望金額の合計や支払金額、粗利などを計算して（Sumや足し算引き算）、サブフォーム内のテキストボックスに表示させています。これが、メインテーブルの（）の中身にあたります。
これをなんとかして（サブフォーム内のコマンドボタンを押すなど？して）計算結果をメインフォームのフィールドに転送したいのですが、方法はないでしょうか？

できれば、いまアクセスのサイトや本を見過ぎていっぱいいっぱいなのでわかりやすく説明していただければ幸いです。
。

回答No.5

noname#140971

なんか、質問と回答がすれ違っている感じがします。

１、テーブルの列を更新したいのか？
２、フォーム間参照をしたいのか？
３、一つのテーブル列を異なるフォームでそれぞれに参照したいのか？

１なら＜３＞。
２なら＜１＞。
３なら＜別解＞。

と、念を押しておきます。

補足

いろいろと丁寧にご回答いただきましてありがとうございます。３でチャレンジしていますが、どうしてもうまくいきません。

＞３、テーブルをＶＢＡで更新します。

連結で更新されるフィールドを参照。

Private Sub Form_AfterUpdate()
　　CnnExecute "UPDATE tab1 SET fld1_Sum=" & DSum("fld1", "tab2", "tab1_ID=" & Me.tab1_ID)
End Sub

僅かに１行のＶＢＡコードですが、以下のような関数をモジュールに登録するのは必須。
で、これも点滅はしません。
ですが、先に述べたようにテーブルの設計としては必ずしも推奨されるもんではありません。

このfld1とかtab2とかって自分でつけたフィールド名とかテーブル名をいれるんですよね？

＞連結で更新されるフィールドを参照。
というのは一体どういう意味でしょう？更新させたいフィールドの枠のことじゃないんですか？モジュールに登録というのはモジュールとして新規作成してしまえばいいということですよね？
これは見積金額、請求金額、入金金額、外注支払い、粗利益の四つ分登録しなければならないのでしょうか？

それから、

＞Public Sub ErrMessage(ByVal CnnErrors As ADODB.Error, ByVal strSQL As String)
　　 MsgBox "ADOエラーが発生しましたので処理をキャンセルします。" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & CnnErrors.Description & Chr$(13) & _
　　　　　　"・Err.Number=" & CnnErrors.Number & Chr$(13) & _
　　　　　　"・SQL State=" & CnnErrors.SQLState & Chr$(13) & _
　　　　　　"・SQL Text=" & strSQL, _
　　　　　　vbExclamation, " ADO関数エラーメッセージ"
End Sub

Public Function CnnExecute(ByVal strSQL As String) As Boolean
On Error GoTo Err_CnnExecute
　　Dim isOK As Boolean
　　Dim cnn　As ADODB.Connection

　　isOK = True
　　Set cnn = CurrentProject.Connection
　　With cnn
　　　　.Errors.Clear
　　　　.BeginTrans
　　　　.Execute strSQL
　　　　.CommitTrans
　　End With
Exit_CnnExecute:
On Error Resume Next
　　cnn.Close
　　Set cnn = Nothing
　　CnnExecute = isOK
　　Exit Function
Err_CnnExecute:
　　isOK = False
　　If cnn.Errors.Count > 0 Then
　　　　ErrMessage cnn.Errors(0), strSQL
　　　　cnn.RollbackTrans
　　Else
　　　　MsgBox "プログラムエラーが発生しました。システム管理者に報告して下さい。(CnnExecute)", _
　　　　　　　 vbExclamation, " 関数エラーメッセージ"
　　End If
　　Resume Exit_CnnExecute
End Function

このVBAのコードもモジュールに登録するのでいいのですか？本当に質問ばかりで申し訳ありません。

とりあえず計算結果がどこかに格納できるようにしたいんです。表示させてみるだけなら今のままでもいいのですが、この後レポートやクエリとして集めてくるためにはどこかに格納させないとできませんよね？（素人なのでできないと思っているだけかもしれませんが、、、）

回答No.4

noname#140971

２の式を用いると、一旦、空値になります。
20=>空=>30
ですから、表示、非表示、表示となります。

３は非推奨とは言っても、これしかないのでは？
でなきゃー、[粗利益]という列をどうやって更新しますか？

非推奨とは、そもそも[粗利益]という計算結果を格納する列そのもの要・不要を示唆してのことです。
でも、[粗利益]がデータ検索を簡易に行うために意識して設けたものならば、それはそれで良いと思います。
設計原則よりも使い勝手です。

そういうことで＜３＞で UPDATE文を実行するのが現実的でしょう。

回答No.3

noname#140971

５時になりましたので具体的な回答を！

１、直接のフォームの値を参照する方式。

=[tab2 サブフォーム].Form!fld1_sum_II

非連結にして、[コントロールソース]をビルドしてフォームを選びます。

＊点滅せず！

２、関数等でテーブルを集計・計算する方式。

非連結にして、[コントロールソース]に式を指定。

=DSum("fld1","tab2","tab1_id=" & [id])

＊点滅します！

３、テーブルをＶＢＡで更新します。

連結で更新されるフィールドを参照。

Private Sub Form_AfterUpdate()
　　CnnExecute "UPDATE tab1 SET fld1_Sum=" & DSum("fld1", "tab2", "tab1_ID=" & Me.tab1_ID)
End Sub

僅かに１行のＶＢＡコードですが、以下のような関数をモジュールに登録するのは必須。
で、これも点滅はしません。
ですが、先に述べたようにテーブルの設計としては必ずしも推奨されるもんではありません。

Public Sub ErrMessage(ByVal CnnErrors As ADODB.Error, ByVal strSQL As String)
　　 MsgBox "ADOエラーが発生しましたので処理をキャンセルします。" & Chr$(13) & Chr$(13) & _
　　　　　　"・Err.Description=" & CnnErrors.Description & Chr$(13) & _
　　　　　　"・Err.Number=" & CnnErrors.Number & Chr$(13) & _
　　　　　　"・SQL State=" & CnnErrors.SQLState & Chr$(13) & _
　　　　　　"・SQL Text=" & strSQL, _
　　　　　　vbExclamation, " ADO関数エラーメッセージ"
End Sub

Public Function CnnExecute(ByVal strSQL As String) As Boolean
On Error GoTo Err_CnnExecute
　　Dim isOK As Boolean
　　Dim cnn　As ADODB.Connection

　　isOK = True
　　Set cnn = CurrentProject.Connection
　　With cnn
　　　　.Errors.Clear
　　　　.BeginTrans
　　　　.Execute strSQL
　　　　.CommitTrans
　　End With
Exit_CnnExecute:
On Error Resume Next
　　cnn.Close
　　Set cnn = Nothing
　　CnnExecute = isOK
　　Exit Function
Err_CnnExecute:
　　isOK = False
　　If cnn.Errors.Count > 0 Then
　　　　ErrMessage cnn.Errors(0), strSQL
　　　　cnn.RollbackTrans
　　Else
　　　　MsgBox "プログラムエラーが発生しました。システム管理者に報告して下さい。(CnnExecute)", _
　　　　　　　 vbExclamation, " 関数エラーメッセージ"
　　End If
　　Resume Exit_CnnExecute
End Function

補足

具体的なご回答ありがとうございます。

このうちのどれかを実行すれば、できるということでしょうか？
点滅する、しないというのはどのような違いがあるのでしょう？（というか何が点滅するのでしょう…）
３は推奨なされないということですよね？

質問ばかりでもうしわけありません。

回答No.2

noname#140971

Ａフォーム内に配置したＢフォームの値をＡフォーム上のコントロールに表示。
そういう意味では、同じこと。
で、Ａフォーム上のコントロールを非連結コントロールにする場合。
で、Ａフォーム上のコントロールを連結コントロールにする場合。
の二通りが考えられます。
（一応、レポートの問題はさておき）

前者ですと、やりようによっては一旦消えて更新されるでしょう。
後者ですと、即座に更新されるでしょう。
ですが、後者のやり方は元データと計算結果の双方を異なるテーブルに格納することを意味します。
ということは、ＤＢのテーブル設計の原則を逸脱する手法。
もちろん、原則は原則であって何らかの高速処理に不可欠とか後処理の簡略化のためであれば。
それは、まましてやることだと思います。

で、前者でしょうか後者でしょうか？

補足

ご回答ありがとうございます。

前者でいったん消えて更新されるというのはどういういみでしょう？

後者は推奨しないやりかたという意味ですか？

回答No.1

noname#140971

tab1:

[ID]_[fld1_Sum]
__1__________0

tab2:

[tab1_ID]_[RowNumbwr]_[fld1]
_______1___________1_____10
_______1___________2_____20

このようなテーブル構造なようですね。
で、[tab1]がメインで[tab2]がサブフォーム。
で、[tab2]![fld1]が更新されると自動的にメインの[tab1]![fld1_Sum]も更新したい。

[tab1_ID]_[RowNumbwr]_[fld1]
_______1___________1_____10->15
_______1___________2_____20

という変更した場合にカーソルは２行目の先頭に移動すればよいということかな。

これは、

Private Sub Form_AfterUpdate()
　　XXXXXXXXXXXXXX
End Sub

で、可能です。
が、質問文を詳細に読んでいないで憶測で「こんな疑問かな?」と。
一応、こういうことかどうかお返事をください。

補足

ご回答ありがとうございました。

ちょっと説明していただいている構造がよくわからないのですが、私の説明にちょっと間違いがありました。メインフォームもサブフォームも単票形式でひとつひとつ表示させています。
メインのフォームにつっこんだサブフォームのフッターのに希望金額等の計算結果が表示される様になっています。この計算結果はサブフォームのページを次に繰っていっても数値さえ代えなければかわりません。見た目だけでいえばこのままでもいいのですが、最終的にレポートとしてメインテーブルの並びで並ばせたいので、格納させたいのです。