First of all, I would keep the mainkey and the attachment number in separate fields.

If there is no need for the end-user to see the attachment number, you could use a single sequence for all attachments:

MainKey  Attachment
   1         1
   2         2
   1         3
   3         4

This fulfils the condition that the attachment has a unique number. It is easier to create this sort of sequences.

If for any reason you don't like this, you will have to check the highest attachment number for a given MainKey:

select max(attachment) as maximum;
  from MyTable;
  where MainKey = lnKey;
  into cursor Temp
? Temp.Maximum