データベース管理ソフト「Access」の技術書を数多く執筆される今村ゆうこさんから挑戦状(クイズ)が届きました。SQLとVBAの知識を活かして解いてみましょう。
AccessはMicrosoft Officeシリーズの1つで、低コストで手軽に利用できる小規模向けデータベース管理ソフトです。独自のUIを作成できる「フォーム」オブジェクトがとっても便利で、テーブルの情報を利用するのも簡単です。
今回は、そんなフォーム上のコントロールにテーブルのレコードを表示して、動的に絞り込む方法についての問題です。覚えておくと便利なテクニックなので、ぜひ一緒に考えてみてくださいね。
同僚がつくったAccessが思うように動かないみたいで、確認しているけれど…。
隣の席に座る同僚が、商品の色を管理するデータベースをAccessでつくっている。けれども、さっきから「うーん……なぜだ……ほかの選択肢で絞り込めない……」と、うなっている。話を聞くと、うまく動作せず困っているようだ。
■Accessの画面を見てみる
「T1」テーブルがある。左端の「ColorID」フィールドが主キーとなっていて、色の名前とカラーコードを格納しているようだ。右端の「Category」フィールドには、その色が属するカテゴリー情報がある。
「F1」フォームがある。フォーム上には「cmb1」「cmb2」というコントロール名の、2つのコンボボックスを設置したようだ。
コンボボックスを展開したときの選択肢は、デザインビューでプロパティシート→「データ」タブ→「集合値ソース」へ、テーブルから任意のレコードを取り出すSELECT文を設定している。
「cmb1」へは「SELECT Category FROM T1 GROUP BY Category;」と書かれていて、「Category」フィールドを、同じ値をグループ化して表示している。
「cmb2」へは「SELECT * FROM T1;」と書かれていて、T1テーブルのすべてのフィールド、レコードを表示している。
隣で戦意喪失している同僚からさらに話を聞いてみると、この状態から、「cmb1」で値が選択されたら、その値で「cmb2」の選択肢を絞り込みたいそうだ。「cmb1」で「RED」が選択されたら、「cmb2」の選択肢で「Category」フィールドが「RED」のものだけに絞り込まれるように……ということだ。
■VBAの編集画面を見てみる
【「cmb1」の値が変更されたら動く】というしくみを確認するため、VBAのイベントプロシージャを開いてみる。「cmb1」を選択し、プロパティシート→「イベント」タブ→「更新後処理」の「…」ボタンをクリック。
VBAを編集する画面が開いた。「cmb1」更新後処理のイベントプロシージャの中には、「cmb2」のソースを変更するコードが書かれている。
同僚の書いたコードは、「cmb2」の「RowSource」プロパティを書き換える命令だ。カテゴリーを 'RED' で絞り込むSELECT構文が書かれている。エラーにはならずに動作はしているようだ。
Private Sub cmb1_AfterUpdate()
Form_F1.cmb2.RowSource = "SELECT * FROM T1 WHERE Category='RED';"
End Sub
だが、確かにこのままでは、「cmb1」で選択されたのがどの値であっても、すべて 'RED’ で絞り込まれてしまう。
動的なコンボボックスの値をSQLに組み込まないといけないが、どう書けばよいのだろうか?
問題
どう書けばよいか、わかりましたか?
3つの選択肢を用意しました。
選択された「cmb1」の値を、動的にSQLに組み込む例として、正しく動作するコードは、以下のうちどれでしょうか?
選択肢1
Private Sub cmb1_AfterUpdate()
Form_F1.cmb2.RowSource = "SELECT * FROM T1 WHERE Category=Form_F1.cmb1.Value;"
End Sub
選択肢2
Private Sub cmb1_AfterUpdate()
Form_F1.cmb2.RowSource = "SELECT * FROM T1 WHERE Category=" & Form_F1.cmb1.Value & ";"
End Sub
選択肢3
Private Sub cmb1_AfterUpdate()
Form_F1.cmb2.RowSource = "SELECT * FROM T1 WHERE Category='" & Form_F1.cmb1.Value & "';"
End Sub
どれもよく似ていますが、違っているのはコードの後半部分です。また、以下のことにも注目してみてください。
- 「cmb1」の値は「Form_F1.cmb1.Value」で取得できる
- VBAのコード内ではSQLを文字列として扱う
- SQLのテキスト型は「'」
- VBAの文字列型は「"」
未完成のAccessを正しく動かすことはできるのか。答えはこちらから。