Prevent Blank and/or Duplicate records

  • Thread starter Thread starter Core.Group
  • Start date Start date
C

Core.Group

Hello,
I have a fairly large database that I have been working on for the past year
and have recently begun allowing some users access for testing input. Entry
problems have developed such as users entering data, saving the record,...
then, instead of Deleting the record they will remove the data from the
individual fields and save it that way, leaving a blank record.

Another problem is that they will add a duplicate record just to have the
data in one field changed. I.e. a third email address. I have provided for
two emails and a memo field for Notes. but if they want to add a third
email, they will add a record with all other fields being equal except for
the email. Therfore I have two records for the same person. (They do the
same for other fields too).

So my question is... How do I prevent blank records and/or duplicate records
from being established.

If more info is required plese let me know.
Thanks Very much!
B. Darby
 
Blank records you can control by adding code to the form's BeforeUpdate
event. In your code, check the value(s) in the fields that are required,
and cancel the update if all are empty.

Preventing "duplicate" records (not truly duplicate, right? they have a
different email address!) is another matter. You could try indexing the
combination of fields that make the record unique (say, name, address, phone
number). But I can break that by altering the spelling of the name, or by
transposing digits in the address or phone number.

One issue you've presented is that users wish to enter more email addresses
than you have fields to hold them. This is (has become) an issue with data
design.

If you were working with a spreadsheet, your only option would be to add
another column. And when users wish to add a fourth, add another column.
And when users wish to add a second, third, fourth and fifth phone number,
add more columns.

But Access is a relational database, and you are not limited to adding
columns. In fact, it is poor relational design to do so. Instead, since
you are describing a one-to-many relationship (person-to-email,
person-to-phone), your table structure needs to reflect this.

Check up on relational design and normalization -- you'll be glad you did!
More work to start with, considerably less work in the long run.

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top