If in...

[GLW]

I am working in access 97 and trying to compare a new
entry on a form to a record in an alternate table. I've
tried putting something like this in the afterupdate event
but it errors when it hits the 'In'. What's the right way
to do this?
If Me![EmpID] In("SELECT [EmpID] FROM Alt_Table") Then
msgbox "This record already exists in another table."
End If

 

[Douglas J. Steele]

VBA doesn't actually know anything about tables or recordsets unless you
tell it about them. Just giving it the SQL isn't enough.

Try the following:

If DCount("*", "Alt_Table", "[EmpID] = " & Me![EmpID]) > 0 Then
msgbox "This record already exists in another table."
End If

That assumes that EmpID is a numeric field. If it's text, try

If DCount("*", "Alt_Table", "[EmpID] = " & Chr$(34) & Me![EmpID] & Chr$(34))

0 Then

msgbox "This record already exists in another table."
End If


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I am working in access 97 and trying to compare a new
entry on a form to a record in an alternate table. I've
tried putting something like this in the afterupdate event
but it errors when it hits the 'In'. What's the right way
to do this?
If Me![EmpID] In("SELECT [EmpID] FROM Alt_Table") Then
msgbox "This record already exists in another table."
End If

 

[Allen Browne]

You cannot use a SQL statement within VBA code like that.
You can open a recordset, but if you just want to see if the value is in the
table, DLookup() would do.

If Not IsNull(DLookup("EmpID", "Alt_Table", "EmpID = " & Me.EmpID)) Then

If you need help forming the 3rd argument for DLookup() see:
http://members.iinet.net.au/~allenbrowne/casu-07.html

 

[GLW]

Thanks guys.