A field with no duplicates except for null

[Alp]

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

 

[Allen Browne]

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

 

[Allen Browne]

Naturally, Ignore Nulls needs to be Yes.

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

 

[Alp]

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

 

[Allen Browne]

When does this error occur?

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

 

[Alp]

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

 

[Allen Browne]

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.