No Duplicates Code?

[Denny G.]

Access 2002. In a Customer's table, I have separate FirstName and LastName
fields. The form's Record Source is a query based on the Customer's table.
At the form or query level, I also display the first-last names concatenated.
I need to ensure, using code, that the FirstName-LastName, when
concatenated, cannot be entered into the table's parsed fields more than once
(not duplicated). How do I do this? I have code that works on the
pre-concatenated, individual fields in the Before Update property of the
FirstName and/or LastName controls of the form, but what is the code that
will work on the concatenated field itself (FullName: [FirstName]&"
"&[LastName])? I tried the same code on the concatenated field and it did
nothing. Thank-you

 

[Allen Browne]

To prevent anyone ever entering 2 people with the same name, create a unique
index on the combination of fields:

1. Open your table in design view.

2. Open the Indexes dialog (View menu).

3. On a fresh line of of the dialog, enter an index name such as:
LastNameFirstName

4. Beside this, enter the first field of the index, say Surname.

5. In the lower pane of the dialog, set the Unique property to Yes.

6. On the next line of the dialog, leave the name column blank, and enter
the other field name: FirstName.

7. Save.

If you just wanted to warn the user if they entered 2 people with the same
name, you would do that in the BeforeUpdate event of the form where you
enter data. This kind of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

If IsNull(Me.FirstName) Or IsNull(Me.Surname) Or _
(Me.FirstName = Me.FirstName.OldValue And _
Me.Surname = Me.Surname.OldValue) Then
'do nothing
Else
strWhere = "([Surname] = """ & Me.Surname & _
""") AND ([FirstName] = """ & Me.FirstName & """)"
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
strMsg = "Record " & varResult & " has the same name." & _
vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, _
"Possible duplicate") <> vbYes Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

 

[Denny G.]

Thank-you Mr. Browne. I will give 'er a go first thing today.

Denny G.

To prevent anyone ever entering 2 people with the same name, create a unique
index on the combination of fields:

1. Open your table in design view.

2. Open the Indexes dialog (View menu).

3. On a fresh line of of the dialog, enter an index name such as:
LastNameFirstName

4. Beside this, enter the first field of the index, say Surname.

5. In the lower pane of the dialog, set the Unique property to Yes.

6. On the next line of the dialog, leave the name column blank, and enter
the other field name: FirstName.

7. Save.

If you just wanted to warn the user if they entered 2 people with the same
name, you would do that in the BeforeUpdate event of the form where you
enter data. This kind of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

If IsNull(Me.FirstName) Or IsNull(Me.Surname) Or _
(Me.FirstName = Me.FirstName.OldValue And _
Me.Surname = Me.Surname.OldValue) Then
'do nothing
Else
strWhere = "([Surname] = """ & Me.Surname & _
""") AND ([FirstName] = """ & Me.FirstName & """)"
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
strMsg = "Record " & varResult & " has the same name." & _
vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, _
"Possible duplicate") <> vbYes Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Access 2002. In a Customer's table, I have separate FirstName and
LastName
fields. The form's Record Source is a query based on the Customer's
table.
At the form or query level, I also display the first-last names
concatenated.
I need to ensure, using code, that the FirstName-LastName, when
concatenated, cannot be entered into the table's parsed fields more than
once
(not duplicated). How do I do this? I have code that works on the
pre-concatenated, individual fields in the Before Update property of the
FirstName and/or LastName controls of the form, but what is the code that
will work on the concatenated field itself (FullName: [FirstName]&"
"&[LastName])? I tried the same code on the concatenated field and it did
nothing. Thank-you