Hi Doug
Comment noted on the Cancel = False
My query is returning correct record sets (ie - any duplicate coordinator
names).
The main query is...
SELECT tblContacts.ContactID, [tblcontacts].[firstname] & " " & [lastname]
AS CombNane, tblRegions.Region
FROM (qryDuplicateRegions INNER JOIN tblRegions ON
qryDuplicateRegions.Region = tblRegions.Region) INNER JOIN tblContacts ON
tblRegions.RegionID = tblContacts.RegionFK
WHERE (((tblContacts.ContactID)=[forms]![frmcontacts]![contactid]) AND
((tblRegions.Region)=[qryduplicateregions].[region]) AND
((tblContacts.ContactStatus)="closed") AND ((tblContacts.ContactType) Like
"*coordinator*"));
The source query is ...
SELECT [tblcontacts].[firstname] & " " & [lastname] AS CombNane,
tblRegions.Region
FROM (qryDuplicateRegions INNER JOIN tblRegions ON
qryDuplicateRegions.Region = tblRegions.Region) INNER JOIN tblContacts ON
tblRegions.RegionID = tblContacts.RegionFK
WHERE (((tblRegions.Region)=[qryduplicateregions].[region]) AND
((tblContacts.ContactStatus)="closed")) OR
(((tblRegions.Region)=[qryduplicateregions].[region]) AND
((tblContacts.ContactStatus)="closed"));
--
Sue Compelling
Douglas J. Steele said:
So did you check qryDuplicateCoordsAlert to see why it's returning values
you're not expecting?
Just a comment that it's meaningless to set Cancel = False.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
Hi Doug
Got waylaid on other pressing tasks.
Anyway - I'm still struggling with this command. No matter how I
rework
it
it either - does nothing or - pops up an alert no matter what status
change
occurs.
This command (our original) does nothing (even if they do appear in the
query):
Private Sub Contact_Type_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[ContactID]", "[qryDuplicateCoordsAlert]",
"[ContactID] = " & Me.ContactID)) = False Then
MsgBox "NOTE: You already have a Coordinator for this site. " & _
"Pls create a new site or change the contact type"
Cancel = True
End If
End Sub
This command pops up the alert regardless:
Private Sub Contact_Type_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[ContactID]", "[qryDuplicateCoordsAlert]",
"[ContactID] = " & Me.ContactID)) = True Then
MsgBox "NOTE: You already have a Coordinator for this site. " & _
"Pls create a new site or change the contact type"
Cancel = False
End If
End Sub
--
Sue Compelling
:
Yes, your code looks correct, although you left out the Cancel = True,
which
is important. (sorry about the typo...)
The DLookup statement is supposed to return one row from the query,
based
on
the criteria. If there isn't a row with the specified value of
ContactID,
DLookup will return a Null value, so what we're doing is simply
checking
whether or not DLookup returned Null by using the IsNull function. If
it
did
return Null, that means you don't already have that value in the
query.
If
it didn't, you did have the value.
If you're always getting a value, you'll need to check
qryDuplicateCoordsAlert to see why it's returning values you're not
expecting.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
message
Hi Douglas
Thanks for the prompt response.
I tried your code (though had to put a " in after the [ContactID] to
stop
an
error message coming up. Now, EVERY time I try to change a contact
type
the
MesgBox comes up.
Note - my query for checking for duplicate coordinators will always
return
nil results UNLESS there is actually a duplictae coordinator.
So 2 things ...
Have I got the code right?
And what is it actually saying (the IsNull has thrown me)
Speak soon
The code I'm using is below ....
Private Sub Contact_Type_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[ContactID]", "[qryDuplicateCoordsAlert]",
"[ContactID]
=
" & Me.ContactID)) = False Then
MsgBox "NOTE: You already have a Coordinator for this site. Pls
create
a
new site or change the contact type"
End If
End Sub
--
Sue Compelling
:
You can't refer to fields in queries in that way. You either need
to
use
a
recordset, or else the DLookup function.
For instance, if ContactID is a numeric field, you'd use
If IsNull(DLookup("[ContactID], "[qryDuplicateCoordsAlert]" , _
"[ContactID] = " & Me.ContactID)) = False Then
MsgBox "NOTE: You already have a Coordinator for this site. " &
_
"Pls create a new site or change the contact type"
End If
Note, though, that it's probably better to use the BeforeUpdate
event:
stop
them before they make the mistake!
Private Sub Contact_Type_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("[ContactID], "[qryDuplicateCoordsAlert]" , _
"[ContactID] = " & Me.ContactID)) = False Then
MsgBox "NOTE: You already have a Coordinator for this site. " &
_
"Pls create a new site or change the contact type"
Cancel = True
End If
End Sub
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
Hi
I have the following expression on my after update
Private Sub Contact_Type_AfterUpdate()
If Me.ContactID = [qryDuplicateCoordsAlert].[ContactID] Then
MsgBox "NOTE: You already have a Coordinator for this site.
Pls
create
a new site or change the contact type"
End If
End Sub
though when I try this I get the following error:
Run-time error '2465'
Pink Ribbon Street Appeal can't find the field [l] referred to in
your
expression
I don't know what field it's referring to but when I debug it
highlights
this line ...
If Me.ContactID = [qryDuplicateCoordsAlert].[ContactID] Then
Me.ContactID definitely exists (it shows the value when I hover
over
it)
and
ContactID definitely exists in the query ... which is ...
SELECT tblContacts.ContactID, [firstname] & " " & [lastname] AS
CombNane,
tblRegions.Region
FROM (qryDuplicateRegions INNER JOIN tblRegions ON
qryDuplicateRegions.Region = tblRegions.Region) INNER JOIN
tblContacts
ON
tblRegions.RegionID = tblContacts.RegionFK
WHERE
(((tblContacts.ContactID)=[forms]![frmcontacts]![contactid])
AND
((tblRegions.Region)=[qryduplicateregions].[region]) AND
((tblContacts.ContactStatus)="closed") AND
((tblContacts.ContactType)
Like
"*coordinator*"));
TIA
Sue Compelling
