Reduce duplicate entries in my database

[Ben]

Hi,
This may be a little difficult to do, but I would really appreciate this
functionality in my form. I am a relative newcomer to access so please bear
with me.
I have a database which contains thousands of records in the 'data elements'
table. Each of these records must be linked to an 'interface' in a seperate
table. As you can imagine, we have a problem in entering duplicate data
elements into the data elements table. There is no way for us to see if a
similar element has been entered previously.
What would be nice is to have a subform on the elements entry form which
will show in datasheet view any records which meet criteria as the new record
is being entered. For example 'matches the first four letters of stored
element'.

This is the only idea I have had for this. How can the entry of duplicates
be minimised? I am relatively unfamiliar with code but I would love to give
some of it a try. Thanks in advance. -Ben

 

[Jeff Boyce]

Ben

It might help to get a bit more description of specific "data elements" and
"interfaces" -- it sounds a little like these two entities have a one-to-one
relationship, so a description of your data structure might help, too.

Another approach might be to have the current data elements listed (say, in
a combo box) and have the user start typing. The AutoExpand property of the
combo would jump to the first "data element" matching the characters entered
so far.

Regards

Jeff Boyce
<Access MVP>

 

[Ben]

The aim is to have the data element link to many interfaces. We receive about
600 interfaces which need all data elements contained in the interface to be
analysed. Currently we simply look at the interface and document all data
elements contained. This leads to a one to one relationship, but it also
means that of the 12,000 elements entered, my guess is that 40-50% are
duplicates. I would like to normalise this data and link one element to many
interfaces. Is the autoexpand function the only way to achieve this? I would
like to see more information on the data element, rather than just the name,
so I can be a bit more accurate in selecting an identical data element. Hope
this is a little clearer. Cheers.

 

[Ben]

Just discovered exactly what I was looking for thanks to you Jeff- works
great! Just one short coming though, if I type in a data element that is not
in the combo box list (i.e not a duplicate) then how can I get Access to
create a new record in the form where the rest of the data can be entered?