今回から、登録した入庫情報、出庫情報を基に、在庫情報を閲覧できる仕組みを設けていきます。T_在庫テーブルでは「在庫数」、「在庫年月日」及び「品名」フィールドを保持しています。そこで、これらを閲覧できるようにするとともに、フィルタリングをして閲覧できるようにもしていきます。
在庫を確認するためのサブフォーム作成
まずは、在庫を確認するためのサブフォーム（F_在庫サブ）を作成します。これは、これまでに作成している「F_入庫サブ」、「F_出庫サブ」と同様の方法で作成します。しかしながら、他のサブフォームと違って、「F_在庫サブ」フォームでは「レコードソース」プロパティには何も設定しません。この理由は、レコード閲覧をする際にユーザーが関心をもった条件でフィルタリングをして出力する必要があり、また、抽出条件が多数あるとそれに併せたレコードソース設定が必要になるからです。
今回作成するデータベースでは、「F_在庫サブ」フォームに対する「レコードソース」プロパティはVBAにより設定するようにします。
一方で、「F_在庫サブ」フォームからレコードの追加、削除、更新ができてしまうと、データ整合性の観点から不都合が生じる可能性がありますので、これら処理はできないようにプロパティ設定をします。
はじめに、図1に示す「F_在庫サブ」フォームを作成します。そして、「データ入力用」、「追加の許可」、「削除の許可」および「更新の許可」プロパティを全て「いいえ」とします。
在庫レコードのフィルタリング条件入力オブジェクトの配置
これまでに作成している「T_在庫テーブル」あるいは「F_在庫サブ」フォームでは、全てのレコードを表示することはできますが、一方で、ユーザーは自身が関心を持っているレコードだけを取得したいものです。そのため、今回作成するデータベースでは品目から在庫情報をフィルタリングして閲覧できるようにします。
また、同一品目でも過去の在庫数量を知りたいニーズもあると思います。したがって、時制を考慮した在庫情報の取得もできるように条件を盛り込む機能を設けます。
加えて、先に作成した「F_在庫サブ」フォームも「F_メイン」フォーム上に配置します。（F_入庫サブ」等と同様の配置方法。）
図2に示すように、「F_在庫サブ」フォームを配置し、さらに在庫情報に対する抽出条件を入力できるように「F_メイン」フォーム上に「品目情報」と「在庫年月日」のテキストボックス（それぞれ、「t_在庫品目」、「t_在庫年月日」という名前にします。）を配置します。
「t_在庫品目」はコンボボックスに切り替え、さらに「値集合ソース」プロパティは「T_品目マスタ」に、「列数」プロパティは「2」、「列幅」プロパティは「0cm;3.805cm」とします。この考え方については、以下の記事をご参照ください。
「t_在庫年月日」は必ず値を入力するようにし、「既定値」プロパティは「今日の日付」が表示されるように「Date()」と設定します。ここで、AccessとExcel（Excelの場合はToday()）での今日の日付取得方法には若干の違いがありますので、注意をしてください。
さらに、フールプルーフの思想として、「t_在庫年月日」に値が入力されていない状態では、当該テキストボックスからフォーカスを外せないような機能を設けます。なお、これらの処理は全てVBAにより実現します。このため、「t_在庫年月日」の「フォーカス喪失時」プロパティに「[イベント プロシージャ]」を設定し、VBAにより以下のソースコードを追加します。
Private Sub t_在庫年月日_Exit(Cancel As Integer) 't_在庫年月日テキストボックスがブランクまたはNULLの場合の処理 If t_在庫年月日 = "" Or IsNull(t_在庫年月日) Then 'エラーメッセージを表示する。MsgBox "在庫年月日に値を入力してください。"'CancelをTrueにすることで、フォーカス喪失時の処理をキャンセルする。Cancel = TrueEnd If End Sub
在庫サブフォーム起動時の設定
作成した「F_在庫サブ」フォームには「レコードソース」プロパティに何も設定していないため、「F_メイン」フォームを起動しても、「F_在庫サブ」には何も表示されません。
ここで、「t_在庫年月日」には既定値として「Date() = 今日の日付」が設定されていますので、この値から「F_在庫サブ」フォームが起動（「F_メイン」フォームの起動含む）される際にSQL文を作成し、かつ「レコードソース」プロパティにSQL文を設定するようにソースコードを記入します。
「F_在庫サブ」フォームの「読み込み時」プロパティに「[イベント プロシージャ]」を設定し、VBAにより以下のソースコードを追加します。
Private Sub Form_Load() 'SQL文を格納する変数の宣言 Dim mySQL As String 'SQL文の作成 mySQL = "SELECT T_品目マスタ.品名, T_品目マスタ.型番, T_品目マスタ.製造元, Sum(T_在庫テーブル.在庫数) AS 在庫数の合計 " mySQL = mySQL + "FROM T_在庫テーブル INNER JOIN T_品目マスタ ON T_在庫テーブル.品名 = T_品目マスタ.品目ID " mySQL = mySQL + "WHERE (((T_在庫テーブル.在庫年月日) Between #1/1/2000# And [Forms]![F_メイン].[t_在庫年月日])) " mySQL = mySQL + "GROUP BY T_品目マスタ.品名, T_品目マスタ.型番, T_品目マスタ.製造元;" 'Me（つまりF_在庫サブフォーム）のレコードソースに作成したSQL文を格納 Me.RecordSource = mySQL 'F_在庫サブフォームの画面を更新 Me.Requery End Sub
在庫レコードのフィルタリング機能実装
さて、「F_メイン」上にフィルタリング条件を入力するテキストボックスを配置しましたが、これだけでは「F_在庫サブ」フォーム上のレコードは更新されません。そこで、コマンドボタン（「cmd_在庫フィルタ」という名称にし、図1に示す位置に配置）をクリックするイベントによりフィルタリングが行われるように機能を実装します。プロセスは以下のようになります。
① 「cmd_在庫フィルタ」コマンドボタンをクリックする。
② 「t_在庫年月日」に条件が入力されていることを確認する。
（上記フォーカス喪失時の処理で②は本来不要。しかしながら、改修によってフォーカス喪失時の機能がはたらかなくなる等の可能性があるため、二重の対策としている。）
③ フィルタリング条件（t_在庫品目）の入力有無によって、「F_在庫サブ」フォームの「レコードソース」プロパティに設定するSQL文を作成する。
④ 「F_在庫サブ」フォームの「レコードソース」プロパティにSQL文を設定する。
⑤ 情報更新により「F_在庫サブ」画面も更新する。（リクエリーをする。）
以上のことを実装するために、まずは「cmd_在庫フィルタ」の「クリック時」プロパティに「[イベント プロシージャ]」を設定し、VBAにより以下のソースコードを追加します。
Private Sub cmd_在庫フィルタ_Click()
'レコードソースの格納変数
Dim mySQL As String
'いつの在庫かを必ず入力してもらうための制限
If t_在庫年月日 = "" Or IsNull(t_在庫年月日) = True Then
MsgBox "在庫年月日には必ず値を入力してください。"
Exit Sub
End If
'在庫品目の有無でSQLの抽出条件を変更する。
If t_在庫品目 <> "" Or Not IsNull(t_在庫品目) Then
'WHERE条件においてt_在庫品目は2列設定となっており、第1番目の列の値で抽出条件を設定
mySQL = "SELECT T_品目マスタ.品名, T_品目マスタ.型番, T_品目マスタ.製造元, Sum(T_在庫テーブル.在庫数) AS 在庫数の合計 "
mySQL = mySQL + "FROM T_在庫テーブル INNER JOIN T_品目マスタ ON T_在庫テーブル.品名 = T_品目マスタ.品目ID "
mySQL = mySQL + "WHERE (((T_在庫テーブル.在庫年月日) Between #1/1/2000# And [Forms]![F_メイン].[t_在庫年月日]) AND T_在庫テーブル.品名 = " & t_在庫品目.Column(0) & ") "
mySQL = mySQL + "GROUP BY T_品目マスタ.品名, T_品目マスタ.型番, T_品目マスタ.製造元;"
Else
'品目にフィルタリング条件が入力されていない場合（全品目の抽出）
mySQL = "SELECT T_品目マスタ.品名, T_品目マスタ.型番, T_品目マスタ.製造元, Sum(T_在庫テーブル.在庫数) AS 在庫数の合計 "
mySQL = mySQL + "FROM T_在庫テーブル INNER JOIN T_品目マスタ ON T_在庫テーブル.品名 = T_品目マスタ.品目ID "
mySQL = mySQL + "WHERE (((T_在庫テーブル.在庫年月日) Between #1/1/2000# And [Forms]![F_メイン].[t_在庫年月日])) "
mySQL = mySQL + "GROUP BY T_品目マスタ.品名, T_品目マスタ.型番, T_品目マスタ.製造元;"
End If
'F_在庫サブのレコードソースプロパティにSQL文をセット
Forms!F_メイン!F_在庫サブ.Form.RecordSource = mySQL
'F_在庫サブのリクエリー
Forms!F_メイン!F_在庫サブ.Form.Requery
End Sub
まず、「t_在庫年月日」に対する入力確認は記載の通りです。続く、SQL文の作成がソースコードの肝になります。「t_在庫品目」の入力有無でSQL文のWHERE文の記載内容が変わります。内部結合に係るSQL文は以下の記事をご参照ください。
作成するSQL文は集計する必要があるため、「GROUP BY句」を用いています。また、フィルタリングに係る条件を「WHERE文」を用いて実現していますが、在庫品目をフィルタリング条件に盛り込む場合には、「T_在庫テーブル」の「品名」フィールド（品目ID情報を保持）との比較をします。
特に「t_在庫品目」テキストボックスには「値集合ソース」プロパティで「T_品目マスタ」を参照しており、また2列の情報を保持していますが、必要なのは「品目ID」フィールドの値なので、上記WHERE文に「t_在庫品目」の情報を反映するためには1列目の情報のみを取得する必要があり、「t_在庫品目.Column(0)」と記載します。
品目をフィルタリング条件にしない場合は、在庫年月日のみをフィルタリング条件にします。これで、SQL文を作成できましたので、次に「レコードソース」プロパティにSQL文を設定しますが、「F_メイン」フォームの情報を「F_在庫サブ」フォームに適用するため、そのコレクション記載方法は注意してください。
最後に、情報を更新した際に「F_在庫サブ」フォームをリクエリーするための処理を盛り込んで一通りのソースコードが完了となります。
データ登録後の在庫情報への反映方法
「F_在庫サブ」のフィルタリングに主眼を置き説明をしてきましたが、入庫・出庫データを登録または削除した際も、「F_在庫サブ」の情報は最新となるようにしたいものです。
そこで、これまでに作成してきたソースコードの指定する場所に以下の内容を追記してください。
Call cmd_在庫フィルタ_Click
このソースコードは、「Call以降に記載のルーチンを呼び出して実行する」ということを意味しています。つまり、上で作成した「cmd_在庫フィルタ_Click」ルーチンを別のルーチン内で呼び出して、処理をすることができるわけです。
■以下にCall文を追記してください。
・cmd_入庫データ削除_Click()： Forms!F_メイン!F_入庫サブ.Form.AllowDeletions = Falseの後ろ
・cmd_出庫データ削除_Click()： Forms!F_メイン!F_出庫サブ.Form.AllowDeletions = Falseの後ろ
・cmd_入庫処理_Click()： Forms!F_メイン!F_入庫サブ.Requeryの後ろ
・cmd_出庫処理_Click()： Forms!F_メイン!F_出庫サブ.Requeryの後ろ
これで、データ登録・削除に係る一通りの処理を実行した後、「F_在庫サブ」には最新情報を反映することができます。
まとめ
今回は、在庫情報を閲覧できるようにサブフォームを追加するとともに、フィルタリング機能を設けることで必要な情報だけを出力できるようにしました。Access内での処理だけであれば、今回までの内容をベースにさらに業務プロセスを分析して改修を進めれば、良い在庫管理システムが出来上がると思います。
次回は、Excel等のファイルエクスポート方法を紹介します。
スポンサーリンク
コメント