This should be an easy one, but I haven’t been able to figure it out.

I have a DB that has a register table and a memo table.  There should only be one record in the memo table for each field in the register table.

I would like to have a sub form (Memo) pop-up from a main form (Register) using a command button.  I have been able to use the following code to open the pop-up form and populate the ID field to match the main form.  

Private Sub Command142_Click()

   DoCmd.OpenForm "frm_memo", , , , acFormAdd, acWindowNormal, Me.ID

End Sub

The following code is in the sub-form on load property:

Private Sub Form_Load()

   If Not IsNull(Me.OpenArgs) Then Me.ID = Me.OpenArgs
   
End Sub

The problem I’m having is that I only want to add the ID if there isn’t a record for this ID already; I don’t want duplicate entries.  I want to search for the ID and pull the matching record and populate the ID field ONLY when no matching fields are found.

Any help would be appreciated.

Thanks
Susan

---

Answer from Richard Rost:

There are a couple of methods you could use. The BEST method would be to use a DLOOKUP first to see if that record exists. It would be something like:

X = Nz(DLOOKUP("ID","frm_memo","ID=" & ID))
If X = 0 then
   ' record not found
else
  ' record found
end if

You can use the DLOOKUP function to look up a specific value from a table or query.

Please see the following FREE video tutorial on my web site for step-by-step instructions on how to use DLOOKUP:

http://599cd.com/tips/access/dlookup-function?key=BlFAlEx