macro condition

[Guest]

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

 

[Steve Schapel]

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

 

[Guest]

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.

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

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

 

[Guest]

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,

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

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

 

[Steve Schapel]

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?

 

[Guest]

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.

 

[Guest]

Oh yeah, I forgot the name of the table is Employees

 

[Steve Schapel]

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?"

 

[Guest]

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

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

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]

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.

 

[Guest]

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,

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

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]

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'.

 

[Guest]

Yes. The record is not being duplicated but the number is.

 

[Steve Schapel]

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?

 

[Guest]

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]

Katherine,

I gave you the email address before. Send to 'steve at mvps dot org'.
(I used the "at" instead of @ and "dot" instead of . to avoid the spam
harvesters )

 

[Steve Schapel]

Darn, then I got it wrong! Send to 'steves at mvps dot org'.

 

[Guest]

Hi Steve, I just wanted to know if you received the data base.

Katherine Clark

 

[Steve Schapel]

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?

 

[Guest]

I don't think its too big. Am I to type the email as you type it?