compare values and send alert

Have data in table
i need an way to alert me when the data is compare

for best example i have table tdata
in tdata field A have value ABCD , EFGH,  KLMN
is for example ABCD is identify  need an alert me

any idea??
STEVE PERRYAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you need to tell us which environment you want so that it will alert you accordingly?

let's say in an Access form, you can try use Dcount function:
if DCount("*", "tdata", "A = 'ABCD' ") > 0 then
    msgbox "Alert me... "
end if

Open in new window

will this make sense to you?
STEVE PERRYAuthor Commented:
Ryan Chong
tnks !! good friend !

have formDATA
but how i put that code to work in ??
STEVE PERRYAuthor Commented:
yes make sense ! to me
but how implement it ?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can try to use the Form_Current() event in your form accordingly.

(image copied from Internet)
form_current eventin the VBA code, try paste above codes accordingly into Form_Current() event.
STEVE PERRYAuthor Commented:
Ryan Chong

because  a situation in here
the value from fieldA comes from an expression in an query
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
ok, the DCount function should work well for query as well...

did you encounter any errors so far?
STEVE PERRYAuthor Commented:
HAVE PROBLEMS
BECAUSE IS WORKING WITH EVRY RECORD NO JUST THE VALUE IM LOOKING FOR!
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
can you explicitly tell us what you intend to show/ how will it be shown when:

1. one record is detected that met the criteria
2. multiple records are detected that met the criteria
STEVE PERRYAuthor Commented:
Ryan Chong
heres is an sample
when
one ofthe code is set i need an alert

but is working with all the record no just the trigger record!
DATASAMPLE.accdb
STEVE PERRYAuthor Commented:
Ryan Chong
DO I EXPLAIN GOOD ?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I will have a look later as there's no MS Access installed in my current working machine.
STEVE PERRYAuthor Commented:
thanks very much good friend !
STEVE PERRYAuthor Commented:
by the way Ryan Chong

if i have an number field like 708 or 600
and i want just tke the cero from thet number  to be show in other field
is that possible ?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>but is working with all the record no just the trigger record!
so i guess you only want to check if the "current record's AB field = 78 ?

if yes, then you can try like:
Private Sub Form_Current()
    If AB.Value = 78 Then
        MsgBox "Alert me this code is on "
    End If
End Sub

Open in new window

>>and i want just tke the cero from thet number  to be show in other field
ok, so if field1 contains 708, what you want to show in field2 ?
DATASAMPLE_b.accdb
STEVE PERRYAuthor Commented:
Ryan Chong

thanks my good angel!

if field is 708 i just want the 0 be in other field2
STEVE PERRYAuthor Commented:
but cero can be in any code
i mean could be 708, 078, 870 ect....
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>if field is 708 i just want the 0 be in other field2

will this field2's value be saved in your table or just for display purposes in your form?
STEVE PERRYAuthor Commented:
Ryan Chong
be saved in table
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>if field is 708 i just want the 0 be in other field2
I guess the "field" refers to "DATA" field?

I have made the following changes accordingly.

1. Add a sample field called "Field2" in Table1
Table12. Update the Query1
Query13. Add Field2 into form FRM_DATA
FRM_DATA14. Add the After Update event for the field "DATA" in form FRM_DATA
Private Sub DATA_AfterUpdate()
    Field2.Value = IIf(InStr(1, DATA.Value, "0", vbTextCompare) > 0, "0", "")
    Form.Refresh
End Sub

Open in new window

DATASAMPLE_c.accdb
STEVE PERRYAuthor Commented:
Ryan Chong
Private Sub Form_Current()
    If AB.Value = 78 Then
        MsgBox "Alert me this code is on "
    End If
End Sub

Open in new window



have a question in here

have so many values will be a long list
there's an way to read it from an table or query
for example i mean the 78 89 90 ect..
the code i need to
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>have so many values will be a long list
>>there's an way to read it from an table or query

yes, that's possible. for the IF statement above it constructs the condition we want to compare with. So:

1. if you need to compare with a range, you can try like:
Private Sub Form_Current()
    If AB.Value >= 78 and AB.Value <= 90 Then
        MsgBox "Alert me this code is on "
    End If
End Sub

Open in new window

2. if you need to compare with a table/query, you can try like:
Private Sub Form_Current()
    if DCount("*", "yourTableorQuery", "AB = '" & AB.Value & "' ") > 0 then
        MsgBox "Alert me this code is on "
    End If
End Sub

Open in new window


try customize accordingly...
STEVE PERRYAuthor Commented:
Ryan Chong

Private Sub DATA_AfterUpdate()
    Field2.Value = IIf(InStr(1, DATA.Value, "0", vbTextCompare) > 0, "0", "")
    Form.Refresh
End Sub

Open in new window


Is working but one issue here
i have an table with data  already
this sub is working for the new data put in

theres an way to have the same result
but with the data already in the table ?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>theres an way to have the same result
>>but with the data already in the table ?

yup, did you manage to see the sample I had uploaded in comment: ID: 40958636? It should able to handle existing records as well.
STEVE PERRYAuthor Commented:
Ryan Chong
I Test it from comment: ID: 40958636

could you please give an check?


i change some  numbers to have 0 value already in the table
taht way it don't work

is because i have  data alredy in with a lot of recods
and i will have to retype one by one
will be difficult
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>is because i have  data alredy in with a lot of recods
>>and i will have to retype one by one
>>will be difficult

For those records that already existed in your table, do you think it would be easy and more feasible if we just run an Update SQL statement to update that column accordingly?

you can try to execute:
Update Table1 set Field2 = 0 Where DATA like '*0*'

Open in new window

you can do that by creating a new Update query.

for more info, see:
http://www.techonthenet.com/access/queries/update1.php
STEVE PERRYAuthor Commented:
Ryan Chong

That's Great !

let me try it..
STEVE PERRYAuthor Commented:
Ryan Chong

UPDATE query is working great !!
but still with one issue
with the AB field this is woking like you say an that's fine

but i will need compare the AB field from table1
to code field table2

is that posible?
DATASAMPLE_b.accdb
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>but i will need compare the AB field from table1
>>to code field table2

sure, that's feasible. Can you give us an example how you want it to be done?

(sorry, I not yet check your attached db in comment: ID: 40961127)
STEVE PERRYAuthor Commented:
Ryan Chong

don't know how to do it

but i need compare values from FIELD AB TO
VALUES IN tables2 if is in an message shoul alert
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>but i need compare values from FIELD AB TO
>>VALUES IN tables2 if is in an message shoul alert

so, you probably can try like this:
in form FRM_DATA:
Private Sub Form_Current()
    if DCount("*", "Table2", "Code = '" & AB.Value & "' ") > 0 then
        MsgBox "Alert me this code is on "
    End If
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
STEVE PERRYAuthor Commented:
Ryan Chong,


IS WORKING  GREAT !!

you my master !
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
glad that i could make some helps cheers!
STEVE PERRYAuthor Commented:
Ryan Chong

keep on the good !
i will miss you !

i have others questions
can we keep here ?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
if you still have a series of questions, I guess it's better to create other posts in case i'm busy and not able to help cheers
STEVE PERRYAuthor Commented:
THE BEST!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.