How can I use two fields to check for duplicates?

[Benkong2]

I have a database that checks for duplicate entries with this code.
Problem is this works but in some cases I could have the same unit
number but it may have had two different types of repairs. My question
is could I use the unit ID field and the repair_date field to check for
duplicates and fire the msgbox?

Here's what currently works but only checks the unit numbers so I have
duplication.

Private Sub Unit_Number_BeforeUpdate(Cancel As Integer)
Dim strwhere As String
Dim varID As Variant
Dim strMsg As String

'Build Where Clause.
strwhere = "([Unit_Number] = """ & Me.Unit_Number & """) and ([Over2KID]
<> " & Nz(Me.Over2KID, 0) & ")"

'Lookup the table for another record like this already.
varID = DLookup("over2kid", "tblmain", strwhere)

If Not IsNull(varID) Then
'Found one: ask user whether to continue.
strMsg = Me.Unit_Number & " " & "is already in the database." & vbCrLf
& " Do you want to continue anyway?"

If MsgBox(strMsg, vbYesNo + vbDefaultButton2, "Possible Duplicate") <>
vbYes Then
Cancel = True
Me.Form.Undo
End If
End If
End Sub

What I really need is a label or text bok that shows the last unit
number entered so that my admin will not forget which one she last did.
You know answered the phone and forgot which one he was doing.

Thanks for any help. tips and code

 

[Allen Browne]

Would it be better to use the BeforeUpdate event of the *form* - rather than
that of the controls - to check for duplicates?

It would be possible to use the AfterUpdate event of your 2 controls to set
the Caption of a label on your form to display the name of the more recently
updated control, but Form_BeforeUpdate would probably be better.

 

[Benkong2]

Would it be better to use the BeforeUpdate event of the *form* - rather than
that of the controls - to check for duplicates?

It would be possible to use the AfterUpdate event of your 2 controls to set
the Caption of a label on your form to display the name of the more recently
updated control, but Form_BeforeUpdate would probably be better.
[/QUOTE]
Thanks for the response Allen,

Never thought about using the form event. I will make that change in the
code should be a cut & paste....I think. Still can two controls be used
as the check for duplicates? And do you have examples of how the two
controls might be used for setting the label caption.

Again thanks

 

[Allen Browne]

Yes. You normally would use Form_BeforeUpdate for any validation that
requires you to compare across fields.

Private Sub Text1_AfterUpdate
Me.SomeLabel.Caption = "Text1"
End Sub
Private Sub Text2_AfterUpdate
Me.SomeLabel.Caption = "Text2"
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Would it be better to use the BeforeUpdate event of the *form* - rather than
that of the controls - to check for duplicates?

It would be possible to use the AfterUpdate event of your 2 controls to set
the Caption of a label on your form to display the name of the more recently
updated control, but Form_BeforeUpdate would probably be better.

Thanks for the response Allen,

Never thought about using the form event. I will make that change in the
code should be a cut & paste....I think. Still can two controls be used
as the check for duplicates? And do you have examples of how the two
controls might be used for setting the label caption.

Again thanks[/QUOTE]