非連結のフォーム
フォーム自体に、テーブルが設定されてないと、レコードソースの部分が空になっており、直接テーブルにアクセスできないことが分かります。
直接テーブルがフォームになっていることで、更新や削除などが容易に行え簡単であるという面がある反面、気付かずに誤入力しても簡単に更新されてしまうという面も存在します。
この回では、テーブルが直接フォームになってない非連結フォームの状態から、テーブルのデータを呼び出して表示したり、更新・削除したりする方法を解説したいと思います。
データの呼び出し
テーブルダイレクトフォームの場合は、最初から全データがフォームに連結されていましたが、非連結フォームの場合は、都度、データを呼び出すスタイルをとります。※同じように全データを呼び出す場合は、一覧表示させるフォームを準備すればOK
「商品名検索」の更新後処理に下記のVBAを書き込みます。
Private Sub 商品名検索_AfterUpdate()
Dim stCD As String
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
rs.CursorLocation = adUseClient
rs.Open "商品マスタ", cn, adOpenKeyset, adLockOptimistic
rs.Filter = "商品名 Like '*" & Me!商品名検索 & "*'"
Set Me.Recordset = rs
If rs.EOF Then
MsgBox ("条件に一致するデータは存在しませんでした。")
With Me
!call_ID = ""
!call_商品名 = ""
!call_分類 = ""
!call_値段 = ""
End With
Else
With Me
!call_ID = rs!ID
!call_商品名 = rs!商品名
!call_分類 = rs!分類
!call_値段 = rs!値段
End With
End If
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
商品名検索 = Nul
Me.Visible = False
Me.Visible = True
Me.商品名検索.SetFocus
End Sub
VBAで呼び出されたデータは、フォームで間違った入力をしたとしても、更新処理をしない限り、テーブルは更新されることが無いという利点がある。
更新処理
テーブルダイレクトフォームのように即座に更新されるわけではありません。
まず、フォームに「更新」させる為のボタンを設置して、クリック後処理として、「コードビルダー」に移行してVBAモードに行きます。
下記のようなプログラムを書き込みます。
Private Sub btn_更新_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL As String
On Error GoTo ErrRtn
If IsNull(call_ID) Then
MsgBox ("データが選択されていません。")
Exit Sub
End If
If MsgBox("更新しますか？ yes/no", vbYesNo, "更新確認") = vbYes Then
SQL = "SELECT * FROM 商品マスタ WHERE ID =" & Me!call_ID & ""
Set cn = CurrentProject.Connection
rs.Open SQL, cn, adOpenKeyset, adLockOptimistic
cn.BeginTrans
While Not rs.EOF
rs!商品名 = call_商品名
rs!分類 = call_分類
rs!値段 = call_値段
rs.Update
rs.MoveNext
Wend
cn.CommitTrans
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
Else
MsgBox ("更新しませんでした。")
Exit Sub
End If
ExitErrRtn:
DoCmd.ShowAllRecords
Exit Sub
ErrRtn:
MsgBox "エラー： " & Err.Description
cn.RollbackTrans
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
End Sub
最初に、データを検索してない時に、更新ボタンを押した場合はの対策と、更新時に、確認をするメッセージを出すように設定しました。
非連結で呼び出されているので、「更新」ボタンをクリックしない限り、データは更新されないという誤入力対策がされています。
テーブルを複数人数で共有しているような場合は、このような非連結フォームを使うので覚えておくといいかもしれません。
追加処理
「追加」する為の、ボタンを設置して、クリック後の処理にて、コードビルダーに移行して、下記のVBAを設置します。
Private Sub btn_追加_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
If IsNull(call_商品名) Then
MsgBox ("商品名が入力されていません。")
Exit Sub
End If
If IsNull(call_分類) Then
MsgBox ("分類が入力されていません。")
Exit Sub
End If
If MsgBox("追加しますか？ yes/no", vbYesNo, "データ追加確認") = vbYes Then
On Error GoTo ErrRtn
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "商品マスタ", cn, adOpenKeyset, adLockOptimistic
' トランザクションの開始
cn.BeginTrans
rs.AddNew
rs!商品名 = call_商品名
rs!分類 = call_分類
rs!値段 = call_値段
rs.Update
MsgBox ("追加しました。")
' トランザクションの保存
cn.CommitTrans
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
Else
MsgBox ("追加しませんでした。")
Exit Sub
End If
ExitErrRtn:
call_ID = Null
call_商品名 = Null
call_分類 = Null
call_値段 = Null
Exit Sub
ErrRtn:
MsgBox "エラー： " & Err.Description
'BeginTransの時点まで戻り、変更をキャンセルする
cn.RollbackTrans
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
End Sub
最初に、未入力だった時には、追加処理にしない対策をしてあります。
その後、追加処理をする為の、確認画面が出るようにしました。
On Error GoTo ErrRtn
は、エラー時の対策の為に挟んでいる物なので、何も考えずに、入れておくとエラー時に、デバッグモードに移行しなくて済みます。
cn.BeginTrans
この部分は、トランザクション処理といって、
エラーが出たときに、作業途中で終了させずに、何もなかったことにデータを戻してくれる機能
データが途中で止まって、どこまでが処理されたかわからないというのでは致命的になってしまいますので、入れておくといいと思います。
ちなみに、テーブルを作成するときに、「ID」はオートナンバーにしたので、追加時には追加処理の中にいれませんでした。
削除処理
削除も、同じように削除ボタンを設置して、クリック時処理として、VBAを設定します。
Private Sub btn_削除_Click()
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
On Error GoTo ErrRtn
If MsgBox("実行しますか？ yes/no", vbYesNo, "削除確認") = vbYes Then
Set CN = CurrentProject.Connection
Set RS = New ADODB.Recordset
CN.BeginTrans
RS.Open "商品マスタ", CN, adOpenStatic, adLockOptimistic
' Debug.Print Me.call_ID
RS.Find "ID = " & call_ID
RS.Delete
CN.CommitTrans
RS.Close: Set RS = Nothing
CN.Close: Set CN = Nothing
Else
MsgBox "削除しませんでした。"
Exit Sub
End If
ExitErrRtn:
DoCmd.ShowAllRecords
Exit Sub
ErrRtn:
MsgBox "エラー： " & Err.Description
CN.RollbackTrans
RS.Close: Set RS = Nothing
CN.Close: Set CN = Nothing
End Sub
VBAのコードをダラダラと書いていると、なんかすごく難しい事をしている様に感じてしまいますが、規則だけ覚えてしまえば、ちょっとだけ修正すれば使いまわしできます。
削除時も、エラー処理とトランザクション処理を入れておきました。
Debug.Print Me.call_ID
この部分はコメントアウトされていますが、call_IDには、この時点では何がはいっているのかな？っていう時に、デバックしていて、Debug.Printは役に立ちます。エラーが出たときなどは、本当に便利です。