Field value in a table

[Soma_rich]

Hi, bit new to VBA but learning quickly, I need to know if a value already exists in a table if it doesnt I want to send an email.
Something like this:

If forms![myform].value in table![mytable]column then
docmd.runmacro "sendemail"


I dont know what command to use for the in bit.
Hope you can help.
Rich

 

[boblarson]

Check out DCount

If DCount("[YourFieldNameHere]","YourTableNameHere","[YourOtherFieldName]=" & Forms!YourFormNameHere.YourTextBoxNameHere)=0 Then
...

 

[Soma_rich]

Thanks Bob that looks like exactly what I need. But what is "[YourOtherFieldName]". I have one value in a text box on a form and I want to know if taht is in a column of a table. So i have this but getting errors still:

If DCount("[IPaddress]", "UserInfo", "&Forms!dir2.field15") > 0 Then
DoCmd.RunMacro "SendIP"
End If

Can you help?

 

[rainman89]

[yourotherfieldname] is the field that u want to know if it already exists
so whatever u are checking for to see if it exists

 

[boblarson]

If you are looking up IPaddress, then it could be:

DCount("[IPaddress]","UserInfo","[IPaddress]=" & Forms!dir2.field15")

Normally you check to see if something exists based on another field, like the ID field, so it would be "[UserID]=" & Forms!YourFormName.YourTextBoxName

One other note - you should rename your controls on your forms to something meaningful, like txtIPAddress instead of field15. It will aid anyone who needs to support your database after you are gone, or even you 2 years down the road when you want to remember what it is in the code.

 

[Soma_rich]

OK i know what you mean about field names I will change these when I get them working.

I am still getting a missing opperator in expression error. Looking at your code above you have an odd number of " is this deliberate as that causes me errors also.

Thanks Bob this is really causing me issues and you are certainly helping

If DCount("[IPAddress]", "UserInfo", "[IPAddress]= & Forms!dir2![field15]") = 0 Then

 

[rainman89]

"[IPAddress] = '" & forms!dir2!field15 & "'"

 

[Soma_rich]

Sorted it! It was because the IP address in the form is a sting so need to change it:

If DCount("[IPAddress]", "UserInfo", "[IPAddress] = " & Chr(39) & Forms![dir2].field15 & Chr(39)) = 0 Then