以前にAccessのテーブルとフォームを直接連結させず、フォーム上の各テキストボックスに入力した値を、ADOを用いてテーブルに追加する方法を紹介しました。
先に紹介した方法では、データをテーブルに登録することはできますが、データを修正・更新したい時にインターフェースがないため対応できません。これを解消するべく、テーブルに登録した値をフォーム上の各テキストボックスに反映し、さらに修正した値を再度テーブルに反映する方法を紹介していきます。
この方法により、ユーザーによるテーブルの直接操作を回避することができ、データベースの堅牢性を維持することができます。
また、以前はADOを用いてレコード操作をする方法を紹介しましたが、今回はDLookUp関数とSQL文による更新処理を組み合わせた方法を示します。
サンプルデータについて
まずは、サンプルデータを保存するためのテーブルとして「T_従業員テーブル」を作成し、表１に示すフィールド名とデータ型を設定し、表2に示すサンプルデータを登録します。
表1 T_従業員テーブルの構成
| フィールド名 | データ型 |
| 従業員番号 | 短いテキスト |
| 氏名 | 短いテキスト |
| 年齢 | 数値型 |
| 所属 | 短いテキスト |
表2 サンプルデータ
| 従業員番号 | 氏名 | 年齢 | 所属 |
| 02A0001 | 佐藤 花子 | 40 | 総務部 |
| 02E0001 | 渡辺 純一 | 40 | 技術部 |
| 10A0001 | 吉田 啓介 | 32 | 総務部 |
| 10E0001 | 平川 博 | 34 | 技術部 |
| 94S0001 | 山本 伸介 | 48 | 営業部 |
| 99S0001 | 麻生 涼子 | 43 | 営業部 |
今回は、これらサンプルデータがテーブルに登録されているものとし、これから作成するフォーム上に従業員番号情報を基に値を呼び出す仕組みを作成していきます。
サンプルフォーム
サンプルフォームでは従業員番号をキーとして、データの呼び出し及び呼び出したデータの更新を行えるものとし、操作手順は次のようにします。
① 従業員番号をコンボボックスから選択する。
② 従業員番号が一致するレコードを検索し、フォーム上の各テキストボックスに反映する。
③ 各テキストボックス（従業員番号を除く）で値を修正入力する。
④ ③の結果に基づき、「T_従業員テーブル」の値を更新する。
以上を踏まえてサンプルフォームを作成していきましょう。
まずは、図1に示すようにフォームデザインで各オブジェクトを配置します。フォームの作成方法については、以下の記事もご参考にしてください。
今回作成しているサンプルフォームでは、フォームヘッダーに「t_検索」コンボボックス及び「cmd_record」コマンドボタンを、詳細セクションに「t_従業員番号」、「t_氏名」、「t_年齢」
及び「t_所属」テキストボックス、また「cmd_更新」コマンドボタンを配置します。
ここで、図1にも示されていますが、コンボボックス及びテキストボックスは「非連結」としているため（コントロールソースプロパティに何も設定されていない状態）、 ユーザーによるフォームを介したテーブルの直接操作はできない設定としています。
続いて、各オブジェクトのプロパティを設定していきます。「F_Main」フォームのレコードソースプロパティは何も設定していません（図2参照）ので、テーブルを直接操作できない理由はここにあります。
「t_検索」コンボボックスでは、「T_従業員テーブル」に登録されている従業員番号を表示できるようにします。そこで、値集合ソースプロパティには以下のSQL文を設定します。
「SELECT T_従業員テーブル.従業員番号 FROM T_従業員テーブル;」
また、値集合タイププロパティは「テーブル/クエリ」を設定します（図3参照）。
詳細セクションの各テキストボックスについては、次のように設定します。
「t_従業員番号」テキストボックスの「編集ロック」プロパティは「はい」を設定し、背景色も灰色にします。なぜなら、従業員番号はデータの更新対象にしていないためです。
一方で、「t_氏名」、「t_年齢」及び「t_所属」テキストボックスの「編集ロック」プロパティは「いいえ」を設定します（図4参照）。
最後に、各コマンドボタンの設定をします。「cmd_record」及び「cmd_更新」コマンドボタンの「クリック時」プロパティには「イベントプロシージャ」を設定します（図5参照）。
そして、イベントプロシージャの右横に出てくる「…」ボタンをクリックしてVBA画面を起動し、ソースコードを追記します（図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に示すように従業員番号をコンボボックスから選択し、続いてレコード検索ボタンをクリックします。
すると、図8に示すように各テキストボックスに従業員番号に応じた値が反映されました。これら値は、「T_従業員テーブル」に登録した値と一致していることを確認できます。
ここでポイントとしては、フォーム上に呼び出した各値を変更しても「T_従業員テーブル」の値は、この段階では変わらないため、ユーザーによる不用意なデータ操作を防ぐことができるという点です。
さて、呼び出した値をフォーム上で修正していきましょう。氏名の「啓介」を「啓輔」に、年齢を「32」から「31」に修正します。そして、最後にレコード更新ボタンをクリックします（図9参照）
レコード更新ボタンをクリックすると、「T_従業員テーブル」の対象レコードを更新するか確認メッセージボックスが表示されますので、「はい」をクリックします。これで更新がされ、更新完了のメッセージボックスが表示されます（図10参照）
最後に、フォーム上で修正した値が「T_従業員テーブル」に反映されているかを確認すると、図11に示すように確かに値が変わっています。
レコード更新時の注意点
今回作成したVBAコードでは、各テキストボックスに入力した値の妥当性等をチェックする機能は設けませんでしたが、例えば、「t_年齢」テキストボックスに数値以外の値が入っていた場合にはテーブル更新時にエラーが発生しますので、これらを正しい値を入力させるようにする機能が必要になります。
このように、ユーザーにどのような値を入力してほしいかを考慮してチェック機能を設けることで、データベースの保守が楽になりますし、データ管理も容易になることが期待できます。
まとめ
本記事では、DLookUp関数とSQL文を用いてフォーム上でテーブルの値を呼び出し更新する方法を紹介しました。
レコード操作は様々な方法で実装することができますが、今回紹介した方法は比較的簡単に実装することができ、また拡張性も高いものかと思いますので、是非活用していただければと思います。
最後に、紹介した内容については以下の書籍でも取り扱っていますので、是非ご参考になさってください。
スポンサーリンク
コメント