Preventing duplicate entries

[KateB]

I have a database for patient details with one table [colp_patients]. It has
a unique ID field, and an NHSNo field (which is a text field - it needs
spaces), which is also unique to the patient. I was led to believe the same
patient could be entered many times, but have since been told that one
patient should only have one record. Therefore, each time the inputter
enters the NHS number on the form I would like it to check for previous
entries and take the inputter to that entry.

I found some code on another web site (see below) which I tried to amend but
without success. If I delete the 'str' before NHSNo it fails each time. If
I leave it in, the before update event procedure isn't stored in the
properties, or it just doesn't do anything! I hope its something really
simple but being unfamiliar with code etc can't solve it. Can anyone give me
"idiot guide" help?!

Private Sub strNHSNo_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strNHSNo.Value
stLinkCriteria = "[strNHSNo]=" & "'" & SID & " ' "

If DCount("strNHSNo", "tblColp_Patients", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
MsgBox "Warning NHS Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

 

[Sprinks]

Kate,

The code looks syntactically correct; the errors must be in your references
to the form *control* that has the value to be looked up, and the table
*field* where it is to be matched. See below.

I have a database for patient details with one table [colp_patients]. It has
a unique ID field, and an NHSNo field (which is a text field - it needs
spaces), which is also unique to the patient. I was led to believe the same
patient could be entered many times, but have since been told that one
patient should only have one record. Therefore, each time the inputter
enters the NHS number on the form I would like it to check for previous
entries and take the inputter to that entry.

I found some code on another web site (see below) which I tried to amend but
without success. If I delete the 'str' before NHSNo it fails each time. If
I leave it in, the before update event procedure isn't stored in the
properties, or it just doesn't do anything! I hope its something really
simple but being unfamiliar with code etc can't solve it. Can anyone give me
"idiot guide" help?!

Private Sub strNHSNo_BeforeUpdate(Cancel As Integer)

''''''''''''' The name of the subroutine must be the name of the form control
''''''''''''' followed by _BeforeUpdate(Cancel As Integer). Select View,
Properties
''''''''''''' and click on the control in form edit view. The name of the
control will
''''''''''''' appear in the Title Bar.

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strNHSNo.Value

''''''''''''' Use the name of the form control here

stLinkCriteria = "[strNHSNo]=" & "'" & SID & " ' "

''''''''''' Use the name of the table field here between the brackets

If DCount("strNHSNo", "tblColp_Patients", stLinkCriteria) > 0 Then

''''''''''' Table fieldname between first set of quotes

'Undo duplicate entry
Me.Undo
MsgBox "Warning NHS Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Hope that helps.
Sprinks

 

[KateB]

Both the control and the field are called NHSNo (unless I'm looking at the
wrong thing), so I think my confusion is around which (if any) should have
"str" before it. (Should I rename one?) If I use NHSNo for the control i
get a compile error and it doesn't like the Dim rsc As DAO.Recordset. I'm
using Access 2000 if that makes any difference.

Kate,

The code looks syntactically correct; the errors must be in your references
to the form *control* that has the value to be looked up, and the table
*field* where it is to be matched. See below.

I have a database for patient details with one table [colp_patients]. It has
a unique ID field, and an NHSNo field (which is a text field - it needs
spaces), which is also unique to the patient. I was led to believe the same
patient could be entered many times, but have since been told that one
patient should only have one record. Therefore, each time the inputter
enters the NHS number on the form I would like it to check for previous
entries and take the inputter to that entry.

I found some code on another web site (see below) which I tried to amend but
without success. If I delete the 'str' before NHSNo it fails each time. If
I leave it in, the before update event procedure isn't stored in the
properties, or it just doesn't do anything! I hope its something really
simple but being unfamiliar with code etc can't solve it. Can anyone give me
"idiot guide" help?!

Private Sub strNHSNo_BeforeUpdate(Cancel As Integer)

''''''''''''' The name of the subroutine must be the name of the form control
''''''''''''' followed by _BeforeUpdate(Cancel As Integer). Select View,
Properties
''''''''''''' and click on the control in form edit view. The name of the
control will
''''''''''''' appear in the Title Bar.

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strNHSNo.Value

''''''''''''' Use the name of the form control here

stLinkCriteria = "[strNHSNo]=" & "'" & SID & " ' "

''''''''''' Use the name of the table field here between the brackets

If DCount("strNHSNo", "tblColp_Patients", stLinkCriteria) > 0 Then

''''''''''' Table fieldname between first set of quotes

'Undo duplicate entry
Me.Undo
MsgBox "Warning NHS Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Hope that helps.
Sprinks

 

[Sprinks]

Kate,

Neither should have "str" before it. There's nothing magical about those
three letters; they are just a commonly used "naming convention" prefix for
string *variables*. Using naming conventions make code easier to understand,
since the variable type is apparent from the name. The code author seems to
have used this prefix in an unconventional way in a fieldname.

Naming the control by the same name as the field to which it's bound (the
default) would not cause an error, but most developers rename their controls,
following the naming convention of a three-character prefix that identifies
the type of control (txt, cbo, chk, etc.), followed by the fieldname to which
it's bound, in this case,"txtNHSNo". Do a Google on "Access Naming
Conventions" for more information.

