I am designing a data entry form and would like to prevent users from
entering duplicate entries in the underlying table. A record would be
considered a duplicate if it has two fields (date and officename)
equal to a record already in the table. I've created a duplicate
record query to search for records that have matching fields, but
cannot determine how to get the form to check the table using the
query. I've tried tying the query to the AfterUdate Event on the
Officename field (the date is entered automatically when the record is
created), but could not get it to work correctly. Any help is
greatly appreciated. Also, any other suggestions on how to better
resolve this is also appreciated.

Thanks,

Jim

There are 2 ways to do this.
The easiest, but not, perhpas, the best, is to make the two fields a
primary key. Or at least a unique index.
You would then get an error message upon saving the record.
But if lots of fields are being entered the user won't know he has a
problem until the end.

The second way is to use a domain aggregate function in the after
update event.

Assume you have a field called x, your date field D and your
officename field O

dim i as integer

i = dcount("x","tablename","d = " & me.date & " and O = '" &
me.officename & "'")

if i >0 then ... ERROR


You might need to put "#" around the date.

Good luck

Ira Solomon

On 27 Apr 2004 11:34:31 -0700, nursingappl (AT) hotmail (DOT) com (Jim) wrote:

Thanks for your reply, Ira. I've incorporated the code that you
suggested, but am still having a couple problems. Because I am not
fluent in VB, could you help me out with a few things...

The Field "X" can be any field that I wish to count, correct? I was
using the index field for the table.

Also, I am getting a debug error relating to the "me.date" entry.
Does that need to be a hard coded date? I want to reference the
current date, which is already listed on the form by the time the user
gets to this field. Does the "me" reference the current form?

Also, you included some single quotes in the code. Are they in the
correct spots? They don't seem to be causing a problem, but I just
want to make sure.

Thanks again,

Jim

Ira Solomon <isolomon (AT) solomonltd (DOT) com> wrote

Hi:

Yes, X is any field in the table. For Dcount any field will do.

"Me" does refer to the current form. So if your date field is named
"CurrentDate" then you would use: me.currentdate.

Yes, the single quotes are required for strings within strings.

Good luck
Ira Solomon
On 4 May 2004 10:38:54 -0700, nursingappl (AT) hotmail (DOT) com (Jim) wrote: