Unique key allows entry in form

[Phil]

Hi,
I have a table with 3 fields, Key (auto number primary Key), Item (text),
and Detail (text). I also have a second key with fields Item and Detail.
This second key has "unique' set to yes.
I have a form based on this table and in this form I can add more than one
record with the same Item and Detail. Why is my unique key not preventing by
duplicate entries?

 

[Ofer Cohen]

Not sure why that happen, but why you are not defining this two fields as
your primary key.

Setting the Index True with duplicate will define each field separetly and
not both together, define this two fields as your primary key and leave the
AutoNumber as informative field only any way there will be no duplicates in
this field (after all access assign the number to it).

 

[Phil]

HI,
Thanks for your response. When I set the two fields as the key I get an
error that a key value can not be NULL. My first field (Item) is required to
have data, but the second field (Detail) may not have data. How do I get
around the NULL problem?
Thanks again

 

[Ofer Cohen]

Not sure if it's the best solution, but key fields or Index can't contain a
Null value.
You can set the default property of the field to "", so it will insert an
empty value instead of Null.

Keep in mind that if you want to search for empty values in this field, the
criteria will be (= "") and not (Is Null)

Again, Im not sure its the best solution

 

[Phil]

Thanks Ofer,

That's just what I needed. It works great now.
Thanks again