Help

[sods]

i have never used VBA in access usually i use macros but i think i should be able to cope with it.

What i need help on is this...
when a text box is updated with text it looks for it in a seperate table on the database if it finds it it brings up a messagebox.
so i have a form and it records Data in a table called "IN"
if i type "John" in the form, then John is saved in the "IN" table, feild = "Name" (Name record is an infinate amount)

now i have another table say "not allowed in" and in that table i have a feild called "not allowed name"
One of the records in "not allowed name" = "Kirt"

If i type "Kirt" in the form "IN" a simple msg box will apprear like "Dont allow them in"

I was wondering if this could be carrried out via a VBA script, Please help.

 

[PHV]

Have a look to the DLookUp function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sods]

THX PHV, ok i have looked at DLookUP and it almost works correctly when it should show the msg it pops up with a runtime error 13.
Can any one help, its the 1st time ive tried this so im sorry for silly questions.
Both NoWarrantySerial and Serial_Number, entered in the text box are text feilds. i can only assume the code thinks that "Serial_Number" is a number rather than text.

/Code
Private Sub Serial_Number_AfterUpdate()
If DLookup("NoWarrantySerial", "NoWarrantyTVs", ("NoWarrantySerial = Serial_Number")) Then
MsgBox "Serial was found in the No Warranty list."
End If
End Sub
/end of Code

 

[sods]

Oh i forgot to add the runtime error 13 said it was a type mismatch. i know it wasnt going to be easy thx in advance for any help.

 

[dhookom]

Try this code changing to DCount() and > 0 after the DCount(). I think this is what you want.

Private Sub Serial_Number_AfterUpdate()
    If DCount("NoWarrantySerial", "NoWarrantyTVs", "NoWarrantySerial =""" & Me.Serial_Number" & """") > 0 Then
        MsgBox "Serial was found in the No Warranty list."
    End If
End Sub

Also, consider using the TGML markup which is now very easy in Tek-Tips. Just select your text and click an icon

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Change this:
If DLookup("NoWarrantySerial", "NoWarrantyTVs", ("NoWarrantySerial = Serial_Number")) Then
with this:

If DLookup("NoWarrantySerial", "NoWarrantyTVs", "NoWarrantySerial='" & Me!Serial_Number & "'") = Me!Serial_Number Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sods]

i have Access 2003 does that matter? As im getting errors.... mummmmy

 

[sods]

Ahhhhh PHV u are a star!!!!!!!!!!!!!!!!!!!
Thx Dood worked like a treat... Rock on!

 

[Andrzejek]

I would strongly suggest reading the 2 links PHV has in his signature.

How Do I Get Great Answers To my Tek-Tips Questions? and
How can I maximize my chances of getting an answer?

That knowledge will make your (and everybody else here at TT) life a LOT easier

And, welcome to Tek-Tips


Have fun.

---- Andy