派遣で働くエンジニアのスキルアップを応援するサイト

PRODUCED BY RECRUIT

【問題】Accessなぜ動かない?VBAコード内のSQLを修正せよ

データベース管理ソフト「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の文字列型は「"」
コンボボックスには、実際にどんな値が入るのかを考えながら、SQL文を組み立ててみてくださいね。

未完成のAccessを正しく動かすことはできるのか。答えはこちらから。

【出題者】今村 ゆうこさん
Excel、Accessなどの書籍を執筆し、事務系プログラマー・ブロガー・ライターとして活動するワーキングマザー。著書のイラストや図解も手掛けている。著書には、『業務アプリ作成のための Access VBA 実践サンプル集~仕事の現場で即使える』『Excel & Access 連携実践ガイド ~仕事の現場で即使える』『Access 実践マスターガイド~仕事の現場で即使える』(以上、技術評論社)など多数。
ブログ https://ateitexe.com/
YouTubeチャンネル https://www.youtube.com/@ateitexe_yt

※本記事に記載されている会社名、製品名はそれぞれ各社の商標および登録商標です。

リクルートスタッフィング