|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I wanted to know if there is a way to create a macro that can send a message
when data in a specified field is duplicated. I don't want to restrict
duplicates, nor do I want to delete or append them. I simply want there to be
a flag when a duplicate is entered in a specific field.
Please help
|
|
Katherine,
If I understand you correctly, you can make a macro with a MsgBox
action, and assign it on the After Update event of the applicable
control on the form. In the Condition forr the macro, something along
these lines...
DCount("*","YourTable","[YourField]=" & [YourField])>0
--
Steve Schapel, Microsoft Access MVP
Katherine wrote:
[Quoted Text] > I wanted to know if there is a way to create a macro that can send a message
> when data in a specified field is duplicated. I don't want to restrict
> duplicates, nor do I want to delete or append them. I simply want there to be
> a flag when a duplicate is entered in a specific field.
>
> Please help
|
|
Thank you so much for your help. You stated that I should put in the table.
Should I enclose the name of the table in []'s or just the quotes? Also does
there have to be an ! dividing the table from the field name? I'm just asking
because all of the expressions seem to be set up that way. For now I'm going
to set it up as your example is and I'll be back to let you know if it works
or not. Thanks again.
"Steve Schapel" wrote:
[Quoted Text] > Katherine,
>
> If I understand you correctly, you can make a macro with a MsgBox
> action, and assign it on the After Update event of the applicable
> control on the form. In the Condition forr the macro, something along
> these lines...
> DCount("*","YourTable","[YourField]=" & [YourField])>0
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Katherine wrote:
> > I wanted to know if there is a way to create a macro that can send a message
> > when data in a specified field is duplicated. I don't want to restrict
> > duplicates, nor do I want to delete or append them. I simply want there to be
> > a flag when a duplicate is entered in a specific field.
> >
> > Please help
>
|
|
No that's not working for me. I have a data base with a form for entering
data and in the form there is a field called Waterfront Number, I want a
condition that can tell me when the same number that is being entered has
already been entered and give me a message. I know how to set up the massage
action but I can't figure out the condition that will lead to the message
action.
"Steve Schapel" wrote:
[Quoted Text] > Katherine,
>
> If I understand you correctly, you can make a macro with a MsgBox
> action, and assign it on the After Update event of the applicable
> control on the form. In the Condition forr the macro, something along
> these lines...
> DCount("*","YourTable","[YourField]=" & [YourField])>0
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Katherine wrote:
> > I wanted to know if there is a way to create a macro that can send a message
> > when data in a specified field is duplicated. I don't want to restrict
> > duplicates, nor do I want to delete or append them. I simply want there to be
> > a flag when a duplicate is entered in a specific field.
> >
> > Please help
>
|
|
Katherine,
What is the name of the table thatyour form is based on? Is Waterfront
Number a number or text data type? Just for clarity, forms do not have
fields - fields are in tables or queries, and forms can have controls
(textbox etc) that are bound to the fields in the form's underlying
record source. So is the control on the form also named Waterfront
Number? The idea I suggested should work for this situation - so, what
is the exact DCount() expression you tried?
--
Steve Schapel, Microsoft Access MVP
Katherine wrote:
[Quoted Text] > No that's not working for me. I have a data base with a form for entering
> data and in the form there is a field called Waterfront Number, I want a
> condition that can tell me when the same number that is being entered has
> already been entered and give me a message. I know how to set up the massage
> action but I can't figure out the condition that will lead to the message
> action.
|
|
Your right things are coming back to me now. Ok it is a form and the control
name is Waterfront Number. The field in the table that the data is coming
from is a number field though I don't think that it should be because it is
not a number that will be calculated but i'm sure you know better than I on
that (if it makes a difference). Oh yeah, I actually copied yours into my
condition section of the Macro, then I filled in the name of the table and
the control name. Now the problem may be the way that I'm setting it up.
Should the condition and the action be on the same line or different lines
and since I want the message to appear after the number is entered the second
time should it be >0 or >1.
Thank you a bunch again.
"Steve Schapel" wrote:
[Quoted Text] > Katherine,
>
> What is the name of the table thatyour form is based on? Is Waterfront
> Number a number or text data type? Just for clarity, forms do not have
> fields - fields are in tables or queries, and forms can have controls
> (textbox etc) that are bound to the fields in the form's underlying
> record source. So is the control on the form also named Waterfront
> Number? The idea I suggested should work for this situation - so, what
> is the exact DCount() expression you tried?
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Katherine wrote:
> > No that's not working for me. I have a data base with a form for entering
> > data and in the form there is a field called Waterfront Number, I want a
> > condition that can tell me when the same number that is being entered has
> > already been entered and give me a message. I know how to set up the massage
> > action but I can't figure out the condition that will lead to the message
> > action.
>
|
|
Oh yeah, I forgot the name of the table is Employees
"Steve Schapel" wrote:
[Quoted Text] > Katherine,
>
> What is the name of the table thatyour form is based on? Is Waterfront
> Number a number or text data type? Just for clarity, forms do not have
> fields - fields are in tables or queries, and forms can have controls
> (textbox etc) that are bound to the fields in the form's underlying
> record source. So is the control on the form also named Waterfront
> Number? The idea I suggested should work for this situation - so, what
> is the exact DCount() expression you tried?
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Katherine wrote:
> > No that's not working for me. I have a data base with a form for entering
> > data and in the form there is a field called Waterfront Number, I want a
> > condition that can tell me when the same number that is being entered has
> > already been entered and give me a message. I know how to set up the massage
> > action but I can't figure out the condition that will lead to the message
> > action.
>
|
|
Katherine,
Ok, then I don't see why this won't work...
DCount("*","Employees","[Waterfront Number]=" & [Waterfront Number])>0
The Condition should be on the same line in the macro design as the
Action. I think it should be >0 because at the point where it is being
evaluated, the one that you are currently entering (new record) will not
be saved yet to the table, so won't be counted. So your condition is
really asking "are there already any in the table with this Waterfront
Number?"
--
Steve Schapel, Microsoft Access MVP
Katherine wrote:
[Quoted Text] > Your right things are coming back to me now. Ok it is a form and the control
> name is Waterfront Number. The field in the table that the data is coming
> from is a number field though I don't think that it should be because it is
> not a number that will be calculated but i'm sure you know better than I on
> that (if it makes a difference). Oh yeah, I actually copied yours into my
> condition section of the Macro, then I filled in the name of the table and
> the control name. Now the problem may be the way that I'm setting it up.
> Should the condition and the action be on the same line or different lines
> and since I want the message to appear after the number is entered the second
> time should it be >0 or >1.
> Thank you a bunch again.
>
|
|
Ok, I copied in the expression below and it didn't work when I tested it by
entering a record with the same waterfront number. I had the event on before
update at that time. When I changed the event to on enter and tried again, I
received the error message "syntax Error (missing operator) in query
expression '[waterfront Number]='.
Also when I go back to the macro from the data base window and double click
on it (to run) I get the message 'Can't find name 'Waterfront Number' you
entered in expression.
"Steve Schapel" wrote:
[Quoted Text] > Katherine,
>
> Ok, then I don't see why this won't work...
> DCount("*","Employees","[Waterfront Number]=" & [Waterfront Number])>0
>
> The Condition should be on the same line in the macro design as the
> Action. I think it should be >0 because at the point where it is being
> evaluated, the one that you are currently entering (new record) will not
> be saved yet to the table, so won't be counted. So your condition is
> really asking "are there already any in the table with this Waterfront
> Number?"
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Katherine wrote:
> > Your right things are coming back to me now. Ok it is a form and the control
> > name is Waterfront Number. The field in the table that the data is coming
> > from is a number field though I don't think that it should be because it is
> > not a number that will be calculated but i'm sure you know better than I on
> > that (if it makes a difference). Oh yeah, I actually copied yours into my
> > condition section of the Macro, then I filled in the name of the table and
> > the control name. Now the problem may be the way that I'm setting it up.
> > Should the condition and the action be on the same line or different lines
> > and since I want the message to appear after the number is entered the second
> > time should it be >0 or >1.
> > Thank you a bunch again.
> >
>
|
|
Katherine,
Can you please copy/paste the exact Condition entry you are using?
As I mentioned before, this should be on the After Update event of the
Waterfront Number control.
No, you won't be able to run the macro independently.
--
Steve Schapel, Microsoft Access MVP
Katherine wrote:
[Quoted Text] > Ok, I copied in the expression below and it didn't work when I tested it by
> entering a record with the same waterfront number. I had the event on before
> update at that time. When I changed the event to on enter and tried again, I
> received the error message "syntax Error (missing operator) in query
> expression '[waterfront Number]='.
> Also when I go back to the macro from the data base window and double click
> on it (to run) I get the message 'Can't find name 'Waterfront Number' you
> entered in expression.
>
|
|
I copied the exact expression that you typed. I didn't type it in. I also
changed the event to after update. Is there anyway that I can send you the
data base? I sure that I am missing something simple and that's why its not
working because Just from the responses from you thus far, I know that you
know your stuff.
"Steve Schapel" wrote:
[Quoted Text] > Katherine,
>
> Can you please copy/paste the exact Condition entry you are using?
>
> As I mentioned before, this should be on the After Update event of the
> Waterfront Number control.
>
> No, you won't be able to run the macro independently.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Katherine wrote:
> > Ok, I copied in the expression below and it didn't work when I tested it by
> > entering a record with the same waterfront number. I had the event on before
> > update at that time. When I changed the event to on enter and tried again, I
> > received the error message "syntax Error (missing operator) in query
> > expression '[waterfront Number]='.
> > Also when I go back to the macro from the data base window and double click
> > on it (to run) I get the message 'Can't find name 'Waterfront Number' you
> > entered in expression.
> >
>
|
|
So, do you mean that there *is* an existing record with the number you
are trying to enter, but the message box does not come up?
Can you check this?... Depending on the formatting used in your
newsreader, if you copy/paste from an email or newsgroup message, ""s
can get changed to “”s which can mess Access up.
I am willing to have a look at it if we are still having problems. Send
to 'steve at mvps dot org'.
--
Steve Schapel, Microsoft Access MVP
Katherine wrote:
[Quoted Text] > I copied the exact expression that you typed. I didn't type it in. I also
> changed the event to after update. Is there anyway that I can send you the
> data base? I sure that I am missing something simple and that's why its not
> working because Just from the responses from you thus far, I know that you
> know your stuff.
|
|
Yes. The record is not being duplicated but the number is.
"Steve Schapel" wrote:
[Quoted Text] > So, do you mean that there *is* an existing record with the number you
> are trying to enter, but the message box does not come up?
>
> Can you check this?... Depending on the formatting used in your
> newsreader, if you copy/paste from an email or newsgroup message, ""s
> can get changed to “â€s which can mess Access up.
>
> I am willing to have a look at it if we are still having problems. Send
> to 'steve at mvps dot org'.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Katherine wrote:
> > I copied the exact expression that you typed. I didn't type it in. I also
> > changed the event to after update. Is there anyway that I can send you the
> > data base? I sure that I am missing something simple and that's why its not
> > working because Just from the responses from you thus far, I know that you
> > know your stuff.
>
|
|
Katherine,
Ok, send your database to me.
Just to clarify, this is the scenario?...
When entering a new record, in a particular field you can enter a number
that might already exist in a previous record in the table. If there is
already a record with that number, you want a messagebox to alert you to
this fact, but not necessarily disallow the entry of that number. Am I
right?
--
Steve Schapel, Microsoft Access MVP
Katherine wrote:
[Quoted Text] > Yes. The record is not being duplicated but the number is.
|
|
Yes you're absolutely right. Thanks for all of your help. I'm not sure
however how to send it to you. I need an email address.
"Steve Schapel" wrote:
[Quoted Text] > Katherine,
>
> Ok, send your database to me.
>
> Just to clarify, this is the scenario?...
> When entering a new record, in a particular field you can enter a number
> that might already exist in a previous record in the table. If there is
> already a record with that number, you want a messagebox to alert you to
> this fact, but not necessarily disallow the entry of that number. Am I
> right?
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Katherine wrote:
> > Yes. The record is not being duplicated but the number is.
>
|
|
Katherine,
I gave you the email address before. Send to 'steve at mvps dot org'.
(I used the "at" instead of [ at ] and "dot" instead of . to avoid the spam
harvesters :-) )
--
Steve Schapel, Microsoft Access MVP
Katherine wrote:
[Quoted Text] > Yes you're absolutely right. Thanks for all of your help. I'm not sure
> however how to send it to you. I need an email address.
|
|
Darn, then I got it wrong! Send to 'steves at mvps dot org'.
--
Steve Schapel, Microsoft Access MVP
Katherine wrote:
[Quoted Text] > Yes you're absolutely right. Thanks for all of your help. I'm not sure
> however how to send it to you. I need an email address.
|
|
Hi Steve, I just wanted to know if you received the data base.
Katherine Clark
"Steve Schapel" wrote:
[Quoted Text] > Darn, then I got it wrong! Send to 'steves at mvps dot org'.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Katherine wrote:
> > Yes you're absolutely right. Thanks for all of your help. I'm not sure
> > however how to send it to you. I need an email address.
>
|
|
Ketherine,
No, sorry I did not receive it yet.
Is it a big file? Can you zip it? Did you use the email address
steves-at-mvps-dot-org?
--
Steve Schapel, Microsoft Access MVP
Katherine wrote:
[Quoted Text] > Hi Steve, I just wanted to know if you received the data base.
|
|
I don't think its too big. Am I to type the email as you type it?
"Steve Schapel" wrote:
[Quoted Text] > Ketherine,
>
> No, sorry I did not receive it yet.
>
> Is it a big file? Can you zip it? Did you use the email address
> steves-at-mvps-dot-org?
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> Katherine wrote:
> > Hi Steve, I just wanted to know if you received the data base.
>
|
|
Steve's "munged" his address so that the harvesters won't get it and sell it
to the spammers.
Replace -at- with [ at ], and -dot- with a period.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Katherine" <Katherine[ at ]discussions.microsoft.com> wrote in message
news:007EE94E-703A-4957-B943-F46197E57168[ at ]microsoft.com...
[Quoted Text] >I don't think its too big. Am I to type the email as you type it?
>
> "Steve Schapel" wrote:
>
>> Ketherine,
>>
>> No, sorry I did not receive it yet.
>>
>> Is it a big file? Can you zip it? Did you use the email address
>> steves-at-mvps-dot-org?
>>
>> --
>> Steve Schapel, Microsoft Access MVP
>>
>>
>> Katherine wrote:
>> > Hi Steve, I just wanted to know if you received the data base.
>>
|
|
Katherine,
I have received your database file.
You had told me that the Waterfront Number field is a Number data type.
It isn't. It is a Text data type. Therefore, your macro Condition
shopuld read...
DCount("*","Employees","[Waterfront Number]='" & [Waterfront Number] &
"'")>0
--
Steve Schapel, Microsoft Access MVP
Katherine wrote:
[Quoted Text] > I don't think its too big. Am I to type the email as you type it?
>
|
|
Yes Steve it works now. I thank you so much. This is the first time that I
used this discussion forum and I must say it was well worth it. Thanks again.
Katherine Clark
"Steve Schapel" wrote:
[Quoted Text] > Katherine,
>
> I have received your database file.
>
> You had told me that the Waterfront Number field is a Number data type.
> It isn't. It is a Text data type. Therefore, your macro Condition
> shopuld read...
> DCount("*","Employees","[Waterfront Number]='" & [Waterfront Number] &
> "'")>0
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Katherine wrote:
> > I don't think its too big. Am I to type the email as you type it?
> >
>
|
|
|