By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,483 Members | 1,039 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,483 IT Pros & Developers. It's quick & easy.

Duplicate Entry Warning Message

P: 6
I'm trying to get a duplicate entry into a textbox flagged as a before_update warning message. The field is "Hospital_Number" within the table "Voc Rehab DB". The Hospital Number is a text field since hospital numbers can include things like: U/G39401

At the moment I have the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Hospital_Number_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If Nz(DCount("Hospital_Number", "Voc Rehab DB", "Hospital_Number = '" & Me.Hospital_Number.Text & "'"), 0) > 0 Then
  4.         If MsgBox("The value you are adding already exists do you wish to continue?", vbOKCancel, "Duplicate Warning") = vbCancel Then
  5.             Cancel = True
  6.             Me.Undo
  7.             Exit Sub
  8.         End If
  9.     End If
  10. End Sub
  11.  
Please help!
Aug 21 '15 #1
Share this Question
Share on Google+
11 Replies


Seth Schrock
Expert 2.5K+
P: 2,941
You need to tell us what is happening. Off the top of my head, nothing looks wrong with your code. Are you getting an error message or is it not doing what you want to happen?
Aug 21 '15 #2

P: 6
Sorry, yes, the code causes it to come up with an error message whatever is entered into the textbox. Even if there isn't an existing duplicate for that particular hospital number.
Aug 21 '15 #3

Seth Schrock
Expert 2.5K+
P: 2,941
What is the exact error message and number? Also, what line gets highlighted if you click the Debug button on the error?
Aug 21 '15 #4

P: 6
Once again, sorry, I need to be clearer!

Every time a number is entered it delivers the MsgBox containing "The value you are adding already exists, do you want to continue?".

This pops up even when you are entering a completely new hospital number and not a duplicate.
Aug 21 '15 #5

Seth Schrock
Expert 2.5K+
P: 2,941
Try removing the Nz() function from around your DCount() function. I don't think that this would break it, but DCount() never returns null, so it isn't needed either.

Also, remove the .Text at the end of your control reference.
Aug 21 '15 #6

P: 6
I removed the .Text as suggested as well as trying removing the Nz() function. However, removing the latter bracket led to an error code stating it expected a "Then or a Go To Statement". Removing the earlier end bracket was allowed but then returned the error: "Compile Error: Wrong number of arguments or Invalid Property Assignment".

This is now the code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Hospital_Number_BeforeUpdate(Cancel As Integer)
  2.  
  3.     If DCount("Hospital_Number", "Voc Rehab DB", "Hospital_Number = '" & Me.Hospital_Number & "'", 0) > 0 Then
  4.         If MsgBox("The value you are adding already exists do you wish to continue?", vbOKCancel, "Duplicate Warning") = vbCancel Then
  5.             Cancel = True
  6.             Me.Undo
  7.             Exit Sub
  8.         End If
  9.     End If
  10. End Sub
  11.  
Aug 26 '15 #7

Seth Schrock
Expert 2.5K+
P: 2,941
You didn't remove the , 0 that is part of the Nz() function. So your first IF statement line should be
Expand|Select|Wrap|Line Numbers
  1. If DCount("Hospital_Number", "Voc Rehab DB", "Hospital_Number = '" & Me.Hospital_Number & "'") > 0 Then
Aug 26 '15 #8

P: 6
Thank you Seth, that prevented the error message but the same problem still applies in that every time I enter ANYTHING in for hospital_number (text field) it generates the message box that is only supposed to warn you if a duplicate already exists. Any ideas? The code as it stands is as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Hospital_Number_BeforeUpdate(Cancel As Integer)
  2.  
  3.    If DCount("Hospital_Number", "Voc Rehab DB", "Hospital_Number = '" & Me.Hospital_Number & "'") > 0 Then
  4.         If MsgBox("The value you are adding already exists do you wish to continue?", vbOKCancel, "Duplicate Warning") = vbCancel Then
  5.             Cancel = True
  6.             Me.Undo
  7.             Exit Sub
  8.         End If
  9.     End If
  10.  
  11. End Sub
  12.  
Aug 26 '15 #9

Seth Schrock
Expert 2.5K+
P: 2,941
Try the following code just to troubleshoot what is going on:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Hospital_Number_BeforeUpdate(Cancel As Integer)
  2.     Dim strCriteria As String
  3.  
  4.     strCriteria = "Hospital_Number = '" & Me.Hospital_Number & "'"
  5.     Debug.Print strCriteria
  6.     If DCount("*", "Voc Rehab DB", strCriteria) > 0 Then
  7.         If MsgBox("The value you are adding already exists do you wish to continue?", vbOKCancel, "Duplicate Warning") = vbCancel Then
  8.             Cancel = True
  9.             Me.Undo
  10.             Exit Sub
  11.         End If
  12.     End If
  13.  
  14. End Sub
  15.  
This will output the criteria to the Immediate window (press Ctrl + G to open it if it isn't already). Please post back what it outputs.
Aug 26 '15 #10

P: 6
Have done as you suggested and the same problem still arises. In the Immediate Window it just comes up with whatever I had entered. For example:

Hospital_Number = 'RTRTRTRTRT'

It even still generates the MsgBox when I input punctuation which is never normally included in any patient's hospital_number. I wanted to make sure it wasn't because every individual digit/letter had appeared in a hospital number at some point.
Aug 26 '15 #11

Seth Schrock
Expert 2.5K+
P: 2,941
Type the following into the immediate window and tell me what returns:
Expand|Select|Wrap|Line Numbers
  1. ?DCount("*", "Voc Rehab DB", "Hospital_Number = 'RTRTRTRTRT'")
Aug 26 '15 #12

Post your reply

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