Access 追加時に重複する商品がある場合そのレコードに色を付けたい。

Question

##質問
Accessで伝票管理システムを作成しています。
メインで伝票を入力するフォームがあるのですが、そこで重複した商品が入力された状態で伝票登録ボタンを使用したときに
「商品が重複しています。」といったメッセージを表示し、登録が取り消されるようにしたいです。
また、どこが重複しているのかがわかるようにそのレコードに色をつけたいです。
※重複した商品を入力すること自体は、OKな仕様ですのでそのあたりは触れないでいただけると幸いです。

##概要
重複することがある状況は、手入力で入力したときのみです。
商品を入力するのは伝票入力フォームのサブフォームです。
行っている方法は、サブフォームのレコードに重複フラグを設け、登録するときにSQL文で重複しているレコードにフラグを立てるようにし、最終的に条件付き式で色をつけるようにしたいと考えています。

##現状
今は下記のようにしているのですが、これだと登録を押したときに重複していないものまでフラグが立ってしまいます。（というより全て立っていると思います。）

重複しているレコードだけ、フラグを立たせたいです。

↓伝票登録ボタン使用時の処理（追加処理など他処理は省略）

'重複チェック
SQL = "UPDATE T_WSlipDetail SET F_ZF = True WHERE EXISTS (SELECT Count(F_ProductCode) FROM Q_WSlipDetail GROUP BY F_ProductCode HAVING T_WSlipDetail.F_ProductCode = Q_WSlipDetail.F_ProductCode and Count(F_ProductCode) >= 2)"
        
        With Application.CurrentDb
            .Execute SQL
        End With
        
            
    Dim dd As String            
    dd = Nz(DLookup("F_ZF", "T_WSlipDetail", "F_ZF = True"), 1)            
    If dd <> 1 Then                   
        MsgBox "重複している商品があります。", vbOKOnly + vbExclamation, "注意"        
        Exit Sub            
    Else            
    End If

サブフォームに参照しているクエリ
Q_WSlipDetail

フィールド名	テーブル名	備考
F_SlipCode	T_WSlipDetail	伝票番号
F_LineNum	T_WSlipDetail	行番号
F_ProductCode	T_WSlipDetail	商品番号
F_ProductName	T_Product	商品名
F_Cost	T_WSlipDetail	単価
F_Qty	T_WSlipDetail	数量
F_ZF	T_WSlipDetail	重複フラグ

↓入力用
T_WSlipDetailテーブル

フィールド名	型	備考
F_SlipCode	テキスト型	伝票番号
F_LineNum	数値型	行番号
F_ProductCode	テキスト型	商品番号
F_Cost	通貨型	単価
F_Qty	数値型	数量
F_ZF	Yes/No	重複フラグ

助言をくだされば幸いです。

Answer 1

伝票内で同じ商品という条件なら

UPDATE  T_WSlipDetail as target SET F_ZF = True
WHERE   (
          select count(*) from T_WSlipDetail 
          where F_SlipCode=target.F_SlipCode and F_ProductCode=target.F_ProductCode
        ) > 1 

※フラグはいつoffにするんでしょう？

追記

件数が多いなら、こっちの方が高速かもしれません。
※フラグの設定に変更

UPDATE  T_WSlipDetail left join (
          select F_SlipCode, F_ProductCode
          from T_WSlipDetail
          group by F_SlipCode, F_ProductCode
          having count(*)>1
        ) as condition
        on   T_WSlipDetail.F_SlipCode=condition.F_SlipCode 
         and T_WSlipDetail.F_ProductCode=condition.F_ProductCode
SET T_WSlipDetail.F_ZF = IIf(condition.F_SlipCode is null, False, True)

※left joinでエラーになるようなら

UPDATE  T_WSlipDetail 
SET F_ZF = IIf(DCount("*", "T_WSlipDetail", "F_SlipCode='" & F_SlipCode & "' and F_ProductCode='" &  F_ProductCode & "'")>1, True, False)

