Looking for duplicates

[Tony Williams]

I have a form which is used to collect data on individuals. So I have two
controls txtfirstname and txtsurname. Before the record is saved I want code
to check whether the combination of txtfirstname and txtsurname already
exists in another record. If it does I want a form to open,
frmduplicatenames, which shows all the duplicates. I have attempted to
create a pretty basic VBA code that illustrates what I want to do but as I'm
not a VBA expert it doesn't work but I think it shows where I want to be.
Here is my attempt at the code which I have put in the BeforeUpdate event of
the control txtsurname:

Private Sub txtsurname_BeforeUpdate(Cancel As Integer)
Dim strname As String
Dim strform As String

strname = "[txtfirstname] & [txtsurname]"
strform = "frmduplicatenames"
If strname = Me.txtfirstname & Me.txtsurname Then
DoCmd.OpenForm strform, acNormal, , Me.txtsurname =
[tbl.individuals].[txtsurname]
End If
End Sub

Can anyone help me with this please?

 

[Allen Browne]

Okay, there's several things here, Tony.

1. You need to use the BeforeUpate event of the *form* (or of both text
boxes), so you catch either change.

2. If the names have not changed, the existing record is not a duplicate of
itself. Test the OldValue.

3. Presumably you only want to run the test if both surname and first name
have been filled in.

4. If the duplicates form is already open, the WhereCondition won't work, so
test and close.

You'll probably end up with something along the lines of this aircode:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Const strcForm = "frmduplicatenames"

If ((Me.txtFirstname = Me.FirstName.OldValue) _
And (Me.txtSurname = Me.txtSurname.OldValue)) _
Or IsNull(Me.txtFirstName) Or IsNull(Me.txtSurname) Then
'do nothing
Else
strWhere = "(txtSurname = """ & Me.txtSurname & _
""") AND (txtFirstName = """ & Me.txtFirstName & "")"
If Not IsNull(DLookup("txtSurname", "Table1", strWhere)) Then
If CurrentProject.AllForms(strcForm).IsLoaded) Then
DoCmd.Close acForm, strcForm
End If
DoCmd.OpenForm strcForm, WhereCondition:=strWhere
End If
End If
End Sub

 

[Tony Williams]

Thanks Allen I'll play around with that and come back to you if I may?
Regards
Tony

Okay, there's several things here, Tony.

1. You need to use the BeforeUpate event of the *form* (or of both text
boxes), so you catch either change.

2. If the names have not changed, the existing record is not a duplicate
of itself. Test the OldValue.

3. Presumably you only want to run the test if both surname and first name
have been filled in.

4. If the duplicates form is already open, the WhereCondition won't work,
so test and close.

You'll probably end up with something along the lines of this aircode:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Const strcForm = "frmduplicatenames"

If ((Me.txtFirstname = Me.FirstName.OldValue) _
And (Me.txtSurname = Me.txtSurname.OldValue)) _
Or IsNull(Me.txtFirstName) Or IsNull(Me.txtSurname) Then
'do nothing
Else
strWhere = "(txtSurname = """ & Me.txtSurname & _
""") AND (txtFirstName = """ & Me.txtFirstName & "")"
If Not IsNull(DLookup("txtSurname", "Table1", strWhere)) Then
If CurrentProject.AllForms(strcForm).IsLoaded) Then
DoCmd.Close acForm, strcForm
End If
DoCmd.OpenForm strcForm, WhereCondition:=strWhere
End If
End If
End Sub

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

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

I have a form which is used to collect data on individuals. So I have two
controls txtfirstname and txtsurname. Before the record is saved I want
code to check whether the combination of txtfirstname and txtsurname
already exists in another record. If it does I want a form to open,
frmduplicatenames, which shows all the duplicates. I have attempted to
create a pretty basic VBA code that illustrates what I want to do but as
I'm not a VBA expert it doesn't work but I think it shows where I want to
be. Here is my attempt at the code which I have put in the BeforeUpdate
event of the control txtsurname:

Private Sub txtsurname_BeforeUpdate(Cancel As Integer)
Dim strname As String
Dim strform As String

strname = "[txtfirstname] & [txtsurname]"
strform = "frmduplicatenames"
If strname = Me.txtfirstname & Me.txtsurname Then
DoCmd.OpenForm strform, acNormal, , Me.txtsurname =
[tbl.individuals].[txtsurname]
End If
End Sub

Can anyone help me with this please?

 

[Tony Williams]

Thanks again Allen here is my amended code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Const strcForm = "frmduplicatenames"

If ((Me.txtfirstname = Me.txtfirstname.OldValue) _
And (Me.txtsurname = Me.txtsurname.OldValue)) _
Or IsNull(Me.txtfirstname) Or IsNull(Me.txtsurname) Then
'do nothing
MsgBox "OK"
Else
strWhere = "(txtSurname = """ & Me.txtsurname & """) AND
(txtfirstName = """ & Me.txtfirstname & """)"
If Not IsNull(DLookup("txtSurname", "tblindividual", strWhere)) Then
If CurrentProject.AllForms(strcForm).IsLoaded Then
DoCmd.Close acForm, strcForm
End If
DoCmd.OpenForm strcForm, WhereCondition:=strWhere
End If
End If

End Sub

You see I've put Msgbox "OK" as the do nothing just to get the code to work
but how do I "do nothing" Is that Cancel=True?
Thanks again
Tony

Okay, there's several things here, Tony.

1. You need to use the BeforeUpate event of the *form* (or of both text
boxes), so you catch either change.

2. If the names have not changed, the existing record is not a duplicate
of itself. Test the OldValue.

3. Presumably you only want to run the test if both surname and first name
have been filled in.

4. If the duplicates form is already open, the WhereCondition won't work,
so test and close.

You'll probably end up with something along the lines of this aircode:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Const strcForm = "frmduplicatenames"

If ((Me.txtFirstname = Me.FirstName.OldValue) _
And (Me.txtSurname = Me.txtSurname.OldValue)) _
Or IsNull(Me.txtFirstName) Or IsNull(Me.txtSurname) Then
'do nothing
Else
strWhere = "(txtSurname = """ & Me.txtSurname & _
""") AND (txtFirstName = """ & Me.txtFirstName & "")"
If Not IsNull(DLookup("txtSurname", "Table1", strWhere)) Then
If CurrentProject.AllForms(strcForm).IsLoaded) Then
DoCmd.Close acForm, strcForm
End If
DoCmd.OpenForm strcForm, WhereCondition:=strWhere
End If
End If
End Sub

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

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

I have a form which is used to collect data on individuals. So I have two
controls txtfirstname and txtsurname. Before the record is saved I want
code to check whether the combination of txtfirstname and txtsurname
already exists in another record. If it does I want a form to open,
frmduplicatenames, which shows all the duplicates. I have attempted to
create a pretty basic VBA code that illustrates what I want to do but as
I'm not a VBA expert it doesn't work but I think it shows where I want to
be. Here is my attempt at the code which I have put in the BeforeUpdate
event of the control txtsurname:

Private Sub txtsurname_BeforeUpdate(Cancel As Integer)
Dim strname As String
Dim strform As String

strname = "[txtfirstname] & [txtsurname]"
strform = "frmduplicatenames"
If strname = Me.txtfirstname & Me.txtsurname Then
DoCmd.OpenForm strform, acNormal, , Me.txtsurname =
[tbl.individuals].[txtsurname]
End If
End Sub

Can anyone help me with this please?

 

[Allen Browne]

