ACCESSで主キーを事前に重複登録を防止
目次
主キーの重複登録を事前に防止
こんにちは。伊川(@naonaoke)です。
今回は、主キーの登録時について、エラーがでないように回避する方法を紹介します。
すでに紹介したように、主キーは、
・重複登録ができない。
・空白の入力をゆるさない。
この原則があります。
しかし、主キーはレコードを登録するまで、主キーが重複されているのかわからないのです。
主キーについては、下記を参照してください。
問題提起
Aさんは、ACCESSの入力フォームでデータを入力しています。
入力項目は、100項目あり、データを入力完了後、
上記のようなエラーが表示されることがあります。
Aさんは、主キーが重複しているということは、理解していますが、もう一度データ入力することが面倒です。
主キーを入力した時点で、重複判定する方法はないだろうか？
ACCESSは、様々なアクションの後に、VBAのコードを記入できる
これまでみなさんに紹介してきたのは、更新後処理になります。
更新後処理というのは、テキストボックスAに値が入力されたら、テキストボックスBで判定をする。
このような処理を更新後処理といいます。
これは、3連動のコンボボックス作成のときに紹介しています。
今回は、値が入力された時点では、すでに遅いわけです。
つまり、更新前処理というところに、VBAのコードを仕込む必要があります。
このような処理は、Excel、ACCESSでもよく使いますので、この機会にぜひ、みなさんのスキルにしてください。
問題提起2
レコード入力完了後、上司にしかられることが度々あります。
どうにかならないだろうか？
みなさんの、周りにもいらっしゃるのではないでしょうか？
何度言っても主キーの桁を間違えて登録してくる人はいませんか？
大目に見たいのですが、さすがに、10回以上繰り返すと、怒りたくもなります。
そんな、ストレスも今回のブログで解消できます。
主キーは4桁であり、4桁しか登録させない。
桁数入力制限作成手順
使用する関数は、LEN関数とIF構文です。
上記のように、1桁で登録しようとすると、アラートが表示されます。
上の図のように、F_商品というフォームをデザインビューで開きます。
商品コードのテキストボックスを選択して、向かって左側のプロパティーシートを表示させます。
イベントというタブの中に、【更新前処理】という項目選択します。
Private Sub 商品コード_BeforeUpdate(Cancel As Integer) If Len(Me!商品コード) > 0 And Len(Me!商品コード) <> 4 Then MsgBox "商品コードは4桁だ。手打するな！！ " Cancel = True Exit Sub End If End Sub
桁数入力制限のコード解説
If Len(Me!商品コード) > 0 And Len(Me!商品コード) <> 4 Then
‘商品コードが0以上で、かつ4以外の時
MsgBox “商品コードは4桁だ。手打するな！！ ”
‘メッセージボックスに”商品コードは4桁だ。手打するな！！ “と表示
Cancel = True
‘処理をキャンセルして
Exit Sub
‘条件から抜ける
End If
Cancel = True
Exit Sub
この処理も良く使います。
条件から抜けるという処理は、この機会に、是非憶えてください。
主キー重複登録防止作成手順
使う関数は、IF構文と、Withステートメント、Dcount関数です。
上の図のように、F_商品というフォームをデザインビューで開きます。
商品コードのテキストボックスを選択して、向かって左側のプロパティーシートを表示させます。
イベントというタブの中に、【更新前処理】という項目選択します。
ここまでは、同じです。
Private Sub 商品コード_BeforeUpdate(Cancel As Integer)
With Me!商品コード
If IsNull(.OldValue) Or (.Value <> .OldValue) Then
If DCount("商品コード", "MT_商品", "商品コード=" & .Value) > 0 Then
MsgBox "入力された商品コードはすでに使われています！" & _
vbCrLf & _
"他の値を再入力してください。", vbOKOnly + vbExclamation
Cancel = True
End If
End If
End With
End Sub
主キー重複登録防止のコード解説
フォーム上の連結コントロールの値が変更されたかどうかを知るのは 【oldvalue】 プロパティを使用します。
With Me!商品コード
If IsNull(.OldValue) Or (.Value <> .OldValue) Then
‘もし商品コードが、Null（空白） または、データの商品コードが合致していないときは登録可能 ここまでが第一条件
If DCount(“商品コード”, “MT_商品”, “商品コード=” & .Value) > 0 Then
‘だけど、ＭＴ_商品から、今入力された商品コードをカウントして、
その商品コードが0以上で、oldvalueと合致しているとき これが、第二条件
MsgBox “入力された商品コードはすでに使われています！” & _
vbCrLf & _
“他の値を再入力してください。”, vbOKOnly + vbExclamation
Cancel = True
第二条件のときは、この条件式を抜ける
End If
End If
End With
完成図
このように、事前に重複登録を防止できます。
今回のコードをまとめる
Private Sub 商品コード_BeforeUpdate(Cancel As Integer)
If Len(Me!商品コード) > 0 And Len(Me!商品コード) <> 4 Then
MsgBox "商品コードは4桁だ。手打するな！！ "
Cancel = True
Exit Sub
End If
Private Sub 商品コード_BeforeUpdate(Cancel As Integer)
With Me!商品コード
If IsNull(.OldValue) Or (.Value <> .OldValue) Then
If DCount("商品コード", "MT_商品", "商品コード=" & .Value) > 0 Then
MsgBox "入力された商品コードはすでに使われています！" & _
vbCrLf & _
"他の値を再入力してください。", vbOKOnly + vbExclamation
Cancel = True
End If
End If
End With
End Sub
まとめ
ACCESSにおいては、主キーは、大切な役割をはたしています。
桁が違うなどは、論外です。
また、ACCESSは、Excelより、データ管理においては、便利だということを、常に、みなさんの部署に人達に、思わせなければなりません。
システムを作成するのであれば、今回のように、気を使ってあげましょう。
そうすれば、あなたの評判も、ACCESSと同じように上昇すると思います。
