Microsoft Access 掲示板

Microsoft Access 掲示板

フォームの入力／未入力に対応する更新ＳＱＬ

7 コメント
108 views
4 フォロー

毎回大変お世話になっています。今回はUPDATEについてです。
テーブル（T_製造履歴）にはフィールド[履歴ID] [製品名] [顧客ID] [firmID] [製造担当者ID] [check](yes/no型)　等があります。このテーブルを基に作成したクエリをソースにしたリスト型フォームがあり、それに[check]=yes した分に対して内容を変更したいのです。変更内容はそのフォームに非連結コントロールを作成してます。変更対象のフィールドは5ヶありまして、変更する項目だけ入力する形です。なので変更項目が入力されたフィールドだけテーブル更新となります（未入力項目はテーブル値のまま）。変更対象の5項目が全て入力済みなら下記コードで更新出来ているのですが未入力がある場合どうすれば一纏めのコードにしたらいいのか分かりません。各フィールドに対して個々の更新コードでは出来ると思うのですが、入力／未入力の組合せが多くなってしまうので。いいアイデアがあれば宜しくお願いします。

Dim txDt  As Variant, koID As Variant, fiID As Variant, taID As Variant, txBiko As Variant
'change○○が一覧ﾌｫｰﾑの非連結ｺﾝﾄﾛｰﾙです
txDt = Me.[change_Date].Value
koID = Me.[change_Kokyaku].Value
fiID = Me.[change_Firm].Value
taID = Me.[change_Tanto].Value
txBiko = Me.[change_Biko].Value

Dim strSQL As String
strSQL = "UPDATE [T_製造履歴] " & _
         "SET [完成日付]=#" & Format(txDt, "yyyy/mm/dd") & "#, " & _
         "[顧客ID]=" & koID & ", " & _
         "[firmID]=" & fiID & ", " & _
         "[製造担当者ID]=" & taID & ", " & _
         "[備考]='" & txBiko & "' " & _
         "WHERE [check]=-1 ;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
beginner
作成: 2025/04/09 (水) 15:15:51
通報 ...
1
hiroton 2025/04/09 (水) 16:18:37 96ec1@f966d

プログラミングにおける変数への値の代入処理は右辺に自分自身（変数）を指定して、自身の内容を書き換える処理が記述できます

x = 1
x = x + 1
Debug.Print x '=>2

ここで記述している「=」は変数への代入命令の記述であるということに注意してください

入力状態をチェックしながらstrSQLを生成していけばいいですね

If IsNull(Me!change_Date) And IsNull(Me!change_Kokyaku) And IsNull(Me!change_Firm) And IsNull(Me!change_Tanto) And IsNull(Me!change_Biko) Then Exit Sub

Dim strSQL As String
strSQL = "UPDATE [T_製造履歴] SET "
If Not IsNull(Me!change_Date) Then strSQL = strSQL & "[完成日付]=#" & Format(Me!change_Date, "yyyy/mm/dd") & "#, " 
If Not IsNull(Me!change_Kokyaku) Then strSQL = strSQL & "[顧客ID]=" & Me!change_Kokyaku & ", "
If Not IsNull(Me!change_Firm) Then strSQL = strSQL & "[firmID]=" & Me!change_Firm & ", "
If Not IsNull(Me!change_Tanto) Then strSQL = strSQL & "[製造担当者ID]=" & Me!change_Tanto & ", "
If Not IsNull(Me!change_Biko) Then strSQL = strSQL & "[備考]='" & Me!change_Biko & "' "
strSQL = strSQL & "WHERE [check]=-1 ;"

CurrentDb.Execute strSQL

もしくは、「元の値と同じ値で書き換える」としても更新分だけを更新することになります

If Me!change_Date & Me!change_Kokyaku & Me!change_Firm & Me!change_Tanto & Me!change_Biko & "" = "" Then Exit Sub

Dim strSQL As String
strSQL = "UPDATE [T_製造履歴] " & _
         "SET [完成日付]=#" & Format(Nz(Me!change_Date, Me!完成日付), "yyyy/mm/dd") & "#, " & _
         "[顧客ID]=" & Nz(Me!change_Kokyaku, Me!顧客ID) & ", " & _
         "[firmID]=" & Nz(Me!change_Firm, Me!firmID) & ", " & _
         "[製造担当者ID]=" & Nz(Me!change_Tanto, Me!製造担当者ID) & ", " & _
         "[備考]='" & Nz(Me!change_Biko, Me!備考) & "' " & _
         "WHERE [check]=-1 ;"

CurrentDb.Execute strSQL

Nz関数を使って、「更新後の値がなければ元の値を使う」ように指示しています
3
hatena 2025/04/09 (水) 23:03:51 修正 >> 1

前者のコードだと、Me!change_Biko がNullのとき、WHERE の直前に , が来ることになりSQLエラーになりますね。
例えば、直前に, を付けておいて最後に先頭の, を削除してSQL文を完成させるようにすればいいでしょう。