Nothing is needed in the Do Nothing case.
(It's just a comment, to say no code is missing there.)

Immediately below the OpenForm, you may want:
Cancel = True

Not sure if you want to open the form in dialog mode.

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

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

Thanks again Allen here is my amended code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Const strcForm = "frmduplicatenames"

If ((Me.txtfirstname = Me.txtfirstname.OldValue) _
And (Me.txtsurname = Me.txtsurname.OldValue)) _
Or IsNull(Me.txtfirstname) Or IsNull(Me.txtsurname) Then
'do nothing
MsgBox "OK"
Else
strWhere = "(txtSurname = """ & Me.txtsurname & """) AND
(txtfirstName = """ & Me.txtfirstname & """)"
If Not IsNull(DLookup("txtSurname", "tblindividual", strWhere))
Then
If CurrentProject.AllForms(strcForm).IsLoaded Then
DoCmd.Close acForm, strcForm
End If
DoCmd.OpenForm strcForm, WhereCondition:=strWhere
End If
End If

End Sub

You see I've put Msgbox "OK" as the do nothing just to get the code to
work but how do I "do nothing" Is that Cancel=True?
Thanks again
Tony

Okay, there's several things here, Tony.

1. You need to use the BeforeUpate event of the *form* (or of both text
boxes), so you catch either change.

2. If the names have not changed, the existing record is not a duplicate
of itself. Test the OldValue.

3. Presumably you only want to run the test if both surname and first
name have been filled in.

4. If the duplicates form is already open, the WhereCondition won't work,
so test and close.

You'll probably end up with something along the lines of this aircode:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Const strcForm = "frmduplicatenames"

If ((Me.txtFirstname = Me.FirstName.OldValue) _
And (Me.txtSurname = Me.txtSurname.OldValue)) _
Or IsNull(Me.txtFirstName) Or IsNull(Me.txtSurname) Then
'do nothing
Else
strWhere = "(txtSurname = """ & Me.txtSurname & _
""") AND (txtFirstName = """ & Me.txtFirstName & "")"
If Not IsNull(DLookup("txtSurname", "Table1", strWhere)) Then
If CurrentProject.AllForms(strcForm).IsLoaded) Then
DoCmd.Close acForm, strcForm
End If
DoCmd.OpenForm strcForm, WhereCondition:=strWhere
End If
End If
End Sub

I have a form which is used to collect data on individuals. So I have two
controls txtfirstname and txtsurname. Before the record is saved I want
code to check whether the combination of txtfirstname and txtsurname
already exists in another record. If it does I want a form to open,
frmduplicatenames, which shows all the duplicates. I have attempted to
create a pretty basic VBA code that illustrates what I want to do but as
I'm not a VBA expert it doesn't work but I think it shows where I want to
be. Here is my attempt at the code which I have put in the BeforeUpdate
event of the control txtsurname:

Private Sub txtsurname_BeforeUpdate(Cancel As Integer)
Dim strname As String
Dim strform As String

strname = "[txtfirstname] & [txtsurname]"
strform = "frmduplicatenames"
If strname = Me.txtfirstname & Me.txtsurname Then
DoCmd.OpenForm strform, acNormal, , Me.txtsurname =
[tbl.individuals].[txtsurname]
End If
End Sub

 

[Tony Williams]

Thanks Allen. I'd like the form to open in Datasheet mode, in case there are
more than one and somehow put a command button on the form to open a
selected record. I thought of creating an unbound form with a subform in
datasheet mode showing the existing records and then have a command button
that when clicked opens another form showing the full details of the
selected record for the user to view. I've done something like this before.
Do you think that's the best way to develop this?
Would appreciate your views.
Regards
Tony

