ACCESS開発事例

HOME
お役立ち情報
ACCESS開発事例
ACCESS サブフォームの合計値をメインフォームに反映させる機能

2023年8月23日 / 最終更新日時 : 2023年8月23日 hakoniwa_gadget ACCESS開発事例

ACCESS サブフォームの合計値をメインフォームに反映させる機能

ACCESSのフォームでは、メインフォームとサブフォームを組み合わせて多彩な表現が可能です。

一般的にはメインフォームを単票形式で作成し、サブフォームを表形式で作成することが多いでしょう。
この際、サブフォームの合計値をメインフォームに表示させたいことが多いと思います。

今回は、サブフォームの合計値をメインフォームに反映させる機能の作成方法をご紹介します。

こんにちは。
はこにわガジェット（@hakoniwagadget）です。

ACCESSを使った売上管理、顧客管理などのデータベース開発を行っています。
ACCESSは使いこなすために少しスキルが必要なものの、うまく活用すればExcelよりも業務の効率化が図れます。
この記事ではACCESSで実際に作成したフォームやレポートを、その作成方法と共にご紹介していきます。

1 サブフォームの合計値をメインフォームに表示する方法
2 メインフォームの合計値がサブフォームの合計値を参照するようにする
3 メインフォームの合計値をVBAで直接更新する

サブフォームの合計値をメインフォームに表示する方法

サブフォームの合計値をメインフォームに表示すると、下記のような形になります。

金額の合計が上段の合計に表示されていますね。
このフォームの合計部分がメインフォーム、下段の一覧部分がサブフォームになっています。

これを実現するには主に2つの方法があります。

メインフォームの合計値がサブフォームの合計値を参照するようにする

メインフォームの合計値をVBAで直接更新する

順に説明していきましょう。

メインフォームの合計値がサブフォームの合計値を参照するようにする

最初に説明するのはメインフォームの合計値がサブフォームの合計値を参照するようにする方法です。
おそらくこれが最もやりやすい方法でしょう。

まず、サブフォーム側のフッターに合計金額を表示するテキストボックスを作成します。

今回は金額を集計できるようにしています。

フォームビューで見てみましょう。

このように、最下段に合計金額が表示されます。
この値を、そのままメインフォームに表示できるようにします。

次はメインフォーム側のデザインビューです。

上部の合計金額部分はコントロールソースを以下のように設定してあります。


 =[T_Sum_Sub]![合計金額]

これは、T_Sum_Subはサブフォームの名称です。
そのため、サブフォームの「合計金額」の値をそのまま表示する、という指示になっています。

こうすることでサブフォームの合計金額をメインフォームに表示することが可能です。
しかし、このままですと下記のようにサブフォーム側の合計金額も表示されたままになってしまいます。

これを表示したくない場合は、サブフォームで設定を変更します。

サブフォームのデザインビューでフッターを選択し、プロパティシートで「可視：いいえ」に設定します。
こうすることでサブフォームのフッター自体が表示されなくなります。

するとこのように合計金額を表示できるようになります。

メインフォームの合計値をVBAで直接更新する

メインフォームの合計値がサブフォームの合計値を参照する方法では困ること

基本的にはここまで紹介したメインフォームの合計値がサブフォームの合計値を参照するようにする方法が便利なのですが、それでは対応できないケースがあります。
それは、合計値をメインフォームのレコードソースとなっているテーブルに格納したい場合です。

前述の方法ではフォーム上の合計金額はサブフォームの合計金額の値を参照して表示しているだけですので、フォームのレコードソースが設定されていても、その特定フィールドをコントロールソースとすることができません。

結果、サブフォームの合計値をメインフォームの元テーブルに反映させることができないのです。

フォームのレコードソース設定

サンプルとして以下のテーブルを準備しました。

このテーブルを先ほどのフォームのメインフォームのレコードソースとして指定し、小計フィールドにサブフォームの合計値を反映してみましょう。
フォームをデザインビューで開きます。

フォーム全体のプロパティでデータタブを開き、レコードソースに先ほどのテーブルを設定します。
これでフォームの入力値がテーブルと連動するようになりました。

次に、合計金額のコントロールソースをテーブルの小計フィールドに変更しましょう。
また、同時にレコードを識別するために売上IDフィールドも追加しておきます。

すると、先ほど作成したサブフォームの合計金額を参照する式もコントロールソースに設定しているため、ここを小計に変更するとテーブルの値を表示するのみで、サブフォームの合計値を参照することができなくなってしまいます。

フォームのコントロールソースの設定だけでは、他のコントロールの値を参照するか、テーブル/クエリの値を表示するかしか選択できないのです。
そのため、VBAでメインフォームの合計金額の値を書き換える処理が必要になります。

VBAでのメインフォームの合計値更新

では、VBAの設定をしていきましょう。

サブフォーム側の金額が更新された際に、メインフォームの合計金額を更新する、という処理です。
そのため、まずはサブフォームをデザインビューで開きます。

このフォームでは金額を合計していますが、金額は数量×単価で計算しています。
そのため、金額を直接書き換えることはなく、数量か単価を書き換えることで金額が自動計算されるため、今回も数量と単価の更新後処理イベントを操作します。

最終的に設定したイベントプロシージャは下記の通りです。


 Private Sub 単価_AfterUpdate()

    Me!金額 = Me!数量 * Me!単価
    
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious

    [Forms]![T_Sum_Main]![合計金額] = DSum("金額", "TRN_売上明細_sum", "売上ID = " & Me!売上ID)
    
 End Sub

1行目のMe!金額 = Me!数量 * Me!単価はもともと設定してある、金額を計算するための式です。
2行目以降が今回追加したものになります。

