2019.08.15

Access SQL VBA 練習問題

【Access VBA】実践演習 #3 (登録機能の実装)

このシリーズでは書籍管理システムの作成を通して、Access VBAの使い方を学ぶことを目標とします。
データベース操作はDAOを使って実装します。
Accessの基本的な操作は知っているけれど、VBAはあまり書いたことがない方を対象としています。

途中SQLも使いますので、不安な方はこちらの記事等で確認してください。
⇒ AccessでSQLの練習

必要なファイルのダウンロードやテーブル等の確認は初回の記事をご覧ください。
⇒ 【Access VBA】実践演習 #1

今回は「F_書籍登録」の各機能を実装していきたいと思います。

■練習3-1 (登録ボタン-入力チェック)

登録ボタンの処理のうち、登録の前段階の処理を実装します。
具体的には、
1. 「タイトル」「カテゴリ」「著者」が全て入力済みであること。
2. 登録処理の直前に確認ダイアログを表示する。
3. 「OK」が押されたとき登録完了メッセージを表示し、「F_書籍検索」を開く。(閉じて開く)

・未入力時

・確認ダイアログ

解答例・解説

解答例は以下になります。
[vb] Private Sub 登録ボタン_Click()

‘ 未入力チェック
If IsNull(Me.タイトル) = True Then
MsgBox "タイトルが入力されていません！", vbCritical, "未入力エラー"
Me.タイトル.SetFocus
Exit Sub
End If

If IsNull(Me.カテゴリ) = True Then
MsgBox "カテゴリが入力されていません！", vbCritical, "未入力エラー"
Me.カテゴリ.SetFocus
Exit Sub
End If

If IsNull(Me.著者) = True Then
MsgBox "著者が入力されていません！", vbCritical, "未入力エラー"
Me.著者.SetFocus
Exit Sub
End If

Dim title As String
Dim category_name As String
Dim author As String
Dim msg As String

title = Me.タイトル
category_name = Me.カテゴリ.Column(1)
author = Me.著者

msg = "■書籍登録を行います。" & vbCrLf & _
"タイトル：" & title & vbCrLf & _
"カテゴリ：" & category_name & vbCrLf & _
"著者：" & author

If MsgBox(msg, vbOKCancel) = vbOK Then
MsgBox "登録が完了しました"
DoCmd.Close acForm, "F_書籍検索", acSaveNo
DoCmd.OpenForm "F_書籍検索"
End If

End Sub
[/vb] 未入力チェックは前回と同様に IsNull関数を使って判定しています。
Trueの時にエラーメッセージを表示し、「Exit Sub」でSubプロシージャを中断しています。

未入力チェックを抜けたら、確認ダイアログを表示するためにフォームの値を取得しています。
カテゴリ名(category_name)ですが、「Me.カテゴリ」だとCATEGORY_IDを取得してしまいます。
元となるコンボボックスの値集合ソースを見ると
[sql] SELECT [カテゴリ一覧].[CATEGORY_ID], [カテゴリ一覧].[カテゴリ名] FROM カテゴリ一覧;
[/sql] となっているため、各カラムの値をVBAで取得するには以下の様にします。
・CATEGORY_ID ⇒「Me.カテゴリ」や「Me.カテゴリ.Column(0)」で指定
・カテゴリ名 ⇒「Me.カテゴリ.Column(1)」で指定

■練習3-2 (登録ボタン-登録処理)

練習3-1で登録確認ダイアログが表示され、OKをクリックしたときに
「蔵書一覧」テーブルに新規レコードが登録される処理を実装してください。
また、登録が完了したら登録フォームの「タイトル」「著者」を空(Null)に、
「カテゴリ」が「小説」となるようにしてください。

解答例・解説
今回も2パターン紹介します。

①レコードセットを使う

まずはデータベース接続と、レコードセットを取得する処理です。
今回は「蔵書一覧」テーブルを操作したいので、OpenRecordsetの引数は「蔵書一覧」です。
[vb] ‘ 変数の宣言
Dim db As DAO.Database
Dim rs As DAO.Recordset
‘ データベース接続
Set db = CurrentDb()
‘ レコードセット取得
Set rs = db.OpenRecordset("蔵書一覧", dbOpenDynaset)
[/vb]

テーブルオブジェクトを取得できたら、以下の手順でレコード追加の処理を実装します。
1. 新規レコードを追加(AddNew)
2. 各カラムに値を設定
3. レコードセット(テーブル)を更新
[vb] ‘ 新規レコードを追加
rs.AddNew

‘ 追加したレコードの各フィールドに値を設定
rs.Fields("タイトル") = Me.タイトル
rs.Fields("CATEGORY_ID") = Me.カテゴリ.Column(0)
rs.Fields("著者") = Me.著者

‘ レコードを更新
rs.Update

