Determining if primary key exists

[wxkeep]

I know this question has been asked numerous times - but I have to be honest - I've become very confused reading through posts looking for answers.

I have an unbound form. Where the user is going to create a new record in tbl2. tbl2 has a field that is linked to a primary key in tbl1. And referential integrity exists - so I can't add the record to tbl2 if the appropriate key doesn't exist in tbl1.

My thought was to pull up a simple form that would allow the user to select from a combobox one of the existing primary keys in tbl1 - or if they know its a new key to just go ahead and type the new key in - and then the appropriate information for tbl2.

The thing I want to avoid is if they go ahead and type a key into the textbox (maybe they assume their key is new - but it's really not) - I want it to tell them if that key already exists.

Essentially the form has 1 textbox for a new key - and 1 combobox listing all the existing keys. Then three more textboxes for the information for tbl2. And then a button that, when clicked, will use an SQL INSERT command to put all the information into BOTH tables. First tbl1 - (just the key at this point - they can modify the other information regarding this key later) and then to tbl2 - they linking key and the three pieces of crucial information.

What is the simplest way to do this?

Thanks!

 

[snyperx3]

On the click event of your button before running the sql to insert the information you can use DLookup on the key in tbl1 to see if it exists, and if so then continue with code, otherwise display a msgbox and exit the sub.

http://www.techonthenet.com/access/functions/domain/dlookup.php

for information on DLookup

-Pete

 

[wxkeep]

That's PERFECT! Thank you so much for making this simpler. I hadn't even seen the DLOOKUP option yet!