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