469,271 Members | 1,668 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

How to override validation rules?

I am creating a data base in access 2003 and I was hoping you could help me out with a question I have.

In my table [Log] I have fields for first name, middle initial, and last name. My primary key is an autonumber, but I have several names that appear exactly the same. Some of the entries are duplicates but not all them. Can I create a validation rule that checks for unique values in the combination of the three fields? Also I wanted my validation text to read "Duplicate entry. Continue?" so that if it is two different people we can still enter the data. I know it is possible to override validation rules in a macro but I'm not sure how to do it. I also tried using the Dlookup function but I had already entered over 9000 applicants into my table before realizing my mistake, which I am currently correcting.

Thanks for your time, any suggestions will be greatly appreciated!
Oct 21 '10 #1

✓ answered by ADezii

Wendy, I'm not exactly sure what is going on, so I created a Demo (Attachment) for you to illustrate my point. Simply Add another Record to the Employees Form with the same First Name, Last Name, and MI, then see what happens.

23 4307
dsatino
393 256MB
Are you entering the data directly into the table or via a form?
Oct 21 '10 #2
ADezii
8,800 Expert 8TB
  1. In the BeforeUpdate() Event of the Form, check for duplication based on the First Name, Last Name, and MI.
  2. If Duplication exists on the combination of those 3 Fields, see if the User wants to Save the Record anyway.
  3. If the User does not wish to enter the Duplicate, Cancel the Updating (Save Record) of the Form.
  4. Below, find Demo Code which illustrates these points. Make your own changes based on Field Names, Table Name, etc.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_BeforeUpdate(Cancel As Integer)
    2. Dim strCriteria As String
    3. Dim intResponse As Integer
    4.  
    5. strCriteria = "[LastName] = '" & Me![txtLastName] & "' AND [FirstName] = '" & Me![FirstName] & _
    6.               "' AND [MI] = '" & Me![MI] & "'"
    7.  
    8. 'See if a Record exists with exactly the Same First Name, Last Name, and MI
    9. If DCount("*", "Employees", strCriteria) > 0 Then
    10.   intResponse = MsgBox("Duplicate Entry, Continue?", vbQuestion + vbYesNo, "Duplicate Entry")
    11.     If intResponse = vbNo Then Cancel = True
    12. End If
    13. End Sub
P.S. - You could also create a Table-Level Validation Rule, but that would 'never' allow a Duplicate Record based on the 3 Fields.
Oct 21 '10 #3
dsatino
393 256MB
ADezii's method will work for you (as is the usual case), but you'll need to expand on it a bit.

This method will get you to the point in which you can tell whether the same name exists or not. But since you have occurrences of duplicate names that are valid, you'll need some functionality that allows you to decide whether your current entry is actually a duplicate of the one(s) that currently reside in your table.
Oct 21 '10 #4
Thank you for such a quick reply. I'm new to access and would appreciate step by step instructions of where to input code if its not too much to ask! I tried entering the code into the beforeupdate event under the expression and code builder but its not working. Should I build this in a macro or Am I subsituting incorrect changes in the code?

Table = Log
Form = Log
Fields: First Name, MI, Last

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer) 
  2. Dim strCriteria As String 
  3. Dim intResponse As Integer 
  4.  
  5. strCriteria = "[Last] = '" & Me![txtLast] & "' AND [First Name] = '" & Me![First Name] & _ 
  6.               "' AND [MI] = '" & Me![MI] & "'" 
  7.  
  8. 'See if a Record exists with exactly the Same First Name, Last Name, and MI 
  9. If DCount("*", "Log", strCriteria) > 0 Then 
  10.   intResponse = MsgBox("Duplicate Entry, Continue?", vbQuestion + vbYesNo, "Duplicate Entry") 
  11.     If intResponse = vbNo Then Cancel = True 
  12. End If 
  13. End Sub
Thanks again,
WO
Oct 21 '10 #5
ADezii
8,800 Expert 8TB
What is the nature of the Error that you are receiving?
Oct 21 '10 #6
colintis
255 100+
Just my guess, are you putting the codes ADezii provided directly into some places that's within another event (or Sub procedure)? As from the codes you posted:

Table = Log
Form = Log
Fields: First Name, MI, Last

These are to be invalid if you didn't specify clearly what they are.
Oct 22 '10 #7
When I put the code (exactly as I have it in my last post) into an expression builder and test it by typing a duplicate name nothing happens at all. when I put it into a code builder and type even just the first name the error message says

Microsoft Can't find the Marco. The marcro (or it's macro group) doesn't exist, or the macro is new but hasn't been saved. Note that when you enter the marcogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under.

When I put this code into the conditions in a macro it is truncated bc it is too long.

The
table = Log ect. only refers to the names on my table form and fields.

very lost, thanks for the help!
Oct 22 '10 #8
ADezii
8,800 Expert 8TB
Wendy, I'm not exactly sure what is going on, so I created a Demo (Attachment) for you to illustrate my point. Simply Add another Record to the Employees Form with the same First Name, Last Name, and MI, then see what happens.
Attached Files
File Type: zip Duplicates.zip (118.8 KB, 92 views)
Oct 22 '10 #9
NeoPa
32,171 Expert Mod 16PB
Wendy, what you describe sounds like the event procedure you have created, and which is correctly in your module, is not linked in to the event, but some macro is instead. To test this take the following steps :
  1. Open the form in Design Mode.
  2. Ensure the Properties Pane is showing (Alt-Enter).
  3. Ensure the Tab is either Event or All.
  4. Scroll down to the Before Update property.
  5. Check that it says [Event Procedure]. Exactly that. As a string. It will always be available from the drop-down to the side too if you prefer that.
  6. Other options may be available in the drop-down if you have macros available. Don't select one of these otherwise all the code you've just set up will be bypassed completely.
