If this method is in the form file with the command button on it
you don't need me. 
It should be able to reference the check boxes any way.
PS
Insert Into Results
Select d1.FirstName,D1.LastName,D2.FirstName, D2.LastName
From data1 D1, data1 D2
& one of the fours possibilities.
Both true (instr not required)
Where D1.FirstName = D1.FirstName and D1.LastName = D2.LastName
Both False
Where (D1.FirstName <> D1.Lastname) And
D1.Lastname <> D2.Lastname) and
((Instr(1,D1.Firstname,D2.Firstname) > 0)) Or (Instr(1,D2.Firstname,D1.FirstName) > 0)) And
((Instr(1,D1.Lastname,D2.Lastname) > 0)) Or (Instr(1,D2.Lastname,D1.LastName) > 0))
First True Last False
Where (D1.FirstName = D2.FirstName)
And
D1.lastName <> D2.Lastname
And
((Instr(1,D1.Lastname,D2.Lastname) > 0)) Or (Instr(1,D2.Lastname,D1.LastName) > 0))
(I'm sure you can figure out the last one)
Followed by select * From results
will be much much faster, and a lot less code
Find Similiar MS Access Records
Invalid use of key word me
Can any help?
Thanks
Private Sub cmdProcess_Click()
On Error GoTo err_end
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Dim ssq1 As String
Dim ssq2 As String
Dim ssq3 As String
Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim F As Boolean ' First Name
Dim L As Boolean 'Second Name
'save checkbox values
F = False
L = False
If Me.chkFirstName = True Then F = True
If Me.chkLastName = True Then L = True
'clear out the results table
conn.Execute "Delete * From Results"
'open recordset on data table
ssql = "Select * From Data1 Order By LastName"
rs.Open ssql, conn, adOpenKeyset, _
adLockOptimistic
Do Until rs.EOF
'build SQL to open inner recordset
ssql2 = "Select * From Data1 Where "
If F = False And L = False Then
ssql2 = ssql2 & "FirstName<>'" & _
rs.Fields("FirstName") & _
"' And LastName<>'" & _
rs.Fields("LastName") & "'"
End If
If F = False And L = True Then
ssql2 = ssql2 & "FirstName<>'" & _
rs.Fields("FirstName") & _
"' And LastName='" & _
rs.Fields("LastName") & "'"
End If
If F = True And L = False Then
ssql2 = ssql2 & "FirstName='" & _
rs.Fields("FirstName") & _
"' And LastName<>'" & _
rs.Fields("LastName") & "'"
End If
If F = True And L = True Then
ssql2 = ssql2 & "FirstName='" & _
rs.Fields("FirstName") & _
"' And LastName='" & _
rs.Fields("LastName") & "'"
End If
'open inner recordset
rs2.Open ssql2, conn, adOpenKeyset, _
adLockOptimistic
Do Until rs2.EOF
'test if value from one recordset field
'is in the same field of the other recordset
If (InStr(1, rs.Fields("FirstName"), _
rs2.Fields("FirstName") > 0) Or _
(InStr(1, rs2.Fields("FirstName"), _
rs.Fields("FirstName"))) > 0) And _
((InStr(1, rs.Fields("LastName"), _
rs2.Fields("LastName")) > 0) Or _
(InStr(1, rs2.Fields("LastName"), _
rs.Fields("LastName"))) > 0) Then
'if values match on at least one field,
'write a result record in Results
ssql3 = "Insert Into Results Values ("
ssql3 = ssql3 & "'" & _
rs.Fields("FirstName") & "', "
ssql3 = ssql3 & "'" & _
rs.Fields("LastName") & "', "
ssql3 = ssql3 & "'" & _
rs2.Fields("FirstName") & "', "
ssql3 = ssql3 & "'" & _
rs2.Fields("LastName") & "')"
conn.Execute ssql3
End If
rs2.MoveNext
Loop
rs2.Close
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set rs2 = Nothing
conn.Close
MsgBox "done"
Exit Sub
err_end:
MsgBox Err.Description
End Sub
This conversation is currently closed to new comments.