I see no point whatsoever in concatenating the two fields.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ryan Tisserand" wrote in message
...
Wow Douglas, you are right. I forgot two main elements. I also put an
unbound textbox on the main form that is not visible and set the control
source to [InspDate]&""&[StoreNo] and call it the same name
"NoDuplicates".
Then on my main form I add a button thats "On Click" event reads like
this:

If isnull (me!InspDate) Then
msgbox "Inspection date is required"
Exit Sub
End if
If isnull (Me!StoreNo) Then
msgbox "Store number is required"
Exit Sub
End if
If not IsNull(DLookup("[NoDuplicates]", (YourQueryName), "[NoDuplicates]="
&
Forms(YourFormName)![NoDuplicates])) Then
MsgBox "Store and Inspection Date already exist. Please correct",
vbCritical, "Duplicate Entry"
End If
Exit Sub

I applogize for my quick incomplete answer.



"Douglas J. Steele" wrote:

Why would you concatenate the fields? (And you've forgotten to
concatenate
the StoreNo into the argument you're passing to DLookup)

As well, you need to check in the BeforeUpdate of both InspDate and
StoreNo,
since you can't be sure what order the fields will be filled in. Use a
generic function like:

Function DuplicateValue() As Boolean

If IsNull(Format(Forms![YourFormName]![InspDate]) And _
IsNull(Forms![YourFormName]![StoreNo]) = False Then
DuplicateValue = (IsNull(DLookup("StoreNo", "[YourTableName]", _
"[InspDate] = " & Format(Forms![YourFormName]![InspDate],
"\#yyyy\-mm\-dd\#") & _
" AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False)
End If

End Function

You can then call that function in the BeforeUpdate event of both
controls:

Private Sub InspDate_BeforeUpdate(Cancel As Integer)

If DuplicateValue() = True Then
MsgBox "Store and Inspection Date already exist. Please correct", _
vbCritical, "Duplicate Entry"
Cancel = True
End If

End Sub

Private Sub StoreNo_BeforeUpdate(Cancel As Integer)

If DuplicateValue() = True Then
MsgBox "Store and Inspection Date already exist. Please correct", _
vbCritical, "Duplicate Entry"
Cancel = True
End If

End Sub



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ryan Tisserand" wrote in
message
...
Here is my solution for multiple field duplication. I first join the
two
fields in a query. For your example I would do this in a query.
NoDuplicates:[InspDate]&""&[StoreNo]
Now that you have one field named NoDuplicates to deal with, in the
"Before
Update" event of InspDate you would use this code

If not IsNull(DLookup("[NoDuplicates]", (YourQueryName),
"[NoDuplicates]="
&
Forms(YourFormName)![InspDate])) Then
MsgBox "Store and Inspection Date already exist. Please correct",
vbCritical, "Duplicate Entry"
End If
Exit Sub

Dont know if this will help but this works for me.


"ridgerunner" wrote:

I have a unique index set on two fields in my table, InspDate and
StoreNo. I
am trying to trap the error of attempting to add a duplicate by having
the
code below in the LostFocus Event for the InspDate. I am running
around
in
circles. Can someone please help?


Private Sub InspDate_LostFocus()
If DMInspections.StoreNo = True Then
ElseIf DMInspDet.InspDate = True Then
MsgBox "Store and Inspection Date already exist. Please correct"
Me.InspDate.SetFocus
Else: Me.DMnameID.SetFocus
Exit Sub
End If
End Sub


tia
ridgerunner