Test whether a name already exists

I have a form with a textbox "Blockname" which is bound to a field "Block" in table "tblBlocks".
Block is supposed to be a unique name.
When I fill in a new "Block", before I commence filling in other applicable data, I want a warning like "This name already exists. You need to enter a uniqe name or press "Escape" to exit."
I will probably use a "before update" action. Is that right?
What does the VBA look like that tests if the name already exists?
Thanks.
Fritz PaulAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Before Update code would look like this:

IF DCount("*","tblBlock", "BlockName = '" & Me.txtBlock & "'") > 0 then
      msgbox  "This name already exists. You need to enter a uniqe name or press 'Escape' to exit."
      Cancel = True
      Me.txtBlock.Undo
End IF

Open in new window

0
 
Rgonzo1971Commented:
Hi,

Maybe by using a validation rule with Isnull(Dlookup()) otr DCount() in your table

Regards
0
 
mbizupCommented:
The above is assuming that Block is a text field.  If it is numeric, you would omit the single quote delimiters:

IF DCount("*","tblBlock", "BlockName = " & Me.txtBlock ) > 0 then
      msgbox  "This name already exists. You need to enter a unique name or press 'Escape' to exit."
      Cancel = True
      Me.txtBlock.Undo
End IF

Open in new window

0
 
Fritz PaulAuthor Commented:
Thanks!
0
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.

All Courses

From novice to tech pro — start learning today.