How to override validation rules?

[Wendy Osullivan]

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!

[dsatino]

Are you entering the data directly into the table or via a form?

[ADezii]

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.
   

   Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim strCriteria As String
   Dim intResponse As Integer
    
   strCriteria = "[LastName] = '" & Me![txtLastName] & "' AND [FirstName] = '" & Me![FirstName] & _
                 "' AND [MI] = '" & Me![MI] & "'"
    
   'See if a Record exists with exactly the Same First Name, Last Name, and MI
   If DCount("*", "Employees", strCriteria) > 0 Then
     intResponse = MsgBox("Duplicate Entry, Continue?", vbQuestion + vbYesNo, "Duplicate Entry")
       If intResponse = vbNo Then Cancel = True
   End If
   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.

[dsatino]

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.

[Wendy Osullivan]

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

Private Sub Form_BeforeUpdate(Cancel As Integer) 
Dim strCriteria As String 
Dim intResponse As Integer 
 
strCriteria = "[Last] = '" & Me![txtLast] & "' AND [First Name] = '" & Me![First Name] & _ 
              "' AND [MI] = '" & Me![MI] & "'" 
 
'See if a Record exists with exactly the Same First Name, Last Name, and MI 
If DCount("*", "Log", strCriteria) > 0 Then 
  intResponse = MsgBox("Duplicate Entry, Continue?", vbQuestion + vbYesNo, "Duplicate Entry") 
    If intResponse = vbNo Then Cancel = True 
End If 
End Sub

Thanks again,
WO

[ADezii]

What is the nature of the Error that you are receiving?

[colintis]

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.

[Wendy Osullivan]

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!

[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.

[NeoPa]

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.

[Wendy Osullivan]

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.

[NeoPa]

I think you need to describe the situation more clearly Wendy. Precise logical thinking is not easy with ambiguous descriptions.

[Wendy Osullivan]

Also, just discovered this if I type any first name the duplicate error apears even if I type nnghagak nonsense

[Wendy Osullivan]

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!

[ADezii]

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?

[Wendy Osullivan]

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.

[Wendy Osullivan]

ADezii that was exactly my problem you are genius! Thank you Thank you a million times!

[ADezii]

You are quite welcome, Wendy, and way to hang in there! You go girl! (LOL).

[NeoPa]

Nice catch ADezii. That didn't occur to me unfortunately.

PS. Wendy, may I suggest you set ADezii's post #3 as Best Answer ;-)

[Wendy Osullivan]

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?

[ADezii]

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.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String
Dim intResponse As Integer
 
If InStr(1, Me![txtLastName], "'") > 0 Then         'Quote in Last Name?
  strCriteria = "[LastName] = '" & Replace(Me![txtLastName], "'", "'" & "'") & "' AND [FirstName] = '" & _
                Me![FirstName] & "' AND [MI] = '" & Me![MI] & "'"
Else
  strCriteria = "[LastName] = '" & Me![txtLastName] & "' AND [FirstName] = '" & Me![FirstName] & _
                "' AND [MI] = '" & Me![MI] & "'"
End If
 
'See if a Record exists with exactly the Same First Name, Last Name, and MI
If DCount("*", "Employees", strCriteria) > 0 Then
  intResponse = MsgBox("Duplicate Entry, Continue?", vbQuestion + vbYesNo, "Duplicate Entry")
    If intResponse = vbNo Then Cancel = True
End If
End Sub

[Wendy Osullivan]

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?

[ADezii]

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...

[NeoPa]

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.