Prevent Duplicates

[TomC]

Hello, I am using a form which consists of a parent and one or many
children. The sub-forms are tied together with a Primary Key. The child
record contains an Auto Number, the Parent Primary Key, and a Date (short
format) among other elements. Anyway, my problem is how do I prevent the
keying of multiple children records with the same date? Thanks for any help
you may provide. hvc

 

[Mike Painter]

Hello, I am using a form which consists of a parent and one or many
children. The sub-forms are tied together with a Primary Key. The child
record contains an Auto Number, the Parent Primary Key, and a Date (short
format) among other elements. Anyway, my problem is how do I prevent the
keying of multiple children records with the same date? Thanks for any
help
you may provide. hvc

Index the date and don't allow duplicate is the easiest way. This is a no
code solution.
You can use a query to search for the date and disallow the duplicate.

 

[TomC]

Thanks Mike for replying. I do have other parent records which will have the
same date. If I index the date, will that preclude entries for just that
parent are all parents? hvc

 

[John W. Vinson]

Hello, I am using a form which consists of a parent and one or many
children. The sub-forms are tied together with a Primary Key. The child
record contains an Auto Number, the Parent Primary Key, and a Date (short
format) among other elements. Anyway, my problem is how do I prevent the
keying of multiple children records with the same date? Thanks for any help
you may provide. hvc

Open the child table in design view and create a new Index. Put some
distinctive name (DupDates say) in the left column of the index toolbox, and
the ParentPrimaryKey field in the right. On the next line down leave the left
column blank and put the datefield (which should NOT be named Date, that's a
reserved word!!) in the right column. Check the "Unique" checkbox.

Now you won't be able to create duplicates on the combination of the fields.

 

[Mike Painter]

Thanks Mike for replying. I do have other parent records which will
have the same date. If I index the date, will that preclude entries
for just that parent are all parents? hvc

Oops.
You should have an index on the date and it could be a two part index using
the parent ID and date.
If not a query using the same criteria is what you want.

 

[TomC]

John,

Your solution sounds very simple, but I am also simple and inexperienced and
could not follow you suggestion. I opened the child table in design view and
created a new index and named it "DupDates" - I got that far. But then you
referred to the left column of the index toolbox and the Primary Key in the
right, and I got lost. And then you went on about the next line?? After I
added the "DupDate" element to the child table, I didn't know where I should
go next, because the left/right and next line instructions have confused me.
I would greatly appreciate it if you could sort of clarify a bit.
Thanks, hvc

 

[John W. Vinson]

Your solution sounds very simple, but I am also simple and inexperienced and
could not follow you suggestion. I opened the child table in design view and
created a new index and named it "DupDates" - I got that far. But then you
referred to the left column of the index toolbox and the Primary Key in the
right, and I got lost. And then you went on about the next line?? After I
added the "DupDate" element to the child table, I didn't know where I should
go next, because the left/right and next line instructions have confused me.
I would greatly appreciate it if you could sort of clarify a bit.

DupDate needs to be in the first row in the column labeled "Index Name"; the
foreign key field name goes in the first row of the column labeled "Field
name". The second row of the grid should have a blank under "Index Name" and
the datefield name under "Field Name". You can leave the default on the "Sort
Order" column. Be sure to set the "Unique" property to Yes.

If this is still confusing, drop me an email at jvinson <at> wysard of info
<dot> com (make the obvious edits) I'll send you a screenshot. I'm not sure
how to phrase it in words any better!

 

[TomC]

John, your solution to my problem worked. Thanks for all your help. I have
one final question. In my indexes I have "DupDate" and the primary key on
the first line, and I left the first column of the second line blank with
"DupDate" in the next column. This works, but I had to change the input
entry in the form from Date to DupDate. I would like to keep the element
name "Date" and have it contain the same date as DupDate, because I reference
it in many places that are not automatically changed when the table is
changed. Any suggestions?? Thanks again

 

[John W. Vinson]

John, your solution to my problem worked. Thanks for all your help. I have
one final question. In my indexes I have "DupDate" and the primary key on
the first line, and I left the first column of the second line blank with
"DupDate" in the next column. This works, but I had to change the input
entry in the form from Date to DupDate. I would like to keep the element
name "Date" and have it contain the same date as DupDate, because I reference
it in many places that are not automatically changed when the table is
changed. Any suggestions?? Thanks again

Go to the effort to change it. And don't store the same data redundantly in
two different fields.

Having a field named Date *WILL* cause problems when you create queries or
reports or forms. Access will confuse it with the builtin Date() function and
give you incorrect results.

 

[TomC]

I can't tell you how much I appreciate the advice I have received from this
discussion group. It is like working in an office with experts around who
can answer the technical questions. The "Help" key just doesn't do it for
me, and this discussion group has never failed. It has saved me so much time
from going around in circles. I appreciate you all very much. Thanks again.
hvc

 

[Mike Painter]

John W. Vinson wrote:

Having a field named Date *WILL* cause problems when you create
queries or reports or forms. Access will confuse it with the builtin
Date() function and give you incorrect results.

As John says, It *WILL* but you never know *WHEN* and it it can be hard to
find the reason a bit down the line.
Not that it ever happened to me!