"Allow no duplicates" issues

[Joshua K Briley]

Hello,

I am managing a database that is not set up to dis-allow multiple entries
into a specific field. I've tried to change the field properites in the
table to allow no duplicates but am unsuccessful due to linking issues. I've
tried to do the same in SQL (Access is being use as the front end app) but
was unsuccessful there as well. Is there a workaround to this issue within
the form that is set up to supply the table, or am I stuck with the issue of
possible redundant entries into the table? Any advice would be greatly
appreciated.

 

[Roger Carlson]

You can't create a unique index on a field until you get rid of the existing
duplicates.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Vinson]

Hello,

I am managing a database that is not set up to dis-allow multiple entries
into a specific field. I've tried to change the field properites in the
table to allow no duplicates but am unsuccessful due to linking issues. I've
tried to do the same in SQL (Access is being use as the front end app) but
was unsuccessful there as well. Is there a workaround to this issue within
the form that is set up to supply the table, or am I stuck with the issue of
possible redundant entries into the table? Any advice would be greatly
appreciated.

If the data is stored in SQL/Server, you should be able to remove all
existing duplicates from the table and then uniquely index the field
(probably using SQL tools rather than Access, though you can use a
pass-through DDL query to create the index).

Or, you can use the Form's BeforeUpdate event to trap attempts to add
a duplicate. For example:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[NoDupsHere]", "[Tablename]", _
"[NoDupsHere] = '" Me!txtNoDupsHere & "'") Then
MsgBox "This already exists", vbOKOnly
Cancel = True
End If
End Sub

This can of course be fancier, give the option to navigate to the
found record, etc. if you wish.

John W. Vinson[MVP]

 

[Joshua K Briley]

Thank you so much for your advice. If I understand you correctly, I should
make another table, indexing the appropriate fields, which will eliminate
duplicates, and then copy that information back into the original table?
Then, make the appropriate field property changes to not-allow duplicates?
Or is there an easier way to remove duplicates?

 

[Joshua K Briley]

John,

Thanks for your response. My knowledge of VB is pretty limited and I can
only assume that the "("[NoDupsHere]", ..." refers to the field name?
Whereas the
" '" Me!txtNoDupsHere...." refers to the actual code that prohibits the
duplicate entries.....

 

[John Vinson]

John,

Thanks for your response. My knowledge of VB is pretty limited and I can
only assume that the "("[NoDupsHere]", ..." refers to the field name?
Whereas the
" '" Me!txtNoDupsHere...." refers to the actual code that prohibits the
duplicate entries.....

Sorry - didn't explain my jargon <g>

[NoDupsHere] was intended to be the name of the table field for which
you wish to prevent duplicates.

[txtNoDupsHere] is the name of the Form control being used to enter
data into that field.

John W. Vinson[MVP]

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com) there is a sample called
"RemoveDuplicates.mdb" which illustrates how to remove all but one of the
duplicate records in your table.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Alex]

Hi Josh,
you can set up a primary key (one or more fields) either by using the
enterprise manager or via an Access Project or by executing a TSQL statement
against your SqlServer database. You can use Access to pass through the query
provided you have permissions against the table(s) in question.
For example ... Alter table tblYours Add Constraint Id_Pk Primary Key
(YourField)

Unfortunately these will all fail if you already have duplicate values so
you may have to clean up your tables first.

Hope this helps