Choose Open Form002 or Form003 After Entry

[Guest_imported]

I’m having trouble writing code to do the following:
When data is entered into textboxTableB.FieldX of Form001 I need to check if there is any previous records in TableB.FieldX with a match to that entry. If yes open Form002 WHERE [Form001]![TableB.FieldX]=[Form002] ELSE open Form003 WHERE [Form001]![TableB.FieldX]=[Form003]![TableA.FieldX]

TableA.FieldX(one)
TableB.FieldX(many)

Form001
TextBox TableB.FieldX
Form002
TextBox TableB.FieldX
Form003
TextBox TableA.FieldX

 

[Durkin]

Use the Dlookup function. You can check if the record exists in the table using this. If it does it will return a value, if not it will return a null. So, put something like this in your code:

if isnull(dlookup("FieldX","TableB", "FieldX = " & [Form001]![TableB.FieldX)) then
'open form003
else
'open form00
end if Durkin
alandurkin@bigpond.com

 

[Guest_imported]

Alan,

Thanks for your help. It took me a while of trial and error but I finally got exactly the results I was looking for, you really got me on the right track. I’ve been working with Access 97 for about 2 years but mostly with the wizards. I’m just starting to get into the meat of it. If you or anyone has any recommendations on a good book or books that would help with code and expressions I would really appreciate it.
Thanks again,
Larry