Check if value exists before Insert

I have a form that is a normal insert form and it works fine. But what I need to do is compare one of the values in a text field to make sure it exists in another table before it Inserts.

So basically I am inserting a record into Table B, But I need to make sure that the value entered in one of the text fields is present in Table A before Inserting.

Is there a way to do this?

Thanks,
Brad

you could try adding a recordset using table A, filtered by the post value of your form field

on your page, when the add record form is posted, the recordset will return 0 rows unless you have a match.

if you have a match, do the insert, if not, don't.

Thanks for your advice Craig, but I don't see how that would work?

Creating the recordset for Table A will of course be empty because there is nothing to filter it by until the form is posted.

So I don't see how that can stop the form from inserting if you have to insert it in order for it to get the form value?

If you have the code ordered correctly, the record set will be created before the insert occurs, this will allow you to look up the value in the recordset and only perform the insert if the recordset is not empty.

Originally Said By: Jason Byrnes

  If you have the code ordered correctly, the record set will be created before the insert occurs, this will allow you to look up the value in the recordset and only perform the insert if the recordset is not empty.  

Thanks Jason,

So it would have to be hand coded .... its not something that Data Assist has in it?

Brad

no, no hand codeing needed, but the wizard will not create the it for you.


you just need to create the recordset, set it to filter using the form input.

then edit the insert record behavior and set the trigger to Recordset Not Empty.

Thanks Jason,

Much appreciated, I'll give it a try.

Brad

PS Off topic: Your Framework Extension is fantastic .... its a must have now.

excellent, I'm glad to hear it is working out for you.