Nothing is needed in the Do Nothing case.
(It's just a comment, to say no code is missing there.)

Immediately below the OpenForm, you may want:
Cancel = True

Not sure if you want to open the form in dialog mode.

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

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

Thanks again Allen here is my amended code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Const strcForm = "frmduplicatenames"

If ((Me.txtfirstname = Me.txtfirstname.OldValue) _
And (Me.txtsurname = Me.txtsurname.OldValue)) _
Or IsNull(Me.txtfirstname) Or IsNull(Me.txtsurname) Then
'do nothing
MsgBox "OK"
Else
strWhere = "(txtSurname = """ & Me.txtsurname & """) AND
(txtfirstName = """ & Me.txtfirstname & """)"
If Not IsNull(DLookup("txtSurname", "tblindividual", strWhere))
Then
If CurrentProject.AllForms(strcForm).IsLoaded Then
DoCmd.Close acForm, strcForm
End If
DoCmd.OpenForm strcForm, WhereCondition:=strWhere
End If
End If

End Sub

You see I've put Msgbox "OK" as the do nothing just to get the code to
work but how do I "do nothing" Is that Cancel=True?
Thanks again
Tony

Okay, there's several things here, Tony.

1. You need to use the BeforeUpate event of the *form* (or of both text
boxes), so you catch either change.

2. If the names have not changed, the existing record is not a duplicate
of itself. Test the OldValue.

3. Presumably you only want to run the test if both surname and first
name have been filled in.

4. If the duplicates form is already open, the WhereCondition won't
work, so test and close.

You'll probably end up with something along the lines of this aircode:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Const strcForm = "frmduplicatenames"

If ((Me.txtFirstname = Me.FirstName.OldValue) _
And (Me.txtSurname = Me.txtSurname.OldValue)) _
Or IsNull(Me.txtFirstName) Or IsNull(Me.txtSurname) Then
'do nothing
Else
strWhere = "(txtSurname = """ & Me.txtSurname & _
""") AND (txtFirstName = """ & Me.txtFirstName & "")"
If Not IsNull(DLookup("txtSurname", "Table1", strWhere)) Then
If CurrentProject.AllForms(strcForm).IsLoaded) Then
DoCmd.Close acForm, strcForm
End If
DoCmd.OpenForm strcForm, WhereCondition:=strWhere
End If
End If
End Sub

I have a form which is used to collect data on individuals. So I have
two controls txtfirstname and txtsurname. Before the record is saved I
want code to check whether the combination of txtfirstname and
txtsurname already exists in another record. If it does I want a form to
open, frmduplicatenames, which shows all the duplicates. I have
attempted to create a pretty basic VBA code that illustrates what I want
to do but as I'm not a VBA expert it doesn't work but I think it shows
where I want to be. Here is my attempt at the code which I have put in
the BeforeUpdate event of the control txtsurname:

Private Sub txtsurname_BeforeUpdate(Cancel As Integer)
Dim strname As String
Dim strform As String

strname = "[txtfirstname] & [txtsurname]"
strform = "frmduplicatenames"
If strname = Me.txtfirstname & Me.txtsurname Then
DoCmd.OpenForm strform, acNormal, , Me.txtsurname =
[tbl.individuals].[txtsurname]
End If
End Sub

 

[Allen Browne]

Whatever suits you, Tony.

You can open your form in datasheet view. Dialog mode too, if you wish:
DoCmd.OpenForm strcForm,acFormDS, WindowMode:=acDialog, _
WhereCondition:=strWhere

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

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

Thanks Allen. I'd like the form to open in Datasheet mode, in case there
are more than one and somehow put a command button on the form to open a
selected record. I thought of creating an unbound form with a subform in
datasheet mode showing the existing records and then have a command button
that when clicked opens another form showing the full details of the
selected record for the user to view. I've done something like this
before. Do you think that's the best way to develop this?
Would appreciate your views.
Regards
Tony

