Access Table Validation Help!!

Im trying to include validation for a field in one table to not allow an entry if that entry does not appear in another table.

EG: When entering in a serial number in a cost table, it will not allow the row to be saved, if that serial number does not appear in the revenue table.

EG: (Validation rule for the cost table's serial num field)...
[Table Cost].[Serial Num] = [Table Revenue].[Serial Num]

Can you do this??? Can I reference another table in a validation rule???

I have not used very many validation rules in my databases. I have used VBA code because the validation was too complicated for a rule. All that said to say I'm not sure what the limits are on validation rules. In the case you are describing, I would write a VBA function that would return True if the SerialNum was found in the Revenue table. Of couse, the function would return False if the SerialNum was not found in the Revenue table.

Ok cool.... How do you write VBA code in access???

I can do VB.NET and such but I dont know where to go in access to write such a script?? Or do I have to be in the .NET environment???

Thanks for all your help again by the way!! :)

Quick answer is Ctrl G gets you into the VBA environment. But just what to do once you are there is much more like VB6 than VB.net.

You could redesign your Cost table. Make your serial number a lookup field. Use the Revenue table as the source to look up. Finally, restrict Lookup values to only those from the lookup table (this will not allow to enter anything that is not in the related Revenue table). Basically your creating a relationship between both tables, and enforcing it via the table's design. This way, whenever you drag'n drop the serial number onto a form, a combo box is generated aut. (without having to do any VBA code).:).

Notes : You might get a warning when saving your changes to the Cost table, this might occur if there is data in the Cost table whose serial num. is not present in the Revenue table. Ignore these warnings. Changes should save OK, and validation will take place for new records, not for existing records.