Checking for Duplicates in Multiple Fields beforeupdate

[john.cole@shawgrp.com]

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

[Phil Stanton]

Re: Checking for Duplicates in Multiple Fields beforeupdate

Could you be clearer on what indexes you have, and what is or is not allowed
to be duplicted

Phil

<john.cole@shaw grp.comwrote in message
news:82df44bb-8381-4cda-aa6e-cfc60b226cb4@m7 3g2000hsh.googl egroups.com...

>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

[Roger]

Re: Checking for Duplicates in Multiple Fields beforeupdate

On Oct 10, 11:08 am, john.c...@shawg rp.com wrote:

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

say the table is tblSpool
and the key is spoolId

in form_beforeUpda te

strwhere = "spoolNo = " & txtSpoolNo & " and spoolId <" & txtSpoolld
if (dcount("spoolI d", "tblSpool", strwhere) 0) then
msgbox "Duplicate"
end if

[john.cole@shawgrp.com]

Re: Checking for Duplicates in Multiple Fields beforeupdate

On Oct 11, 12:19 am, "Phil Stanton" <p...@myfamilyn ame.co.ukwrote:

Could you be clearer on what indexes you have, and what is or is not allowed
to be duplicted
>
Phil
>
<john.c...@shaw grp.comwrote in message
>
news:82df44bb-8381-4cda-aa6e-cfc60b226cb4@m7 3g2000hsh.googl egroups.com...
>
>
>

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- Hide quoted text -

>
- Show quoted text -

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

[Roger]

Re: Checking for Duplicates in Multiple Fields beforeupdate

On Oct 11, 3:29 am, john.c...@shawg rp.com wrote:

On Oct 11, 12:19 am, "Phil Stanton" <p...@myfamilyn ame.co.ukwrote:
>
>
>
>
>

Could you be clearer on what indexes you have, and what is or is not allowed
to be duplicted

>

Phil

>

<john.c...@shaw grp.comwrote in message

>

news:82df44bb-8381-4cda-aa6e-cfc60b226cb4@m7 3g2000hsh.googl egroups.com....

>

>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- Hide quoted text -

>

- Show quoted text -

>
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 -

say the table is tblSpool
and the key is spoolId

in form_beforeUpda te


strwhere = "spoolNo = " & txtSpoolNo & " and " & _
"baseJob2 = " & txtBaseJob2 & " and " & _
"spoolRev = " & txtSpoolRev & " and " & _
"spoolId <" & txtSpoolld
if (dcount("spoolI d", "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 " & _

[john.cole@shawgrp.com]

Re: Checking for Duplicates in Multiple Fields beforeupdate

On Oct 11, 4:40 pm, Roger <lesperan...@na tpro.comwrote:

On Oct 11, 3:29 am, john.c...@shawg rp.com wrote:
>
>
>
>
>

On Oct 11, 12:19 am, "Phil Stanton" <p...@myfamilyn ame.co.ukwrote:

>

Could you be clearer on what indexes you have, and what is or is not allowed
to be duplicted

>

Phil

>

<john.c...@shaw grp.comwrote in message

>

>news:82df44b b-8381-4cda-aa6e-cfc60b226cb4@m7 3g2000hsh.googl egroups.com....

>

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- Hide quoted text -

>

- Show quoted text -

>

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 -

>
say the table is tblSpool
and the key is spoolId
>
in form_beforeUpda te
>
strwhere = "spoolNo = " & txtSpoolNo & " and " & _
                "baseJob2 = " & txtBaseJob2 & " and " &_
                "spoolRev = " & txtSpoolRev & " and " &_
                  "spoolId <" & txtSpoolld
if (dcount("spoolI d", "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 -

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("spoolI d", "tblSpoolLi st", 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

[john.cole@shawgrp.com]

Re: Checking for Duplicates in Multiple Fields beforeupdate

On Oct 11, 8:03 pm, john.c...@shawg rp.com wrote:

On Oct 11, 4:40 pm, Roger <lesperan...@na tpro.comwrote:
>
>
>
>
>

On Oct 11, 3:29 am, john.c...@shawg rp.com wrote:

>

On Oct 11, 12:19 am, "Phil Stanton" <p...@myfamilyn ame.co.ukwrote:

>

Could you be clearer on what indexes you have, and what is or is not allowed
to be duplicted

>

Phil

>

<john.c...@shaw grp.comwrote in message

>

news:82df44bb-8381-4cda-aa6e-cfc60b226cb4@m7 3g2000hsh.googl egroups.com...

>

>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 ofthe
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- Hide quoted text -

>

- Show quoted text -

>

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 -

>

say the table is tblSpool
and the key is spoolId

>

in form_beforeUpda te

>

strwhere = "spoolNo = " & txtSpoolNo & " and " & _
                "baseJob2 = " & txtBaseJob2 & " and "& _
                "spoolRev = " & txtSpoolRev & " and "& _
                  "spoolId <" & txtSpoolld
if (dcount("spoolI d", "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 -

>
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("spoolI d", "tblSpoolLi st", 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 -

UPDATE!!!!!!
I've got teh syntax of the code correct, and it's working properly. I
have only one question left. After the message box, I would like to
stop the action to save the record. What's teh syntax for that.
Here's the code I have right now.

Dim strWhere As String
strWhere = "[txtSpoolNo] = '" & txtSpoolNo & "' and " & _
"[txtbaseJob2] = '" & BaseJob2 & "' and " & _
"[txtSpoolRev] = '" & SpoolRev & "'"

If (DCount("txtSpo olNo", "tblSpoolLi st", strWhere) 0) Then
MsgBox "The Spool and Rev was previously released for this
project. Check your data and try again."
End If

JC

[john.cole@shawgrp.com]

Re: Checking for Duplicates in Multiple Fields beforeupdate

On Oct 11, 8:03 pm, john.c...@shawg rp.com wrote:

On Oct 11, 4:40 pm, Roger <lesperan...@na tpro.comwrote:
>
>
>
>
>

On Oct 11, 3:29 am, john.c...@shawg rp.com wrote:

>

On Oct 11, 12:19 am, "Phil Stanton" <p...@myfamilyn ame.co.ukwrote:

>

Could you be clearer on what indexes you have, and what is or is not allowed
to be duplicted

>

Phil

>

<john.c...@shaw grp.comwrote in message

>

news:82df44bb-8381-4cda-aa6e-cfc60b226cb4@m7 3g2000hsh.googl egroups.com...

>

>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 ofthe
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- Hide quoted text -

>

- Show quoted text -

>

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 -

>

say the table is tblSpool
and the key is spoolId

>

in form_beforeUpda te

>

strwhere = "spoolNo = " & txtSpoolNo & " and " & _
                "baseJob2 = " & txtBaseJob2 & " and "& _
                "spoolRev = " & txtSpoolRev & " and "& _
                  "spoolId <" & txtSpoolld
if (dcount("spoolI d", "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 -

>
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("spoolI d", "tblSpoolLi st", 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 -

Me.Undo
Got it.
Roger, thank you for your help.
Everything is working as it should.
Thanks again.