Help needed on macro

Sparky

Registered User.
Local time
Today, 15:06
Joined
Jan 17, 2000
Messages
20
I need to write a macro that searches two tables. If the id that i'm searching for doesn't exist in both tables then throw uop a message box saying "not a valid id!".

Please can anyone help

Many thanks
 
Going to need to do it with VBA. Let me know if you need help.
 
if the two tables are exactly the same in structure, just different in contents, you could do it like this:

first make a Union query to add the two tables together; like this:

SELECT Table1.* FROM Table1
UNION SELECT Table2.* FROM Table2;

Save this query as 'BothTables' or something

then you can use DCount in the conditions column of your macro, like this (presumably your search ID is coming from a text box on a form):

If the field in the table is a number type field then:

DCount("[MyFieldNameInTheTable]","[BothTables]","[MyFieldNameInTheTable] = " & Forms![MyForm]![MyTextBox]) = 0

if it's a text field

DCount("[MyFieldNameInTheTable]","[BothTables]","[MyFieldNameInTheTable] like '" & Forms![MyForm]![MyTextBox] & "'") = 0

But Pdx is right, VBA is easier for this kind of thing.

Mike
 

Users who are viewing this thread

Back
Top Bottom