Question

Locked

Find Similiar MS Access Records

By shieldsco ·
When I try to execute the following VB I get a complie error:
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.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

highlights

by john.a.wills In reply to Find Similiar MS Access R ...

Which "me" is highlighted?

Collapse -

If this method is in the form file with the command button on it

by Tony Hopkinson In reply to Find Similiar MS Access R ...

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

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums