Validation Rule Assistance Needed

[AndrewB]

I would like to use the validation rule of a text box named [Account#] on a
form called [Form1] to validate that the Account# being entered is not
already in [Table1] or [Table2] or [Table3]. If the Account# is found in one
of the 3 tables I would like a msgbox to tell me which table. If the
Account# in not in any table I would like a msgbox to say "Account# Cleared
for Input".

I made a series of queries to search each table for the Account# which works
fine, but is very crude... I am just looking for a more automated way to do
the same. Any suggestions would be greatly appreciated

Thanks,

 

[pietlinden]

I would like to use the validation rule of a text box named [Account#] ona
form called [Form1] to validate that the Account# being entered is not
already in [Table1] or [Table2] or [Table3].  If the Account# is found in one
of the 3 tables I would like a msgbox to tell me which table.  If the
Account# in not in any table I would like a msgbox to say "Account# Cleared
for Input".

I made a series of queries to search each table for the Account# which works
fine, but is very crude... I am just looking for a more automated way to do
the same.  Any suggestions would be greatly appreciated

Thanks,

why would you have the same value in 3 different tables? Sounds to me
like you have normalization issues. You could use some code in the
BeforeInsert event of the form. (Can't be done directly in a table -
you have to use VBA code.) You could add a single field to one table
that would differentiate the three records, and then put all of the
records in a single table. Then your problem goes away, right?

 

[AndrewB]

Thanks pietlinden...

You know.... That does make sence... You have steered me in another
direction. I think I have a solution to this. Thanks much!
--
AndrewB.

I would like to use the validation rule of a text box named [Account#] on a
form called [Form1] to validate that the Account# being entered is not
already in [Table1] or [Table2] or [Table3]. If the Account# is found in one
of the 3 tables I would like a msgbox to tell me which table. If the
Account# in not in any table I would like a msgbox to say "Account# Cleared
for Input".

I made a series of queries to search each table for the Account# which works
fine, but is very crude... I am just looking for a more automated way to do
the same. Any suggestions would be greatly appreciated

Thanks,

why would you have the same value in 3 different tables? Sounds to me
like you have normalization issues. You could use some code in the
BeforeInsert event of the form. (Can't be done directly in a table -
you have to use VBA code.) You could add a single field to one table
that would differentiate the three records, and then put all of the
records in a single table. Then your problem goes away, right?