‘ レコードセットとデータベースを閉じる
rs.Close
db.Close
[/vb]

最後に、登録フォームの値を初期化する処理が以下です。
[vb] Me!タイトル = Null
Me!カテゴリ = "小説"
Me!著者 = Null
[/vb]

これらと練習3-1を組み合わせた完成プログラムは以下になります。
[vb] Private Sub 登録ボタン_Click()

‘ 未入力チェック
If IsNull(Me.タイトル) = True Then
MsgBox "タイトルが入力されていません！", vbCritical, "未入力エラー"
Me.タイトル.SetFocus
Exit Sub
End If

If IsNull(Me.カテゴリ) = True Then
MsgBox "カテゴリが入力されていません！", vbCritical, "未入力エラー"
Me.カテゴリ.SetFocus
Exit Sub
End If

If IsNull(Me.著者) = True Then
MsgBox "著者が入力されていません！", vbCritical, "未入力エラー"
Me.著者.SetFocus
Exit Sub
End If

Dim title As String
Dim category_name As String
Dim author As String
Dim msg As String

title = Me.タイトル
category_name = Me.カテゴリ.Column(1)
author = Me.著者

msg = "■書籍登録を行います。" & vbCrLf & _
"タイトル：" & title & vbCrLf & _
"カテゴリ：" & category_name & vbCrLf & _
"著者：" & author

If MsgBox(msg, vbOKCancel) = vbOK Then
‘ テーブルにレコードを追加する
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()

Set rs = db.OpenRecordset("蔵書一覧", dbOpenDynaset)

‘ 新規レコードを追加
rs.AddNew

‘ 追加したレコードの各フィールドに値を設定
rs.Fields("タイトル") = Me.タイトル
rs.Fields("CATEGORY_ID") = Me.カテゴリ.Column(0)
rs.Fields("著者") = Me.著者

‘ レコードを更新
rs.Update

‘ レコードセットとデータベースを閉じる
rs.Close
db.Close

Me!タイトル = Null
Me!カテゴリ = Me!カテゴリ.ItemData(0)
Me!著者 = Null

MsgBox "登録が完了しました"
DoCmd.Close acForm, "F_書籍検索"
DoCmd.OpenForm "F_書籍検索"
End If

End Sub
[/vb]

②SQLを使う

解答例は以下になります。
[vb] Private Sub 登録ボタン_Click()

‘ 未入力チェック
If IsNull(Me.タイトル) = True Then
MsgBox "タイトルが入力されていません！", vbCritical, "未入力エラー"
Me.タイトル.SetFocus
Exit Sub
End If

If IsNull(Me.カテゴリ) = True Then
MsgBox "カテゴリが入力されていません！", vbCritical, "未入力エラー"
Me.カテゴリ.SetFocus
Exit Sub
End If

If IsNull(Me.著者) = True Then
MsgBox "著者が入力されていません！", vbCritical, "未入力エラー"
Me.著者.SetFocus
Exit Sub
End If

Dim title As String
Dim category_name As String
Dim author As String
Dim msg As String

title = Me.タイトル
category_name = Me.カテゴリ.Column(1)
author = Me.著者

msg = "■書籍登録を行います。" & vbCrLf & _
"タイトル：" & title & vbCrLf & _
"カテゴリ：" & category_name & vbCrLf & _
"著者：" & author

If MsgBox(msg, vbOKCancel) = vbOK Then
Dim db As DAO.Database
Dim sql As String

sql = "INSERT INTO 蔵書一覧 (タイトル,CATEGORY_ID,著者) VALUES (‘" & Me.タイトル & "’," & Me.カテゴリ.Column(0) & ",’" & Me.著者 & "’)"

Set db = CurrentDb()

db.Execute sql
db.Close

Me!タイトル = Null
Me!カテゴリ = Me!カテゴリ.ItemData(0)
Me!著者 = Null

MsgBox "登録が完了しました"

DoCmd.Close acForm, "F_書籍検索", acSaveNo
DoCmd.OpenForm "F_書籍検索"
End If

End Sub
[/vb]

解答例①と違うのは登録処理のところだけで、以下の流れで登録しています。
1. SQLを用意(INSERT文)
2. db.ExecuteでSQLを実行

このようにINSERTやUPDATE、DELETEの処理の場合はSQLを用意してしまえば、
Database.Executeメソッドで簡単にSQLを実行することができます。

なお、やはり文字列結合によってSQL文が複雑に見えますが、作りたいのは以下のようなINSERT文です。
[sql] INSERT INTO 蔵書一覧 (タイトル,CATEGORY_ID,著者) VALUES (‘2019年一番売れる本’,2,’ほげほげ’)
[/sql] (上手くいかない場合はブレークポイントを置いてローカルウィンドウなどで確認しましょう。)