Checking for Duplicates in Multiple Fields beforeupdate

>I have searched all the groups I can, and I still haven't been able to
come up the solution I need. I have the following problem.
>
In my form named sbfrmSpoolList, I am entering a job, spool and
revision number. My table is indexed properly to not allow
duplicates, but I would like teh user to be notified that they are
typing a duplicate via a message box, then I woulld the update of the
record to be cancelled. I have tried the DLookup, but I'm not sure
how to input it with multiple fields. I have set the the beforeupdate
event on the form, not the control. I would like to know the syntax
for checking for duplicates in the following fields txtBaseJob2,
txtSpoolNo, txtSpoolRev.
>
Any help would appreciated.
>
Thanks, JC

I have searched all the groups I can, and I still haven't been able to
come up the solution I need. *I have the following problem.
>
In my form named sbfrmSpoolList, I am entering a job, spool and
revision number. *My table is indexed properly to not allow
duplicates, but I would like teh user to be notified that they are
typing a duplicate via a message box, then I woulld the update of the
record to be cancelled. *I have tried the DLookup, but I'm not sure
how to input it with multiple fields. *I have set the the beforeupdate
event on the form, not the control. *I would like to know the syntax
for checking for duplicates in the following fields txtBaseJob2,
txtSpoolNo, txtSpoolRev.
>
Any help would appreciated.
>
Thanks, JC

Could you be clearer on what indexes you have, and what is or is not allowed
to be duplicted
>
Phil
>
<john.c...@shawgrp.comwrote in message
>
news:82df44bb-8381-4cda-aa6e-cfc60b226cb4@m73g2000hsh.googlegroups.com...
>
>
>>>>>
- Show quoted text -

On Oct 11, 12:19*am, "Phil Stanton" <p...@myfamilyname.co.ukwrote:
>
>
>
>
>>>>>>>>>>
My apologies for the lack of clarity. *I am entering a job, spool and
the spool rev level. *I don't want to the combination of all three be
repeated. *I have the table (tblSpoolList) formatted properly with an
index for all three fields to not allow duplicates. *Instead of
receiving the standard Access message for duplicate fields, I would
like to check if a duplicate Job, Spool and Rev exist and inform the
user. *The table name is tblSpoolList. *The field names and database
text field names are txtBaseJob2, txtSpoolNo and txtSpoolRev
>
I thought teh DLookup function could do this, but as I understand it,
it only looks for duplicates in one field. *Each of the three fields
have duplicates for sure, but as a group, they CANNOT duplicate.
>
JC- Hide quoted text -
>
- Show quoted text -

On Oct 11, 3:29*am, john.c...@shawgrp.com wrote:
>
>
>
>
>>>>>>>>>>>>>>>
say the table is tblSpool
and the key is spoolId
>
in form_beforeUpdate
>
strwhere = "spoolNo = " & txtSpoolNo & " and " & _
* * * * * * * * "baseJob2 = " & txtBaseJob2 & " and " &_
* * * * * * * * "spoolRev = " & txtSpoolRev & " and " &_
* * * * * * * * * "spoolId <" & txtSpoolld
if (dcount("spoolId", "tblSpool", strwhere) 0) then
* * msgbox "Duplicate"
end if
>
note this assumes all fields are numeric, if any field is text you
need an extra '
>
ie. "spoolNo = '" & txtSpoolNo & "' and " & _- Hide quoted text -
>
- Show quoted text -

On Oct 11, 4:40*pm, Roger <lesperan...@natpro.comwrote:
>
>
>
>
>>>>>>>>>>>>>>>>>>>>>>
Ok, you've lost me. *They primary key for the table consists of the
ID, txtSpoolNo and txtSpoolRev fields. *There is no field named
spoolid. *Can you clarify what you mean that for? *Here is teh code I
currently have, but its working.
>
Dim strWhere As String
strWhere = "spoolNo = '" & txtSpoolNo & " and " & _
* * * * * * * * "baseJob2 = '" & txtBaseJob2 & " and " & _
* * * * * * * * "spoolRev = '" & txtSpoolRev & " and " & _
* * * * * * * * "spoolId <" & txtSpoolld
If (DCount("spoolId", "tblSpoolList", strWhere) 0) Then
* * MsgBox "Duplicate"
End If
>
I'm sorry for the trouble. *I'm really good in vba for Excel, but I'm
almost lost in Access. *Thanks for the help.
>
JC- Hide quoted text -
>
- Show quoted text -

On Oct 11, 4:40*pm, Roger <lesperan...@natpro.comwrote:
>
>
>
>
>>>>>>>>>>>>>>>>>>>>>>
Ok, you've lost me. *They primary key for the table consists of the
ID, txtSpoolNo and txtSpoolRev fields. *There is no field named
spoolid. *Can you clarify what you mean that for? *Here is teh code I
currently have, but its working.
>
Dim strWhere As String
strWhere = "spoolNo = '" & txtSpoolNo & " and " & _
* * * * * * * * "baseJob2 = '" & txtBaseJob2 & " and " & _
* * * * * * * * "spoolRev = '" & txtSpoolRev & " and " & _
* * * * * * * * "spoolId <" & txtSpoolld
If (DCount("spoolId", "tblSpoolList", strWhere) 0) Then
* * MsgBox "Duplicate"
End If
>
I'm sorry for the trouble. *I'm really good in vba for Excel, but I'm
almost lost in Access. *Thanks for the help.
>
JC- Hide quoted text -
>
- Show quoted text -