duplicate record error

G

Guest

I would like my users to get an error message when they put in a duplicate
name in my database. I have the database set up so it will not accept
duplicates but I'd still like them to get a message. This is my code that is
attached to an on click:

If Not IsNull(DLookup("Employee", "Census", "employee")) Then
MsgBox "This user has previously been added, please click Existing User on
the previous screen. Click OK to be redirected to the previous screen."
DoCmd.Close
End If
Me![Hide input info].Visible = False

The question is: Why does my error show up whether the name is duplicate or
not.
Employee is the field on the table Census and employee is the combo box on
the form. Oh, and Hide input info is an opaque box - it should disappear if
the above DLookup comes up with nothing.
You all have been so much help to me recently. Thanks again for any help
you can provide! Please let me know if there is any additional information
that would be helpful as well.
-Jenny
 
G

Guest

Hi Jenny,

I don't think your criteria is correct in the DLookup statement, ie.
DLookup("Employee", "Census", "employee")

It is likely picking up the first record in your table. Try printing the
result of this expression to the Immediate Window, by adding this line of
code to the beginning of your procedure:

Debug.Print DLookup("Employee", "Census", "employee")

Run it a few times. Then view the results (Ctrl G). You will likely see the
employee name for the first record in the Immediate Window.

Your procedure should probably be something like this, assuming the name of
the text box on your form is Employee:

Private Sub cmdVerify_Click()
On Error GoTo ProcError

Dim strEmployee As String
strEmployee = Replace(Me.txtEmployee, Chr(39), Chr(39) & Chr(39))

If Not IsNull(DLookup("Employee", "Census", "Employee = '" & strEmployee &
"'")) Then
MsgBox "This user has previously been added, please click Existing User" _
& "on the previous screen. Click OK to be redirected to the
previous screen."

DoCmd.Close acForm, Me.name
End If

Me![Hide input info].Visible = False


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdVerify_Click..."
Resume ExitProc
End Sub


The Replace function is available in Access 2000 and later. If a person's
name includes an apostrophe, such as O'Malley, any domain aggregrate function
(DLookup, DCount, DMin, DMax, etc.) will choke unless you double up the
apostrophe, which is what replacing with two Chr(39) concatenated together
does. (You could also replace with '', but not ". I find Chr(39) easier to
read.

See this article on the MVPS web site for help on DLookup:
http://www.mvps.org/access/general/gen0018.htm


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Jennyrd said:
I would like my users to get an error message when they put in a duplicate
name in my database. I have the database set up so it will not accept
duplicates but I'd still like them to get a message. This is my code that is
attached to an on click:

If Not IsNull(DLookup("Employee", "Census", "employee")) Then
MsgBox "This user has previously been added, please click Existing User on
the previous screen. Click OK to be redirected to the previous screen."
DoCmd.Close
End If
Me![Hide input info].Visible = False

The question is: Why does my error show up whether the name is duplicate or
not.
Employee is the field on the table Census and employee is the combo box on
the form. Oh, and Hide input info is an opaque box - it should disappear if
the above DLookup comes up with nothing.
You all have been so much help to me recently. Thanks again for any help
you can provide! Please let me know if there is any additional information
that would be helpful as well.
-Jenny
Click to expand...
 
G

Guest

On second thought, we need to conditionally execute this line of code:
Me![Hide input info].Visible = False

by using the Else statement:

Private Sub cmdVerify_Click()
On Error GoTo ProcError

Dim strEmployee As String
strEmployee = Replace(Me.txtEmployee, Chr(39), Chr(39) & Chr(39))

If Not IsNull(DLookup("Employee", "Census", "Employee = '" & strEmployee &
"'")) Then
MsgBox "This user has previously been added, please click Existing User" _
& "on the previous screen. Click OK to be redirected to the
previous screen."

DoCmd.Close acForm, Me.name
Else
Me![Hide input info].Visible = False
End If


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdVerify_Click..."
Resume ExitProc
End Sub




Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Tom Wickerath said:
Hi Jenny,

I don't think your criteria is correct in the DLookup statement, ie.
DLookup("Employee", "Census", "employee")

It is likely picking up the first record in your table. Try printing the
result of this expression to the Immediate Window, by adding this line of
code to the beginning of your procedure:

Debug.Print DLookup("Employee", "Census", "employee")

Run it a few times. Then view the results (Ctrl G). You will likely see the
employee name for the first record in the Immediate Window.

Your procedure should probably be something like this, assuming the name of
the text box on your form is Employee:

Private Sub cmdVerify_Click()
On Error GoTo ProcError

Dim strEmployee As String
strEmployee = Replace(Me.txtEmployee, Chr(39), Chr(39) & Chr(39))

If Not IsNull(DLookup("Employee", "Census", "Employee = '" & strEmployee &
"'")) Then
MsgBox "This user has previously been added, please click Existing User" _
& "on the previous screen. Click OK to be redirected to the
previous screen."

DoCmd.Close acForm, Me.name
End If

Me![Hide input info].Visible = False


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdVerify_Click..."
Resume ExitProc
End Sub


The Replace function is available in Access 2000 and later. If a person's
name includes an apostrophe, such as O'Malley, any domain aggregrate function
(DLookup, DCount, DMin, DMax, etc.) will choke unless you double up the
apostrophe, which is what replacing with two Chr(39) concatenated together
does. (You could also replace with '', but not ". I find Chr(39) easier to
read.

See this article on the MVPS web site for help on DLookup:
http://www.mvps.org/access/general/gen0018.htm


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Jennyrd said:
I would like my users to get an error message when they put in a duplicate
name in my database. I have the database set up so it will not accept
duplicates but I'd still like them to get a message. This is my code that is
attached to an on click:

If Not IsNull(DLookup("Employee", "Census", "employee")) Then
MsgBox "This user has previously been added, please click Existing User on
the previous screen. Click OK to be redirected to the previous screen."
DoCmd.Close
End If
Me![Hide input info].Visible = False

The question is: Why does my error show up whether the name is duplicate or
not.
Employee is the field on the table Census and employee is the combo box on
the form. Oh, and Hide input info is an opaque box - it should disappear if
the above DLookup comes up with nothing.
You all have been so much help to me recently. Thanks again for any help
you can provide! Please let me know if there is any additional information
that would be helpful as well.
-Jenny
Click to expand...
Click to expand...
 
G

Guest

Oh happy day! Thanks so much for your help!!! It finally works!

Tom Wickerath said:
On second thought, we need to conditionally execute this line of code:
Me![Hide input info].Visible = False

by using the Else statement:

Private Sub cmdVerify_Click()
On Error GoTo ProcError

Dim strEmployee As String
strEmployee = Replace(Me.txtEmployee, Chr(39), Chr(39) & Chr(39))

If Not IsNull(DLookup("Employee", "Census", "Employee = '" & strEmployee &
"'")) Then
MsgBox "This user has previously been added, please click Existing User" _
& "on the previous screen. Click OK to be redirected to the
previous screen."

DoCmd.Close acForm, Me.name
Else
Me![Hide input info].Visible = False
End If


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdVerify_Click..."
Resume ExitProc
End Sub




Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Tom Wickerath said:
Hi Jenny,

I don't think your criteria is correct in the DLookup statement, ie.
DLookup("Employee", "Census", "employee")

It is likely picking up the first record in your table. Try printing the
result of this expression to the Immediate Window, by adding this line of
code to the beginning of your procedure:

Debug.Print DLookup("Employee", "Census", "employee")

Run it a few times. Then view the results (Ctrl G). You will likely see the
employee name for the first record in the Immediate Window.

Your procedure should probably be something like this, assuming the name of
the text box on your form is Employee:

Private Sub cmdVerify_Click()
On Error GoTo ProcError

Dim strEmployee As String
strEmployee = Replace(Me.txtEmployee, Chr(39), Chr(39) & Chr(39))

If Not IsNull(DLookup("Employee", "Census", "Employee = '" & strEmployee &
"'")) Then
MsgBox "This user has previously been added, please click Existing User" _
& "on the previous screen. Click OK to be redirected to the
previous screen."

DoCmd.Close acForm, Me.name
End If

Me![Hide input info].Visible = False


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdVerify_Click..."
Resume ExitProc
End Sub


The Replace function is available in Access 2000 and later. If a person's
name includes an apostrophe, such as O'Malley, any domain aggregrate function
(DLookup, DCount, DMin, DMax, etc.) will choke unless you double up the
apostrophe, which is what replacing with two Chr(39) concatenated together
does. (You could also replace with '', but not ". I find Chr(39) easier to
read.

See this article on the MVPS web site for help on DLookup:
http://www.mvps.org/access/general/gen0018.htm


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Jennyrd said:
I would like my users to get an error message when they put in a duplicate
name in my database. I have the database set up so it will not accept
duplicates but I'd still like them to get a message. This is my code that is
attached to an on click:

If Not IsNull(DLookup("Employee", "Census", "employee")) Then
MsgBox "This user has previously been added, please click Existing User on
the previous screen. Click OK to be redirected to the previous screen."
DoCmd.Close
End If
Me![Hide input info].Visible = False

The question is: Why does my error show up whether the name is duplicate or
not.
Employee is the field on the table Census and employee is the combo box on
the form. Oh, and Hide input info is an opaque box - it should disappear if
the above DLookup comes up with nothing.
You all have been so much help to me recently. Thanks again for any help
you can provide! Please let me know if there is any additional information
that would be helpful as well.
-Jenny
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

J

JNana

I am having the same problem. Duplicates cannot be entered; however, there
is no error message displaying letting the user know that the entry cannot
be accepted.
 
J

JNana

From Access help, to prevent duplicates, I went to my table and on two
fields that should not contain duplicate, I changed the index to Yes (No
Duplicates). There is no event procedure. When duplicate entry is made,
there is no indication that it is duplicate; however, entry is not
obligated.
 
J

JNana

Greetings, Tom: Following is code behind ControlNum.

Private Sub ControlNum_AfterUpdate()
If Not IsNull([ControlNum]) Then
Me![ControlNum].Locked = True
End If
End Sub

Sub Form_Current()
On Error GoTo Form_Current_Err

If ChildFormIsOpen() Then FilterChildForm

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub

Joy
 
G

Guest

Hi JNana,

If you are willing to send me a compacted and zipped copy of your database,
I will take a look at it for you. You can sanitize the data, if necessary. My
e-mail address is available at the bottom of the contributor's page indicated
below. Please do not post your e-mail address (or mine) to a newsgroup reply.
Doing so will only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

JNana said:
Greetings, Tom: Following is code behind ControlNum.

Private Sub ControlNum_AfterUpdate()
If Not IsNull([ControlNum]) Then
Me![ControlNum].Locked = True
End If
End Sub

Sub Form_Current()
On Error GoTo Form_Current_Err

If ChildFormIsOpen() Then FilterChildForm

Form_Current_Exit:
Exit Sub

Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit

End Sub

Joy
Tom Wickerath said:
Have you verified that you do not have an On Error Resume Next statement
in
your code?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

duplicate error message 4
Excel VBA - counting cells by colors and names to it in two different rows. 0
unique first name and last name 7
Validation Question 4
Dlookup (AGAIN!!) 6
Access Data Project, Dlookup() and A stored Procedure 1
GoTo a record from a combo in the form 4
DLookup on Text Box 1

Top