UPDATE T_WSlipDetail As Target
SET F_ZF = IIf((select Count(*) from T_WSlipDetail where F_SlipCode=Target.F_SlipCode and F_ProductCode=Target.F_ProductCode)>1, True, False)

追記２

手っ取り早くfalseにするSQL※1番目の逆

UPDATE  T_WSlipDetail as target SET F_ZF = False
WHERE   (
          select count(*) from T_WSlipDetail 
          where F_SlipCode=target.F_SlipCode and F_ProductCode=target.F_ProductCode
        ) = 1 

Comments

[退会済みユーザー]

回答ありがとうございます。
フラグは、登録ボタンをしたときの一番最初の処理でFalseにしています。
target　とはなんでしょうか？？

[sazi]

targetはエイリアスです。テーブル名が同じなので相関問い合わせで識別するための別名です。
別に識別できればt1とか何でもいいですよ

[退会済みユーザー]

望んだ通りの動作になりました。
ありがとうございました。

[退会済みユーザー]

先程のフラグを外す件についてやや誤りがありました。
重複チェックを行うのは登録ボタンを使用するときなので、重複チェックする前に一度、フラグを外すというのが先程のことで、この他にも色がついたところを変更したときもにも変更時イベントで重複チェックを行い重複していなかったらフラグを外すように考えています。

[sazi]

２番目のSQLを全体に対してフラグのon/offを設定する内容に変更しました。

[退会済みユーザー]

提示してくださったコードは全て、動きは同様のものですか？？

[sazi]

２番目と３番目は同じですね。
ただ、３番目はD系関数を使用しているので２番目より遅いものです。

[sazi]

３番目をコードに埋め込む際には、"は"を重ねて("")下さいね。

[sazi]

３番目をサブクエリーにした４番目を追加しました

[退会済みユーザー]

件数がそこまで多いわけではないので、一番目のもので十分動作できました。
ありがとうございました。
フラグをFalseにするタイミングについても質問しようと思うのですが、新しく開設したほうがいいでしょうか？

[sazi]

タイミングは別質問が良いかと思います。

[退会済みユーザー]

SQL文の中でIIF関数は使えるのですか？

[sazi]

使えますよ。というか、VBAよりSQLでの使用を想定したものです。

[退会済みユーザー]

三番目と四番目の違いはなんでしょうか？？

[退会済みユーザー]

商品番号テキストボックスのダーティー時に四番目のSQLを追加してみましたが、
「更新可能なクエリであることが必要です。」というエラーが発生しました。

[sazi]

３番目と４番目の違いはサブクエリーを使うかどうかです。
３番目のD系関数よりは文字列としての埋め込みが楽なので作ってみましたが、更新可能でないなら利用できませんので、３番目を試してください。

[退会済みユーザー]

今、三番目のものを実行しているのですが、三番目の文を全てSQL文として実行させてるのですがもしかして違っていたりしますか？？

[sazi]

SQL文というのは文字列として編集しているという意味ですか？
そうなら問題はないです。
ただ、文字列として扱うには"をエスケープ（"を重ねる）してあげないといけない点が注意点です。
別途クエリーとして定義する方が簡単ではありますが。

[退会済みユーザー]

下記のように実行してみたのですが、ダーティー時にFalseうまくなってくれなかったです。
どこか、問題がありましたらご指摘お願いします。

SQL = "Update T_WSlipDetail Set F_ZF = IIf(DCount(""*"", ""T_WSlipDetail"", ""F_SlipCode='"" & F_SlipCode & ""' and F_ProductCode='"" & F_ProductCode & ""'"") > 1, True, False)"

With Application.CurrentDb
.Execute SQL
End With

[sazi]

上手くいかないというのがどのような事なのか分かりません。
その生成した文字列をクエリービューに張り付けて動作させて結果を見るなどの確認を行って、どのようにな点がうまくいっていないのか明確にして下さい。
F_ProductCodeのデータにNullがあったりしていないですか？

[sazi]

追記しました。
１番目と追記分をセットで実行するようにすれば良いと思います。
３番目とか４番目はこれを一つに纏めたものですので。
※F_ProductCodeにNullがあるような場合はまた別な問題