I'd create another table (junction) and relate them together.
Then you could just do a DCOUNT to see if there are any.

I would put the code in the Before Update event, not the After Update event.

Dear Alex

I know Dcount in preventing duplicate from Richard's extended cut video, it prevents duplicate from 2 fields. However, the drug items are from one field (they are not duplicate, just do not want item 1 and 2 appear in a single purchase)

Do you have any link for me to learn what you meant by "create another table (junction) and relate them together" ?
thanks

Many-to-Many

Hi John, this is admittedly not an easy task, but it's not terribly difficult, just a little trickery in query design.

First, you design tables for the various drug interactions.  See my table setup diagram below.  It's pretty straightforward here.  You enter an InteractiveID for each interaction and its description.  Then, create a detail table that stores all the drugs involved in each interaction.  For instance, in my diagram, Drug 1 and 2 both have the same InteractionID of 1, and so on.  I'm no pharmaceutical expert, so this is just what I think it may look like in real life.

Note that each drug may involve in multiple different interactions.  In my example, Interaction ID 1 involves Drug 1 and 2; ID 3 involves Drug 1 and 4; and ID 4 has Drug 1 and 5.  But one thing is for certain (I think): When you look up Drug 5, you want Drug 1 to be flagged as having an interaction with Drug 1; and when you look up Drug 1, you also want Drug 5 to be among the flagged drugs.  In short, you want some kind of "two-way" lookups.  Whatever you look up, you want to see all related interactions.  But DLookup() only works one-way.  If you need two-way lookups, you likely need two separate DLookup().  So how do we do this?

See the query setup diagram below for my suggestion.  You create a query by adding *the same table twice* with *no relation*.  You essentially want the table to do lookups "on itself."  That is how "two-way" lookups are possible.  The two tables can't have the same name in the query.  So, when you drag in the InteractionDetail twice, Access automatically renames the second one by adding "_1" at the end, as shown in my diagram.

When two tables have no relationship, then every record in one table "is related" to every record of the second table.  And that is how we compare every record against each other.

Then you have the query check two conditions:
(1) The InteractiveID in one table is equal to the InteractiveID of the other table.  This is because drugs that are involved in the same interaction share the same InterativeID.
(2) The drug name in one table is NOT equal to the drug name of the other table.  This is needed because you don't want repeated drug names to be shown (since each record is related to all records, as I said).

The final step is to use "SELECT DISTINCT", because, again, you don't want repeated data.  In the query's property sheet, setting "Unique Value" to Yes does the same thing.

The final query result is as shown.  As you see, it allows two-way lookups.  If you look up Drug 1, you will find Drug 5, and vice versa.  Every drug you look up, you'll find the other drugs that share an interaction with it.

Just to be complete, as a final step, you set up a query that does the actual lookup.  See my picture below.  You link the tricky query in my last post to the InteractionHeader table.  This time you do need a relationship.  Set a condition to look up, say, Drug 4.  The query will return all the drugs that have an interaction with Drug 4.  

And since two-way lookups are possible, you can have:

     WHERE DrugName1 = "Drug 1" AND DrugName2 = Drug 4"

or

     WHERE DrugName1 = "Drug 4" AND DrugName2 = Drug 1"

and you will get the same info: interaction shared by Drug 1 and 4.

Here's how I accomplished this task (see screenshot).

Thank you all you guys, special thanks to Teachers Kevin Yip and Kevin Robertson. Both methods works well in solving my "difficult question". Finally I adopt the approach of Teacher Robertson as it suits lazy guy like me.