Nothing is needed in the Do Nothing case.
(It's just a comment, to say no code is missing there.)

Immediately below the OpenForm, you may want:
Cancel = True

Not sure if you want to open the form in dialog mode.

Thanks again Allen here is my amended code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Const strcForm = "frmduplicatenames"

If ((Me.txtfirstname = Me.txtfirstname.OldValue) _
And (Me.txtsurname = Me.txtsurname.OldValue)) _
Or IsNull(Me.txtfirstname) Or IsNull(Me.txtsurname) Then
'do nothing
MsgBox "OK"
Else
strWhere = "(txtSurname = """ & Me.txtsurname & """) AND
(txtfirstName = """ & Me.txtfirstname & """)"
If Not IsNull(DLookup("txtSurname", "tblindividual", strWhere))
Then
If CurrentProject.AllForms(strcForm).IsLoaded Then
DoCmd.Close acForm, strcForm
End If
DoCmd.OpenForm strcForm, WhereCondition:=strWhere
End If
End If

End Sub

You see I've put Msgbox "OK" as the do nothing just to get the code to
work but how do I "do nothing" Is that Cancel=True?
Thanks again
Tony
Okay, there's several things here, Tony.

1. You need to use the BeforeUpate event of the *form* (or of both text
boxes), so you catch either change.

2. If the names have not changed, the existing record is not a
duplicate of itself. Test the OldValue.

3. Presumably you only want to run the test if both surname and first
name have been filled in.

4. If the duplicates form is already open, the WhereCondition won't
work, so test and close.

You'll probably end up with something along the lines of this aircode:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Const strcForm = "frmduplicatenames"

If ((Me.txtFirstname = Me.FirstName.OldValue) _
And (Me.txtSurname = Me.txtSurname.OldValue)) _
Or IsNull(Me.txtFirstName) Or IsNull(Me.txtSurname) Then
'do nothing
Else
strWhere = "(txtSurname = """ & Me.txtSurname & _
""") AND (txtFirstName = """ & Me.txtFirstName & "")"
If Not IsNull(DLookup("txtSurname", "Table1", strWhere)) Then
If CurrentProject.AllForms(strcForm).IsLoaded) Then
DoCmd.Close acForm, strcForm
End If
DoCmd.OpenForm strcForm, WhereCondition:=strWhere
End If
End If
End Sub

I have a form which is used to collect data on individuals. So I have
two controls txtfirstname and txtsurname. Before the record is saved I
want code to check whether the combination of txtfirstname and
txtsurname already exists in another record. If it does I want a form
to open, frmduplicatenames, which shows all the duplicates. I have
attempted to create a pretty basic VBA code that illustrates what I
want to do but as I'm not a VBA expert it doesn't work but I think it
shows where I want to be. Here is my attempt at the code which I have
put in the BeforeUpdate event of the control txtsurname:

Private Sub txtsurname_BeforeUpdate(Cancel As Integer)
Dim strname As String
Dim strform As String

strname = "[txtfirstname] & [txtsurname]"
strform = "frmduplicatenames"
If strname = Me.txtfirstname & Me.txtsurname Then
DoCmd.OpenForm strform, acNormal, , Me.txtsurname =
[tbl.individuals].[txtsurname]
End If
End Sub

 

[Tony Williams]

Just realised I can't use an unbound form because there's no data on it to
filter the results which appear in the subform. When I open the main form
the sub form shows all the records not just my duplicates. can you put a
command button on a datasheet form?
Regards
Tony

Whatever suits you, Tony.

You can open your form in datasheet view. Dialog mode too, if you wish:
DoCmd.OpenForm strcForm,acFormDS, WindowMode:=acDialog, _
WhereCondition:=strWhere

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

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

Thanks Allen. I'd like the form to open in Datasheet mode, in case there
are more than one and somehow put a command button on the form to open a
selected record. I thought of creating an unbound form with a subform in
datasheet mode showing the existing records and then have a command
button that when clicked opens another form showing the full details of
the selected record for the user to view. I've done something like this
before. Do you think that's the best way to develop this?
Would appreciate your views.
Regards
Tony

