DLookUp関数とSQLの活用 フォーム上でテーブルの値を呼び出し更新する方法

VBA

以前にAccessのテーブルとフォームを直接連結させず、フォーム上の各テキストボックスに入力した値を、ADOを用いてテーブルに追加する方法を紹介しました。

先に紹介した方法では、データをテーブルに登録することはできますが、データを修正・更新したい時にインターフェースがないため対応できません。これを解消するべく、テーブルに登録した値をフォーム上の各テキストボックスに反映し、さらに修正した値を再度テーブルに反映する方法を紹介していきます。

この方法により、ユーザーによるテーブルの直接操作を回避することができ、データベースの堅牢性を維持することができます。

また、以前はADOを用いてレコード操作をする方法を紹介しましたが、今回はDLookUp関数とSQL文による更新処理を組み合わせた方法を示します。

スポンサーリンク

サンプルデータについて

まずは、サンプルデータを保存するためのテーブルとして「T_従業員テーブル」を作成し、表1に示すフィールド名とデータ型を設定し、表2に示すサンプルデータを登録します。

表1 T_従業員テーブルの構成

フィールド名データ型
従業員番号短いテキスト
氏名短いテキスト
年齢数値型
所属短いテキスト

表2 サンプルデータ

従業員番号氏名年齢所属
02A0001佐藤 花子40総務部
02E0001渡辺 純一40技術部
10A0001吉田 啓介32総務部
10E0001平川 博34技術部
94S0001山本 伸介48営業部
99S0001麻生 涼子43営業部

今回は、これらサンプルデータがテーブルに登録されているものとし、これから作成するフォーム上に従業員番号情報を基に値を呼び出す仕組みを作成していきます。

サンプルフォーム

サンプルフォームでは従業員番号をキーとして、データの呼び出し及び呼び出したデータの更新を行えるものとし、操作手順は次のようにします。

① 従業員番号をコンボボックスから選択する。
② 従業員番号が一致するレコードを検索し、フォーム上の各テキストボックスに反映する。
③ 各テキストボックス(従業員番号を除く)で値を修正入力する。
④ ③の結果に基づき、「T_従業員テーブル」の値を更新する。

以上を踏まえてサンプルフォームを作成していきましょう。

まずは、図1に示すようにフォームデザインで各オブジェクトを配置します。フォームの作成方法については、以下の記事もご参考にしてください。

図1 サンプルフォームの作成①

今回作成しているサンプルフォームでは、フォームヘッダーに「t_検索」コンボボックス及び「cmd_record」コマンドボタンを、詳細セクションに「t_従業員番号」、「t_氏名」、「t_年齢」
及び「t_所属」テキストボックス、また「cmd_更新」コマンドボタンを配置します。

ここで、図1にも示されていますが、コンボボックス及びテキストボックスは「非連結」としているため(コントロールソースプロパティに何も設定されていない状態)、 ユーザーによるフォームを介したテーブルの直接操作はできない設定としています。

続いて、各オブジェクトのプロパティを設定していきます。「F_Main」フォームのレコードソースプロパティは何も設定していません(図2参照)ので、テーブルを直接操作できない理由はここにあります。

図2 サンプルフォームの作成②

「t_検索」コンボボックスでは、「T_従業員テーブル」に登録されている従業員番号を表示できるようにします。そこで、値集合ソースプロパティには以下のSQL文を設定します。

「SELECT T_従業員テーブル.従業員番号 FROM T_従業員テーブル;」

また、値集合タイププロパティは「テーブル/クエリ」を設定します(図3参照)。

図3 サンプルフォームの作成③

詳細セクションの各テキストボックスについては、次のように設定します。

「t_従業員番号」テキストボックスの「編集ロック」プロパティは「はい」を設定し、背景色も灰色にします。なぜなら、従業員番号はデータの更新対象にしていないためです。

一方で、「t_氏名」、「t_年齢」及び「t_所属」テキストボックスの「編集ロック」プロパティは「いいえ」を設定します(図4参照)。

図4 サンプルフォームの作成④

最後に、各コマンドボタンの設定をします。「cmd_record」及び「cmd_更新」コマンドボタンの「クリック時」プロパティには「イベントプロシージャ」を設定します(図5参照)。

図5 サンプルフォームの作成⑤

そして、イベントプロシージャの右横に出てくる「…」ボタンをクリックしてVBA画面を起動し、ソースコードを追記します(図6参照)。

図6 サンプルフォームの作成⑥

