Limiting data input

[JeffT]

Probably simple but I can't figure this out.

I have three tables; jobs, invoices, and payments, all three are related by
a sales order number.

I have a form to enter each job in the jobs table with a subform for
invoices and a subform for payments. I want to limit entries for payments on
this form to match only the invoices related to the sales order number for
each job. Example: SO# 123 has invoice 456, only payment for 456 should be
posted in 123. Right now if someone mistakenly enters payment for 789 on SO#
123 the entry is still allowed because 789 exists in the table invoices. I
want a message to popup that says 789 is not related to SO# 123 and the entry
will not be allowed until the user goes to the SO# that 789 is entered under.

 

[tina]

a lot of form "data entry" issues are actually table/relationship issues.

I have three tables; jobs, invoices, and payments, all three are related by
a sales order number.

exactly *how* are your tables related? the sales order number is the primary
key of which table? and is it a foreign key in both of the other tables?
after answering the previous, forget the tables/relationships for a minute;
how are job, invoices, and payments related in your "real world" process?

hth

Probably simple but I can't figure this out.

I have three tables; jobs, invoices, and payments, all three are related by
a sales order number.

I have a form to enter each job in the jobs table with a subform for
invoices and a subform for payments. I want to limit entries for payments on
this form to match only the invoices related to the sales order number for
each job. Example: SO# 123 has invoice 456, only payment for 456 should be
posted in 123. Right now if someone mistakenly enters payment for 789 on SO#
123 the entry is still allowed because 789 exists in the table invoices. I
want a message to popup that says 789 is not related to SO# 123 and the entry
will not be allowed until the user goes to the SO# that 789 is entered

under.