AccessVBAで非連結コントロールのイベント処理を一括制御する

Accessは連結フォームっていうお手軽かんたんにテーブルにデータを入力するフォームが作れちゃうんですが、ダイレクトすぎてちょっとな、ということも…、、敢えて非連結のフォームでレコード操作したいというときに、イベント処理がほしいなと思って調べたメモです。

コントロールの準備

こういう形を想定します。コントロールの名前は、それぞれ「名称+数値」で統一させておきます。数値が特定できればMe("○○" & i)で同じレコード部分を操作できますからね。

この形を作ること自体がまずめんどくさいので、ここをVBAでやっちゃう方法もあるのですが…、Accessは自身のフォームにはコードでコントロール追加できないみたいで、別フォームから書けばできるのですが（試した）、うーん、まぁ10個くらいなら手でやっちゃってもいいかな、という所感でした。

デザインビューで見るとこんな感じの非連結。ただし、商品IDにはコンボボックスの集合値ソースとして商品マスタのフィールドが指定されているので、選ぶことはできます。

で、これらのコントロールが変更されたときに動かしたいわけですが、イベントを一個一個書いていたらキリがないので、一括で書きたいと思います。

前、ExcelVBAで同じような目的のことをしたかったとき、クラスモジュールを使ってこんな感じで書きました。

• ExcelVBAでControlオブジェクトのChangeイベントを一括制御する方法

Accessでも同じようにするのかなーと思ってごねごねしたら、クラス使わなくてもできるんですって！　へー！！

コード

該当のフォームモジュールにて。

Private Sub Form_Load() 'フォームを読み込むとき
  Dim i As Integer
  For i = 1 To 10
    Me("更新" & i).OnClick = "=更新_Click(" & i & ")" 'チェックボックス
    Me("商品ID" & i).OnChange = "=商品ID_Change(" & i & ")" 'コンボボックス
    Me("単価" & i).OnExit = "=単価_Exit(" & i & ")" 'テキストボックス
    Me("数量" & i).OnExit = "=数量_Exit(" & i & ")"
  Next i
End Sub

イベントを拾いたいコントロールについて、イベントプロパティに関数を設定して、コントロールの数値部分を引数として持たせます。

使えるイベントはOn～の指定で、各コントロールでいろいろなのですが、

• TextBox メンバー (Access)

テキストボックスだけでもOn～プロパティ、たくさんありますね。個人的にはテキストボックスはOnChangeにしちゃうと1文字入力するたびに走っちゃうので、OnExitにしています。コンボボックスはOnChangeでいいかなと。

そしたら、これを走らせる側のコードをこのように。

Private Function 更新_Click(i As Integer) '「更新」チェックボックスクリック時

End Function

Private Function 商品ID_Change(i As Integer) '「商品ID」コンボボックス変更時

End Function

Private Function 単価_Exit(i As Integer) '「単価」テキストボックス離脱時

End Function

Private Function 数量_Exit(i As Integer) '「数量」テキストボックス離脱時

End Function

試しに、「商品ID」コンボボックス変更時の中身に、何か書いてみましょう。

Private Function 商品ID_Change(i As Integer) '「商品ID」コンボボックス変更時
  MsgBox _
    "コントロール名: " & Me.ActiveControl.Name & vbNewLine & _
    "番号: " & i & vbNewLine & _
    "値: " & Me.ActiveControl.Value
End Function

こう書いておいて、任意のコンボボックスを変更すると、

このように、変更されたコントロールの情報が取得できました。

実用

上記の仕組みを利用して書いたものがこちら。

Private Function 更新_Click(i As Integer) '明細レコード「更新」チェックボックスクリック時
  '「更新」チェックを外したら、そのレコードをすべてクリア
  If Me("更新" & i) = False Then
    Me("商品ID" & i) = Null
    Me("商品名" & i) = Null
    Me("単価" & i) = Null
    Me("数量" & i) = Null
    Me("小計" & i) = Null
  End If
End Function

Private Function 商品ID_Change(i As Integer) '明細レコード「商品ID」コンボボックス変更時
  '「商品名」と「単価」へ、テーブルから読み込んだ値を挿入
  Me("商品名" & i) = DLookup("商品名", "商品マスター", "商品ID='" & Me("商品ID" & i) & "'")
  Me("単価" & i) = DLookup("単価", "商品マスター", "商品ID='" & Me("商品ID" & i) & "'")
End Function

Private Function 単価_Exit(i As Integer) '明細レコード「単価」テキストボックス離脱時
  '「単価」と「数量」に値があったら「小計」を計算
  If IsNull(Me("単価" & i)) = False Or IsNull(Me("数量" & i)) = False Then
    Me("小計" & i) = Me("単価" & i) * Me("数量" & i)
    Me("更新" & i) = True
  End If
End Function

Private Function 数量_Exit(i As Integer) '明細レコード「数量」テキストボックス離脱時
  '「単価」と「数量」に値があったら「小計」を計算
  If IsNull(Me("単価" & i)) = False Or IsNull(Me("数量" & i)) = False Then
    Me("小計" & i) = Me("単価" & i) * Me("数量" & i)
    Me("更新" & i) = True
  End If
End Function

実行してみます。

コンボボックスを変更すると、

同じレコードに、商品名と単価が入ります。

数量と単価が両方入れば、

このように。

チェックが外れると、そのレコードの情報がクリアされるとか。応用すればいろいろできそうですねー。

参考

• Access 2003、（VBA） フォームの上に273個のcomndボタンのコントロール
• VBA – Access VBA クリックイベントをまとめたい(26259)｜teratail

ありがとうございました！