実際のソースコードを以下に示します。

■「cmd_record」コマンドボタンの「クリック時」に実装するコード

Private Sub cmd_record_Click()

'DLookUpを用いて各テキストボックスに値を反映させる。
Me.t_従業員番号 = DLookup("従業員番号", "T_従業員テーブル", "従業員番号 = '" & Me.t_検索 & "'")
Me.t_氏名 = DLookup("氏名", "T_従業員テーブル", "従業員番号 = '" & Me.t_検索 & "'")
Me.t_年齢 = DLookup("年齢", "T_従業員テーブル", "従業員番号 = '" & Me.t_検索 & "'")
Me.t_所属 = DLookup("所属", "T_従業員テーブル", "従業員番号 = '" & Me.t_検索 & "'")

End Sub

ここでポイントとしては、データの呼び出しにDLookUp関数を用いている点です。DLookUp関数については、以下の記事で紹介していますので、ご参考にしてください。

■「cmd_更新」コマンドボタンの「クリック時」に実装するコード

Private Sub cmd_更新_Click()

'SQL文を格納するための変数を宣言する。
Dim mySQL As String

'更新をするためのSQL文
mySQL = "UPDATE T_従業員テーブル SET 氏名 = '" & Me.t_氏名 & "' , 年齢 = " & Me.t_年齢 & " , 所属 = '" _
& Me.t_所属 & "' WHERE 従業員番号 = '" & Me.t_従業員番号 & "';"

'SQL文の実行
DoCmd.RunSQL mySQL

'処理を終了した際のメッセージ表示
MsgBox "データベースの情報を更新しました。"

End Sub

ここでのポイントとしては、SQL文の実行は以下のコードで実装できるという点です。(今回はmySQLという変数にレコードを更新するためのSQL文を入れ込んでいます。)

DoCmd.RunSQL mySQL

なお、更新に係るSQL文は以下の記事で紹介していますので、ご参考にしてください。

以上で、サンプルフォームを作りこむことができましたので、次からは実際の動作を見てみましょう。

サンプルフォームの動作確認

作成したサンプルフォームをフォームビューで開き、上記の操作手順に従いレコードを呼び出してみましょう。

まずは、図7に示すように従業員番号をコンボボックスから選択し、続いてレコード検索ボタンをクリックします。

図7 サンプルフォームの動作確認①

すると、図8に示すように各テキストボックスに従業員番号に応じた値が反映されました。これら値は、「T_従業員テーブル」に登録した値と一致していることを確認できます。

図8 サンプルフォームの動作確認②

ここでポイントとしては、フォーム上に呼び出した各値を変更しても「T_従業員テーブル」の値は、この段階では変わらないため、ユーザーによる不用意なデータ操作を防ぐことができるという点です。

さて、呼び出した値をフォーム上で修正していきましょう。氏名の「啓介」を「啓輔」に、年齢を「32」から「31」に修正します。そして、最後にレコード更新ボタンをクリックします(図9参照)

図9 サンプルフォームの動作確認③

レコード更新ボタンをクリックすると、「T_従業員テーブル」の対象レコードを更新するか確認メッセージボックスが表示されますので、「はい」をクリックします。これで更新がされ、更新完了のメッセージボックスが表示されます(図10参照)

図10 サンプルフォームの動作確認④

最後に、フォーム上で修正した値が「T_従業員テーブル」に反映されているかを確認すると、図11に示すように確かに値が変わっています。

図11 サンプルフォームの動作確認⑤

レコード更新時の注意点

今回作成したVBAコードでは、各テキストボックスに入力した値の妥当性等をチェックする機能は設けませんでしたが、例えば、「t_年齢」テキストボックスに数値以外の値が入っていた場合にはテーブル更新時にエラーが発生しますので、これらを正しい値を入力させるようにする機能が必要になります。

このように、ユーザーにどのような値を入力してほしいかを考慮してチェック機能を設けることで、データベースの保守が楽になりますし、データ管理も容易になることが期待できます。

まとめ

本記事では、DLookUp関数とSQL文を用いてフォーム上でテーブルの値を呼び出し更新する方法を紹介しました。

レコード操作は様々な方法で実装することができますが、今回紹介した方法は比較的簡単に実装することができ、また拡張性も高いものかと思いますので、是非活用していただければと思います。

最後に、紹介した内容については以下の書籍でも取り扱っていますので、是非ご参考になさってください。

スポンサーリンク

コメント

タイトルとURLをコピーしました