Check For Existing Record

[ridgerunner]

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

 

[ruralguy via AccessMonster.com]

Usually verification code is placed in the BeforeUpdate event of a control
using a Domain function to look for duplicates.

 

[Ryan Tisserand]

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]

Can you please explain "Domain Function".

Usually verification code is placed in the BeforeUpdate event of a control
using a Domain function to look for duplicates.

 

[ruralguy via AccessMonster.com]

Either the Dlookup() or DCount() functions as the next post shows.

Can you please explain "Domain Function".

Usually verification code is placed in the BeforeUpdate event of a control
using a Domain function to look for duplicates.

[quoted text clipped - 16 lines]

 

[Douglas J. Steele]

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

(no e-mails, please!)

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.

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

 

[Klatuu]

I would recommend using the form BeforeUpdate event.
There is no certainty that any control will get the focus.
When more than one field, thus more than one control, the testing is more
complex and likely to be less accurate.

 

[ridgerunner]

I am getting this message "Runtime error 424" "Object Required". The second
and third lines, below, are highlighted in the debug screen.


Private Sub InspDate_BeforeUpdate(Cancel As Integer)

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


End Sub

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.

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

 

[Klatuu]

You are missing quote marks------------v---------------------v
If Not IsNull(DLookup("[NoDuplicates]", (qryFrmAddDMInsp), "[NoDuplicates]="
& frmAddDMInspections![InspDate])) Then

Should be:
If Not IsNull(DLookup("[NoDuplicates]", "qryFrmAddDMInsp", "[NoDuplicates]="
& frmAddDMInspections![InspDate])) Then

--
Dave Hargis, Microsoft Access MVP

I am getting this message "Runtime error 424" "Object Required". The second
and third lines, below, are highlighted in the debug screen.


Private Sub InspDate_BeforeUpdate(Cancel As Integer)

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


End Sub

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.

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

 

[ridgerunner]

Thank you. Can you please tell me where I need to put the Function?

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

(no e-mails, please!)

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.

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

 

[ruralguy via AccessMonster.com]

All arguments to Domain functions need to be strings. Here's a good reference:

http://www.mvps.org/access/general/gen0018.htm

I am getting this message "Runtime error 424" "Object Required". The second
and third lines, below, are highlighted in the debug screen.


Private Sub InspDate_BeforeUpdate(Cancel As Integer)

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

End Sub

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.

[quoted text clipped - 28 lines]

 

[Ryan Tisserand]

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.

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

(no e-mails, please!)

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.

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

 

[Douglas J. Steele]

I see no point whatsoever in concatenating the two fields.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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.

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

(no e-mails, please!)


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.


:

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

 

[ridgerunner]

Thanks. After looking at the example, I changed a few things and I am not
receiving any error messages but the "trap" is failing. Closer examination
causes me to ask if the = 'frmAddDMInspections![InspDate]'")) is
correct since it only refers to the InspDate? I am going to try Doug's
example when I find out where to put the Function.


Private Sub InspDate_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[NoDuplicates]", "qryFrmAddDMInsp", "[NoDuplicates]=
'frmAddDMInspections![InspDate]'")) Then
MsgBox "Store and Inspection Date already exist. Please correct",
vbCritical, "Duplicate Entry"
End If
Exit Sub


End Sub

All arguments to Domain functions need to be strings. Here's a good reference:

http://www.mvps.org/access/general/gen0018.htm

I am getting this message "Runtime error 424" "Object Required". The second
and third lines, below, are highlighted in the debug screen.


Private Sub InspDate_BeforeUpdate(Cancel As Integer)

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

End Sub

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.

[quoted text clipped - 28 lines]

tia
ridgerunner

 

[Douglas J. Steele]

Well, you'd put it in the same module as the rest of the code associated
with the form.

However, I agree with Klatuu that it probably makes more sense just to put
the code in the form's BeforeUpdate event, as opposed to in the BeforeUpdate
event of the two text boxes.

Private Sub Form_BeforeUpdate(Cancel = True)
Dim strMessage As String

If IsNull(Me.StoreNo) Then
strMessage = strMessage & "You must provide a Store Number." & vbCrLf
End If

If IsNull(Me.InspDate) Then
strMessage = strMessage & "You must provide an Inspection Date." &
vbCrLf
End If

