ACCESSのフォームでは、、メインフォームとサブフォームを組み合わせて利用することが良くあります。
サブフォームでは一覧形式でデータを入力し、その合計結果をメインフォームに表示するという画面はよく見かけるのではないでしょうか。
しかし、サブフォームの入力結果をメインフォームに即座に反映させるには少しコツが必要です。
今回は、サブフォームの合計値をメインフォームに即時反映させる方法をご紹介します。
こんにちは。
はこにわガジェット （@hakoniwagadget） です。
ACCESSを使った売上管理、顧客管理などのデータベース開発を行っています。
ACCESSは使いこなすために少しスキルが必要なものの、うまく活用すればExcelよりも業務の効率化が図れます。
この記事ではACCESSで実際に作成したフォームやレポートを、その作成方法と共にご紹介していきます。
サブフォームの結果をメインフォームに反映する機能
最初に完成形をお見せします。
以下の売上伝票入力フォームを作成しました。
こちらは1つの売上伝票に対して、複数の売上明細が紐づく形のデータ構成になっています。
売上伝票本体をメインフォーム、売上明細をサブフォームで作成しています。
サブフォームの売上明細に数量や単価を入力すると、その合計金額がメインフォームの売上伝票に表示されます。
この処理はサブフォームの値が変更されたあとにVBAでメインフォームの合計を再計算するという処理を作ることで実現しています。
テーブル構成
では最初にテーブルの構成から見ていきましょう。
このフォームは2つのテーブルからできています。
まず、メインフォームのレコードソースであり、売上伝票1枚を1レコードで格納する「TRN_売上」テーブルです。
そしてもう一つはサブフォームのレコードソースであり、売上明細1行を1レコードとして保存する「TRN_売上明細」テーブルです。
この2つのテーブルは売上IDでリンクしています。
売上IDは、「TRN_売上」テーブルの主キーです。
一方で、「TRN_売上明細」テーブルは売上明細IDという異なる主キーを持ちつつ、「TRN_売上」テーブルとリンクさせるために売上IDを持たせています。
「TRN_売上明細」の合計をフォームで表示するのであれば、わざわざ「TRN_売上」テーブルに合計値を保存せず、毎回クエリもしくはフォーム上のSum関数で集計すればよいのではないかと思われるかもしれません。
「TRN_売上」テーブルに合計値を保存する理由は、主にパフォーマンスの向上にあります。
合計値を都度クエリで計算する場合、大量のデータがあると処理に時間がかかることがあります。これに対し、合計値をあらかじめ保存しておくことで、必要なときにすぐに表示でき、システム全体の応答速度が向上します。
フォームの作成
では次に、上記のテーブルをもとにフォームを作成しましょう。
フォームは、「TRN_売上」を基にしたメインフォームと、「TRN_売上明細」を基にしたサブフォームの2つを作成します。
サブフォーム
まずは部品となるサブフォームを作成します。
デザインビューでフォームを以下のように作成しました。
売上明細は明細を一覧表示するため、帳票フォームで作成しています。
赤枠で囲った部分は売上IDです。
売上IDはメインフォームと連結するための値として作成していますので、表示させる必要はありません。
メインフォーム
次にメインフォームです。
こちらもデザインビューで作成します。
メインフォームは1レコードを1画面で表示するために、単票フォームで作成します。
さらに、先ほど作成したサブフォームを画面下段に配置しています。
サブフォームとメインフォームのリンクは以下のように「売上ID」を使って行っています。
このようにフォーム上で設定することにより、サブフォームに入力するだけで、自動的に「TRN_売上明細」テーブルの売上IDには、メインフォームのレコードソースである「TRN_売上」の売上IDと同じ値が反映されます。
VBAプログラミング
テーブルとフォームが準備できたところで、いよいよサブフォームの合計をメインフォームに反映するためのVBAのプログラミングを行います。
今回はサブフォームが変更されたタイミングで、メインフォームへ合計を反映するため、サブフォーム側にVBAのプロシージャを作成します。
サブフォーム側で合計金額に影響を及ぼす入力箇所は、数量、単価、消費税率ですので、それぞれが更新された後に合計値を反映します。例えば、単価の更新後イベントには以下のプロシージャを設定しています。
本当はもう少し細かい計算があるのですが、ここではわかりやすくするためにメインフォームの「小計」にサブフォームの「金額」の合計を反映する処理のみ記載しています。
Private Sub 単価_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
'合計計算
[Forms]![売上伝票入力]![小計] = Nz(DSum("金額", "TRN_売上明細", "売上ID = " & Me!売上ID & " and 削除 = false"))
End Sub
まず最初に
DoCmd.RunCommand acCmdSaveRecord
を行って、レコードを保存しています。
これは、サブフォームので入力中はテーブルに値が反映されていないため、入力した値を強制的にテーブルに反映させる処理です。
これを行わないと入力する前の値をもとに合計値がメインフォームに反映されてしまい、値がずれてしまいますので注意してください。
次に
[Forms]![売上伝票入力]![小計] = Nz(DSum("金額", "TRN_売上明細", "売上ID = " & Me!売上ID & " and 削除 = false"))
で、メインフォームの「小計」に、Dsum関数で「TRN_売上明細」の金額を、メインフォームの主キーである「売上ID」をもとに集計して反映します。
メインフォームの「小計」を指定する際、上記のプロシージャはサブフォーム側で動作していますので別フォームのコントロールを指定することになります。そのため、
[Forms]![売上伝票入力]![小計]
という書き方でフォームから指定する必要があります。
また、サブフォームの合計値を集計する場合は、Sum関数を利用すると表示されたレコードの合計を抽出条件に合わせて自動的取得してくれるので便利ですが、今回はSum関数を使わずDSum関数を使っています。
これは、前述したフォームから入力された値の反映タイミングの問題によるものです。
Sum関数を使ってサブフォームの値を集計する場合、フォーム入力値の反映タイミングが若干ずれるため、正しい集計値をリアルタイムに出すことができません。
そのため、DSum関数を使って明示的に保存したテーブルの値を集計しています。
このあたりは、論理的なプログラムの記述方法としてはどちらでも同じ結果が出るはずなのですが、ACCESSの仕様上によって実務上の影響が出る部分ですのでご注ください。
更にDSum関数での集計をする際にレコードが0件ですと集計結果がNullになってしまう可能性があります。
Nullを出さずにレコードが0件であれば0が表示されるようNz関数で処理をしています。
この処理方法であれば、サブフォームの合計値をメインフォームに即時反映させることが可能です。
以上、サブフォームの合計値をメインフォームに即時反映させる方法をご紹介しました。
ACCESSを使いこなせば、業務の効率化や自動化が実現できます。
しかし、自分でACCESSを学ぶには時間がない、難しそうで不安、という方も多いでしょう。
そんな時は、ACCESS開発歴20年以上、過去に300以上のACCESSデータベースの開発・修正実績のあるはこにわガジェット（@hakoniwagadget）にお任せください。
ACCESSの新規開発、既存のACCESSの修正、ACCESSの操作レッスンなど様々なサービスをご提供しています。
ご興味のある方は下記のサービス一覧をご覧ください。
最後までお読みいただき、ありがとうございました。
