I am really lost here. Let's add tests to make sure all the controls you
are using have a value
Private Sub IPOAN_AfterUpdate()
Dim strExistingCaseID As String
If IsNull(Me.CaseTypeID) Then
'Skip the test
Debug.Print "No CaseTypeID"
ElseIf IsNull(me.IPOAN) Then
'Skip the test
Debug.Print "NO IPOAN"
ElseIF isDate(Me.DateReceived) = False Then
'Skip the test
Debug.print "Invalid DateReceived"
ELSE
'See if there is any case with same caseid and ipoan within 60 days
strExistingCaseID = Nz(DLookup("[CaseID]", "tblCases", _
"[CaseTypeID]=" & Me.CaseTypeID & _
" AND [IPOAN]='" & Me.IPOAN & _
"' AND [DateReceived] BETWEEN #" & _
DateAdd("d", -60, Me.DateReceived) & _
"# AND #" & Me.DateReceived & "#"), "") Then
'If strExistingcaseId is not blank (match)
If Len(strExistingCaseID) > 0 THEN
MsgBox "A possible duplicate case was created on " &
DLookup("[DateReceived]", "tblCases", _
"[CaseID]='" & strExistingCaseID & "'") _
& "." & vbCrLf & vbCrLf & "Please refer to this case ID: " &
_
strExistingCaseID, vbOKOnly + vbExclamation, "Warning"
'By the way the DateReceived returned in this message box is not necessarily
'going to be a date within the last 60 days. You did not specify that in
the
'DLookup criteria.
End IF
End If
End IF 'Tested to make sure needed search values are there
End Sub
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
ploddinggaltn said:
I so appreciate your help here John; now the error message doesn't fire at
all. It doesn't display for any CaseID no matter the date or even if it
exists in the table; how can I get this to identify and fire the error
message for CaseID with Case REceived in last 60 days only? Perhaps the
problem is that the CaseID will never be blank or empty in the table, the
caseID might not exist but the field will always have a caseID in it.
Does
that make sense.
Again, I appreciate your expertise.
John Spencer said:
Well NZ is a function that will return the value of the variable/field
unless the variable/field is null. In that case it will return the value
of
the second argument. SO in this case, if the DLookup returned null,
strExistindCaseID would get changed to a zero-length string.
Private Sub IPOAN_AfterUpdate()
Dim strExistingCaseID As String
strExistingCaseID = Nz(DLookup("[CaseID]", "tblCases", _
"[CaseTypeID]=" & Me.CaseTypeID & _
" AND [IPOAN]='" & Me.IPOAN & "'"), "")
If strExistingCaseID = Nz(DLookup("[CaseID]", "tblCases", _
"[CaseTypeID]=" & Me.CaseTypeID & _
" AND [IPOAN]='" & Me.IPOAN & _
"' AND [DateReceived] BETWEEN #" & _
DateAdd("d", -60, Me.DateReceived) & _
"# AND #" & Me.DateReceived & "#"), "") Then
'Need to add a test to see if strExistingcaseId is blank (no match)
If strExistingCaseID <> "" THEN
MsgBox "A possible duplicate case was created on " &
DLookup("[DateReceived]", "tblCases", _
"[CaseID]='" & strExistingCaseID & "'") _
& "." & vbCrLf & vbCrLf & "Please refer to this case ID: " &
_
strExistingCaseID, vbOKOnly + vbExclamation, "Warning"
End IF
End If
End Sub
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
message
Hi Again John,
There is one issue with this code, it displays the error message when
there
is no dupicate found. How would I alter the code to only display when
it
finds a duplicate?
Also what does NZ indicate...I try to learn something new with each
inquiry.
Thanks again!
Here is the code that I'm using:
Private Sub IPOAN_AfterUpdate()
Dim strExistingCaseID As String
strExistingCaseID = Nz(DLookup("[CaseID]", "tblCases",
"[CaseTypeID]="
&
Me.CaseTypeID & " AND [IPOAN]='" & Me.IPOAN & "'"), "")
If strExistingCaseID = Nz(DLookup("[CaseID]", "tblCases",
"[CaseTypeID]=" & Me.CaseTypeID & " AND [IPOAN]='" & Me.IPOAN & "' AND
[DateReceived] BETWEEN #" & DateAdd("d", -60, Me.DateReceived) & "# AND
#"
&
Me.DateReceived & "#"), "") Then
MsgBox "A possible duplicate case was created on " &
DLookup("[DateReceived]", "tblCases", "[CaseID]='" & strExistingCaseID
&
"'")
& "." & vbCrLf & vbCrLf & "Please refer to this case ID: " &
strExistingCaseID, vbOKOnly + vbExclamation, "Warning"
End If
End Sub
:
Your restriction does not appear to make sense. The where clause is
added
on to what you already have and uses the current system date to
restrict
the
range of records searched for a match.
IF you are looking for matches that occurred within 60 days of the
date
you
have entered in the record, then you need to modify the DLookup
statement
to use that date control in place of the Date() function.
Assumig that DateReceived is the name of your date control on the
form,
the
modification would look like
strExistingCaseID = Nz(DLookup("[CaseID]", "tblCases", "[CaseTypeID]="
& Me.CaseTypeID & " AND [IPOAN]='" & Me.IPOAN & "' AND DateReceived
BETWEEN #" & DateAdd("d",-60, Me.DateReceived) & "# AND #" &
Me.DateReceived& "#"), "")
Of course you should be testing to see if CaseTypeID and your
DateControl
have values before you attempt to match.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Good Morning Tom,
I really can't have a Where Clause with this because the time it
would
take
to enter this with every record. We are using this form to enter
records,
over a hundred each morning....I just want the code to prompt if
this
is a
duplicate request already in the table. Do you have any other
suggestions
that would work? Thank you for your time.
:
On Tue, 12 Feb 2008 17:52:00 -0800, ploddinggaltn
This code does not explicitly search for 3 years, but I assume you
meant that tblCases has 3 years of data in it.
To limit the first query to 60 days, you could replace the existing
code with:
strExistingCaseID = Nz(DLookup("[CaseID]", "tblCases",
"[CaseTypeID]="
& Me.CaseTypeID & " AND [IPOAN]='" & Me.IPOAN & "' AND DateReceived
BETWEEN #" & DateAdd("d",-60,Date()) & "# AND #" & Date() & "#"),
"")
IOW, add a new criterion to the WHERE clause: DateReceived between
#12/12/2007# and #2/12/2008#
-Tom.
I'm using this code to identify existing cases in a table based on
an
account
number. This code searches all records for the past 3 years, I'd
like
to
alter the code to only look at the past 60 days cases rather than
the
entire
table. The date is in a field called "DateReceived". Any help
you
can
give
me is appreciated.
Private Sub IPOAN_AfterUpdate()
Dim strExistingCaseID As String
strExistingCaseID = Nz(DLookup("[CaseID]", "tblCases",
"[CaseTypeID]=" &
Me.CaseTypeID & " AND [IPOAN]='" & Me.IPOAN & "'"), "")
If strExistingCaseID <> "" Then
MsgBox "A possible duplicate case was created on " &
DLookup("[DateReceived]", "tblCases", "[CaseID]='" &
strExistingCaseID &
"'")
& "." & vbCrLf & vbCrLf & "Please refer to this case ID: " &
strExistingCaseID, vbOKOnly + vbExclamation, "Warning"
End If
End Sub