Let us know what you found and how well it's working after you changed it to say [Event Procedure] ;-)

PS ADezii's code is spot on, and should work perfectly for you.
Oct 22 '10 #10
Thanks for that it was a big help. I'm so close now but there is one flaw:

When I enter a duplicate name and press tab or enter, there is no error message, but when I delete the first name and re type it then the message appears. Not sure what the reason could be for this problem.
Oct 22 '10 #11
NeoPa
32,171 Expert Mod 16PB
I think you need to describe the situation more clearly Wendy. Precise logical thinking is not easy with ambiguous descriptions.
Oct 22 '10 #12
Also, just discovered this if I type any first name the duplicate error apears even if I type nnghagak nonsense
Oct 22 '10 #13
Sorry about that let me try to explain this a little better. I test the code in my form (Log) by typing a name that already exists, when I move to another record the beforeupdate event does not occur. However, when I delete the first name and retype it in the same record, the beforeupdate event occurs. Hope I'm not testing your patience I really appreciate the help!
Oct 22 '10 #14
ADezii
8,800 Expert 8TB
Wendy, it sounds like you have the proper code, but the wrong BeforeUpdate() Event. The code must be placed in the BeforeUpdate() Event of the Form and NOT in the BeforeUpdate() Event of either the First Name, Last name, or MI Fields. Worse case scenario, can you Upload your Database? The only thing that we would need would be the Form (with code contained within), and the underlying Data Source.

P.S. - Have you even looked at the Demo that I sent you?
Oct 22 '10 #15
OK I will try that out, and yes the demo is what got me this far. Again thanks for the help, I've never worked with code before so it is all very unfamiliar to me. I will work it out from here.
Oct 22 '10 #16
ADezii that was exactly my problem you are genius! Thank you Thank you a million times!
Oct 22 '10 #17
ADezii
8,800 Expert 8TB
You are quite welcome, Wendy, and way to hang in there! You go girl! (LOL).
Oct 22 '10 #18
NeoPa
32,171 Expert Mod 16PB
Nice catch ADezii. That didn't occur to me unfortunately.

PS. Wendy, may I suggest you set ADezii's post #3 as Best Answer ;-)
Oct 22 '10 #19
Hello again my patient friends! I have discovered a slight problem: Entering names with an apostrophe, such as O'Sullivan, seems to disrupt the code. Any way to correct this?
Oct 25 '10 #20
ADezii
8,800 Expert 8TB
Hello Wendy. I realize that you are not that proficient in programming, at least not yet (LOL). The problem that you are describing relates to the manner in which Access handle 'Quotes' in Strings, under certain circumstances. I've corrected the problem for you, assuming that a Quote may only exist in a Last Name. Look at the revised Code carefully, and try to determine what is happening when it encounters a Quote in the Last Name Field, but better yet, download the Revised Attachment. The last Employee on the Form is Robert A O'Brian. Try to duplicate this Employee exactly, and see what happens. Any questions, we are here.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Dim strCriteria As String
  3. Dim intResponse As Integer
  4.  
  5. If InStr(1, Me![txtLastName], "'") > 0 Then         'Quote in Last Name?
  6.   strCriteria = "[LastName] = '" & Replace(Me![txtLastName], "'", "'" & "'") & "' AND [FirstName] = '" & _
  7.                 Me![FirstName] & "' AND [MI] = '" & Me![MI] & "'"
  8. Else
  9.   strCriteria = "[LastName] = '" & Me![txtLastName] & "' AND [FirstName] = '" & Me![FirstName] & _
  10.                 "' AND [MI] = '" & Me![MI] & "'"
  11. End If
  12.  
  13. 'See if a Record exists with exactly the Same First Name, Last Name, and MI
  14. If DCount("*", "Employees", strCriteria) > 0 Then
  15.   intResponse = MsgBox("Duplicate Entry, Continue?", vbQuestion + vbYesNo, "Duplicate Entry")
  16.     If intResponse = vbNo Then Cancel = True
  17. End If
  18. End Sub
Attached Files
File Type: zip Duplicates_2.zip (120.1 KB, 98 views)
Oct 25 '10 #21
Not proficient doesn't really cut it lol. The revised code worked perfectly. As always very grateful.

Wo

PS where can I learn more on my own?
Oct 25 '10 #22
ADezii
8,800 Expert 8TB
PS where can I learn more on my own?
Introductory Classes would be a good start. In my case, it was books, books, and more books...
Oct 25 '10 #23
NeoPa
32,171 Expert Mod 16PB
SQL Injection Attack may be a little overboard for this question, but may be worth a look nevertheless.

ADezii's solution to replace all quotes with two quotes is the sensible one. Using Replace({Value}, "'", "''") should always work and causes no problems even if there is no quotes in the {Value} string.
Oct 25 '10 #24

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Dalan | last post: by
reply views Thread by ssmith86 | last post: by
7 posts views Thread by Tom | last post: by
3 posts views Thread by tsnyder | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.