Dim strSQL As String
If Not IsNull(Me!change_Date) Then strSQL = ", [完成日付]=" &  Format(Me!change_Date, "\#yyyy/mm/dd\#") 
If IsDate(Me!change_Kokyaku) Then strSQL = strSQL & ", [顧客ID]=" & Me!change_Kokyaku
If Not IsNull(Me!change_Firm) Then strSQL = strSQL & ", [firmID]=" & Me!change_Firm
If Not IsNull(Me!change_Tanto) Then strSQL = strSQL & ", [製造担当者ID]=" & Me!change_Tanto
If Not IsNull(Me!change_Biko) Then strSQL = strSQL & ", [備考]='" & Me!change_Biko & "'"

If strSQL = "" Then Exit Sub

strSQL = "UPDATE [T_製造履歴] SET " & Mid(strSQL, 3) & " WHERE [check]=True;"
CurrentDb.Execute strSQL

後者のコードはカレントレコードの値で上書きしてしまいますね。
「元の値と同じ値で書き換える」なら下記のような感じになるかと。

Dim txDt  As String, koID As String, fiID As String, taID As String, txBiko As String
txDt = IIf(IsNull(Me!change_Date), "[完成日付]", Format(Me!change_Date, "\#yyyy/mm/dd\#"))
koID = Nz(Me.change_Kokyaku, "[顧客ID]") 
fiID = Nz(Me.change_Firm, "[firmID]") 
taID = Nz(Me.change_Tanto, "[製造担当者ID]")
txBiko = IIf(IsNull(Me.change_Biko), "[備考]", "'" & Me.change_Biko & "'")

Dim strSQL As String
strSQL = "UPDATE [T_製造履歴] " & _
         "SET [完成日付]=" & txDt & ", " & _
         "[顧客ID]=" & koID & ", " & _
         "[firmID]=" & fiID & ", " & _
         "[製造担当者ID]=" & taID & ", " & _
         "[備考]=" & txBiko & " " & _
         "WHERE [check]=True;"

CurrentDb.Execute strSQL
4
hiroton 2025/04/10 (木) 09:26:05 b4b71@f966d >> 3

あー、そうですね。訂正ありがとうございます
フォームに見えてるデータならDAOでやるかなぁとか考えていたらろくにテストもせず投稿してしまいました

2
りんご 2025/04/09 (水) 19:34:15 935bc@0e907

履歴って更新するものなの？

5
beginner 2025/04/10 (木) 09:44:03 61dd6@e1025

hirotonさん hatenaさん　ありがとうございます。
なるほどです！ 複数ＦｉｌｔｅｒのＷＨＥＲＥ組立の様にしたらいいのですね（,の件も確かにそうですね）。
コマンドボタンにコード記述したら上手くいきました。
あと一つだけアイデア頂きたいのですがchange_Bikoに誤って""（長さ0の文字列）だけが入力されて、それが更新に反映されるのを防ぎたいのですが、それはどうすればいいでしょうか？

★この履歴の更新ですが登録ミスの変更や仕様変更等が発生した場合の変更をまとめてする為のものです（最終的な正しい履歴にする為の）。

宜しくお願いします。

6
hiroton 2025/04/10 (木) 10:48:08 b4b71@f966d

通常、非連結のテキストボックスに""（長さ0の文字列）が入ることはありません
フォーム上での人の操作で「何か入力」→「Delete等で削除」を行うと、非連結のテキストボックスの内容はNULLになります。（レコードソースに連結されたコントロールの場合、設定によっては""が入る場合があります）

VBAによる操作等で、直接 Me!テキストボックス = "" のような処理を行うと、非連結のテキストボックスでもその値が""になったりはします

そもそも気にしていなかったのですが、逆に、「備考を削除したい」という場合はないのでしょうか？
「NULLなので更新対象外」と、「内容削除のために""が入力されている」は実作業の上では判別できないので、「備考を削除する」チェックボックスを追加で用意するとかになると思います

また、とりあえず、回答ではIsNull()を使っていますが、NULLも""同じように扱われればよいなら Me!テキストボックス & "" = "" で判定できます

Select Case True
    Case Me!Delete_Biko
        strSQL = strSQL & ", [備考]=''"
        '// データ構造によっては
        '// strSQL = strSQL & ", [備考]=NULL"
    Case Me!change_Biko & "" <> ""
        strSQL = strSQL & ", [備考]='" & Me!change_Biko & "'"
End Select

そのほかのコントロールについても、データ型が文字列型以外になるのならばIsDate()やIsNumeric()等で判定してあげるとより良いと思います

Not IsNull(Null) ' =>False
Not IsNull("")   ' =>True
IsDate(Null)     ' =>False
IsDate("")       ' =>False
7
beginner 2025/04/10 (木) 11:35:41 61dd6@e1025

hirotonさん　ありがとうございます。大変勉強になります。
""では上手く検出出来ませんでしたが、よく考えると全角スペースには有効ではありませんでした。この非連結テキストボックスには入力し易い様にプロパティに ひらがな　設定してます。なので全角状態になっているからでした。なのでフォーカス喪失後イベントに下記を記述しました。他の変更用非連結部は全てコンボボックスからの選択ですので大丈夫です。
確かに「備考を削除する」の想定もありますね。様子みて作成していきます。
Nullと""、" " の違いは難しいですね。本当にありがとうございました。

     If Me.[change_Biko] = "" Or Me.[change_Biko] = " " Then
        Me.[change_Biko] = Null
     End If
    ```