If Len(strMessage) = 0 Then
If IsNull(DLookup("StoreNo", "[YourTableName]", _
"[InspDate] = " & Format(Forms![YourFormName]![InspDate],
"\#yyyy\-mm\-dd\#") & _
" AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then
strMessage = strMessage & "Store and Inspection Date already exist."
End If
End If

If Len(strMessage) > 0 Then
Cancel = True
MsgBox strMessage, vbCritical
End If

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you. Can you please tell me where I need to put the Function?

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

(no e-mails, please!)


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.


:

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

 

[ridgerunner]

I have added the Function to the OnOpen Property of the form and I am getting
a syntax error message when I try to run it.

Function DuplicateValue() As Boolean

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

End Function

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

(no e-mails, please!)

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.

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

 

[ridgerunner]

I am sorry I missed seeing this earlier. I have the part about making
certain that a store and a date are entered covered in a command button on
the form.

I copied and pasted the code but I am getting a syntax error message and
"IF" through the "THEN" are in red when I run compile.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("StoreNo", "[DMInspections]", _
"[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate],
"\#yyyy\-mm\-dd\#") & _
" AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False)
Then
strMessage = strMessage & "Store and Inspection Date already exist."
End If


End Sub

Well, you'd put it in the same module as the rest of the code associated
with the form.

However, I agree with Klatuu that it probably makes more sense just to put
the code in the form's BeforeUpdate event, as opposed to in the BeforeUpdate
event of the two text boxes.

Private Sub Form_BeforeUpdate(Cancel = True)
Dim strMessage As String

If IsNull(Me.StoreNo) Then
strMessage = strMessage & "You must provide a Store Number." & vbCrLf
End If

If IsNull(Me.InspDate) Then
strMessage = strMessage & "You must provide an Inspection Date." &
vbCrLf
End If

If Len(strMessage) = 0 Then
If IsNull(DLookup("StoreNo", "[YourTableName]", _
"[InspDate] = " & Format(Forms![YourFormName]![InspDate],
"\#yyyy\-mm\-dd\#") & _
" AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then
strMessage = strMessage & "Store and Inspection Date already exist."
End If
End If

If Len(strMessage) > 0 Then
Cancel = True
MsgBox strMessage, vbCritical
End If

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you. Can you please tell me where I need to put the Function?

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

(no e-mails, please!)


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.


:

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

 

[Douglas J. Steele]

You may have fallen victim of word-wrap. Then is supposed to be on the line
above, after = False)

That's odd, though. It looks correct when I look at what I posted to you.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am sorry I missed seeing this earlier. I have the part about making
certain that a store and a date are entered covered in a command button on
the form.

I copied and pasted the code but I am getting a syntax error message and
"IF" through the "THEN" are in red when I run compile.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("StoreNo", "[DMInspections]", _
"[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate],
"\#yyyy\-mm\-dd\#") & _
" AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False)
Then
strMessage = strMessage & "Store and Inspection Date already exist."
End If


End Sub

Well, you'd put it in the same module as the rest of the code associated
with the form.

However, I agree with Klatuu that it probably makes more sense just to
put
the code in the form's BeforeUpdate event, as opposed to in the
BeforeUpdate
event of the two text boxes.

Private Sub Form_BeforeUpdate(Cancel = True)
Dim strMessage As String

If IsNull(Me.StoreNo) Then
strMessage = strMessage & "You must provide a Store Number." & vbCrLf
End If

If IsNull(Me.InspDate) Then
strMessage = strMessage & "You must provide an Inspection Date." &
vbCrLf
End If

If Len(strMessage) = 0 Then
If IsNull(DLookup("StoreNo", "[YourTableName]", _
"[InspDate] = " & Format(Forms![YourFormName]![InspDate],
"\#yyyy\-mm\-dd\#") & _
" AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then
strMessage = strMessage & "Store and Inspection Date already
exist."
End If
End If

If Len(strMessage) > 0 Then
Cancel = True
MsgBox strMessage, vbCritical
End If

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you. Can you please tell me where I need to put the Function?

:

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

(no e-mails, please!)


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.


:

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

 

[ridgerunner]

Yes, ord wrap is messing things up. "Then" is in the correct place in the
property sheet or should I call it module, but this screen makes it look like
it is one line down.

At any rate, the code below is highlighted with I get the syntax error
message.

If IsNull(DLookup("StoreNo", "[DMInspections]", _
"[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate],

You may have fallen victim of word-wrap. Then is supposed to be on the line
above, after = False)

That's odd, though. It looks correct when I look at what I posted to you.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am sorry I missed seeing this earlier. I have the part about making
certain that a store and a date are entered covered in a command button on
the form.

I copied and pasted the code but I am getting a syntax error message and
"IF" through the "THEN" are in red when I run compile.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("StoreNo", "[DMInspections]", _
"[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate],
"\#yyyy\-mm\-dd\#") & _
" AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False)
Then
strMessage = strMessage & "Store and Inspection Date already exist."
End If


End Sub

Well, you'd put it in the same module as the rest of the code associated
with the form.

However, I agree with Klatuu that it probably makes more sense just to
put
the code in the form's BeforeUpdate event, as opposed to in the
BeforeUpdate
event of the two text boxes.

Private Sub Form_BeforeUpdate(Cancel = True)
Dim strMessage As String

If IsNull(Me.StoreNo) Then
strMessage = strMessage & "You must provide a Store Number." & vbCrLf
End If

If IsNull(Me.InspDate) Then
strMessage = strMessage & "You must provide an Inspection Date." &
vbCrLf
End If

If Len(strMessage) = 0 Then
If IsNull(DLookup("StoreNo", "[YourTableName]", _
"[InspDate] = " & Format(Forms![YourFormName]![InspDate],
"\#yyyy\-mm\-dd\#") & _
" AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then
strMessage = strMessage & "Store and Inspection Date already
exist."
End If
End If

If Len(strMessage) > 0 Then
Cancel = True
MsgBox strMessage, vbCritical
End If

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thank you. Can you please tell me where I need to put the Function?

:

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

(no e-mails, please!)


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.


:

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

 

[ridgerunner]

Sorry about the "ord". I found out why the syntax error was popping up: I
had to pull this "\#yyyy\-mm\-dd\#") & _ to the line above. I now do not
received any syntax errors but the code is not catching a duplicate entry.

This is how it looks now. I had to make a correction to the table name.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("StoreNo", "[tblDMInspections]", _
"[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate],
"\#yyyy\-mm\-dd\#") & _
" AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False)
Then
strMessage = strMessage & "Store and Inspection Date already exist."
End If


End Sub

Yes, ord wrap is messing things up. "Then" is in the correct place in the
property sheet or should I call it module, but this screen makes it look like
it is one line down.

At any rate, the code below is highlighted with I get the syntax error
message.

If IsNull(DLookup("StoreNo", "[DMInspections]", _
"[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate],

You may have fallen victim of word-wrap. Then is supposed to be on the line
above, after = False)

That's odd, though. It looks correct when I look at what I posted to you.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am sorry I missed seeing this earlier. I have the part about making
certain that a store and a date are entered covered in a command button on
the form.

I copied and pasted the code but I am getting a syntax error message and
"IF" through the "THEN" are in red when I run compile.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("StoreNo", "[DMInspections]", _
"[InspDate] = " & Format(Forms![frmAddDMInspection]![InspDate],
"\#yyyy\-mm\-dd\#") & _
" AND [StoreNo] = " & Forms![frmAddDMInspections]![StoreNo]) = False)
Then
strMessage = strMessage & "Store and Inspection Date already exist."
End If


End Sub

:

Well, you'd put it in the same module as the rest of the code associated
with the form.

However, I agree with Klatuu that it probably makes more sense just to
put
the code in the form's BeforeUpdate event, as opposed to in the
BeforeUpdate
event of the two text boxes.

Private Sub Form_BeforeUpdate(Cancel = True)
Dim strMessage As String

If IsNull(Me.StoreNo) Then
strMessage = strMessage & "You must provide a Store Number." & vbCrLf
End If

If IsNull(Me.InspDate) Then
strMessage = strMessage & "You must provide an Inspection Date." &
vbCrLf
End If

If Len(strMessage) = 0 Then
If IsNull(DLookup("StoreNo", "[YourTableName]", _
"[InspDate] = " & Format(Forms![YourFormName]![InspDate],
"\#yyyy\-mm\-dd\#") & _
" AND [StoreNo] = " & Forms![YourFormName]![StoreNo]) = False) Then
strMessage = strMessage & "Store and Inspection Date already
exist."
End If
End If

If Len(strMessage) > 0 Then
Cancel = True
MsgBox strMessage, vbCritical
End If

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thank you. Can you please tell me where I need to put the Function?

:

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

(no e-mails, please!)


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.


:

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