Enforcing unique pairs of data using count function?

[securitywyrm]

I'm working on a database where I have entries of "Fiscal year" and "ProgramID". I want to enforce unique pairs, meaning that one shouldn't create multiple entries with the same fiscal year and programID.

I've seen a few strange coding solutions out there, and I was wondering if the following would work.



In the table, have a calculated field that is "FiscalYear & ProgramID", let's call it "UniquePairValidate"
In the form, have an 'after update' event on both FiscalYear and ProgramID where it runs a COUNT query on the table for UniquePairValidate value. If the number is greater than 1, a duplicate exists.

My questions

1. Does this have to be a separate SQL query, or can it be done in the visual basic?
2. Is there anything that would block this from working?

[June7]

Can set a compound index in table and duplicate pairs will not be allowed.

I would use domain aggregate function instead of recordset in VBA. Research DCount and DLookup.

The calculated field is not necessary.

[securitywyrm]

Can set a compound index in table and duplicate pairs will not be allowed.

I would use domain aggregate function instead of recordset in VBA. Research DCount and DLookup.

The calculated field is not necessary.

Thank you. "Compound index" was the term I needed to find the guides.
Oh my gosh I'm so excited that this is going to work!

[orange]

see http://www.geeksengine.com/article/c...ex-access.html
for example/usage