combining two or more fields into one field

[david d]

being somewhat new to Access and Vba I need the answer to a question. I want
to combine three fields into one. as FirstName+MI+LastName. I want to do
this after FirstName, MI, and LastName have entered. The reason for this it
to be able to check the created field for duplicates. Any suggestions

 

[John W. Vinson]

being somewhat new to Access and Vba I need the answer to a question. I want
to combine three fields into one. as FirstName+MI+LastName. I want to do
this after FirstName, MI, and LastName have entered. The reason for this it
to be able to check the created field for duplicates. Any suggestions

It is not NECESSARY to combine the three fields into one (you can check for
duplicates on three fields, or on ten fields).

It is not SUFFICIENT to check the three fields for duplicates; I once worked
with Dr. Lawrence David Wise and his colleague Dr. Lawrence David Wise. Names
are not unique.

And it is not APPROPRIATE to store the concatenated field redundantly in a
second table.

Instead, I'd use VBA code in a Form's BeforeUpdate event to *warn* the user
that they may be entering a duplicate - not prevent it, because it might be
legitimately a second person with the same name. You could use code like:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset ' define a Recordset object
Dim strSQL As String
Dim iAns as Integer
Set rs = Me.RecordsetClone ' get the form's Recordset
strSQL = "[FirstName] = """ & Me!txtFirstName & """
If Not IsNull(Me!txtMI) Then
strSQL = strSQL & " AND [MI] = """ & Me!txtMI & """"
End If
strSQL = strSQL & " AND [LastName] = """ & Me!txtLastName & """"
' strSQL will be something like
' [FirstName] = "Jane" AND [MI] = "A." AND [LastName] = "O'Neill"
rs.FindFirst strSQL ' find the first record with this name
If Not rs.NoMatch Then
iAns = MsgBox("Possible duplicate name. Go to it? Click Yes to do so," _
& " No to add new record, Cancel to erase this entry and start over", _
vbYesNoCancel)
Select Case iAns ' find out which button the user clicked
Case vbYes
Me.Undo ' erase the current record
Cancel = True ' cancel the update
Me.Bookmark = rs.Bookmark ' go to the found record
Case vbCancel
Me.Undo ' erase
Cancel = True
Case vbNo
' do nothing
End Select
End If
End Sub


John W. Vinson [MVP]

 

[david d]

John, Thanks for the suggestion, The is one problem, I keep getting a
compilor error on the first Dim statement.
Any Ideal ?

 

[John W. Vinson]

John, Thanks for the suggestion, The is one problem, I keep getting a
compilor error on the first Dim statement.

Open the VBA editor and select Tools... References from the menu. Scroll down
and check the

Microsoft DAO x.xx

reference library, highest version number if you have more than one.

If you're not using ADO, you may want to also uncheck the Microsoft ActiveX
Data Objects reference; both DAO and ADO have Recordset objects, but they are
*different* objects.

John W. Vinson [MVP]

 

[david d]

John, Your fix to the Dim statement error worked but now I have another
error. "Run time error 2465 {Program} can't find the field 'txtFIRSTNAME'
referred to in your expression"
I have included a copy of the code
Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset 'define a recordset object
Dim strSQL As String
Dim iAns As Integer
Set rs = Me.RecordsetClone
strSQL = "[FIRSTNAME] =""" & Me!txtFIRSTNAME & """"
If Not IsNull(Me!txtMI) Then
strSQL = strSQL & " AND[MI]=""" & Me!txtMI & """"
End If
strSQL = strSQL & " AND[LASTNAME] = """ & Me!LASTNAME & """"
rs.FindFirst strSQL
If Not rs.NoMatch Then
iAns = MsgBox("Possible duplicate name. Go to it? Click Yes to do so, " &
"No to add new record, Cancel to erase and start over", vbYesNoCancel)
Select Case iAns
Case vbYes
Me.Undo
Cancel = True
Me.Bookmark = rs.Bookmark
Case vbCancel
Me.Undo
Cancel = True
Case vbNo
End Select
End If





End Sub

 

[John W. Vinson]

John, Your fix to the Dim statement error worked but now I have another
error. "Run time error 2465 {Program} can't find the field 'txtFIRSTNAME'
referred to in your expression"

I can't see your form, so I don't know what your control names are. I
habitually rename textboxes - Access names them the same as the name of the
field to which they are bound, but this convention can be confusing.

Replace txtFIRSTNAME by the actual Name property of your textbox (perhaps just
FIRSTNAME).

John W. Vinson [MVP]

 

[david d]

John,
thanks that worked perfectly

I can't see your form, so I don't know what your control names are. I
habitually rename textboxes - Access names them the same as the name of the
field to which they are bound, but this convention can be confusing.

Replace txtFIRSTNAME by the actual Name property of your textbox (perhaps just
FIRSTNAME).

John W. Vinson [MVP]