duplicate record error

[Jennyrd]

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

 

[Tom Wickerath]

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
__________________________________________

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

 

[Tom Wickerath]

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
__________________________________________

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
__________________________________________

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

 

[Jennyrd]

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

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
__________________________________________

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
__________________________________________

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

 

[Tom Wickerath]

Hi Jenny,

Glad I could be of help. After re-reading my first reply today, I now
realize that this part was in error:

"....assuming the name of the text box on your form is Employee:"

This should have read:

"....assuming the name of the text box on your form is txtEmployee:"


Good Luck with your project!


Tom Wickerath
Microsoft Access MVP

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