The code below worked perfectly on a control named NHSNo bound to a field
named the same. I suspect all you need to do is add a reference for the
"Microsoft DAO x.x Object Library. "x.x" is "3.6" for my version of Access
2003; yours might be an older version. From the Visual Basic editing window,
choose Tools, References, and check the library and save.

Also, this code will generate an error if the user deletes the NHSNo and
attempts to leave the field, as the code will attempt to assign the value
Null to a string variable. Moreover, I would assume you don't want the user
to leave this field blank. The code below executes the Else clause if the
value is Null.

Hope that helps.

Sprinks

Private Sub NHSNo_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

If Not IsNull(Me.NHSNo.Value) Then

SID = Me.NHSNo.Value
stLinkCriteria = "[NHSNo]=" & "'" & SID & " ' "

If DCount("NHSNo", "NHSNo", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
MsgBox "Warning NHS Number " & _
SID & " has already been entered." & _
vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

Else
' Display message and cancel record add
MsgBox "The NHSNo is a required field. Please enter a value."
Cancel = True

End If

End Sub

Both the control and the field are called NHSNo (unless I'm looking at the
wrong thing), so I think my confusion is around which (if any) should have
"str" before it. (Should I rename one?) If I use NHSNo for the control i
get a compile error and it doesn't like the Dim rsc As DAO.Recordset. I'm
using Access 2000 if that makes any difference.

Kate,

The code looks syntactically correct; the errors must be in your references
to the form *control* that has the value to be looked up, and the table
*field* where it is to be matched. See below.

I have a database for patient details with one table [colp_patients]. It has
a unique ID field, and an NHSNo field (which is a text field - it needs
spaces), which is also unique to the patient. I was led to believe the same
patient could be entered many times, but have since been told that one
patient should only have one record. Therefore, each time the inputter
enters the NHS number on the form I would like it to check for previous
entries and take the inputter to that entry.

I found some code on another web site (see below) which I tried to amend but
without success. If I delete the 'str' before NHSNo it fails each time. If
I leave it in, the before update event procedure isn't stored in the
properties, or it just doesn't do anything! I hope its something really
simple but being unfamiliar with code etc can't solve it. Can anyone give me
"idiot guide" help?!

Private Sub strNHSNo_BeforeUpdate(Cancel As Integer)

''''''''''''' The name of the subroutine must be the name of the form control
''''''''''''' followed by _BeforeUpdate(Cancel As Integer). Select View,
Properties
''''''''''''' and click on the control in form edit view. The name of the
control will
''''''''''''' appear in the Title Bar.

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strNHSNo.Value

''''''''''''' Use the name of the form control here

stLinkCriteria = "[strNHSNo]=" & "'" & SID & " ' "

''''''''''' Use the name of the table field here between the brackets

If DCount("strNHSNo", "tblColp_Patients", stLinkCriteria) > 0 Then

''''''''''' Table fieldname between first set of quotes

'Undo duplicate entry
Me.Undo
MsgBox "Warning NHS Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Hope that helps.
Sprinks

 

[KateB]

Brilliant! Apart from one slight change it worked a treat. Many thanks.

Kate,

Neither should have "str" before it. There's nothing magical about those
three letters; they are just a commonly used "naming convention" prefix for
string *variables*. Using naming conventions make code easier to understand,
since the variable type is apparent from the name. The code author seems to
have used this prefix in an unconventional way in a fieldname.

Naming the control by the same name as the field to which it's bound (the
default) would not cause an error, but most developers rename their controls,
following the naming convention of a three-character prefix that identifies
the type of control (txt, cbo, chk, etc.), followed by the fieldname to which
it's bound, in this case,"txtNHSNo". Do a Google on "Access Naming
Conventions" for more information.

