Should be easy... checking for an existing record

Hi all, I need help on something that seems like it should be easy to
do.

When entering new data onto any form in my database the field [SubNo]
has to exist in another table (tblPatients). If a SubNo is entered
that isn't already on tblPatients, I want it to open the form
frmNewPatient to add the patient to tblPatients; If the SubNo is
already in tblPatients, I want it to do nothing. I can't figure out
what the condition should be in a macro to make this happen. Any
suggestions?

Thanks!!

Cheryl

re: Should be easy... checking for an existing record

Put the following code in AfterUpdate event of [SubNo]:

If DCount("[SubNo]","TblPatients","[SubNo] = " &
Forms!NameOfYourForm![SubNo]) = 0 Then
DoCmd.OpenForm "FrmNewPatient"
End If

The above assumes [SubNo] is numeric. If it is text, change the first line
to:
If DCount("[SubNo]","TblPatients","[SubNo] = '" &
Forms!NameOfYourForm![SubNo] & "'") = 0 Then

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
resource@pcdatasheet.com


<Cheryl.D.Monroe@gmail.comwrote in message
news:1187814332.893854.271910@i13g2000prf.googlegr oups.com...

re: Should be easy... checking for an existing record

"Steve" <sorry@private.emailaddressschreef in bericht news:13cpi9o4t55ssea@corp.supernews.com...
--
This is to inform 'newbees' here about PCD' Steve:
http://home.tiscali.nl/arracom/whoissteve.html
Until now 3650+ pageloads, 2350+ first-time visitors (these figures are rapidly increasing)

Why is this ???
Because Steve is the ONLY person here who continues to advertise in the groups.

It is not relevant whether he advertised in *this* particular post or not...
==We want him to know that these groups are *not* his private hunting grounds!

For those who don't like too see all these messages:
==Simply killfile 'StopThisAdvertising'.
Newbees will still see this warning-message.

ArnoR