null value in compound foreign key with referential integrity enforced raises error

[meridianPhone]

Microsoft Access 2007
No forms are involved.

I am trying to enforce referential integrity between two tables, but only if there is a non-null value in one of the foreign fields of a compound index. I am not having any success.

Tables 1 and 2 are related such that each record in table 2 is related to some record in table 1. Table 3 is the table with the issue. All records in table 3 must be related to a record in table 1 but need not be related to a record in table 2 unless the opCode field is Not Null. The problem seems to be that unless all of fields in the foreign key for table 3 are Null then they are collectively evaluated against table 2.

Is there any way around this?

Table 1
locNum (Primary Key)
locCode (Primary Key)
locDesc

Table 2
locNum (Primary Key)
locCode (Primary Key)
subLocCode (Primary Key)
subLocDesc

Table 3
recId (Primary Key)
locNum (Required)
locCode (Required)
subLocCode (Not Required, no default value)
recDesc

thanks,

Paul