If in...

  • Thread starter Thread starter GLW
  • Start date Start date
G

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
 
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
Click to expand...
msgbox "This record already exists in another table."
End If


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


GLW said:
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
Click to expand...
 
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
 
Back
Top