How to avoid duplicate based on 2 fields.?

[2vao]

Hi,

I have 2 fields that I want to be evaluated to avoid duplicates.

First field is called ArrDate (Date),and 2nd field is Ldg(Number). What I
would like to achieve is to get a message if only both fields are equal.
Eg: ArrDate Ldg rcdID (PK)
02-04-09 2 1
02-04-09 5 2
02-04-09 2 3 ( This is duplicate of record with PK
1 ).
How would the code before update of the form be ?

Many thanks.

 

[Jeff Boyce]

It sounds like you want to have a way to know after the fact. Wouldn't that
mean your user would have to enter the complete (new/proposed) record before
learning that what they chose for ArrDate and Ldg was already taken? If so,
that sounds quite user-unfriendly.

Are you open to alternate approaches?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[2vao]

Thanks Jeff, yes I would like an alternative, at the moment I can avoid the
duplicates using indexes ( you are absolutely correct about the steps ), but
I prefer that the moment the users choose the date and ldg they get the
duplicate message.

 

[Jeff Boyce]

One way to approach this is to use a combobox for [ldg] (and a textbox
first, for [YourDate]).

In the AfterUpdate event on the textbox, requery the combobox for [ldg].

The query underlying the [ldg] combobox includes a selection criterion that
points to the textbox on the form, so it will only return [ldg] values that
already exist for the date entered.

Then, if you set the LimitToList property of the combobox to Yes, and add in
a NotInList event procedure, your users will either automatically see an
already existing combination of YourDate and ldg, or they can be prompted to
add a new [ldg] value. (check Access HELP for code examples using
NotInList).

By the way, if you use [date] as the name for your date-related field,
Access may get confused, thinking you mean the Date() function. You (and
Access) will be less confused if you change the title of that field to
something more descriptive ... for example, DateOfBirth is more descriptive
than Date as a field name.

Regards

Jeff Boyce
Microsoft Office/Access MVP


The user begins typing in the

 

[2vao]

Thanks Jeff, I followed your advice with slight modification that suits my
case and it is working. The bottom line is that your recommendation works,
however I m getting a new problem with the combo so I will start a new thread
under a title "assigning a value from a combo" . It would be great if could
have a look at it.

Many thanks.

One way to approach this is to use a combobox for [ldg] (and a textbox
first, for [YourDate]).

In the AfterUpdate event on the textbox, requery the combobox for [ldg].

The query underlying the [ldg] combobox includes a selection criterion that
points to the textbox on the form, so it will only return [ldg] values that
already exist for the date entered.

Then, if you set the LimitToList property of the combobox to Yes, and add in
a NotInList event procedure, your users will either automatically see an
already existing combination of YourDate and ldg, or they can be prompted to
add a new [ldg] value. (check Access HELP for code examples using
NotInList).

By the way, if you use [date] as the name for your date-related field,
Access may get confused, thinking you mean the Date() function. You (and
Access) will be less confused if you change the title of that field to
something more descriptive ... for example, DateOfBirth is more descriptive
than Date as a field name.

Regards

Jeff Boyce
Microsoft Office/Access MVP


The user begins typing in the

Thanks Jeff, yes I would like an alternative, at the moment I can avoid
the
duplicates using indexes ( you are absolutely correct about the steps ),
but
I prefer that the moment the users choose the date and ldg they get the
duplicate message.