Validation Rule needed

[Guest]

I have 2 tables, Quotes and Invoices. I have a form to manually input all the
details of an order's quote, including the quote number. Once an invoice has
been received for the order, I have setup an AutoLookup query so that I type
the Quote Number, Access then shows some of the quote's details (ie the price
and items ordered)to show that the correct Quote No has been selected and I
then continue to input the invoice details. How can I setup a validation rule
so that I cannot input a quote number that isn't in the Quote table.

 

[Larry Linson]

I have 2 tables, Quotes and Invoices.
I have a form to manually input all the
details of an order's quote, including
the quote number. Once an invoice has
been received for the order, I have setup
an AutoLookup query so that I type
the Quote Number, Access then shows
some of the quote's details (ie the price
and items ordered)to show that the
correct Quote No has been selected and I
then continue to input the invoice details.
How can I setup a validation rule so that
I cannot input a quote number that isn't in
the Quote table.

Even better, don't give them a chance to enter the wrong quote number -- use
a ComboBox with the LimitToList property set, whose RowSource is a Query
returning distinct quite numbers from the Quote table, from which the user
can only SELECT one of the valid numbers. The default of "yes" for the
Autoexpand property will cause the dropdown list to scroll as the user types
the number; alternatively, the user can scroll the list and just choose. Or,
use a ListBox if the user scrolling the list is your preferred mode of
operation.

You can't do what you want with a "validation rule", but if you want to do
it the hard way, you could write code to read the Quote table and make sure
the number exists after the user types it in. Post back if you are
_compelled_ to deal with it this way and someone will be able to provide
some "air code".

Larry Linson
Microsoft Access MVP