Hi Carl
Take a look at Richards Video Many to Many, for I think you need to create an intermediate table with the DriverId and the ShipperID. You will need to flag only the banned drivers from a given shipper. Not too difficult once you see the many to many in Richards Video
HTH  John

Hi Carl
I build a prototype together for you to get you started. If you send your email address to me at [email protected], I will send it to you. It is not a finished product but it will show you the potential of a many to many relationship. If you need further help, let me know.  John

Hi Carl, I suggest adding a textbox next to the combo box that would show "Banned" depending on the driver selected in the combo box (see picture below).  This textbox would contain a calculated field that would check the selected driver against the selected shippers to determine if the driver is banned.

On your order form, how many shippers can you enter?  Let's say an order can have up to 3 shippers, named ShipperID1, ShipperID2, and ShipperID3.  And the driver's combo box is named DriverID.

If your banned driver list is in a table named BannedDriversT, and it looks like this:

BannedDriverID    BannedByShipperID
121               12
121               30
379               12
402               78
576               5
576               9
576               12
Etc.
(Driver# 121 is banned by shipper# 12 and 30; shipper 12 bans driver 121, 379, and 576, etc.)


Then the textbox that is supposed to show "Banned" should have this formula:

=IIf(IsNull(DLookup("BannedDriverID", "BannedDriversT", "BannedDriverID=" & Nz(DriverID, 0) & " And BannedByShipperID IN (" & Nz(ShipperID1, 0) & ", " & Nz(ShipperID2, 0) & ", " & Nz(ShipperID3, 0))), "", "Banned")

In this expression, the DLookup() function checks if the selected driver AND *any one* of the selected shippers are in the same record in the banned drivers table.  If they do, that means the driver is banned by one of the shippers.  If they don't, DLookup() will return null, and the word "Banned" won't show.  

All the Nz() functions above are used because I assume all the IDs are numeric in your setup and they could be null on the order form if no entry is made.  If they are null, Nz() turns them into zeroes, which can be used to indicate nonexistent driver or shippers -- because autonumber fields (often used by IDs) always start from 1 and could never be zero.

Note that my formula does not involve your driver table, which you say has 50,000 records.  This is to improve performance.  My formula only uses your banned driver list, which I assume is a much smaller table in your database.

Thank you very much Kevin, this works great

Hi, I think you should follow Kevin's advice.  I am happy that you found a solution that works.  John