Check For Value in a Table

[crhodus]

How can I check to see if a value exist in my table with VBA? This is what I would like to be able to do, but I may be way off.

IF [Forms]![frmTransfer].[MyValue] NOT IN tblCompany.CompNum THEN
ELSE


Thanks!

 

[pdx_man]

Check out the DLookup function under help.

If the function returns a value, it's there, if it returns null ...

 

[crhodus]

I entered the following code for the onclick event for one of my forms.

If DLookup("[ContactID]", "Contacts", "[ContactID] = " & [Forms]![frmTransNotesHist].[ContactID]) Then
MsgBox "is there"
Else
MsgBox "not there"
End If

When the code is executed, I receive the following message:
The expression yu entered as a query parameter produced this error. 'The object doesn't contain the Automation object '123'."

(Note: 123 is the number that I entered into the ContactID field on the form. )

I don't exactly know how everything is working.

 

[SteveA]

Is ContactID a numeric or alphanumeric field? Also you might want to remove the square brackets from around your Form and field names ie Forms!Formname.Fieldname.

HTH
SteveA

 

[crhodus]

I have ContactID set as a string in my table. Whenever I enter an alphanumeric ID, I receive the following message:
The expression yu entered as a query parameter produced this error. 'The object doesn't contain the Automation object '123'."

Whenever I enter in a numeric ID, I receive this message:
Data type mismatch in criteria expression.

I also took out the brackets in the expression.

 

[The_Doc_Man]

That message makes it look like Access is interpreting the value as a variable.

Try

If DLookup("[ContactID]", "Contacts", "[ContactID] = """ & [Forms]![frmTransNotesHist].[ContactID] & """") Then
MsgBox "is there"
Else
MsgBox "not there"
End If

Getting the quotes right is always a bummer for text fields. NOTE: If the value itself could contain quotes, this won't work right either.

 

[crhodus]

I made the changes you suggested and it seemed to help.

If I enter a numeric value, the "is there" and "not there" works correctly. If I enter an alphanumeric ContactID that is not in the database, it works correctly, BUT if I enter an alphanumeric ContactID that is in the database, I receive a Type MisMatch Error. Weird.

[This message has been edited by crhodus (edited 11-01-2001).]

 

[pdx_man]

Use single quotes:

"[ContactID] = '" & [Forms]![frmTransNotesHist].[ContactID] & "'")

 

[crhodus]

I tried changing it to single quotes, but it is still behaving the same way as before. Any other suggestions?