Have you created a relationship linking the two tables by agreement number? If so, the Go To Related Record script step can bring up what you want. This is a good one to read about in the help file before you try to use it. Also you may need to test for whether a related record exists before using this step if there is any chance that there is no related record.
Here's another approach.
Set variable [$AgreementNo, table1::Agreementnumberfield]
Go To Layout [Your detail layout]
Enter find mode  deselect the pause option
set field [table2::agreementnumberfield, $agreementNo]
set error capture [on]
Perform find 
Set error capture [off]
if [get(foundcount) = 0]
show custom dialog ["record not found"]