Solved

Test whether a name already exists

Posted on 2014-01-28
4
341 Views
Last Modified: 2014-01-28
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.
0
Comment
Question by:Fritz Paul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39814594
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
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 39814595
Hi,

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

Regards
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39814597
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
 

Author Closing Comment

by:Fritz Paul
ID: 39814626
Thanks!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question