【Access】VBA内でSQLで抽出条件をフォームの値で行う注意点

Accessは癖がありすぎます。ちょっとしたことでVBA内でエラーが起こり、その為に数時間を要することになります。今回も

したので備忘録にしてみました。

うまく行ったVBAコード

Private Sub arrival_del_btn_Click()
Dim cn As New ADODB.Connection
Dim cmd As ADODB.Command
Dim SQL As String
 
If MsgBox("本当に削除しますか？ yes/no", vbYesNo, "データ削除確認") = vbYes Then
  
                Set cn = CurrentProject.Connection
                Set cmd = New ADODB.Command
                cmd.ActiveConnection = cn
                  
On Error GoTo ErrorHandler
                  
            cn.BeginTrans
     
                SQL = "UPDATE dbo_stock "                         'フォームの値が文字型"
                SQL = SQL & "SET dbo_stock.schedule_arrival = [dbo_stock]![schedule_arrival] - '" & Forms![arrival_product_run]![schedule_arrival] & "' "
                SQL = SQL & "WHERE dbo_stock.jan_sku_code = '" & Forms![arrival_product_run]![jan_sku_code] & "'"
                cmd.CommandText = SQL
                cmd.Execute
                
                SQL = "DELETE * FROM dbo_arrival "                'フォームの値が数値型"
                SQL = SQL & "WHERE dbo_arrival.no = " & Forms![arrival_product_run]![no]
                cmd.CommandText = SQL
                cmd.Execute

                 
            cn.CommitTrans
                  
                Set cmd = Nothing
                cn.Close: Set cn = Nothing
  
  
        Else
                MsgBox "処理せずに終了しました。"
                Exit Sub
        End If
  
   
ExitErrorHandler:
                DoCmd.ShowAllRecords
                Exit Sub
          
          
ErrorHandler:
                cn.RollbackTrans
                Set cmd = Nothing
                cn.Close: Set cn = Nothing
                    Debug.Print (Err.Description) ' 型が一致しません。
                    Debug.Print (Err.number)     ' 13
                MsgBox "エラーが発生しました。処理せずに戻ります。"
                End

End Sub

上記のVBAコードが最終的にはエラーが回避されたコードになりますが、ここにたどり着くまで苦労しました。

エラーを知るためのコード

Debug.Print (Err.Description) ' 型が一致しません。
Debug.Print (Err.number)     ' 13

最初は、上記のコードを入れていなかったので、ただ単に「エラーが発生しました。処理せずに戻ります。」というボックスメッセージしか表示されずに何がエラーなのか悩みました。

過去の例から言っても、カンマやダブルコーテーションなどの記述が間違っているとは踏んでいました。

上記のコードをエラー行に入れておくだけで、何のエラーなのかが分かって助かりました。

文字型と数値型の違いでの記述方法

フォームから値を引っ張ってくるときのSQL内での記述が、文字型と数値型では違うのです。

また、Dlookupなどでも記述の違いで悩んだので備忘録に残してありますが、その記述とも、ストアドの記述ともちょっと違うのです。

https://pctips.jp/pc-soft/access-dlookup-howto2019/

具体的な記述の違いなのですが、文字型の場合は、

ところが、数値型の場合は、

となるのです。

紛らわしい最終行のダブルコーテーション

                SQL = "DELETE * FROM dbo_arrival "
                SQL = SQL & "WHERE dbo_arrival.no = " & Forms![arrival_product_run]![no]

数値型の場合は、最後のダブルコーテーションは必要ないのです。

私は、

というように思い込んでしまっていました。

なので、数値型も最後にダブルコーテーションを入れてしまいエラーになってしまったという結論です。

備忘録として書いておけばスグに思い出すので以後は、これで悩む事も減るかと思います。

結構、フォームの値を使って処理させたいという場合が多いので、この決まりを覚えておくといいですね。