Cannot allow duplicate records in 2 tables based on 2 criteria

[limhodav]

Dear all,

I would like to seek your help on this problem.

I would like to do a search for my records based on 2 different criterias. If found, the function should just return a boolean value so that I can act further based on the boolean value.

The reason I'm doing this is that I have 2 similar tables that contains EventID and AttendeeID and these 2 tables cannot have the same EventID and AttendeeID. So before I can allow a record to be inserted in the 1st table, it must check that there is no record in the 2nd table that has the same EventID and AttendeID. If there is, then i cannot allow the record to be inserted in the 1st table.

The same thing applies when I want to insert a record in the 2nd table. It must now check the first table.

Recordset.find seems to be only catering to 1 criteria, not 2.

Pls help. Codes are most welcome too.

 

[neileg]

This looks like a design problem if you have two similar tables with fields that have a relationship with each other. If you combine the tables, or at least combine them as far as these two fields are concerned you can create an index on the two fields and disallow duplicates.

 

[limhodav]

Thanks, Neil for the advice. I have already created an index on either table to ensure AttendeeID and EventID is not duplicated within the same table.

I have 2 tables. Attendees and Absentees table. The 1st table contains a list of participants and the latter contains a list of absentees, those who cannot make it. Can these 2 tables be related when the fields must be mutually exclusive.

I have to ensure that both tables cannot have duplicate records based on the 2 fields.

 

[neileg]

Surely attendees and absentees are all people with links to events. Whether they attend or not is just a single piece of data and not a reason for having separate tables.