ホチキス先生の「プログラマーと呼ばれたい」

InfoPath & SQL Server !

Access – レコードソースを設定せずVBAで動的にテーブルのレコードを取得する

with 4 comments

アクセスのフォームやレポートは通常レコードソースをプロパティ値に設定してデータを表示する。しかしあらかじめレコードソースを設定しなくても、VBAを使って動的にレコードソースを設定し、データを表示することができる。このときレコードソースの記述がSQL文でできるので、SQLを使ったレコード操作に慣れた人には、このVBAを使った処理はわかりやすく、応用しやすいだろう。たとえばこんなかんじだ。

まずAccessデータベース上に2つのテーブル「生徒1」と「生徒2」を作ってみる。テーブルのフィールドはどちらも同じ構造とし、「学籍番号」、「氏名」、「性別」があるとする。しかしデータの内容は違う。

Access_SQL_001

<Fig 1 : 「生徒1」テーブルを作成する>

Access_SQL_002

<Fig 2 : 「生徒2」テーブルを作成する>

テーブルを作成し適当なデータを作れば、次にフォームを作成する。Access2003の場合は「挿入」-「フォーム」で「新しいフォーム」ウィンドウを開き「基になるテーブルまたはクエリの選択」を空白にしたまま「デザインビュー」をクリックする。Access2007とAccess2010の場合は「作成」-「空白のフォーム」をクリックする。

新しいフォームができたらデザインビューにし、「フォームヘッダー/フッター」を作り、フォームの既定のビューを「帳票フォーム」に変更する。フォームヘッダーと詳細の高さを調節する。フォームの名前は「生徒フォーム」として保存しておこう。

Access_SQL_003

<Fig 3 : レコードソースを指定しないフォームを作る>

ここでいちどフォームを「フォームビュー」にして表示してみよう。レコードソースが指定されておらず、テキストボックスもなにもないので、単に真っ白な画面が表示されるだけである。

Access_SQL_004

<Fig 4 : レコードソースを指定しないフォームを表示した>

次に「詳細」エリアにテキストボックスを3つ作る。各テキストボックスは、プロパティの「データ」タブ「コントロールソース」に、それぞれ表示させたいフィールド名を記述する。通常レコードソースを設定したフォームの編集では、ここでテーブルのレコードソースが表示されドロップダウンリストから選択できるが、ここではレコードソースが設定していないのでリスト表示はされない。キーボードから文字入力でフィールドを設定する。

Access_SQL_005

<Fig 5 : 「学籍番号」、「氏名」、「性別」のテキストボックスを作成する>

テキストボックスを作成したら、もういちどフォームを開いてみよう。もちろんレコードソースが指定されていないので、データは何も表示されない。各フィールドには「#Name?」というエラーが表示されている。

Access_SQL_006

<Fig 6 : データソースが指定されていないフォームを開いたので「#Name?」エラーが表示される>

次にデータソースをVBAで記述するコードを書くが、このコードをボタンのクリックイベントで実行させることにする。そのためにフォームのフォームヘッダーに2つボタンを作成する。ボタンの「表題」はそれぞれ「生徒1データ」、「生徒2データ」としておこう。

Access_SQL_007

<Fig 7 : フォームヘッダーにボタンを2つ作り表題を設定した>

次にVBAコードを書くため、「生徒1データ」の表題を書いたボタンを選択し、プロパティの「イベント」タブから「クリック時」の横の「・・・」をクリックする。すると「ビルダーの選択」ウィンドウが表示されるので、「コードビルダー」を選択して「OK」をクリックする。

Access_SQL_008

<Fig 8 : ボタンのクリック時イベントをVBAで記述するためにコードビルダーを開く>

コードビルダーが開いたら、クリックイベントに対応するイベントプロシージャができている。ここに次のコードを書く。

——————————————————————————–

Me.RecordSource = "select 学籍番号,氏名,性別 from 生徒1"

——————————————————————————–

ダブルクオーテーションで挟まれた部分は単純なSQL文である。

Access_SQL_009

<Fig 9 : コードビルダーでイベントプロシージャを表示する>

Access_SQL_010

<Fig 10 : コードビルダーにレコードソースを指定するコードを書く>

コードが書けたらフォームを開いてみよう。フォームを開いた時点ではレコードソースが指定されていないので、各テキストボックスには「#Name?」のエラーが表示されているが、「生徒1データ」ボタンをクリックすると「生徒1」テーブルを指定したクエリが実行され、データが表示される。

Access_SQL_011

<Fig 11 : レコードソースを指定しないフォームを開く>

Access_SQL_012

<Fig 12 : 「生徒1データ」ボタンのクリックでレコードソースを指定し「生徒1」テーブルのデータを表示した>

では次に「生徒2データ」ボタンのクリックイベントで「生徒2」テーブルからデータを取得するレコードソースを記述しよう。SQL文のfrom句でテーブル名を変更するだけだ。

Access_SQL_013

<Fig 13 : 「生徒2データ」ボタンのイベントプロシージャにレコードソースを指定するコードを書く>

これで「生徒1データ」ボタンをクリックすると「生徒1」テーブルのデータを表示し、「生徒2データ」ボタンをクリックすると「生徒2」テーブルのデータを表示するフォームができた。

Access_SQL_014

<Fig 14 : 完成したフォームを開き「生徒2データ」ボタンをクリックすると「生徒2」テーブルのデータが表示される>

Access_SQL_015

<Fig 15 : 完成したフォームを開き「生徒1データ」ボタンをクリックすると「生徒1」テーブルのデータが表示される>

このようにフォームやレポートをデータソースの指定なしに作成し、ボタンクリックなどで動的にレコードソースを指定してデータを表示させることができる。ここで重要なことは、レコードソースの記述が一般的なSQL文で記述できることである。SQL文に習熟したものなら、自由にレコードセットを記述してフォームやレポートに動的に表示させることができる。とてもわかりやすい。

Written by Yoshio Matsumoto

2011年12月25日 @ 2:08 PM

コメント / トラックバック4件

Subscribe to comments with RSS.


コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中

%d人のブロガーが「いいね」をつけました。