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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|