A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to avoid duplicate based on 2 fields.?



 
 
Thread Tools Display Modes
  #1  
Old April 1st, 2009, 04:52 PM posted to microsoft.public.access.forms
2vao
external usenet poster
 
Posts: 18
Default How to avoid duplicate based on 2 fields.?

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.






  #2  
Old April 1st, 2009, 07:26 PM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default How to avoid duplicate based on 2 fields.?

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" wrote in message
...
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.








  #3  
Old April 2nd, 2009, 09:02 AM posted to microsoft.public.access.forms
2vao
external usenet poster
 
Posts: 18
Default How to avoid duplicate based on 2 fields.?

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" wrote:

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" wrote in message
...
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.









  #4  
Old April 2nd, 2009, 03:25 PM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default How to avoid duplicate based on 2 fields.?

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" wrote in message
...
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" wrote:

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" wrote in message
...
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.











  #5  
Old April 2nd, 2009, 04:16 PM posted to microsoft.public.access.forms
2vao
external usenet poster
 
Posts: 18
Default How to avoid duplicate based on 2 fields.?

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.

"Jeff Boyce" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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.












 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:57 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.