ACCESSのフォームやレポートにはコントロールソースを設定します。
コントロールソースとは、フォームやレポートに表示されるデータがテーブルやクエリのどのフィールドを基にしているかを設定するものです。
レポートの表示をする際に、フォームでの入力内容に応じてレポートのコントロールソースを動的に変更させることができます。
今回は、コントロールソースを動的に変更する方法をご紹介します。
こんにちは。
はこにわガジェット （@hakoniwagadget） です。
ACCESSを使った売上管理、顧客管理などのデータベース開発を行っています。
ACCESSは使いこなすために少しスキルが必要なものの、うまく活用すればExcelよりも業務の効率化が図れます。
この記事ではACCESSで実際に作成したフォームやレポートを、その作成方法と共にご紹介していきます。
ControlSourceプロパティでレポートのコントロールソースを変更
レポートのコントロールソースを動的に変更するには、レポートの開くときイベントで該当のテキストボックスのControlSourceプロパティを変更します。
ControlSourceプロパティは、テキストボックスがレコードソースであるテーブルやクエリのどのフィールドを参照すべきかを指定するプロパティです。
コントロールソースを動的に変更するためには、ここで指定する値にフォームの入力値や変数を用います。
例えば、レポート上にある金額というテキストボックスのコントロースソースを、レポート出力フォームの「月指定」というテキストボックスの値を参照させるには以下のように記述します。
Me!金額.ControlSource = [Forms]![レポート出力]![月指定]
なかなかこれだけだと分かりにくいので、以下に実際の機能の作成方法をご紹介します。
完成形
はじめに完成形をお見せします。
以下のフォームで指定した月に応じて、レポートに表示される月の値を変更できるようにしています。
レポートには該当月の顧客別の売上合計が表示されます。
例えば、1月を指定してレポート出力ボタンを押すと
1月分の売上データを表示できます。
2月を指定すると
2月分の売上データを表示するように変更されます。
上段に表示された月数の表示だけでなく、表の中の値も変わっていることが確認できると思います。
作成方法
では、作成方法を見ていきましょう。
テーブル・クエリの準備
まずはレポートのデータ元となるテーブル、クエリを作成します。
最初に売上データテーブルを準備します。
顧客名や数量、単価などの基礎的な値が格納されています。
このデータを売上月別に集計したいのですが、売上日で集計すると日単位で分かれてしまうため、まずは売上月をクエリで求めます。
また、売上金額も数量×単価で計算します。
上記がクエリのデザインビューです。
以下のように記載して演算フィールドを作成しています。
まずは売上月です。
売上月: Format(Month([売上日]),"00")
売上月は売上日からMonth関数で月の値を抽出します。
しかし、単にMonth関数を使っただけですと1桁の月と2桁の月が混在します。
2桁に統一した方が処理がしやすいのでFormat関数を使って2桁に統一しています。
次に金額です。
売上金額: [数量]*[単価]
こちらは単純に数量と単価を掛けて計算しています。
今回のテーブルでは数量、単価ともにNullにならないようにしているのでこれでよいのですが、もしNullが入る可能性がある場合はNz関数を使ってNullの場合でも計算できるようにしておきましょう。
これで売上月変換クエリが完成しました。
売上月と売上金額が計算されていますね。
こちらをもとにクロス集計クエリを作成します。
クロス集計クエリは、選択クエリをデザインビューで作成したのち、メニューから「クロス集計」を選択することで作成できます。
今回作成したクロス集計クエリは以下の通りです。
売上月を列見出し、顧客名を行見出し、売上金額の合計を値としています。
データシートビューで結果を見てみましょう。
このように、行に顧客、列に月が並び、該当顧客の月別の売上が集計できました。
これでレポートに表示するクエリの作成は完了です。
フォームの作成
次にフォームを準備します。
今回のフォームには月の指定をするテキストボックスと、レポートを出力するボタンを配置します。
デザインビューで見ると以下のようになります。
フォーム自体にはレコードソースを指定せず、月指定のテキストボックスも非連結です。
レポート出力ボタンのクリック時イベントには、イベントプロシージャを設定します。
イベントプロシージャの内容は以下の通りで、あとで作成する月別集計レポートを印刷プレビューで開く指示をしています。
Private Sub レポート出力_ボタン_Click()
DoCmd.OpenReport "月別集計", acViewPreview
End Sub
ここでは単純にレポートを開くのみで、コントロールソースを動的に変更する処理は入れていません。
コントロールソースの変更はレポートを開くときのイベントで設定します。
レポートの作成
最後に、出力するレポートを作成します。
デザインビューで見ると以下の形になっています。
レポートの上部に指定した月を表示するため、「出力月」という可変型のテキストボックスを配置しています。
出力月のコントロールソースは以下に設定し、フォームで指定した月がそのまま表示されるようにしました。
=[Forms]![レポート出力]![月指定]
レポートのレコードソースは先ほど作成した「月別売上クロス集計」にしています。
月別売上クロス集計クエリのフィールドである「顧客名」、「金額」を詳細セクションで繰り返し表示されるように配置しました。ただし、金額についてはどの月を表示するか可変にするため、コントロールソースを設定せずに非連結としています。
レポートには開くときイベントでイベントプロシージャを設定します。
このイベントプロシージャの内容は後述します。
レポートを開くときイベントのVBAプログラミング
それではいよいよ、レポートを開く時のイベントプロシージャを設定しましょう。
このプロシージャでレポートのコントロールソースを動的に変更して、フォームで指定した月の売上を表示します。
Private Sub Report_Open(Cancel As Integer)
Me!金額.ControlSource = [Forms]![レポート出力]![月指定]
End Sub
レポートの「金額」テキストボックスのControlSourceプロパティを、レポート出力フォームの月指定テキストボックスの値に指定しています。
ControlSourceプロパティはその名の通り、テキストボックスのコントロールソース、つまりどのフィールドの値を表示するかを規定するものです。
レポートのレコードソースは、月別売上クロス集計クエリです。
つまり、「金額」テキストボックスのControlSourceは、月別売上クロス集計クエリのどのフィールドなのかを指定するということです。
ここでもう一度月別売上クロス集計クエリを見てください。
顧客名以外は月が2桁のフィールドになっとおり、どの月の売上なのかをフィールド名を月で指定することができます。
そこで、ControlSourceプロパティの値にフォームの月指定の値を入力することで、指定した月の売上金額のみを表示できるということです。
なお、このイベントプロシージャはレポートの開くときイベントに設定しましたが、読み込み時イベントではうまくいかないのでご注意ください。
以上、コントロールソースを動的に変更する方法をご紹介しました。
この記事の内容を実際に試したい方へ
本記事の内容を実際にACCESSを動かしながら確認したいという方向けに、記事で紹介した機能が実装されたサンプルファイルを販売しています。記事内で解説しているテーブル、クエリ、フォーム、レポート、VBAのプロシージャなどをそのまま動く形で実装しました。
学習用としてはもちろん、加工して自社業務に利用することも可能です。
ご興味のある方は以下からご覧ください。
ACCESSを使いこなせば、業務の効率化や自動化が実現できます。
しかし、自分でACCESSを学ぶには時間がない、難しそうで不安、という方も多いでしょう。
そんな時は、ACCESS開発歴20年以上、過去に300以上のACCESSデータベースの開発・修正実績のあるはこにわガジェット（@hakoniwagadget）にお任せください。
ACCESSの新規開発、既存のACCESSの修正、ACCESSの操作レッスンなど様々なサービスをご提供しています。
ご興味のある方は下記のサービス一覧をご覧ください。
最後までお読みいただき、ありがとうございました。