The code below worked perfectly on a control named NHSNo bound to a field
named the same. I suspect all you need to do is add a reference for the
"Microsoft DAO x.x Object Library. "x.x" is "3.6" for my version of Access
2003; yours might be an older version. From the Visual Basic editing window,
choose Tools, References, and check the library and save.

Also, this code will generate an error if the user deletes the NHSNo and
attempts to leave the field, as the code will attempt to assign the value
Null to a string variable. Moreover, I would assume you don't want the user
to leave this field blank. The code below executes the Else clause if the
value is Null.

Hope that helps.

Sprinks

Private Sub NHSNo_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

If Not IsNull(Me.NHSNo.Value) Then

SID = Me.NHSNo.Value
stLinkCriteria = "[NHSNo]=" & "'" & SID & " ' "

If DCount("NHSNo", "NHSNo", stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
MsgBox "Warning NHS Number " & _
SID & " has already been entered." & _
vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

Else
' Display message and cancel record add
MsgBox "The NHSNo is a required field. Please enter a value."
Cancel = True

End If

End Sub

Both the control and the field are called NHSNo (unless I'm looking at the
wrong thing), so I think my confusion is around which (if any) should have
"str" before it. (Should I rename one?) If I use NHSNo for the control i
get a compile error and it doesn't like the Dim rsc As DAO.Recordset. I'm
using Access 2000 if that makes any difference.

Kate,

The code looks syntactically correct; the errors must be in your references
to the form *control* that has the value to be looked up, and the table
*field* where it is to be matched. See below.

:

I have a database for patient details with one table [colp_patients]. It has
a unique ID field, and an NHSNo field (which is a text field - it needs
spaces), which is also unique to the patient. I was led to believe the same
patient could be entered many times, but have since been told that one
patient should only have one record. Therefore, each time the inputter
enters the NHS number on the form I would like it to check for previous
entries and take the inputter to that entry.

I found some code on another web site (see below) which I tried to amend but
without success. If I delete the 'str' before NHSNo it fails each time. If
I leave it in, the before update event procedure isn't stored in the
properties, or it just doesn't do anything! I hope its something really
simple but being unfamiliar with code etc can't solve it. Can anyone give me
"idiot guide" help?!

Private Sub strNHSNo_BeforeUpdate(Cancel As Integer)
''''''''''''' The name of the subroutine must be the name of the form control
''''''''''''' followed by _BeforeUpdate(Cancel As Integer). Select View,
Properties
''''''''''''' and click on the control in form edit view. The name of the
control will
''''''''''''' appear in the Title Bar.


Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.strNHSNo.Value
''''''''''''' Use the name of the form control here

stLinkCriteria = "[strNHSNo]=" & "'" & SID & " ' "
''''''''''' Use the name of the table field here between the brackets

If DCount("strNHSNo", "tblColp_Patients", stLinkCriteria) > 0 Then
''''''''''' Table fieldname between first set of quotes

'Undo duplicate entry
Me.Undo
MsgBox "Warning NHS Number " _
& SID & " has already been entered." _
& vbCr & vbCr & "You will now been taken to the record.",
vbInformation _
, "Duplicate Information"
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

Hope that helps.
Sprinks

 

[Grannash]

I have the same question as Kate, except that all the talk about 'code'
scared me off. :-0 I have no idea about entering code. Is there a "for
dummies" explanation of how I can go about specifying in that a particular
field is not to accept duplicate entires? For example, if you try to input a
duplicate entry under your primary key, you get an error message. How can I
make this happen for other fields as well?

 

[Jeff Boyce]

Open the table in design mode.

Select the field for which you wish no duplicates.

In the lower window, set the Index(ed) property to "Yes, no duplicates".

Save the change.

Be aware that if your table already has duplicates for this field, Access
won't save this change.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jhrBanker]

Sprinks:

I have the same situation as KateB had, except that I need to prevent
duplicate records based on two fields and not just one. And to complicate
matters, one of the fields is a number field [fKey], and the other is a text
field [fYR]. I've tried several ways of concatenating the fields, but am not
having any luck. The other difference is that the form control is a combobox.

Here's my code:
Private Sub cboInitiativeName_BeforeUpdate(Cancel As Integer)
Dim rKey As Long
Dim rYR As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

rKey = Me.cboInitiativeName.Value
rYR = Forms!frmForecastCriteria!txtFilterYR
stLinkCriteria = rKey & rYR

' Check SepcForecast table for duplicate fKey
If DCount("[fKey] & [fYR]", "SepcForecast", stLinkCriteria) > 0 Then
' Undo Duplicate entry
Me.Undo
' Message box warning of Duplication
MsgBox "A record for this Initiative Name " _
& "has already been entered." _
& vbCr & vbCr & "Press the ESC Key to Cancel", vbInformation _
, "Duplication Information"
End If

Set rsc = Nothing
End Sub

Any help would be greatly appreciated
Thanks,
John

 

[Jeff Boyce]

John

Instead of using code, another approach would be to set the two fields
(together) as a combined index in the table definition.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jhrBanker]

