Checking for duplicate data

I have a table whose field "ID" has a "No duplicates" set as a
property.

Just before refreshing my record, I want to check to make sure that
Forms!StopsTableForm!ID is not duplicated. I have the field's property
set to "No duplicates", which results in a very cryptic message from
the system when I refresh that I want to supercede with my own
message. I would like to check for duplicates and if found, have a
message box pop up that says "Duplicate Data. Closing Form.", remove
current data and close the StopsTableForm form. If data is not
duplicated, keep the form open with the current data.

How do I check for duplicate data? (Note: ID is concatenated in this
code, if that makes a difference) I'm thinking that if the data has
not been saved to the table, how does it check to see if it is a
duplicate?

What I have so far.

Private Sub OpenForm1_Click()

If IsNull(Forms!CreateStopsTableForm!PICKADD1) Then
MsgBox "Address is Null. Cannot add record", vbOKOnly, "Warning"
Else
DoCmd.OpenForm "StopsTableForm", , , , acFormAdd
Forms!StopsTableForm!StudID = Forms!CreateStopsTableForm!StudID
Forms!StopsTableForm!ID = Forms!CreateStopsTableForm!ID & "A"
Forms!StopsTableForm!ADDRESS = Forms!CreateStopsTableForm!PICKADD1
Forms!StopsTableForm!PU_DO_CODE = "PU1"

' Check for duplicate data code here, I think

Forms("StopsTableForm").Refresh
End If

End Sub

Thanks in advance for any and all help.

It sounds like you are allowing the user to do a lot of work filling in a
form and THEN checking to see if there's a duplication. If that's how
you're approaching this, how much do you enjoy working on a screen and then
being told !!**WRONG**!! by your computer? g

If what you want to accomplish is to ensure that users cannot re-enter
duplicate primary keys, consider giving them a combo box in which they can
either select an existing record (for editing) or can (using the LimitToList
property and the NotInList event) create a new ID on the fly.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Debbiedo" wrote in message
oups.com...
I have a table whose field "ID" has a "No duplicates" set as a
property.

Just before refreshing my record, I want to check to make sure that
Forms!StopsTableForm!ID is not duplicated. I have the field's property
set to "No duplicates", which results in a very cryptic message from
the system when I refresh that I want to supercede with my own
message. I would like to check for duplicates and if found, have a
message box pop up that says "Duplicate Data. Closing Form.", remove
current data and close the StopsTableForm form. If data is not
duplicated, keep the form open with the current data.

How do I check for duplicate data? (Note: ID is concatenated in this
code, if that makes a difference) I'm thinking that if the data has
not been saved to the table, how does it check to see if it is a
duplicate?

What I have so far.

Private Sub OpenForm1_Click()

If IsNull(Forms!CreateStopsTableForm!PICKADD1) Then
MsgBox "Address is Null. Cannot add record", vbOKOnly, "Warning"
Else
DoCmd.OpenForm "StopsTableForm", , , , acFormAdd
Forms!StopsTableForm!StudID = Forms!CreateStopsTableForm!StudID
Forms!StopsTableForm!ID = Forms!CreateStopsTableForm!ID & "A"
Forms!StopsTableForm!ADDRESS = Forms!CreateStopsTableForm!PICKADD1
Forms!StopsTableForm!PU_DO_CODE = "PU1"

' Check for duplicate data code here, I think

Forms("StopsTableForm").Refresh
End If

End Sub

Thanks in advance for any and all help.

On Aug 13, 3:27 pm, "Jeff Boyce" wrote:
It sounds like you are allowing the user to do a lot of work filling in a
form and THEN checking to see if there's a duplication. If that's how
you're approaching this, how much do you enjoy working on a screen and then
being told !!**WRONG**!! by your computer? g

If what you want to accomplish is to ensure that users cannot re-enterduplicateprimary keys, consider giving them a combo box in which they can
either select an existing record (for editing) or can (using the LimitToList
property and the NotInList event) create a new ID on the fly.

Regards

Jeff Boyce
Microsoft Office/AccessMVP

"Debbiedo" wrote in message

oups.com...



I have a table whosefield"ID" has a "No duplicates" set as a
property.

Just before refreshing my record, I want to check to make sure that
Forms!StopsTableForm!ID is not duplicated. I have thefield'sproperty
set to "No duplicates", which results in a very cryptic message from
the system when I refresh that I want to supercede with my own
message. I would like to check for duplicates and if found, have a
message box pop up that says "DuplicateData. Closing Form.", remove
currentdataand close the StopsTableForm form. Ifdatais not
duplicated, keep the form open with the currentdata.

How do I check forduplicatedata? (Note: ID is concatenated in this
code, if that makes a difference) I'm thinking that if thedatahas
not been saved to the table, how does it check to see if it is a
duplicate?

What I have so far.

Private Sub OpenForm1_Click()

If IsNull(Forms!CreateStopsTableForm!PICKADD1) Then
MsgBox "Address is Null. Cannot add record", vbOKOnly, "Warning"
Else
DoCmd.OpenForm "StopsTableForm", , , , acFormAdd
Forms!StopsTableForm!StudID = Forms!CreateStopsTableForm!StudID
Forms!StopsTableForm!ID = Forms!CreateStopsTableForm!ID & "A"
Forms!StopsTableForm!ADDRESS = Forms!CreateStopsTableForm!PICKADD1
Forms!StopsTableForm!PU_DO_CODE = "PU1"

