Redundancy Issue

I’ll apologize up front; I’m not quite sure where to put this. It is an issue I’m having with a form, but it involves SQL and VBA…

I need some help with an access 2007 project I’ve been working on. I’m new to access, SQL and VBA so I’m not really sure how to make it work correctly. Here is the issue:

The form “frmAddCitationsToDissertations” does not work the way it needs to. It currently allows identical records to be made in the tblCitations. This is not correct, what I need it to do is before creating a new record look at the values from the 3 drop down records and see if that value set already exist, if it does then it should enter the existing CitationID and DissertationID in the join table. If it does not exist, then it should create a record in the tblCitations and insert the new CitationID along with the dissertationID into the join table.

The logic goes as follows:

A dissertation can have N citations.

A citation may be sited N times by any single dissertation.

Two citations may have any two of the 3 attributes in common and not be the same record, but if it has all 3 attributes in common then it is the same citation and a new record should not be created in the tblCitations, it should only be created in the join table. Two of the 3 attributes are able to be blank or null, so if a citation is simply “The bible” and the other 2 attributes are the same then I need to be able to compare it as well, and ascertain that it is the same citation.

I’m hoping for a programmatic solution, I don’t want some long involved process with forms because this database will end up having more than 50,000 citations. A long process will increase the expense of data entry exponentially.

Any advice that someone can offer is greatly appreciated. If you’re interested in helping an old dog learn new tricks you can download the database I’m working on here: http://c-helm.com/access/