Jeff:

As I noted, one of the fields is a number field and the other is a text, so
if you mean to combine the 2 values into 1 field, I can't do that. One of
the fields is the Project Number, and the other is the forecasting date year
(a text field in yyyy format. If you mean to use both fields as the record
key, I've already done that.

John

Instead of using code, another approach would be to set the two fields
(together) as a combined index in the table definition.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sprinks:

I have the same situation as KateB had, except that I need to prevent
duplicate records based on two fields and not just one. And to complicate
matters, one of the fields is a number field [fKey], and the other is a
text
field [fYR]. I've tried several ways of concatenating the fields, but am
not
having any luck. The other difference is that the form control is a
combobox.

Here's my code:
Private Sub cboInitiativeName_BeforeUpdate(Cancel As Integer)
Dim rKey As Long
Dim rYR As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

rKey = Me.cboInitiativeName.Value
rYR = Forms!frmForecastCriteria!txtFilterYR
stLinkCriteria = rKey & rYR

' Check SepcForecast table for duplicate fKey
If DCount("[fKey] & [fYR]", "SepcForecast", stLinkCriteria) > 0 Then
' Undo Duplicate entry
Me.Undo
' Message box warning of Duplication
MsgBox "A record for this Initiative Name " _
& "has already been entered." _
& vbCr & vbCr & "Press the ESC Key to Cancel", vbInformation _
, "Duplication Information"
End If

Set rsc = Nothing
End Sub

Any help would be greatly appreciated
Thanks,
John

 

[Jeff Boyce]

John

Open the table in design view.

Click on the Indexes button.

Create a new index by giving it a name, then select your first field.
Without adding a new name, directly under the first field, select the second
field. This makes the index on the two fields together (i.e., combined).

In the Unique property, select Yes. This makes the combined two fields'
index Unique.

This should prevent the creation of a new record with a matching value for
both fields.

And you could let users select values for each field from a combo box for
each. That way they'd KNOW if the fields' values had already been entered.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff:

As I noted, one of the fields is a number field and the other is a text,
so
if you mean to combine the 2 values into 1 field, I can't do that. One of
the fields is the Project Number, and the other is the forecasting date
year
(a text field in yyyy format. If you mean to use both fields as the
record
key, I've already done that.

John

Instead of using code, another approach would be to set the two fields
(together) as a combined index in the table definition.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Sprinks:

I have the same situation as KateB had, except that I need to prevent
duplicate records based on two fields and not just one. And to
complicate
matters, one of the fields is a number field [fKey], and the other is a
text
field [fYR]. I've tried several ways of concatenating the fields, but
am
not
having any luck. The other difference is that the form control is a
combobox.

Here's my code:
Private Sub cboInitiativeName_BeforeUpdate(Cancel As Integer)
Dim rKey As Long
Dim rYR As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

rKey = Me.cboInitiativeName.Value
rYR = Forms!frmForecastCriteria!txtFilterYR
stLinkCriteria = rKey & rYR

' Check SepcForecast table for duplicate fKey
If DCount("[fKey] & [fYR]", "SepcForecast", stLinkCriteria) > 0 Then
' Undo Duplicate entry
Me.Undo
' Message box warning of Duplication
MsgBox "A record for this Initiative Name " _
& "has already been entered." _
& vbCr & vbCr & "Press the ESC Key to Cancel", vbInformation _
, "Duplication Information"
End If

Set rsc = Nothing
End Sub

Any help would be greatly appreciated
Thanks,
John