ACCESS フォームの入力に応じてテーブルを呼び出して更新する
ACCESSでよく利用されるのはデータの検索画面と入力画面です。
これらの画面を2つ別々に作成することもできますが、入力フォームのレイアウトで、過去データの検索と呼び出し、そして修正までを一画面で行うことも可能です。
今回は、フォームの入力値に応じてテーブルの特定レコードを呼び出し、テーブルのデータを更新する方法をご紹介します。
こんにちは。
はこにわガジェット （@hakoniwagadget） です。
ACCESSを使った売上管理、顧客管理などのデータベース開発を行っています。
ACCESSは使いこなすために少しスキルが必要なものの、うまく活用すればExcelよりも業務の効率化が図れます。
この記事ではACCESSで実際に作成したフォームやレポートを、その作成方法と共にご紹介していきます。
完成形
まずは完成形から提示します。
以下は、作業時間管理用のACCESSデータベースです。
上半分は入力欄になっており、下半分にリスト型で入力済みの本日の作業予定が記載されています。
下段のリストのIDをダブルクリックすると、上段の入力欄にデータを呼び出すことが可能です。
さらに、呼び出した情報を更新して登録ボタンを押すと、データが反映されます。
このような、下段のリストから上段の入力用フォームに情報を呼び出す機能の実装方法をご紹介します。
テーブルの準備
まずはテーブルです。
今回は、TRN_業務明細という業務内容を1レコードごとに保存するテーブルを準備しました。
フォームの準備
次にフォームです。
先ほど完成形としてお見せしたフォームをデザインビューで見ると下記になります。
上段はすべて非連結のテキストボックスになっています。
下段はサブフォームになっており、このサブフォームはTRN_業務明細をレコードソースにしています。
そのため、TRN_業務明細に登録されたレコードが一覧表示されます。
レコードの呼び出し機能
それでは、IDをダブルクリックすると、上段に該当のレコードのデータを呼び出す機能の作成方法をご紹介します。
IDのダブルクリックは、サブフォーム側で行われます。
そのため、サブフォームをデザインビューで開き、IDのダブルクリック時イベントに、イベントプロシージャを設定します。
設定したイベントプロシージャの内容がこちらです。
テキストでも記載しますね。
Private Sub 業務明細ID_DblClick(Cancel As Integer)
[Forms]![メインメニュー]![業務明細ID] = Me!業務明細ID
[Forms]![メインメニュー]![日付] = Me!日付
[Forms]![メインメニュー]![役割ID] = Me!役割ID
[Forms]![メインメニュー]![業務大分類ID] = Me!業務大分類ID
[Forms]![メインメニュー]![業務小分類ID] = Me!業務小分類ID
[Forms]![メインメニュー]![作業区分ID] = Me!作業区分ID
[Forms]![メインメニュー]![開始予定時間] = Me!開始予定時間
[Forms]![メインメニュー]![終了予定時間] = Me!終了予定時間
[Forms]![メインメニュー]![開始実績時間] = Me!開始実績時間
[Forms]![メインメニュー]![終了実績時間] = Me!終了実績時間
End Sub
行数は多いですが処理は非常に単純で、サブフォームの選択されたレコードの各フィールドの値を、メインフォームの非連結のテキストボックスにコピーしています。
式の右辺のMeがサブフォーム側を指しているわけです。
レコードの更新機能
次にレコードの更新機能です。
レコードの呼び出し機能で呼び出した情報は、フォームで変更することが可能です。
しかし、フォーム上で直接テーブルと連結しているわけではないので、そのままではテーブルの値は更新されません。
そこで、登録ボタンを押した際に、テーブルの値を更新するプロシージャを作成しています。
上記の登録ボタンのクリック時イベントで設定したイベントプロシージャの内容が下記です。
Public Sub touroku()
'変数定義
Dim cn As ADODB.Connection
Dim rs1 As ADODB.Recordset
'コネクションオープン
Set cn = CurrentProject.Connection
Set rst1 = New ADODB.Recordset
rst1.Open "TRN_業務明細", cn, adOpenKeyset, adLockOptimistic
'入力チェック
If IsNull(Forms!メインメニュー!日付) = True Then
MsgBox "日付が未入力です。", vbCritical + vbOKOnly, "日付未入力"
Exit Sub
End If
If Forms!メインメニュー!役割ID = "" Then
MsgBox "役割が未入力です。", vbCritical + vbOKOnly, "役割未入力"
Exit Sub
End If
If Forms!メインメニュー!業務大分類ID = "" Then
MsgBox "業務大分類が未入力です。", vbCritical + vbOKOnly, "業務大分類未入力"
Exit Sub
End If
If Forms!メインメニュー!業務小分類ID = "" Then
MsgBox "業務小分類が未入力です。", vbCritical + vbOKOnly, "業務小分類未入力"
Exit Sub
End If
If Forms!メインメニュー!作業区分ID = "" Then
MsgBox "作業区分が未入力です。", vbCritical + vbOKOnly, "作業区分未入力"
Exit Sub
End If
If Forms!メインメニュー!開始予定時間 = "" And Forms!メインメニュー!開始実績時間 = "" Then
MsgBox "開始予定時間が未入力です。", vbCritical + vbOKOnly, "開始予定時間未入力"
Exit Sub
End If
If Forms!メインメニュー!終了予定時間 = "" And Forms!メインメニュー!終了実績時間 = "" Then
MsgBox "終了予定時間が未入力です。", vbCritical + vbOKOnly, "終了予定時間未入力"
Exit Sub
End If
'登録
'業務明細IDがNULLの場合（新規）
If Forms!メインメニュー!業務明細ID = "" Or IsNull(Forms!メインメニュー!業務明細ID) = True Then
'実績時間未入力の場合
If Forms!メインメニュー!開始実績時間 = "" And Forms!メインメニュー!終了実績時間 = "" Then
rst1.AddNew
rst1!日付 = Forms!メインメニュー!日付
rst1!役割ID = Forms!メインメニュー!役割ID
rst1!業務大分類ID = Forms!メインメニュー!業務大分類ID
rst1!業務小分類ID = Forms!メインメニュー!業務小分類ID
rst1!作業区分ID = Forms!メインメニュー!作業区分ID
rst1!開始予定時間 = Forms!メインメニュー!開始予定時間
rst1!終了予定時間 = Forms!メインメニュー!終了予定時間
rst1.Update
'予定時間未入力の場合
ElseIf Forms!メインメニュー!開始予定時間 = "" And Forms!メインメニュー!終了予定時間 = "" Then
rst1.AddNew
rst1!日付 = Forms!メインメニュー!日付
rst1!役割ID = Forms!メインメニュー!役割ID
rst1!業務大分類ID = Forms!メインメニュー!業務大分類ID
rst1!業務小分類ID = Forms!メインメニュー!業務小分類ID
rst1!作業区分ID = Forms!メインメニュー!作業区分ID
rst1!開始実績時間 = Forms!メインメニュー!開始実績時間
rst1!終了実績時間 = Forms!メインメニュー!終了実績時間
rst1.Update
'予定・実績時間入力済の場合
Else
rst1.AddNew
rst1!日付 = Forms!メインメニュー!日付
rst1!役割ID = Forms!メインメニュー!役割ID
rst1!業務大分類ID = Forms!メインメニュー!業務大分類ID
rst1!業務小分類ID = Forms!メインメニュー!業務小分類ID
rst1!作業区分ID = Forms!メインメニュー!作業区分ID
rst1!開始予定時間 = Forms!メインメニュー!開始予定時間
rst1!終了予定時間 = Forms!メインメニュー!終了予定時間
rst1!開始実績時間 = Forms!メインメニュー!開始実績時間
rst1!終了実績時間 = Forms!メインメニュー!終了実績時間
rst1.Update
End If
'業務明細IDがNOT NULLの場合（更新）
Else
rst1.Filter = "業務明細ID = " & Forms!メインメニュー!業務明細ID
'実績時間未入力の場合
If Forms!メインメニュー!開始実績時間 = "" And Forms!メインメニュー!終了実績時間 = "" Then
rst1!日付 = Forms!メインメニュー!日付
rst1!役割ID = Forms!メインメニュー!役割ID
rst1!業務大分類ID = Forms!メインメニュー!業務大分類ID
rst1!業務小分類ID = Forms!メインメニュー!業務小分類ID
rst1!作業区分ID = Forms!メインメニュー!作業区分ID
rst1!開始予定時間 = Forms!メインメニュー!開始予定時間
rst1!終了予定時間 = Forms!メインメニュー!終了予定時間
rst1.Update
'予定時間未入力の場合
ElseIf Forms!メインメニュー!開始予定時間 = "" And Forms!メインメニュー!終了予定時間 = "" Then
rst1!日付 = Forms!メインメニュー!日付
rst1!役割ID = Forms!メインメニュー!役割ID
rst1!業務大分類ID = Forms!メインメニュー!業務大分類ID
rst1!業務小分類ID = Forms!メインメニュー!業務小分類ID
rst1!作業区分ID = Forms!メインメニュー!作業区分ID
rst1!開始実績時間 = Forms!メインメニュー!開始実績時間
rst1!終了実績時間 = Forms!メインメニュー!終了実績時間
rst1.Update
'予定・実績時間入力済の場合
Else
rst1!日付 = Forms!メインメニュー!日付
rst1!役割ID = Forms!メインメニュー!役割ID
rst1!業務大分類ID = Forms!メインメニュー!業務大分類ID
rst1!業務小分類ID = Forms!メインメニュー!業務小分類ID
rst1!作業区分ID = Forms!メインメニュー!作業区分ID
rst1!開始予定時間 = Forms!メインメニュー!開始予定時間
rst1!終了予定時間 = Forms!メインメニュー!終了予定時間
rst1!開始実績時間 = Forms!メインメニュー!開始実績時間
rst1!終了実績時間 = Forms!メインメニュー!終了実績時間
rst1.Update
End If
End If
'フォーム入力値クリア
Forms!メインメニュー!業務明細ID = ""
Forms!メインメニュー!役割ID = ""
Forms!メインメニュー!業務大分類ID = ""
Forms!メインメニュー!業務小分類ID = ""
Forms!メインメニュー!作業区分ID = ""
Forms!メインメニュー!開始予定時間 = ""
Forms!メインメニュー!終了予定時間 = ""
Forms!メインメニュー!開始実績時間 = ""
Forms!メインメニュー!終了実績時間 = ""
Forms!メインメニュー!開始予定時刻入力 = ""
Forms!メインメニュー!終了予定時刻入力 = ""
Forms!メインメニュー!開始実績時刻入力 = ""
Forms!メインメニュー!終了実績時刻入力 = ""
'クローズ処理
rst1.Close
Set rs1 = Nothing
cn.Close
Set cn = Nothing
End Sub
かなり長いコードですが解説していきます。
基本的にはADOを使ってレコードの更新を行うプロシージャです。
ADOについては以下の記事でも解説していますので、興味のある方はご覧ください。
最初の「変数定義」と「コネクションオープン」はADOを使うための準備です。
'変数定義
Dim cn As ADODB.Connection
Dim rs1 As ADODB.Recordset
'コネクションオープン
Set cn = CurrentProject.Connection
Set rst1 = New ADODB.Recordset
rst1.Open "TRN_業務明細", cn, adOpenKeyset, adLockOptimistic
その後、「入力チェック」が続きます。
これは、フォームの入力値がテーブルのフィールドに対して漏れがないかをチェックしています。
'入力チェック
If IsNull(Forms!メインメニュー!日付) = True Then
MsgBox "日付が未入力です。", vbCritical + vbOKOnly, "日付未入力"
Exit Sub
End If
フォームの入力値に漏れがあるとテーブルに必要な値が不足してしまい、不完全なレコードができてしまうので、その漏れがないかをIf分でチェックしているのです。
このフォームの場合は日付、役割ID、業務大分類ID、業務小分類ID、作業区分ID、開始予定時間、終了予定時間は必須入力項目なのでそれをすべてチェックしています。
特に、他のテーブルとリレーション設定をしているフィールドは、値を入れないとリレーションが崩れてレコード自体が表示されなくなる危険性があります。私の場合はリレーション設定しているフィールドは、〇〇IDという名称にしているので、ここはもれずに入力されるようチェックしています。
次にいよいよ「登録」、つまりレコードの更新処理です。
ここでは、業務明細IDがテーブルの主キーなので、業務明細IDがNullかそうでないかで、新規レコードを作成すべきか既存レコードの更新を行うべきかを判定しています。
'業務明細IDがNULLの場合（新規）
If Forms!メインメニュー!業務明細ID = "" Or IsNull(Forms!メインメニュー!業務明細ID) = True Then
この後は、実績時間未入力の場合、予定時間未入力の場合、予定・実績時間入力済みの場合とパターンは分かれますが、新規の場合はADOのAddNewメソッドを使ってレコードの追加、既存の場合はUpdateメソッドを使ってレコードの更新を行います。
新規の場合
rst1.AddNew
rst1!日付 = Forms!メインメニュー!日付
rst1!役割ID = Forms!メインメニュー!役割ID
rst1!業務大分類ID = Forms!メインメニュー!業務大分類ID
rst1!業務小分類ID = Forms!メインメニュー!業務小分類ID
rst1!作業区分ID = Forms!メインメニュー!作業区分ID
rst1!開始予定時間 = Forms!メインメニュー!開始予定時間
rst1!終了予定時間 = Forms!メインメニュー!終了予定時間
rst1.Update
更新の場合
If Forms!メインメニュー!開始実績時間 = "" And Forms!メインメニュー!終了実績時間 = "" Then
rst1!日付 = Forms!メインメニュー!日付
rst1!役割ID = Forms!メインメニュー!役割ID
rst1!業務大分類ID = Forms!メインメニュー!業務大分類ID
rst1!業務小分類ID = Forms!メインメニュー!業務小分類ID
rst1!作業区分ID = Forms!メインメニュー!作業区分ID
rst1!開始予定時間 = Forms!メインメニュー!開始予定時間
rst1!終了予定時間 = Forms!メインメニュー!終了予定時間
rst1.Update
最後に、フォームの入力値をクリアして終わりです。
'フォーム入力値クリア
Forms!メインメニュー!業務明細ID = ""
Forms!メインメニュー!役割ID = ""
Forms!メインメニュー!業務大分類ID = ""
Forms!メインメニュー!業務小分類ID = ""
Forms!メインメニュー!作業区分ID = ""
Forms!メインメニュー!開始予定時間 = ""
Forms!メインメニュー!終了予定時間 = ""
Forms!メインメニュー!開始実績時間 = ""
Forms!メインメニュー!終了実績時間 = ""
Forms!メインメニュー!開始予定時刻入力 = ""
Forms!メインメニュー!終了予定時刻入力 = ""
Forms!メインメニュー!開始実績時刻入力 = ""
Forms!メインメニュー!終了実績時刻入力 = ""
上記のように、フォームでテーブルの値を呼び出して利用することが可能です。
フォームの入力値をテーブルに戻す際は、不整合が出るレコードにならないよう、入力チェックや条件分岐をしっかりと考慮する点に注意ください。
以上、フォームの入力に応じてテーブルを呼び出して更新する方法をご紹介しました。
ACCESSを使いこなせば、業務の効率化や自動化が実現できます。
しかし、自分でACCESSを学ぶには時間がない、難しそうで不安、という方も多いでしょう。
そんな時は、ACCESS開発歴20年以上、過去に100以上のACCESSデータベースを開発してきた私（@hakoniwagadget）にお任せください。
ACCESSの新規開発、既存のACCESSの修正、ACCESSの操作レッスンなど様々なサービスをご提供しています。
ご興味のある方は下記のサービス一覧をご覧ください。
最後までお読みいただき、ありがとうございました。