Nothing is needed in the Do Nothing case.
(It's just a comment, to say no code is missing there.)

Immediately below the OpenForm, you may want:
Cancel = True

Not sure if you want to open the form in dialog mode.

Thanks again Allen here is my amended code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Const strcForm = "frmduplicatenames"

If ((Me.txtfirstname = Me.txtfirstname.OldValue) _
And (Me.txtsurname = Me.txtsurname.OldValue)) _
Or IsNull(Me.txtfirstname) Or IsNull(Me.txtsurname) Then
'do nothing
MsgBox "OK"
Else
strWhere = "(txtSurname = """ & Me.txtsurname & """) AND
(txtfirstName = """ & Me.txtfirstname & """)"
If Not IsNull(DLookup("txtSurname", "tblindividual", strWhere))
Then
If CurrentProject.AllForms(strcForm).IsLoaded Then
DoCmd.Close acForm, strcForm
End If
DoCmd.OpenForm strcForm, WhereCondition:=strWhere
End If
End If

End Sub

You see I've put Msgbox "OK" as the do nothing just to get the code to
work but how do I "do nothing" Is that Cancel=True?
Thanks again
Tony
Okay, there's several things here, Tony.

1. You need to use the BeforeUpate event of the *form* (or of both
text boxes), so you catch either change.

2. If the names have not changed, the existing record is not a
duplicate of itself. Test the OldValue.

3. Presumably you only want to run the test if both surname and first
name have been filled in.

4. If the duplicates form is already open, the WhereCondition won't
work, so test and close.

You'll probably end up with something along the lines of this aircode:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Const strcForm = "frmduplicatenames"

If ((Me.txtFirstname = Me.FirstName.OldValue) _
And (Me.txtSurname = Me.txtSurname.OldValue)) _
Or IsNull(Me.txtFirstName) Or IsNull(Me.txtSurname) Then
'do nothing
Else
strWhere = "(txtSurname = """ & Me.txtSurname & _
""") AND (txtFirstName = """ & Me.txtFirstName & "")"
If Not IsNull(DLookup("txtSurname", "Table1", strWhere)) Then
If CurrentProject.AllForms(strcForm).IsLoaded) Then
DoCmd.Close acForm, strcForm
End If
DoCmd.OpenForm strcForm, WhereCondition:=strWhere
End If
End If
End Sub

I have a form which is used to collect data on individuals. So I have
two controls txtfirstname and txtsurname. Before the record is saved I
want code to check whether the combination of txtfirstname and
txtsurname already exists in another record. If it does I want a form
to open, frmduplicatenames, which shows all the duplicates. I have
attempted to create a pretty basic VBA code that illustrates what I
want to do but as I'm not a VBA expert it doesn't work but I think it
shows where I want to be. Here is my attempt at the code which I have
put in the BeforeUpdate event of the control txtsurname:

Private Sub txtsurname_BeforeUpdate(Cancel As Integer)
Dim strname As String
Dim strform As String

strname = "[txtfirstname] & [txtsurname]"
strform = "frmduplicatenames"
If strname = Me.txtfirstname & Me.txtsurname Then
DoCmd.OpenForm strform, acNormal, , Me.txtsurname =
[tbl.individuals].[txtsurname]
End If
End Sub

 

[Allen Browne]

can you put a > command button on a datasheet form?

Use Continuous Form view.

 

[Tony Williams]

Allen, thanks obvious when you know how! However if I leave the record
selector property as yet what code would I put behind the command button to
open the selected record?
Thanks for sticking with me on this
Regards
Tony

 

[Allen Browne]

Didn't follow the bit about the Record Selector (which appears to the left
of the form.)

A form has a current record anyway (whether you show the Record Selector or
not.) The values will refer to the current record.

 

[Tony Williams]

Sorry Allen getting carried away with the hint of success!

When the form opens in Continuous Forms view I could get say 3 records
showing with the same names. If one looks like it might be the same person I
want to select it and then click on a command button that opens another
form, (frmregistration) which has a lot more detail on it from which we can
identify whether or not we have the same person. What I'm looking for now is
help with how do I code the onclick event of the command button to open my
other form with the record of the one I have selected in the continuous form
view?
Hope that makes sense.
Regards
Tony

 

[Allen Browne]

The command button wizard with create that for you.

 

[Tony Williams]

Thanks Allen all done, not a bad day's work thanks to you. have a great
festive season, whatever you're doing.
Regards
Tony