Checking for Duplicates in Multiple Fields beforeupdate

[john.cole]

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]

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

Phil

<jo*******@shawgrp.comwrote in message
news:82**********************************@m73g2000 hsh.googlegroups.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]

On Oct 10, 11:08*am, [e-mail] 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_beforeUpdate

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

[john.cole]

On Oct 11, 12:19*am, "Phil Stanton" <[e-mail]:

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

Phil

<[e-mail] in message

news:82**********************************@m73g2000 hsh.googlegroups.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]

On Oct 11, 3:29*am, [e-mail] wrote:

On Oct 11, 12:19*am, "Phil Stanton" <[e-mail]:

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

Phil

<[e-mail] in message

news:82**********************************@m73g2000 hsh.googlegroups.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_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 " & _

[john.cole]

On Oct 11, 4:40*pm, Roger <[e-mail]:

On Oct 11, 3:29*am, [e-mail] wrote:

On Oct 11, 12:19*am, "Phil Stanton" <[e-mail]:

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

Phil

<[e-mail] in message

>news:82**********************************@m73g200 0hsh.googlegroups.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_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 -

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

[john.cole]

On Oct 11, 8:03*pm, [e-mail] wrote:

On Oct 11, 4:40*pm, Roger <[e-mail]:

On Oct 11, 3:29*am, [e-mail] wrote:

On Oct 11, 12:19*am, "Phil Stanton" <[e-mail]:

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

Phil

<[e-mail] in message

news:82**********************************@m73g2000 hsh.googlegroups.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_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 -

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 -

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("txtSpoolNo", "tblSpoolList", 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]

On Oct 11, 8:03*pm, [e-mail] wrote:

On Oct 11, 4:40*pm, Roger <[e-mail]:

On Oct 11, 3:29*am, [e-mail] wrote:

On Oct 11, 12:19*am, "Phil Stanton" <[e-mail]:

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

Phil

<[e-mail] in message

news:82**********************************@m73g2000 hsh.googlegroups.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_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 -

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 -

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