Unique Value Cross Table

[JessyD]

Good afternoon.

I'm curently building a database and I'm running into a problem.

This is my situation:

We work with goods which are always packed into bundles. Upon creation bundles get an unique number, this way we are able to track the bundles during the proces.

Goods have 2 ways of arriving:
1) Bought. Directly after buying the goods are packed into bundles.
2) Produced. In the earlier process materials get made into goods, after which the goods are packed in bundles.

The user can select the number of the bundle. However, there needs to be a double check: Number doesnt exist in 'Bought' and number doesnt exist in 'Produced'. I figured out the first control by simply making the field value unique in the respective tables.

I can't figure out how to do the second control though.

Example:
Image
It's not possible to combine the two tables in my original database. This is a simplified example. BundleNr should be unique cross Bought and Produced.

Thanks in advance.

 

[Micron]

The user can select the number of the bundle. However, there needs to be a double check: Number doesnt exist in 'Bought' and number doesnt exist in 'Produced'. I figured out the first control by simply making the field value unique in the respective tables.

I don't understand what you are trying to do. If each table has it's own bundle number, and you're basing combo row source values on bundle numbers (one for each table) then why do you want to pick a value that doesn't exist in one or the other? If I pick bundle number 12 from [Bought] and move to the next control, what am I doing? What do I want to see? Every bundle number but 12? You need to explain more about what you have and what you are trying to achieve, but be thorough and explicit.