A field with no duplicates except for null

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alp - 02 Jan 2005 05:57 GMT

Hi Experts,

Is setting such a field possible via table structure? Or should it be done
via data verification on data entry form?

TIA

Alp

Reply to this Message

Allen Browne - 02 Jan 2005 06:51 GMT

To create a unique index that ignores nulls:

1. Open your table in design view.

2. Open the Indexes dialolg (View menu)

3. Enter an index name (typically same as field), and choose the field name.

4. In the lower pane of the Indexes dialog, set these properties:
Primary No
Unique Yes
Ignore Nulls No

> Hi Experts,
>
> Is setting such a field possible via table structure? Or should it be done
> via data verification on data entry form?

Reply to this Message

Allen Browne - 02 Jan 2005 07:19 GMT

Naturally, Ignore Nulls needs to be Yes.

> To create a unique index that ignores nulls:
>
[quoted text clipped - 13 lines]
>> done
>> via data verification on data entry form?

Reply to this Message

Alp - 02 Jan 2005 08:09 GMT

Hi Allen,

Thanks for the advice. How can I (or should I) trap its error? Now the form
returns a "You can't go to the specified record" attempting to go to next
record which doesn't help the user much other than puzzling.

Thanks,

Alp

> Naturally, Ignore Nulls needs to be Yes.
>
[quoted text clipped - 15 lines]
> >> done
> >> via data verification on data entry form?

Reply to this Message

Allen Browne - 03 Jan 2005 05:09 GMT

When does this error occur?

The Error event of the form is generally the place to trap engine-level
errors.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps.org.

> Hi Allen,
>
[quoted text clipped - 26 lines]
>> >> done
>> >> via data verification on data entry form?

Reply to this Message

Alp - 03 Jan 2005 08:18 GMT

It occurs when going to the next (or proevious) record if you enter an
already existing figure to the form.

There are no error numbers indicated so I don't know how to trap this. Would
it be possible to use the error message's text? i.e. If Error = "You can't
go to the specified record" ... or such?

Otherwise I'm afraid I might end up trying a form based validation that
searches wether the entered value already exists in the table.

Alp

> When does this error occur?
>
[quoted text clipped - 31 lines]
> >> >> done
> >> >> via data verification on data entry form?

Reply to this Message

Allen Browne - 03 Jan 2005 11:12 GMT

The best solution is to explicitly save the record before your code tries to
do anything that requires a save, e.g. moving, filtering, closing, changing
sort order, reassigning RecordSource, Requery, ...

If you use the Error event of the form, you can set a breakpoint (F9) and
discover what the DataErr is. AFAIK, they are not listed anywhere, but some
common ones are:
3022 - duplicate index;
3201 - related record required;
2113 - wrong data type.

You can use the BeforeUpdate event of the form to check for record-level
validation issues.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps.org.

> It occurs when going to the next (or proevious) record if you enter an
> already existing figure to the form.
[quoted text clipped - 47 lines]
>> >> >> done
>> >> >> via data verification on data entry form?

Reply to this Message