' Check forduplicatedatacode here, I think

Forms("StopsTableForm").Refresh
End If

End Sub

Thanks in advance for any and all help.- Hide quoted text -

- Show quoted text -

I know it seems like this, but actually the user is not entering any
new data in yet. When the user clicks on a command button from one
form (CreateStopsTableForm) it opens up a new form (StopsTableForm)
and copies a few fields from one table (Student) a different table
(StopsTable) and displays it in the new form. The primary key field
"ID" is generated at this time. The reason I need to check for
duplicate data in the ID field is because if the user clicks on the
command button twice for the same record in the CreateStopsTableForm,
a duplicate ID will be created and I get that annoying message popping
up. The user does not enter any data until after the form is opened!

And yes, I know that I am duplicating some data but the software
appliation I am using does not recognize joined tables, and I have to
extract as many as four separate pieces of information from each
record in the Students table and make make them separate records in
the Stops table (trust me, it has to be this way). Associated with
these 4 records are data that is buried in a text/memo field that
needs to extracted and placed in the appropriate fields in the Stops
table.

So actually, I am trying to reduce as much data entry for the user as
possible while ensuring that duplicate data does not get entered and
that annoying, confusing messages don't frustrate them. The annoying
system message also forces the user to change the data (which I don't
want) or it locks up the form. This is not a message I want the user
to get.

Again, any ideas how I can check for duplicate data before refreshing?

Thanks again for any and all help.

Deb

In the event procedure behind the button click you could:
* open a recordset and count how many (?1) you already have like that
* set a flag ("I'm starting") and check that flag to see if a second start
is happening
* add error handling that intercepts the "duplicate" condition and handles
it however you wish.

Just a few thoughts...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Debbiedo" wrote in message
oups.com...
On Aug 13, 3:27 pm, "Jeff Boyce" wrote:
It sounds like you are allowing the user to do a lot of work filling in a
form and THEN checking to see if there's a duplication. If that's how
you're approaching this, how much do you enjoy working on a screen and
then
being told !!**WRONG**!! by your computer? g

If what you want to accomplish is to ensure that users cannot
re-enterduplicateprimary keys, consider giving them a combo box in which
they can
either select an existing record (for editing) or can (using the
LimitToList
property and the NotInList event) create a new ID on the fly.

Regards

Jeff Boyce
Microsoft Office/AccessMVP

"Debbiedo" wrote in message

oups.com...



I have a table whosefield"ID" has a "No duplicates" set as a
property.

Just before refreshing my record, I want to check to make sure that
Forms!StopsTableForm!ID is not duplicated. I have thefield'sproperty
set to "No duplicates", which results in a very cryptic message from
the system when I refresh that I want to supercede with my own
message. I would like to check for duplicates and if found, have a
message box pop up that says "DuplicateData. Closing Form.", remove
currentdataand close the StopsTableForm form. Ifdatais not
duplicated, keep the form open with the currentdata.

How do I check forduplicatedata? (Note: ID is concatenated in this
code, if that makes a difference) I'm thinking that if thedatahas
not been saved to the table, how does it check to see if it is a
duplicate?

What I have so far.

Private Sub OpenForm1_Click()

If IsNull(Forms!CreateStopsTableForm!PICKADD1) Then
MsgBox "Address is Null. Cannot add record", vbOKOnly, "Warning"
Else
DoCmd.OpenForm "StopsTableForm", , , , acFormAdd
Forms!StopsTableForm!StudID = Forms!CreateStopsTableForm!StudID
Forms!StopsTableForm!ID = Forms!CreateStopsTableForm!ID & "A"
Forms!StopsTableForm!ADDRESS = Forms!CreateStopsTableForm!PICKADD1
Forms!StopsTableForm!PU_DO_CODE = "PU1"

' Check forduplicatedatacode here, I think

Forms("StopsTableForm").Refresh
End If

End Sub

Thanks in advance for any and all help.- Hide quoted text -

- Show quoted text -

I know it seems like this, but actually the user is not entering any
new data in yet. When the user clicks on a command button from one
form (CreateStopsTableForm) it opens up a new form (StopsTableForm)
and copies a few fields from one table (Student) a different table
(StopsTable) and displays it in the new form. The primary key field
"ID" is generated at this time. The reason I need to check for
duplicate data in the ID field is because if the user clicks on the
command button twice for the same record in the CreateStopsTableForm,
a duplicate ID will be created and I get that annoying message popping
up. The user does not enter any data until after the form is opened!

And yes, I know that I am duplicating some data but the software
appliation I am using does not recognize joined tables, and I have to
extract as many as four separate pieces of information from each
record in the Students table and make make them separate records in
the Stops table (trust me, it has to be this way). Associated with
these 4 records are data that is buried in a text/memo field that
needs to extracted and placed in the appropriate fields in the Stops
table.

So actually, I am trying to reduce as much data entry for the user as
possible while ensuring that duplicate data does not get entered and
that annoying, confusing messages don't frustrate them. The annoying
system message also forces the user to change the data (which I don't
want) or it locks up the form. This is not a message I want the user
to get.

Again, any ideas how I can check for duplicate data before refreshing?

Thanks again for any and all help.

Deb