Should be easy... checking for an existing record

[cdmonroe]

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

[Jim Doherty]

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

Cheryl

There are a number of ways you can do this. Depending on the design of your database (in terms of the SQL statement supporting the recordsource of your form) ie: you could have a query as the recordsource, where the query is a combination of the patients table and whatever table you are actually entering data into. In this scenario there would need to be a one to many relationship defined between the primary key (presumably SubNo) in the patients table and the SubNo (Foreign key) in the table you are actually entering data into.

The practical effect of this visibly on screen is that the moment you enter a subno into the subno field on the form Access 'automatically' looksup the value of that which you have typed in, and returns any match in the patients table.

You see the practical effect of this if you place the patients table (subno) on the form also. You would test for where there was nothing returned ie: a NULL value in the 'after update' event of of the subNo field and if so display a message box prompting that their exists No such patient having the number just entered and displaying a pop up form to enter the values for a new patient

Bottom line! look at the AUTO LOOKUP feature of Access it may be all you need in this scenario

Jim