DoCmd.GoToRecordでサブフォームのレコードを移動させることでフォーム入力値をテーブルに反映させ、その上でDsum関数を使ってメインフォームの合計金額を書き換えています。

Dsuｍ関数ではTRN_売上明細_sumというサブフォームのレコードソースに設定したテーブルの金額を合計しています。
この際、メインフォームに表示した売上IDを指定してレコードを抽出しているのです。

最終形はこの形ですが、ここに至るまでに様々な試行をしています。
その経緯を以下でご紹介します。

サブフォームの合計金額をメインフォームに反映

はじめは単価の更新後処理に以下のイベントプロシージャを設定しました。


 Private Sub 単価_AfterUpdate()

    Me!金額 = Me!数量 * Me!単価
    [Forms]![T_Sum_Main]![合計金額] = Me!合計金額
    
 End Sub

メインフォームの「合計金額」コントロールに、Me、つまりサブフォームの合計金額の値を代入する、という処理です。
これで単価更新時にサブフォームの合計金額をメインフォームに反映することが可能なはずです。

実際にフォームビューで試してみましょう。

1行目の単価を20,000円から25,000円へ変更しましたが、うまくいかないですね。

サブフォーム側は金額が更新されているものの、メインフォームに反映されていません。
しかしこれは、処理に問題があるというよりはデータ反映タイミングの問題です。

ACCESSではデータ反映のタイミングが重要なため、こうしたケースは良く発生します。
今回は
1.単価
2.金額
3.サブフォームの合計金額
4.メインフォームの合計金額
という順番で値が更新されているはずですので、その4つの値がどこまで反映されているかを確認していきましょう。

エラー原因検証

いったん、非表示にしていたサブフォームの合計金額を表示します。

1行目の単価を20,000円から25,000円へ変更してみます。

すると、金額欄は更新されましたが、サブフォームの合計金額が更新されていないことが分かります。
では、次のレコードに移動してみましょう。

すると、サブフォームの合計金額が更新されました。

これは、カーソルが同一レコード上にある時は合計金額で利用しているSum関数の値が更新されない、ということです。

先ほど記述したVBAのイベントプロシージャでは


    Me!金額 = Me!数量 * Me!単価
    [Forms]![T_Sum_Main]![合計金額] = Me!合計金額

と書いていますが、1行目で金額を更新することはできていても、その時点で2行目の右辺にあるMe!合計金額（サブフォームの合計金額）が更新されていないために、左辺のメインフォームの合計金額も更新されない、という状況であることが分かります。

サブフォームの合計金額の最新化

では、金額を更新後にサブフォームを最新化して、合計金額を反映させてから2行目を実行するようにイベントプロシージャを変更しましょう。

やり方は2種類あります。

1.DoCmd.Requeryを使う

2.レコードを前後に移動させる

まずは1.からやってみます。
VBAのイベントプロシージャを以下のように変更しました。


 Private Sub 単価_AfterUpdate()

    Me!金額 = Me!数量 * Me!単価
    DoCmd.Requery
    [Forms]![T_Sum_Main]![合計金額] = Me!合計金額
    
 End Sub

DoCmd.Requeryを2行目に追加して、サブフォームの最新化を行うことで合計金額を更新させています。
フォームビューで実行してみましょう。

同じように単価を更新してみました。
結果、サブフォームの合計金額はカーソルを移動しなくても反映されるようになりましたが、メインフォームの合計金額が0円になってしまっています。

DoCmd.Requeryは便利なメソッドなのですが、どこまでが最新化されてどこが最新化されないのかわかりにくいときがあります。
どうもうまくいかないのでDoCmd.Requeryは諦めます。

では単純にレコードが移動すれば合計金額が更新できるので、レコードを移動させる方法にしてみましょう。


 Private Sub 単価_AfterUpdate()

    Me!金額 = Me!数量 * Me!単価
    
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious

    [Forms]![T_Sum_Main]![合計金額] = Me!合計金額
    
 End Sub

DoCmd.GoToRecordを2回行い、acNextで次へ進んでacPreviousで元に戻る、という原始的な方法にしてみました。

こちらですと、レコードを移動しなくてもサブフォームの合計金額が更新されるようになりました。
しかし、なぜか同じタイミングで処理しているのに、サブフォームの合計金額をメインフォームの合計金額に反映する処理ができないようです。

Dsum関数でメインフォームの値を直接書き換える

そこで、サブフォームの合計金額の値をメインフォームに反映するのやめ、直接メインフォームの合計金額の値を計算して書き換えることにします。

イベントプロシージャを以下のように修正しました。


 Private Sub 単価_AfterUpdate()

    Me!金額 = Me!数量 * Me!単価
    
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious

    [Forms]![T_Sum_Main]![合計金額] = DSum("金額", "TRN_売上明細_sum", "売上ID = " & Me!売上ID)
    
 End Sub

ここで注意したいのは、テーブル「TRN_売上明細_sum」の値から金額の合計をVBAで計算する際は、テーブル内のレコードを概要する売上IDのレコードのみに絞る必要があるということです。そのために、Dsum関数の3つ目の引数で売上IDを、メインフォームに表示された売上IDに指定しています。

以上、サブフォームの合計値をメインフォームに反映させる機能をご紹介しました。

ACCESSを使いこなせば、業務の効率化や自動化が実現できます。
しかし、自分でACCESSを学ぶには時間がない、難しそうで不安、という方も多いでしょう。

そんな時は、ACCESS開発歴20年以上、過去に100以上のACCESSデータベースを開発してきた私（@hakoniwagadget）にお任せください。

ACCESSの新規開発、既存のACCESSの修正、ACCESSの操作レッスンなど様々なサービスをご提供しています。
ご興味のある方は下記のサービス一覧をご覧ください。