• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

Write record to table if certain criteria exists

I have a form with a field name txtProdRef which is a text field.

I want to add a record to an unbound table using the following code IF the ProdRef string DOES NOT already exist in tblProducts.  I do not know what code to put before the code below.

        Set RS = CurrentDb.OpenRecordset("tblProducts")
        RS!ProdName = Me.txtfieldname
        RS!FormatID = Me.txtfieldname
        RS!SizeID = Me.txtfieldname
        RS!LocationID = Me.txtfieldname
        RS!ClassID = Me.txtfieldname
        RS!ProdRef = Me.txtfieldname
        RS!ImageID = Me.txtfieldname
  • 2
  • 2
1 Solution
You could use DLookup() or DCount() to determine if the value already exists.  If it doesn't, then you would do the AddNew.  If it does, you would bypass the add and perhaps raise an error message.

A better technique for simple rules like this is to create a unique index on the column in the table.  That way if someone attempts to add a record via any means, the add will fail if the value already exists.
SteveL13Author Commented:
Ok.  I'm trying to use DLookup with this line of code but the syntax must be wrong.

If DLookup("[ProdRef]", "tblProducts" <> Me.txtTransferredProdRef Then

In other words, if no ProdRef in tblProducts equals Me.txtTransferredProdRef then the next line should fire which is a msgbox

You need to include a where argument to tell the DLookup() what record you are interested in.

If DLookup("[ProdRef]", "tblProducts", "ProdRef = " & me.txtTransferredProdRef) <> Me.txtTransferredProdRef

The code assumes that ProdRef is the column name and it is numeric.  If it is text, the syntax would be:

If DLookup("[ProdRef]", "tblProducts", "ProdRef = '" & me.txtTransferredProdRef & "'") <> Me.txtTransferredProdRef

Intellisense should have shown you the correct syntax.  Are you not getting it?
SteveL13Author Commented:
This was it...

If DLookup("[ProdRef]", "tblProducts", "ProdRef = '" & me.txtTransferredProdRef & "'") <> Me.txtTransferredProdRef

Yes, intellisese is working.  I just didn't know how to correct it.

Thanks again.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now